Table update

[Data69]

I have a problem I can not solve. I have a form that provides with mfg name and serial number. I have a mfg table that lists mfg name and mfg ID#. I have a Serial number table that lists serial # and Mfg ID#. When I update the Inventory table how can i get the Mfg ID to write to the inventory table based upon mfg name.

 

[vbajock]

There are number of ways,but calling an update query from an event on your form seems like what you are looking to do.

 

[Data69]

Here is my update statement.

.Open "Inventory", CurConn, , , adCmdTableDirect
.AddNew
![serial_no_] = (Me!serial_no_)
![Mfg_id] = Select mfg.id From manufacturer where mfg = me!mfg

.Update
.Close

I figured a SQL select statement would work but I get an expression error when I run the code.

 

[vbajock]

![Mfg_id] =Select mfg.id From manufacturer where mfg = me!mfg

Will not work, for a variety of reasons.

Try
![Mfg_id]=Dlookup("[id]","manufacturer","[mfg] = '"+me!mfg+&quot