Updating Table Via VBA Help (1 Viewer)

Hello

New to this forum

Have a question

Have a Database containing 2 tables (for simplicity)

table 1
Has all inventory information, location etc this is the main table where the users will enter info. On this table there is a dropdown list that is populated by a query of another table
______________

Table 2
Contains a tracking number and a Yes/no field.

the yes no field denotes if the item number is used

all possible item numbers will be entered and only used once no duplicates
______________

The users will enter all the info on table 1 via a form the form will have a list box on it that runs a query to look on table 2 to see what tracking numbers are NOT used and place these on the form for the user to select one to apply to the item he just entered.

I have the list box working with the query and the user can select a tracking number and it populates to the proper field on the form and thus into the proper place in table 1

I have it set up via an update button to also open the table 2 and find the record the user has select but what i need is the following

What I need help on is :

I need when the user clicks the update button the tracking number selected by the user is taken out (the yes/no field in table 2 changes to a YES ) to denote that it is used and thus will not appear on the query again.

I need some kind of command to update the yes no field

I have aready managed when the update button is clicked the table 2 opens and the tracking number the user selected from the list box found and selected but I just Can't get the proper code to change the Yes/No field in the table


any help would be apreciated. either reply here or if you need more info please call me at 204-947-0194

Tim Hellsten
Great West Life

Tim,

You can either:

' ****************************************
Dim dbs As Database
Dim sql As String

Set dbs = CurrentDb
sql = "Update YourTable Set YourFlag = -1 " & _
"Where TrackingNumber = '" & Me.TrackingNumber & "'"
dbs.Execute(sql)
' ****************************************


' ****************************************
Dim dbs As Database
Dim rst As Recordset
Dim sql As String

Set dbs = CurrentDb
sql = "Select * from YourTable " & _
"Where TrackingNumber = '" & Me.TrackingNumber & "'"
Set rst = dbs.OpenRecordset(sql)
If rst.EOF And rst.BOF Then
MsgBox("Wow, no such number")
Else
rst.Edit
rst!YourFlag = -1
rst.Update
End If
' ****************************************

hth,
Wayne

Thank you

Thank you very much