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

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

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

  Reply With Quote
Old 01-09-2003, 11:51 AM   #2
WayneRyan
AWF VIP
 
Join Date: Nov 2002
Location: Camarillo, CA
Posts: 7,065
Thanks: 5
Thanked 54 Times in 52 Posts
WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough WayneRyan is a jewel in the rough
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
WayneRyan is offline   Reply With Quote
Old 01-09-2003, 12:26 PM   #3
TimH
Guest
 
Posts: n/a
Thank you

Thank you very much

  Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 11:40 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


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