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]

<sheepish grin>

 

[Elvis72]

Just got your email.

I see the combobox...but there is a Object Variable or With Block Variable not set Run-time error 91.

 

[Remou]

What happens when you open:

Plant Services Edit Entry form Staff Query subform

 

[Elvis72]

OK...if I select bugs bunny I brings up bugs bunny...but all the records show bugs bunny in the combobox? And the fields don't change.

 

[Remou]

This is the databse I just sent to you zipped in a file CPSD.zip? and you are viewing a form called
Plant Services Edit Entry form Staff Query subform

Yes No?

 

[Elvis72]

I don't think I was beign clear enough.

When I open the subform in the database you sent me the first record is Test if I select Magilla Gorilla in the combobox it changes that record to Magilla Gorilla.

AND it also changes every other records combobox to Magilla Gorilla.

 

[Remou]

What did you want to happen? Did you not wish to pick a worker by ID and to see details for that worker?

 

[Elvis72]

Exactly...BUT I need to see the already existing workers assigned to that project also.

 

[Remou]

What you have is a continuous form that shows the detail for all the workers associated with the project (once the link fields are put back). The sample I sent shows how a combo box can be used to find a worker by ID. There is a little code attached to the after update event that does this. You can change the form to single form and use the combobox to find each worker associated with the project. You can limit the workers shown in the combobox to only those associated with the project by adding a Where statement that references the Job Number/Proposal Number to the RowSource:

SELECT [Worker ID], [Last Name] & ", " & [First Name] FROM [Plant Services Edit Entry form Staff Query]
WHERE  [Job Number/Proposal Number]=Forms![Plant Services Edit Entry Form]![Job Number/Proposal Number]

 

[Elvis72]

Remou -

This brings me back to my original issue....

If I need to add a worker to the list from the form what do I need to do to make this happen?

If I have worker 1 and worker 2 assigned to the project they show up...but what if I need to add worker 3?

This does not give me the option to add someone to the list?

 

[Elvis72]

OK...I have finally figured it out...dang it~!

I changed the link in my query that fed the subform to read where the table that had the job number/proposal number was linked to the resume table that only contained the worker id.

Then I used the statement:

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

In the Default Value under properties.

The only issue I have at the moment is an #Error in the email field of the blank record form before you add a new worker id...any suggestions?

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

 

[Remou]

You have two things going on here, one, to find a worked and two, to add a worker. The combobox can be used to find a worker. You should be able to add a worker by creating a new record. You can use the >* (new record button) or you can use the wizard to create a button. You could also change the combo box to include all workers, and if that worker is not assigned to the project, you could add them with some code (set the project number), but I imagine the best bet is a new record.

It is likely that you will run into problems unless you have a project table, a worker table, and a project/worker junction table.

 

[Elvis72]

Well, I feel alot better...I had a space in the Email Address Default Value statement that was throwing the #Error...duh!~