Syntax error in RunSQL

[mdmarney]

Hi. Having trouble with the following command:

DoCmd.RunSQL "UPDATE tbl_attendants SET Arrival_Verification = '" &Yes& "' WHERE Me.Parish_Group_Arrival = '"&Yes&"'"

Receiving syntax error.
Help... *************
M. MARNEY

http://www.tgw.com

 

[PaulBricker]

If the field is a Yes/No field then your syntax it wrong. It should look like this because Yes/No is boolean. That is, it stores -1 for Yes and 0 for No.

DoCmd.RunSQL "UPDATE tbl_attendants SET Arrival_Verification = " & Yes & " WHERE Me.Parish_Group_Arrival = " & Yes

See if that works.
Paul

 

[mdmarney]

Thank you paul...
the following worked, but with a few complications:

DoCmd.RunSQL "UPDATE tbl_attendants SET Arrival_Verification = TRUE WHERE Parish_Group_Arrival = TRUE"

1) I wants to update the whole table. I need to add something to the where clause to indicate the link between the form and subform. (i.e. Where Parish_Group_ID = " & Me![Parish_Group_ID]) How do I do this?

2) Is there anyway to remove the Update Table verification screen. The code errors out if you hit NO on that screen. I'd prefer to either give a custom dialog or no option at all. Ideas?
*************
M. MARNEY

http://www.tgw.com

 

[PaulBricker]

Sorry, Yes/True/-1 aren't variables so shouldn't be surrounded with quotes and ampersands. I know better than that.
If Parish_Group_ID is a number then the syntax you have should work but the Me! signifies the active form. If the info is on a subform and you are calling this code from the main form you will need to use the full identifier.
Forms!Mainformname!Subformname.Form!Parish_Group_ID.

If the ID is datatype Text, then you would have to surround with single/double quotes.

Where Parish_Group_ID = '" & Me![Parish_Group_ID] & "'"

To get rid of the message box us this code

DoCmd.SetWarning False
DoCmd.RunSQL .....
DoCmd.SetWarning True


Paul

 

[mdmarney]

Paul.
Thank you for the assist. I got it working.
One last question if I may.
Is there any way to make this statement work for both true and false. So If I check it it will mark the corresponding items true and if I uncheck it it will mark the corresponding items false.

Thank you! *************
M. MARNEY

http://www.tgw.com

 

[PaulBricker]

Do you mean something like this. Where you set Arrival_Verification to whatever Parish_Group_Arrival equals?

DoCmd.RunSQL "UPDATE tbl_attendants SET Arrival_Verification = Parish_Group_Arrival"

I'm not sure I understand what you want exactly.

Paul

 

[mdmarney]

There is a one-to-many relationship between the Parish_Group_Arrival (1) and Arrival_Verification (Many).

I would ideally like it to work both ways:
If Parish_Group_Arrival is set (TRUE or FALSE), all Arrival_Verification records will be set (TRUE or FALSE). That's the easy part, I think.
Additionally, If ALL of the Arrival_Verification records are set to TRUE, then Parish_Group_Arrival should be TRUE, but if some of or all of the Arrival_Verification records are FALSE, then Parish_Group_Arrival should be FALSE.
Make sense??? Thank you. *************
M. MARNEY

http://www.tgw.com

 

[PaulBricker]

This looks a lot like an open loop. I understand what you are saying but I don't understand how this action would happen. You update tblAttendants with the update query and then what? Do you go into tblAttendants and see what the value of all the Arrival_Verifications are and based on that you set Parish_Group_Arrival to something. Where is Parish_Group_Arrival? On a form/subform? Sorry, I just don't quite understand where we would be doing all this.

Paul

 

[mdmarney]

Sorry for the confusion Paul.

Yes, this would be happening on a form/subform.
To answer your specific question: Parish_Group_Arrival is a Binary field in tbl_Groups. tbl_Groups is linked to tbl_People via Group_ID.

Senerio...
A group comes to check in for an event. All of the members are there, so I check the Parish_Group_Arrival field to TRUE, and it updates all the Arrival_Verification fields to TRUE. But lets say half the group comes in at 10 am and the other half at noon. By noon I have checked each of the individual Arrival_verification fields to TRUE, therefore I would like the Parish_Group_Arrival to change to TRUE.

Let me know if that helps. If not, don't worry about it. I know its complicated... *************
M. MARNEY

http://www.tgw.com

 

[PaulBricker]

OK, that makes more sense to me. So in the AfterUpdate Event for Parish_Group_Arrival you would run the update query to update all the Arrival_Verification values. Now I would assume that this would be based on Group_ID so that you only update the Arrival_Verification for that specific Group_ID. I don't see any provision for that in the Update syntax.
In the AfterUpdate event for the Arrival_Verification you would need something like this(which we are addressing in the other post I think)
Dim rs2 as DAO.Recordset
Set rs2 = CurrentDb.OpenRecordset("Select * From....Where Group_ID = 12345 And Arrival_Varification = False&quot
If rs2.RecordCount >= 1 Then
we still have people missing so don't update Parish_Group_Arrival
Else
Parish_Group_Arrival = True
End If

We should probably keep this discussion in the other post. It's already confusing enough.

Paul

 

[mdmarney]

This gives me something to go on. Thank you. *************
M. MARNEY

http://www.tgw.com