Update using VB

screwsoft

Registered User.
Local time
Today, 03:33
Joined
Nov 18, 2003
Messages
10
I have an access 2002 database which includes 2 tables in particular. Work and Order. When a work request is received in the office it is entered in the Work table and has filed 'Status' that is set to I for identified.

When the work is to be come an order on a contractor a record is created in the Order Table which is linked to the Work Table by the 'Work No' Field which is the primary key of the Work Table.

The form to input the order has a command button which prints the order an closes the form. I would also like the command button to update the 'status' Field of the Work table to O for ordered when it is pressed.

The SQL code i have come up with looks something like this:

SET Work.Status = "O"
WHERE Work.WorkNo = Me.WorkNo

I'm not sure if this SQL is right and have no idea of how to actually implement it in the event procedure, can anybody help?
 
Give this a go, and see if it works...

Code:
Dim strSQL As String
strSQL = "UPDATE Work SET Status = ""O"" WHERE OtherField = " & Me.WorkNo & ";"

DoCmd.RunSQL strSQL


If so, you'll want to make a slight change:

Code:
Dim strSQL As String
strSQL = "UPDATE Work SET Status = ""O"" WHERE OtherField = " & Me.WorkNo & ";"

DoCmd. SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
 
Tried your idea but it didn't work using the strSQL as:

Code:
"UPDATE IDWorks SET Status = ""O"" WHERE IDWkNo = " & Me.IDWkNo & ";"

Pops up a 'Enter Parameter Value' Dialog Box

With the Value of IDWkNo is what it seems to be requesting.
 
Is IDWkNo text or a number? And which IDWkNo?

If you insist on a textbox on your form with the same name as a field in a table you lose flexibility in coding options and can run into unexpected problems. Consider changing it to txtIDWkNo
 
Hi again,

On the form it's actually a combobox which is populated from the list of works in the Work table that have a Status 'I' and therefore available for ordering. It is actually called Combo28 and its control source is the IDWkNo field of the Order Table.

Sorry if this information misled you earlier.

Thanks for your help by the way.
 
But what data-type is the Bound Column of this combo?
 
Code:
Dim strSQL As String

strSQL = "UPDATE IDWorks SET Status = 'O' WHERE IDWkNo = """ & Me.Combo28 & """;"

DoCmd. SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
 

Users who are viewing this thread

Back
Top Bottom