Hi there
In Access , If i click find button, i want to display the list of records based on SNo.
Now if i click records on the list ,the selected item of record should be filled in the form.
i have the following field in the form
sno,
ename,
portno
package
phone number
any one can help me, please.
by
thanx in advance
yasi
I have a long list of ID's that I need to find in a database in order to get their additional information. Will I be creating a query for that?
View 2 Replies View RelatedCan anyone point me to where I might get help setting the above up. I have a list of unique enties in a query, and have set up a global variable so that on my form when I choose an option from the list only data relating to that option is pulled back from a number of tables/queries and output on a report. I would like to be able to get the data for my full list of options. I'm afraid I'm lost here. I don't know of any way of setting my variable to 'ALL'. Is this available in Access 2000? I have Access 2002 at home but I need to work on a PC that only has Access 2000, so I have kept it as a 2000 database. All help appreciated.
rgs
Ginny
Hi all:)
I am looking for some advise, I work for a small service company and we our trying to computerize our service jobs through the use of access 2007, We have completed 80% of the database but are having a few problems completing, for example the search function on some forms etc, I have searched the web for help and have posted the problem on this forum a number of times but I am still unable to get it to work myself as I am new to access , the problem is I need the database completed asap before we take on a bigger contract and I am running out of time, so I was wondering if anyone knew of anyone / or any company out there that could have a look at the search form, I would obviously pay for it, I have tried to approach a few companies but they will not take on such a small job, they only want to take on the development of the entire database?
Anyone know of anyone?
Jackie :)
I'm searching through some text fields like the following:
Ann12 #1
Ann12 #2
Ann12 #3
Ann1 #12
Ann2 #14
ect...
I'm doing a like query
Like "*#*2*"
However, the # is the keyword for Date, so what do I use instead?
Hi
I hope someone can help with this as I can't seem to get this query to work.
I am trying to use a query to search for the highest value in a column but based upon a specific criteria.
i.e. in the column will be ABC1234, ABC1235, ABC1236 and BBC1234 and I want to get the highes 'ABC' value
I looked on the forum and found this code
Dim strSQL As String
Dim MyValue As DataType 'Replace the proper datatype of field
Dim rs As ADODB.Recordset, Cn As ADODB.Connection
Set rs = New ADODB.Recordset
Set Cn = New ADODB.Connection
Set Cn = CurrentProject.Connection
strSQL = "SELECT tableName.ColumnName FROM tableName ORDER BY tableName.ColumnName DESC"
rs.Open strSQL, Cn, adOpenStatic, adLockReadOnly
MyValue = rs!ColumnName
rs.Close
Set rs = Nothing
I've modified the query to this
strSQL = "SELECT tableName.ColumnName FROM tableName WHERE tableName.ColumnName Like 'ABC*' ORDER BY tableName.ColumnName DESC"
This doesn't bring anything back but I was expecting 'ABC1236'
I'm new to using 'Like' so this could be the problem.
Your help is much appreciated.
Greetings all,
It's been a while since I've had to turn to you for help. I'm usually able to find what I need in searching through previous posts, but no luck this time around.
I am trying to write a query criteria that will return
BAC-5, BAC-8 And BAC-12
I can get the -5 and -8 with:
Like "BAC-[58]"
But I can't figure out how to get the -12...
The actual part numbers are quite a bit longer and there are hundreds where I only need specific dash numbers returned.
I'm sure this one will be simple, I just couldn't seem to figure out how to search for it.
Thanks in advance!
Hi guys
I have a DB for insurance permits.
tblSubtractors
ID
NAME
ADDRESS
PHONE
tblPolicy
ID
SubbyID
POLICYID
Insurer
PolicyNumber
ExpiryDate
tblPolicyTypes
ID
Name
Most subcontractors will have a policy number for each type of policy in tblPolicy. But is there a way I can run a query to show each subby that doesnt have one of the policies? As an example, the query would show me that ABCcontractors doesnt have Marine Transit insurance. XYZContractors doesnt have Public Liability, etc
I know how to show all the policies that have expired, but I'm looking to identify which ones dont have a policy at all
:confused:
Hello,
I have this code that I use to update a record:
Code:<%Dim idid = session("loginID")Dim rsSaveSet rsSave = Server.CreateObject("ADODB.RecordSet")rsSave.open "log", conn, adOpenDynamic, adLockOptimistic, adCmdTablersSave.find "id=" & idDim Datumlogout, Uurlogout, loggedoutOKrsSave("Datumlogout") = datersSave("Uurlogout") = timersSave ("LoggedoutOK") = ("true")rsSave.updatersSave.closeset rsSave=Nothing%>
Can someone help me please, I would like to have the code changed to:
When the ID not excists, that there is a new record written.
Thanks in advance!
I have a customer concerns database that contains the dates for when the concerns were reported and tyhe dates for when the concerns were resolved. I am trying to make a query that finds the average of how long it takes for the concerns to be resolved. How can I do this?
View 1 Replies View RelatedNow this is not like me, posing a question, but I thought its about time I codes the app correctly.
My app opens up any given spreadsheet. The first task is to establish the last row used in the spreadsheet. Normally you would loop until there is nothing in a cell in column A, whatever.
However the way the spreadsheets are provided there may be pleanty of blank rows. I have tried using the Edit GoTo Special LastCell and converting it to a macro
But when I run this VB it does not like it for some reason.
I am sure someone out there has come up with a solution by now.
CodeMaster::cool:
I have a table, with 4 records in it. 2 records have the "date" field for 01/11/2005, and 2 dates are for 02/11/2005.
Is there a way to just query the "current" date? Weather it be in a pop-up box once the query is selected, asking for the date? or is there a restricted word within access2003 that lets you find the current date?
I would essentially like a command button within a form to allow the user hit teh button, and it will automatically find those records within the DB with the current date(set to system clock) and export it to excel(i have this portion, the exporting to excel complete)
Any help would be greatly appreciated.
Connor
ok ihopefully i wont make this too complicated to under stand.
i ahev a form which will populate a form from a query. the query is pulling info out a table included int hti sinfo is the date when it was entered. in the criterai of the query i hve added this code
Between [Forms]![Boxes Reviewed]![txtFromDate1] And [Forms]![Boxes Reviewed]![txtToDate1]
whcih means that the date must be between the two differnt dates that must be entered on the form.
the problem is, is that a value can be entered more that once with different dates. i woul dwant the report just to shouw the most recent date. i considered doing a query which requested all max dates from the table and then having a query calling forma query. but i do not know enough on how to get just the most recent date.
so if i wrote a query which held
box num, shelf num, resp_person, and date. what would i have to put int he criteria to only bring back the entery witht he most recent date.
sorry if i have rambled and look forward to your help
lots of customers. lots of orders. i need to query for the first order of each customer only. thoughts?
View 1 Replies View RelatedHi
I apologise if this is really basic - I am learning as i go along. I have designed a database. Most of the info is stored in the clientinfotbl. On the main form, I would have provided an option for users to search for a record based on its ID number and then pull up various forms accordingly. I am using the "find and replace" option to do this. My first question is, is there a better way? This feels a bit clunky!
Second question is, once I have entered a new record in the "newclientfrm" and go back to the mainfrm and search for that record, it cannot be found (once I have closed db and gone back in, it can then find it). Is there a way round this?
Any help would be hugely appreciated :)
I would like to make a value equal to another value in another form, but it keeps telling me that it can't find the Form.
newID = Forms![frmLogIn]![UserID]
Error message is 'Access can't find the form 'frmLogIn' referred to in a macro expression or Visual Basic code'.
I have rechecked the spelling and caps on the form name 5 times so that's not it :(
James
Here's a real stumper... I have a form with a subform based on the account number in the main form. This form has been running without a hitch for maybe two years! Now, while updating an unrelated form in the same database, I test the new additions. I get an error message that the main form cannot find the subform, and it goes on to display a blank white box instead! Here's what I have already tried:
1. Checked the name of the subform. It's correct, just like it's been for 2 years.
2. Running compact/repair in case of corruption
3. Deleting, then re-placing the subform on the Main Form.
One other wierdism - after getting the white box where the subform belongs, and after being told that the [subform name] can't be found, I can right-click on the white box, select the "Form" option, and the subform appears! To me, that means that the "can't find" error message is a misnomer, but I'm at a loss as to where to look from here.
I guess what I'm asking is if anyone has seen this behavior of a form "not finding" its subform, yet it can be forced to display it with a popup menu? Also, any experiences with this or any related information would be appreciated.
Thanks-
Scott
I need to create a function that will return the last day of a month, regardless of which month it is and, in the case of February, which year it is. (Gilbert & Sullivan got it right - the powers that be played a cruel joke on us with leap years!) I could hard-code in the months into my code, but there's got to be a way to do it without typing in everything manually...right?
View 1 Replies View RelatedIs there a way to find all my old posts except looking at all 381 pages?
View 4 Replies View RelatedHi guys, need a bit of help with my Database. I have a Table with has ever player to play for my football club (1700 records in total), now i have another table called games (which has a record for every season played for each player, and is linked to the first table using the relationships and a field called ID. Make sense so far?
Well i then use a query to combine and calculate totals of certain fields and then i can use the query to show every single statistic possible on ASP pages. But when viewing my query in Access only 1699 records show up. So my thinkin is that either two records are identical (which is impossible because every p;layer has an ID and they cant be duplicates) OR a player has ZERO games in all years (in other words the player has no records related to it in the second table called games).
My question is, instead of searching 1700 records manually to find which one is there any way i can find which player would have NO records from the related table joint to it?
Im looking to display the 4 lowest figuers obviously Min is the lowest but how would you dind the 2nd 3rd and 4th Min or is not possible?
View 14 Replies View RelatedI have a table with assignments if employees to teams.
PositionsID(autowert),EmployeeID, timefrom, timeto, TeamNo
3, 123, 13:30, 16:45, 4
4, 123, 17:00, 19:00, 7
5, 432, 17:00, 20:00, 8
6, 987, 17:00, 19:00, 9
7, 987, 13:30, 16:45, 5
....
Some of the employees can be assigned to more than one team, the assignement is not chronicaly, meaning an earlier timefrom can be assigned later (see Employee 987)
As a resultlist I need following:
123, 13:30, 19:00, 4
432, 17:00, 20:00, 8
987, 13:30, 19:00, 5
I can get out MIN(timefrom) and MAX(timeto) per EmployeeID but not the first teamNo, which is the first team he starts his duty!!
In my access form I provide the user a list of locations from various countries in a listbox . But the list is too long so I provide him a combobox for selecting a country. Selecting the country should update the listbox showing only the locations in that specific country.
So my SELECT from the listbox must cover the unselected state and show all entries and when a country is selected it must narrow the selection.
I tried to get this happen with the following SELECT statement containing a variable. Choosing a country in the Combobox results in a change of the variable and in a requery. This works after the first country is selected and for each country change, but the initial list is empty.
VBA in the loadform
'Application.TempVars.Add "varcountryselect", "*"
SELECT in the listbox "lstlocationsperproject"
SELECT tbllocations.locationID, tbllocations.country, tbllocations.localstreet, tbllocations.localcity FROM tbllocations WHERE ((tbllocations.country) Like [TempVar]![varcountryselect]);
VBA in the combobox
Application.TempVars("varcountryselect") = [Form]![kombcountryselect].Column(0)
Me.lstlocationsperproject.Requery
The values in [kombcountryselect].Column(0) are texts like "SPAIN", "MEXICO", etc.
Any hints, how I have to use the * for getting the complete list on the initial view ?
Hi All a newbie here so any help will be appreciated,
sorry for the long post but trying to give you all the information you might need.
I wrote a basic access database for my Church to aid in a paperwork audit for a charity food drop which we do monthly to give free food to the needy.
But each month it gets harder to find out who was in line first so I thought with all your help we may be able to randomize the names each month in a different order as to avoid confusion and also avoid people waiting in line as they turn up at 5am and we don't start until 9am.
So if this will work in access they can all come for 9am
I don't mind creating a new database and adding the additional information, if that's what it would take.
My Background I have created basic databases from scratch not using wizards, But I don't know much about code or how to implement it so any help in where code goes it would be very much appreciated.
Database details (Microsoft Access 2002 version)
Table Name = details
Field name = ID (auto-generated)
Field name = FirstName (text)
Field name = Surname (text)
If possible it would be nice to keep a record of the randomized lists (in the database somewhere ?) each month in case anyone wants to see it or disputes the lists, where I can just create a report to show the details.
There will be approximately 90 to 125 names.
Thank you in advance for all your help in this matter
Britgent
I have created my tables and form and am now trying to run reports to organize the data. I have figured out how to group the individuals by group and treatment, but can't figure out how exclude individuals from the final list if they are already in another. As background, some of the individuals are eventually excluded from the experiment, though I keep the initial data. When the exclude individual checkbox is checked ("True" on the report) I do not want the individual to be listed in the "False" list, even though there are entries for that individual when that checkbox wasn't checked (when it was "True"). This is so I can get an idea of the current totals in each group.
Implant Period
>Treatment
>>Exclude individual?
>>>Individual ID
I feel like this should be a fairly simple task, but I cannot figure it out... Maybe there is coding to exclude individuals from the "False" list if they are already listed in the "True" list?
Allen Browne's "Use a multi-select list box to filter a report" solution, in particularly with two multi-select list boxes? The code works fine for me for either box so long as I code it for one box alone. Combining the two into one code results in a type mismatch error. I'm trying to use the code to pass the contents of both multi-select boxes as Where conditions to a report. Both boxes are based on number fields. To try to isolate the problem, I've removed Allen's setDescription and OpenArgs conditions. We're unfortunately still on Access 2003 as the company desires to squeeze every dime by using until end-of-life next year.
Code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the list box.
Dim varItem As Variant 'Selected items
[Code] .....