Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

Thread Tools Rate Thread Display Modes
Old 01-09-2003, 09:12 AM   #1
Posts: n/a
Updating Table Via VBA Help


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

  Reply With Quote
Old 01-09-2003, 11:51 AM   #2
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,076
Thanks: 6
Thanked 55 Times in 53 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough

You can either:

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

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

' ****************************************
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")
rst!YourFlag = -1
End If
' ****************************************

WayneRyan is offline   Reply With Quote
Old 01-09-2003, 12:26 PM   #3
Posts: n/a
Thank you

Thank you very much

  Reply With Quote

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

All times are GMT -8. The time now is 07:37 AM.

Microsoft Access Help
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit

Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World