Combo box & Search Function

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I'm trying to help someone who inherited a database that really needs to be
re-worked, but she has to get a database released within a few days. Neither
one of us has used Access in recently, so any help you can give would be
great. I've been searching for a response, but can't seem to get any non-VBA
solutions to work. We plan to attend training, but are in emergency mode
now. Two question.

Question #1 Display Employee Number when Employee Name is selected

Table:
C3 Employee Master.Name (form control Name is Employee Name & is a combo box)
C3 Employee Master.Agent_ID (form Name is ID# & is a textbox)

Form Name = "Name" & is a look-up from the C3 Employee Master.name table
Form Name = "ID #" & is a look-up from the same table [Agent_ID]

I tried to accomplish this by entering this in the Control Source for the
Employee Number field but I'm wondering if I need an AfterEvent on the
Employee Name fields:
=DLookUp("[Agent ID]","[C3 Employee Master]","[Name] = Forms![Employee Name]")

Question #2 Add a Search function.
If any one of the following 3 fields are entered in the form, display the
associated records from one of 3 tables:

Tables:
C3 Employee Master.Name
QA Master.Manager
Call Form Table: Values input on form are written to this table

Form Name = "Name" & is a look-up from the C3 Employee Master.name table
Form Name = "QA Name" & is a look-up from the QA Master.Manager table
Form Name = Ref No & is a user input
 
To display a name and store an ID, use a combo box with a rowsource type of
Table/Query and a SQL Statement like:

Select EmployeeID, EmployeeName From [C3 Employee Master]

A few problems I see in your database:

1. Name is a reserved word. Change everything to something like FullName, or
EmployeeName
2. If you use spaces in table or field names, you MUST surround them with
Square Brackets (see my sample above)
3. Avoid using "#" but, if necessary like ID#, make sure you surrounf it
with Square Brackets [ID#] in every query or in code.

Forn the search function, create a query and use a parameter like:

[Enter QA Name]

in the criteria box for the field you are searching. Notice the square
brackets again? When they surround a non-existant value, like:

Enter QA Name

Access will prompt for the value, and return only those records. To be able
to enter a partial value for the search (like Arv to find Arvin) use the
following:

Like [Enter QA Name] & "*"

which will find "Arv" and anything following it.
 
Chris said:
I'm trying to help someone who inherited a database that really needs to
be
re-worked, but she has to get a database released within a few days.
Neither
one of us has used Access in recently, so any help you can give would be
great. I've been searching for a response, but can't seem to get any
non-VBA
solutions to work. We plan to attend training, but are in emergency mode
now. Two question.

Question #1 Display Employee Number when Employee Name is selected

Table:
C3 Employee Master.Name (form control Name is Employee Name & is a combo
box)
C3 Employee Master.Agent_ID (form Name is ID# & is a textbox)

Form Name = "Name" & is a look-up from the C3 Employee Master.name table
Form Name = "ID #" & is a look-up from the same table [Agent_ID]

I tried to accomplish this by entering this in the Control Source for the
Employee Number field but I'm wondering if I need an AfterEvent on the
Employee Name fields:
=DLookUp("[Agent ID]","[C3 Employee Master]","[Name] = Forms![Employee
Name]")

Question #2 Add a Search function.
If any one of the following 3 fields are entered in the form, display the
associated records from one of 3 tables:

Tables:
C3 Employee Master.Name
QA Master.Manager
Call Form Table: Values input on form are written to this table

Form Name = "Name" & is a look-up from the C3 Employee Master.name table
Form Name = "QA Name" & is a look-up from the QA Master.Manager table
Form Name = Ref No & is a user input
Click to expand...
 
RE: Question #1
First of all, do not use reserved words as field names. NAME is a property
of every object in VBA. This will cause flakey performance.

You don't specifiy the data type of NAME. This affects the syntax of the
DLookup.
you have:
=DLookUp("[Agent ID]","[C3 Employee Master]","[Name] = Forms![Employee Name]")
The where argument must evaluate to a string, but you have the whole thing
in quotes.


If Name is a LONG try this:
=DLookUp("[Agent ID]","[C3 Employee Master]","[Name] = " & Forms![Employee
Name])

If NAME is text (i.e., a string) try this:
=DLookUp("[Agent ID]","[C3 Employee Master]","[Name] = '" & Forms![Employee
Name] & "'")
Note the single quotes. If your data type is string, then the Where must
have single quotes around the string.

You may need to requery the text box containing the DLookup in the
AfterUPdate of the combo box.

Question 2:
I'd need more info about your application; can't respond here.
 
Back
Top