ComboBox and DLookup 1

[Elvis72]

OK, last week I posted a question on how to populate a subform when selecting one criteria.

I received two replies one for using a ComboBox and one for DLookup.

The ComboBox is not working as when I select Worker ID 2 all I see is 2, the rest of the fields I don't see unless I hit the Drop Down.

Now on top of that when I select Worker ID 2 from the Drop Down all the records in the list turn to 2.

So, I can't add records nor see the ones that exist?

On the DLookup I honestly am very lost.

I have the subform which has these fields

Worker ID Last Name First Name Cell Phone Email Hourly Wage Role

I need to be able to type a number into Worker ID and have the other fields populated from the query.

No matter what I try I get a #Error Message.

Worker ID is currently an AutoNumber, which I believe may be part of my problem.

Can I not have Worker ID setup to type a number into and then have text fields that pull the information based on that number?

Thanks so much for your time!~

 

[Remou]

Set the link child field of the subform to WorkerID and the link master field to the name of the control that you want to type the WorkerID into. Set the recordsource of the subform to the relevant table.

 

[BobChesh]

Folks,
On the same thread trying to use the Dlookup function. I have viewed FAQ 705-4978, but can't make it work.

I have a form where there are two combo boxes which select two "Reasons for Failure", combo1 being a Primary and the combo2 being a Secondary. I want to select the Primary cause in the first combo and then in the second combo only to select those Secondary causes that are related to the Primary. I then want to hold this data in a data table for later analysis. I have two tables Primary and Secondary. The Primary holds just the Primary reason, while the secondary holds both Primary and Secondary. So, for instance, in the Primary there is "Customer Caused" whilst in the Secondary there is "Customer Caused" (Primary field) and then Secondary Cause field reasons like "Not In", "No longer Required", "Cancelled Earlier" etc etc.

I am using the Dlookup function =DLookUp("[Secondary]","[SecondaryFailureReasons2]","[Primary]=forms!Frm_Data![PrimaryReason]") in the Row Source line of combo2, but all I get is a blank. I can make it work using a query, but since this is going to used by several users, I felt that Dlookup is more dynamic and does not lock the data.

Can anybody help me out here. I know its a long post, but I am trying to make this as clear as possible.

 

[Remou]

Hi BobChesh
In Tek-Tips, we generally start a new thread for quite similar topics because it leads to a better range of answers and the original poster is not troubled by unwanted email.

 

[Elvis72]

OK...

Well here is where I am today....

I came up with this:

=DLookup("[First Name]", "Resume Table", "[Worker ID] = Form![Worker ID]")

which worked...until I saved it...now I get the #Error again...Dang it!~

Sooo...can't see the issue here...

 

[Remou]

Did you try my suggestion?

 

[Elvis72]

OK...I found it...grin

Now here is my issue...the Main Form is linked to the Subform by Job Number/Proposal Number.

But the field I want to populate and have the rest filled is the Worker ID?

 

[Remou]

You can link by more than one field and / or control. Is Job Number a suitable field for linking?

 

[Elvis72]

Well, there isn't a Worker ID field on the Main Form as it can have many to its one Job Number/Proposal Number field.

 

[Remou]

You do not need a worker id field. Any control will do: txtEnterWorkerIDHere, for example.

 

[Remou]

... or if you still have a listbox or combo with a suitable rowsource, you can use that.

 

[Elvis72]

OK...this is what I have done.

I changed the Control Source for:

First Name to First Name
Last Name to Last Name
Cell Phone to Cell Phone
Email Address to Email Address

And put this statement under the default value which populates the correct data.

=DLookup("[Last Name]", " Plant Services Edit Entry form Staff Query ", "[Worker ID] = Form![Worker ID]")

When I change the Worker ID Field to a Text Box and change the statements to:

=DLookup("[Email Address]", " Plant Services Edit Entry form Staff Query ", "[Text147] = Form![Text147]")

It still comes up with the correct information in the fields.

What I can't do is type a number into the Worker ID box and have a new record populate?

 

[Remou]

Please attach a zipped copy of a db with the problem form and some sample data.

 

[Elvis72]

If you enter this into your browser:

http://home.comcast.net/~akrtx/CopyofPlantServicesDatabase.zip

and hit enter you should get a save option.

thanks!~

 

[Remou]

You have several forms. Which is it? Resume Personnel Data Entry Form?

 

[Elvis72]

Plant Services Edit Entry Form

 

[Remou]

There seems to be a number of problems with this form. You have linked the subform on a field that does not exist in the subform.

You may wish to read

http://www.r937.com/relational.html.

You may wish to look at the Northwind Sample.mdb, available with all versions of Access.

It is best not to use spaces in the names of controls and tables, they will drive you nuts evetually.

I have added a combo box to the form but the upload here no longer works. Have you an email address? Please mess it up a little to prevent robots reading it.

 

[Elvis72]

Remou-

All the subforms are linked vie Job Number/Proposal Number which is on all the subforms but just hidden?

Thank you so much for your help the address is

akr _tx @ ya hoo . com

 

[Remou]

When the form opens, it asks for a Job Number/Proposal Number which implies that it is missing somewhere or other. I have removed the Link Child Master information from the employee subform, so you can see the combo working. The whole form needs a little tidying in the background

 

[Elvis72]

Remou...it asks for it because it wants you to call up a specific record to edit.

If you type in 23456 it brings up that record.