DLookup Question

[illy2k]

I have a form and a subform. The field "Status" is filled in on the subform for a certain "ItemNumber", and I need the status that was entered there to be sent to the Status field of 2 tables. one Being the "Item File" the other being "Item Subform File". Can this be done with the DLookup function, if so how? Can it be done at all? All I need is it to lookup to see what item number's status was changed and then change the status accordingly in two tables.

 

[illy2k]

The deal

What I need is this: I have a subform that needs to send a single value to two tables. I need the form to lookup the Item Number of the item that was entered on that line and change the Status field in two tables by looking up the item number and changing the value to the one that was selected in the form.

 

[illy2k]

Better explaination

Ok, I think I need to give a better explaination. I want on the after update property of a form, for a function or something, to match the ItemNumber that was just entered in the form, to the same ItemNumber in a table and for it to change a the Status field to what was entered on the form. It is a lookup to another table in order to change one field. Does anyone know how to do this?

 

[WayneRyan]

illy,

Dim dbs As Database
Dim sql As String

Set dbs = CurrentDb
sql = "Update YourOtherTable " & _
      "Set    StatusField = '" & Me.MainFormStatus & "' " & _
      "Where  YourKey = " & Me.YourFormKey
dbs.Execute(sql)

This assumes that your status field is text and your key field
is a number.

Wayne