Create a macro to change a value in a record

[dcornett63]

Happy Friday to you all.
First, I'd like to say thank you to everyone who helps us out on this
discussion board. I have learned a lot from you all (both by posting my own
questions and by reading other people's quesitons).

My question is:
I have a relational database with multiple tables. Is there a way to create
a macro (or maybe a module) that will change the value of a field on one
table based on the data in two different fields on another (linked) table.

Here's what I'm trying to do exactly:
We award jobs to vendors that bid on the work we need done. The vendors will
submit an invoice(s) after the work is done. I have one table that tracks
that Award contract and another that tracks the invoices. The invoice records
are linked by a primary key on the Award table (one to many relationship). On
the Invoice table, I have a boolean check box that is checked if the invoice
is a final invoice for that job. I have another field in which the date that
the invoice is sent to our finance department for payment is entered. On my
Awards table, I have a drop-down box in which the status of the Award is
entered. Options include; Pending, Awarded, Completed, or Canceled. I would
like some way to automate changing the Award Status from Awarded to Completed
on the Awards table when the final invoice checkbox is checked and a date is
entered in the To Finance field on the Invoice table. (Note, all changes are
done by a form)
Can this be done?
Thanks,
David

 

[dcornett63]

Thanks Ken, I'll try this out on Monday morning and will let you know how it
turns out

You can execute an SQL statement in the AfterUpdate event procedure of the
invoice form. The SQL statement will be built so that it updates the
relevant row in the awards table by including a WHERE clause to restrict it
to that where the AwardID (or whatever) matches the value of the AwardID
foreign key column in the current invoice record:

Dim cmd As ADODB.Command
Dim strSQL As String

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

If Me.[Final Invoice] AND Not IsNull(Me.[To Finance]) Then
strSQL = "UPDATE Awards " & _
"SET [Award Status] = ""Completed"" " & _
"WHERE AwardID = " & Me.AwardID

cmd.CommandText = strSQL
cmd.Execute
End If

This assumes AwardID is a number data type. If it’s a text data type amend
the code to:

"WHERE AwardID = """ & Me.AwardID & """"

Ken Sheridan
Stafford, England

Happy Friday to you all.
First, I'd like to say thank you to everyone who helps us out on this
discussion board. I have learned a lot from you all (both by posting my own
questions and by reading other people's quesitons).

My question is:
I have a relational database with multiple tables. Is there a way to create
a macro (or maybe a module) that will change the value of a field on one
table based on the data in two different fields on another (linked) table.

Here's what I'm trying to do exactly:
We award jobs to vendors that bid on the work we need done. The vendors will
submit an invoice(s) after the work is done. I have one table that tracks
that Award contract and another that tracks the invoices. The invoice records
are linked by a primary key on the Award table (one to many relationship). On
the Invoice table, I have a boolean check box that is checked if the invoice
is a final invoice for that job. I have another field in which the date that
the invoice is sent to our finance department for payment is entered. On my
Awards table, I have a drop-down box in which the status of the Award is
entered. Options include; Pending, Awarded, Completed, or Canceled. I would
like some way to automate changing the Award Status from Awarded to Completed
on the Awards table when the final invoice checkbox is checked and a date is
entered in the To Finance field on the Invoice table. (Note, all changes are
done by a form)
Can this be done?
Thanks,
David