AUTOMATIC FIELD UPDATE IN A TABLE

[BOONEY]

Assuming the folling tables and field names

Table Name = "Lot"
Field 1 = LotNumber
Field 2 = Weight

Primary Key is Field 1 "LotNumber"

This table is populated with lot numbers and weights.

Table Name = "LotEntry"
Field 1 = LotNumber
Field 2 = Weight

This is a data entry table or form.

I am trying to have the "weight" field in the table "LotEntry" populate automatically once a lot number is entered in the lot entry table. I do not want a drop down box, just an automatic update of the weight field.

Please help

 

[dianemarie]

Hello, are you talking about an Access table? Or a Word table?

 

[Dreamboat]

How do we know the weight of that Lot number?

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[BOONEY]

dianemarie, we are working with 2 Access tables. Dreamboat, the weight of the lot is in the "Lot" Table which has lot number and weights. While entering a lot number in the 2nd table, I want the weights for the corresponding lot to populate the weight field in the 2nd table, as I explained above.

thanks,

 

[Dreamboat]

If you have the weight in another table, you don't NEED it in this table. That's why Access is Access and you're talking about an Excel VLOOKUP kinda thing. However, you can make it show up in your form or query by including the two tables under either of those. More?

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX

 

[BOONEY]

I am looking at this from the perspective of data entry into a table or form.

A simpler example would be where you have an Item Master table with an Item # and Item Description Field. This table is fully populated with all items and descriptions.

In a second table, the user is keying in the item # and would like the description for that item it populate the descritpion field automatically based on the item number being entered. I am trying to accomplish this in Access 97 and not in exel using a vlookup.

I am looking for the specific mechanics to accomplish this. Either in a form or in the table itself.

I dont think I can be much clearer that this.

Thanks for your help regarding this.

Mike

 

[Zion7]

Hi Booney, I'm not familiar with Access '97, but with Access 2000. 1st suggestion; look into "Auto Look-up query". An explanation is given in Access "Help". Secondly, on the "query forum", the same question was posed. I don't know if the answers suggested have worked yet, not to mention "I" didn't understand it enough to try it.
I'm having the exact same concern, an automatic transfer/entry of a record/records from one table to another. Auto lookup query should work but my Queries in general aren't working so I can't verify that either. Good luck, let me know!

 

[tonyflavell]

Hi Guys and Gals

In your LotNo field set your "After Update" event to:

Forms!FormName!Weight = DLookup("Weight", "Lot", "[LotNo] = " & Forms!FormName!LotNo)

 

[Dreamboat]

The whole point here is that Boonie is trying to do in a table something that should be done in a form. Doing it in a table is defeating the whole purpose of the relational capability of a database. In the ideal database, you don't view your data in the tables. You view it in queries, forms, and reports.

Anne Troy

http://www.TheOfficeExperts.com

Word and Excel Macros
Coming soon: wX