default value in a combo box from a query

[tryingtolearn]

Hi all,
New to the board and access (2000)

I have been tasked w/ creating a database to schedule and track incoming cases for my job (Dental Lab)

This entire thing is new to me but have developed most of it through reading this forum so thanks for that!
I still have a long way to go...

Here is what I am stuck on -
I have a table of technicians
here you can add the first name, last name, and department (The department is coming from another table called department)

These are working fine,

Now I have a table for the doctors to fill out a form to initiate a new case,
In this table I have a field called techassigned1 which pulls from a query of the technician table where the deptmant is set to plaster bench.

This works fine - I get a dropdown box with the technicians name that is assigned to the plaster bench (This name changes periodically)

But All I want is for that 1 name to be set as a default value - I need it in the table so I know who started the work on the case
I dont want the doctor to have to choose this name

I read in the forum and used
Me!techassign1.DefaultValue = Me!techassign1.ItemData(0)
in the forms ONOpen event

But this is what happens
The form just displays #Name? in the dropdown box but the correct name is under it if I click the arrow
But if I right click and look at the property sheet the name is in the default value property but it still doesnt update the techassign1 field in the table.

Is there a way to have this automatically add the technician from the query to the table? without haveing to select it?

Any help is greatly appreciated -
If I am not making sense plese let me know and I will try to explain it better.

 

[Melaz29]

Upload

If you upload your db I will have a look.

 

[tryingtolearn]

Thanks Melaz29
Here is a slimmed down version (Mainly because the original has a bunch of junk since I am learning as I go but didnt want to get rid of anything just in case I forgot something along the way) but this is doing exactly as I described -

If you open the form named case
look at the field that says
Your case is assigned to:
-If you click the dropdown arrow the name frm the query is there (The tech that is on the plaster bench from the department)
If you right click that field and look at the default value property the name is there as well

But it isnt selected upon loading the fore therefor not being entered as the tech assigned first to the case.

Thanks for taking the time - Id love to hear any suggestions that anyone has.

Well trying to add the db??? Doesnt seem to be here?

 

[tryingtolearn]

Hope this works... sorry

 

[Melaz29]

Change Default Value in the data properties of the form.

Delete your "on open" event and change the default read "John Franks" (include the inverted commas), works fine

Not entirely sure what you are doing with the techassign1 drop box, will there be more than one person appearing in the future?

 

[tryingtolearn]

Well thats the problem, this name will change on a weekly basis.
If you look at the tech form there is a department block.
Every so often 1 of the technicians department will be changed to plaster bench.
So the form for a Dr to initiate a new case will have the name that is associated with the plaster bench on it -
Something along the lines of
Your case is being initiated through - Name Here - Plese direct all questions or special instructions to them (With an email)

So as the department section of the tech table changes that name changes but Id like it done dynamically.

This name also needs to be added to the case table so it starts the technician tracking for each step as the case goes through the lab.

Hope that explains it better.
I somehow need the name of the plaster bench technician to be on the form and in the table and change as the person changes.

Sounds confusing to me.

But on the other hand, why does it show up in the properties value but not in the form with the way I have it set up now??

 

[tryingtolearn]

Well after alot more reading I am thinking I am going about this all wrong.
Queries are not meant to add info to a table thats what the forms are for
But when the name changes regularly is there a way w/ access to do this dynamically or do I need to change the default value every time the name changes?
Seems to me that there would be a way but...

 

[tryingtolearn]

Solved!

Here is what I ended up doing just in case someone else is going through it also.
I got rid of everything listed above (The on load event for the form etc...)
Changed the field in the table from a combo or list box to a text box
On the form I added that text box
Then in the text box default value (From properties) I added
=DLookUp("queryfield","Queryname")
(Replacing queryfield with your queries field that is holding the info you want to use and replacing Queryname with the name of your query)

Nice and simple - worked like a charm.

- Melaz29 - I do appreciate you taking the time and making suggestions. Thank you...