Combo box gets values from one table and saves it to another field in another table

[SPRTRMP]

Hello all;

I am sure this is an easy one, but i am new to access and can't seem to find an answer to my exact question, and it is about to drive me crazy:

I have a form with a combo box ("Combo376"). This Combo box gets it's values from field "LotNo" on table "Acetaminophen". The combo box populates just fine, but once the correct row is selected, I need to save the chosen value (now visible in the combo box on the form) to field "Acetaminophen" on table "240825". Can't seem to come up with the correct code/expression for the afterUpdate event (or wherever else it should go).

Any help is appreciated!!

 

[pbaldy]

The most common setup would be for the form that combo is on to be bound to the target table, and the combo bound to the appropriate field within. Does that sound feasible?

 

[SPRTRMP]

I am not 100% sure of what you mean (sorry): the form is based on a query, and the combo wont allow me to set the control source to the field that I am trying to get the data into.

 

[pbaldy]

Is the query read-only? I'm not sure I've seen an instance where it won't let you set the control source to a field in the data. Can you post the db?

 

[SPRTRMP]

I tried: something about a "security token being missing"???

 

[pbaldy]

That field is not currently included in the record source of the form (that query). If I add that field to the query I can then bind the combo to the field, and it works as I assume it should.

As a side note, it is generally not a good idea to have multiple tables with similar information. In your case, rather than a table for each medication, I'd probably have a single table, with an additional field for the medication. A field might look like:

Acetaminophen...123456...11-Feb-11

That way when a new medication is used, you don't have to redesign the whole application around it. Your combo row source would simply include a criteria that limited it to Acetaminophen. Similarly, rather than have a field in the 24085 table for each medication/, I might have a related table that a record for each medication. Again, that would let a new medication just flow through the application without design changes being required. I don't know enough about your specific needs to say anything with authority, but those are general concepts. You might want to research "normalization", but here's one link:

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

On a side note, you should generally avoid spaces and symbols in your names. They are not worth the bother in the long run.