Populate field from data in another table
in [Getting Started]
|
From: Stephen Beasley on 19 Jun 2008 09:54 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
From: Klatuu on 19 Jun 2008 11:13 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. -- Dave Hargis, Microsoft Access MVP "Stephen Beasley" wrote: > 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
From: Stephen Beasley on 19 Jun 2008 12:15 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" wrote: > 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. > -- > Dave Hargis, Microsoft Access MVP > > > "Stephen Beasley" wrote: > > > 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
From: Klatuu on 19 Jun 2008 12:25 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. -- Dave Hargis, Microsoft Access MVP "Stephen Beasley" wrote: > 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" wrote: > > > 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. > > -- > > Dave Hargis, Microsoft Access MVP > > > > > > "Stephen Beasley" wrote: > > > > > 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
From: Stephen Beasley on 19 Jun 2008 13:54
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" wrote: > 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. > -- > Dave Hargis, Microsoft Access MVP > > > "Stephen Beasley" wrote: > > > 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" wrote: > > > > > 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. > > > -- > > > Dave Hargis, Microsoft Access MVP > > > > > > > > > "Stephen Beasley" wrote: > > > > > > > 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 |