Populate field from data in another table

[Stephen Beasley]

Hi, and thanks in advance, this seems so simple I am embarrased to admit I
can't figure it out...Oh well, here is what I have (fields ommited for saving
space) And feel free to correct me if I am going about this the wrong way are
there is an easier method:
2 Tables - Assets and Service
1 Form - Service Entry (will be used on-site via wireless laptop)

Asset Table Fields - DB_ID , AssetTag, MachineName - All fields DO NOT allow
duplicates.
Asset Table Data Row 1 - autonumber,12345,Machine1
Service Table Fields - ServiceID, Date, AssetTag (lookup from Assets),
MachineName, ServicePerformed
Service Entry Form Fields - Same as Service Table Fields

What I am trying to achieve is the technician opens form, enters Date>
Drop-down (or other method) to select AssetTag(12345)> upon selecting
AssetTag, MachineName field is populated with the related field in the Assets
Table (Machine1)>Tech enters service performed and saves data.
The part I cannot figure out is how to "auto-populate" the MachineName in
the Service Entry Form. Instructions for populating the MachineName in the
Services table would be greatly appreciated as well!
THANKS!
Steve

 

[Klatuu]

Actually, you should not even have the machine name field in the service
table. The only fields you really need are:

Service Table Fields - ServiceID, Date, AssetTag (lookup from Assets),
ServicePerformed

That is storing duplicate data. Since the AssetTag relates the service
table record to a record in the Asset table, that creates a many to one
relationship between the Asset table and the Service table.

When you need to present the machine name on a form or report, use a query
with the two tables joined on the Asset Tag field.

 

[Stephen Beasley]

Thanks Klatuu,
Does this hold true if the MachineName changes occasionally, but the
AssetTag never changes. I would like to keep the historical data intact and a
query would always pull the current MachineName, Right? Sorry, I did not
mention this earlier, but I was trying to keep it as brief as possible.

 

[Klatuu]

Well, that does make a difference. But, you original post has something I
don't quite understand. I think you said the service form is based on the
service table and you are using a combo box to populate fields on the form
and the combo is based on the asset table. Is that correct?
In that case, you could include the machine name field from the asset table
in the combo's row source and use the combo's After Update event to populate
the machine name control using the column the machine name is in.

If my assumptions and understanding are incorrect, please let me know and
include the row source query of the comb box.

 

[Stephen Beasley]

No, I did not mention a Combo box directly, just that the Service Form had
the same fields as the service table. However, the combo box was what I was
having problems with. The database is in 2003, but I did a test in 2007 by
running the form wizard based on the service table and selecting all fields
except MachineName which was selected from the assets table, and this is now
behaving correctly. So when I get back to the 2003 machine, I can update the
form field properties to match my test (hopefully?). Now I'm left with the
"After Update" (services.machinename, correct?), in 2007 it gives me 3
choices (macro,expression,code) when i click the elipse, and I haven't a clue
how to proceed??

 

[Klatuu]

Select Code
Then you will use code similar to this to populate the control bound to the
machine name field:

Me.txtMachineNameField = Me.MyCombo.Column(?)

Where ? is the column the machine name is in.
Note that combo column indexes are zero based. That is, the first column is
(0), the second is (1), etc.

 

[StormWater1]

Hi Folks,
Is there a way to do this task "Without" using VB? I am not VB literate, so
when I tried this code, I got an error. Game over.
I really need help on this, my form is 95% complete but for this function.

 

[Klatuu]

You could use a macro in the after update event with a SetValue action.