Problem Updating another table within continuous form 1

[freddydog]

Hi, Can't figure out why this doesn't work. I would like to update 2 cols (date and operator id) in tableB when a control (lettertype - also in TableB) on a continuous form is updated.
- when the lettertype is updated, TableB show the update
- I then have a query to update the date/operator id in that same table - TableB
- the query fails at the where clause ... when I specifically specify the record id in the where clause in TableB, the update works but when I try to use a variable in which I write the record id, the query fails?? How can I update the correct row?

Hope this is clear. Thanks for any insight.

 

[lameid]

First any reason you are not just using a bound form?

Secondly, please post the SQL or code that doesn't do what you expect and explain what you want it to do.

 

[freddydog]

Lameid, thank you for your response ... I added the record id to the form and the update worked. Is it a requirement to have the record id as a bound control on the form?

This is the query which did not work:
sSQL = " UPDATE DonationAmttbl "
sSQL = sSQL & " SET DonationAmttbl.StageDate=Now(), "
sSQL = sSQL & " DonationAmttbl.OperID=[Forms][TYWorkingfrm]![OperID] "
sSQL = sSQL & " WHERE DonationAmttbl.DonID = UpdtDonID; "

Dim UpdtDonID AS Integer

When I changed UpdtDonID to a bound control the query worked.
Don't understand but thanks for the advice.

 

[lameid]

Your form doesn't necessarily have to be bound. There is just no coding to update records fields if it is bound (the record gets saved when you tell it to, you close the form or you move to a different record).

If it is unbound you have to update the data using some sort of code if you want to keep your changes.


As for your code your where clause is not right. Notice how you include a reference to the form in the line above it. You should be doing the same in the where clause or concatenating in the variable. It seems odd that you decalare the variable below the select if you intend to use it as part of the select (maybe you are not intending this).

 

[freddydog]

Lameid, in the code, I did declare the variable above the SQL. As per the SQL, originally, I did reference the control prefixed by the form id but that gave me an error .. something about not recognizing the form ... so I resorted to using a variable ... I set the variable to the form id and control name.
I fixed the problem by putting the bound control on the form as you suggested.
Thanks for your help.

 

[lameid]

If you use a bound form, you shouldn't need to run an update query as the changes will be saved by the form.

If you use unbound and your variable UpdtDonID is set then something like this will fix your SQL...

sSQL = " UPDATE DonationAmttbl "
sSQL = sSQL & " SET DonationAmttbl.StageDate=Now(), "
sSQL = sSQL & " DonationAmttbl.OperID=[Forms][TYWorkingfrm]![OperID] "
sSQL = sSQL & " WHERE DonationAmttbl.DonID = " & UpdtDonID & "; "

Note the changes to the last line.

 

[freddydog]

Thanks Lameid!