Updating Yes/No field of a table using a command button on a form

[Datament]

I have a table named tbl_REVBal with Yes/No field named PMTRequests.
There is a form named frm_Payments that has one of many textboxes named PmtStatus and a command button named cmd_PMTReq
If the textbox PmtStatus has a credit amount it turns the cmd_PMTReq visible which is used to determine that the Accounting Dept is required to request overdue Payment. If required, the cmd_PMTReq is clicked. That action is to update the tbl_REVBal

I have a command button on frm_Payments and using the following VBA to update the tbl_REVBal

****PLEASE NOTE: tbl_REVBal is part of the database but not the record source for my frm_Payments***

On my form's click event
Dim strSQL As String
strSQL = "Update tbl_REVBal SET
strSQL = "UPDATE tbl_REVBal SET PMTRequests = 0;"
CurrentDb.Execute strSQL, dbFailOnErrors '(This line triggers an error message)

strSQL = "UPDATE tbl_REVBal SET PMTRequests = 1 where PMTStatus = <0;"
CurrentDb.Execute strSQL, dbFailOnErrors '(This line triggers an error message)

Me.Form.Requery



Please I need help with updating tbl_REVBal, field PmtRequests whenever cmd_PMTReq is visible and clicked. Thanks so much in advance.

[June7]

What is the exact error message?

Why does your code show an incomplete line?

A yes/no type field in Access database will have a value of 0 (for No/False) or -1 (for Yes/True). The first SQL should work but the second should not.

Saving calculated data is usually unnecessary and maybe even dangerous. Why can't this yes/no value be calculated when needed since it's already dependent on other data?

If you want to provide db for analysis, follow instructions at bottom of my post.

[Micron]

In addition, the equal sign must come after the < or > and not before.

[June7]

VBA will auto-fix the sign in code (as in an If Then statement). Both versions are recognized in the immediate window:
?1 <= 2
True
?1 =< 2
True
?1 >= 2
False
?1 => 2
False

A query will give an error message.

And if sign is between quotes in SQL string built in VBA (as in your code), better get it right. Good catch, Micron!

[Datament]

Error message is "Compile Error, Variable Not Defined"
You were right about the value of 0 for (Y/N), I changed the 1 in the second SQL to -1 and it still did not update the table

[June7]

Okay, VBA is not recognizing dbFailOnErrors. Should be dbFailOnError.

[Micron]

Another good catch. I gotta start reading/seeing more.

[Datament]

I was able to get both the first and second SQL to work after changing the position of the = sign in the second SQL.

Dim strSQL As String
strSQL = "Update tblREVBal SET SET Check_Req = 0;"
strSQL = "Update tbl_REVBal SET PMTRequests = -1 where PMTStatus <=0;"
'CurrentDb.Execute strSQL, dbFailOnErrors


Me.Form.Requery

But now unfortunately if there are identical values in column "Store #" of tbl_REVBal below it checks PMTReq boxes of identical "Store #". It should be checking only "Unpaid" boxes of Store #33 and Store #25 as indicated below. example:

Item No	Store#	Rev	Pmts	Status	PMTReq
1	33	412	10.00	Paid
2	33	400	105.00	Unpaid	X
3	25	200	76.00	Paid
4	25	215	88.75	Unpaid	X
5	25	202	66.25	Paid

Please get me thru. Many thanks.

[June7]

Micron, I didn't consider it until read error message.


Add that criteria to WHERE clause.


Or, again, calculate when needed instead of saving to table.

[Datament]

As suggested I added the criteria to the existing WHERE clause as follows:
DoCmd.RunSQL "UPDATE tbl_REVBal TabStatus = '1' WHERE ID LIKE '" & & "Me!txtID.Value & "';"


strSQL = "UPDATE tbl_REVBal SET PMTReq = -1 where Store # = " " & REV PMTStatus = <=0;"


The result I am expecting from the above table is to have the PMTReq column = Yes if the value in coulmn "Status" is Unpaid like rows 2 and 4. Currently the result I was getting was 1,2 and 4,5 because identical values in column "Store#".


Please your help would be greatly appreciated.

[June7]

You need status and ID in WHERE clause.

Advise not to use spaces nor symbols in naming convention. If you do, must enclose in []: [Store #]. Better would be StoreNo or StorNum.

[Datament]

I appreciate your time. I did not use space and I have changed the Store # to StoreNo, yet I am still not get anything out of the second line. The result is still the same. I need the result to show only those rows "UnPaid". I know you suggested adding status and ID in where clause. I am lost as a newby. I am not very familiar with using WHERE clause properly, and any help would be greatly appreciated.

[June7]

If you want to update all stores then just use PMTStatus criteria:

strSQL = "UPDATE tbl_REVBal SET PMTReq = -1 WHERE PMTStatus <=0;"

If you want to update a specific store then include store number in criteria by reference to a control on form:

strSQL = "UPDATE tbl_REVBal SET PMTReq = -1 WHERE StoreNo = " & Me.tbxStore & " AND PMTStatus <= 0;"


Why would you reference REV field?


Again, why even bother with PMTReq field since this value can be calculated based on PMTStatus?

[Datament]

Thank you so much for your assistance. Your suggested strSQL line got it working. Gracias...