update of yes/no field using code

[patkeaveney]

Hi All

Table relationships:
the table FOIRequests is linked to the Exemptions table via a link table in a many to many relationship

FOIrequests: PK intFOIRequestsID

Link Table: intFOIRequestsID
intExemptionID

Exemptions Table PK intExemptionID

The main form opens and displays only a combo box of existing Request Numbers.
The user selects a request number and the data for the selected request is displayed.
The main form (tblFOIRequests) has several tabs and one of the tabs has a subform (tblExemptions), if more than one record exists in the sub form i need to update a field (flgExemptionsFlag) in the main record (tblFOIRequests) without user input.

The rest of the main record, which is spread over the other tabs is saved using a save button on the form, giving the user the choice to save or not.

I need to update this one field in the main record using code as i dont want the forms dirty property to be set when this field is updated

intFOIrequestID is the PK of the FOIRequests table (autonumber in design)
Table Name to be updated: FOIRequests
Field Name in table: flgExemptionsFlag (yes/no field in table design)

sub RecordCount

Dim SubFormRecordCountExemp As Long
Dim ExemptionFlag As Variant (not sure if this is correct)

' Subform Exemptions
Set rs = Me.frmExemptionListsubform.Form.RecordsetClone
If Not (rs.EOF) Then
rs.MoveLast
End If

SubFormRecordCountExemp = rs.RecordCount

If SubFormRecordCountExemp > 0 Then
ExemptionFlag = -1
Else
ExemptionFlag = 0
End If

Set rs = Nothing

' Main form
Set rs = Me.RecordsetClone
If Not (rs.EOF) Then
rs.MoveLast
End If

FormRecordCount = rs.RecordCount

DoCmd.RunSQL "UPDATE [tblFOIRequests] SET flgExemptionFlag = '" & ExemptionFlag & "' WHERE tblFOIRequests.intFOIRequestID = '" & [Forms]![frmAmendRequest]![intFOIRequestID] & "'"

End Sub

When running the above code I get the error: data type mismatch in criteria expression:



Thanks in advance

Pat

 

[Galaxiom]

You are putting quotes around values that are not strings.

Also there is no need to concatenate textbox values in a DoCmd.RunSQL statement. Simply refer to the textbox name inside the SQL string. Note however you must provide a full reference to the textbox. Me! is not valid in SQL.

BTW please post code inside a code box. In particular series of quotes are much easier to read in a code box.

 

[boblarson]

Also there is no need to concatenate textbox values in a DoCmd.RunSQL statement. Simply refer to the textbox name inside the SQL string.

Actually, you SHOULD concatenate the text box names and not include them in the SQL of the runcommand because you are looking to include the VALUES not the text box referrals themselves.

Note however you must provide a full reference to the textbox. Me! is not valid in SQL.

No you don't because this is being done in CODE. The SQL String is being built in code and run in code so ME is perfectly valid in this instance.

 

[Galaxiom]

Actually, you SHOULD concatenate the text box names and not include them in the SQL of the runcommand because you are looking to include the VALUES not the text box referrals themselves.

Why do you say that Bob?
DoCmd.RunSQL simply submits the string to the engine to be run just like any other query.

No you don't because this is being done in CODE. The SQL String is being built in code and run in code so ME is perfectly valid in this instance.

No, it is not being done in code. This is not the Execute command. It is an SQL query and a query does not understand Me!

 

[boblarson]

Why do you say that Bob?
DoCmd.RunSQL simply submits the string to the engine to be run just like any other query.

Okay, I ran a test to verify and it works both ways. However, I would suggest that users do the concatenation method simply to remain consistent throughout their coding because it does NOT always work that same way.

No, it is not being done in code. This is not the Execute command. It is an SQL query and a query does not understand Me!

Every place the OP used ME in this example is in CODE, not in a SQL Query. Read it again. There is no ME in the SQL part.

 

[boblarson]

Oh, and if you DO use the concatenation method you CAN use ME in there

 

[patkeaveney]

Thanks guys, i am new to sql, taking out the quotes worked.

PS
sorry for causing a discussion.

Galaxiom
I do not know how to use the codebox again sorry

Pat

 

[boblarson]

I do not know how to use the codebox again sorry

See this VISUAL:

 

[Galaxiom]

sorry for causing a discussion.

Not at all. Thankyou for causing a discussion.

You can also automatically put code tags on text in the Advanced view by highlighting it and pressing the second button from the right (#).

 

[patkeaveney]

hi guys

The code updates the main record on loading which is fine.
If i now click on one of the other tabs and amend a value then return to the exemptions tabs, the subform is locked.
ie i cant add or remove another exemption.
(this is done by selecting an exemption id from a dropdown), but the dropdonm appears to be locked, not displaying values.
The same situation occurs on another tab with a subform on it.

 

[Galaxiom]

Okay, I ran a test to verify and it works both ways. However, I would suggest that users do the concatenation method simply to remain consistent throughout their coding because it does NOT always work that same way.

RunSQL always works the same way. It runs a query, nothing more, nothing less. If the query works from the designer, its SQL will work from the command.

IMHO it is a case of "Horses for Courses".

I frequently recommend using the DoCmd.RunSQL Method especially for those users just starting out in code. The query can be generated in the query designer and simply pasted into the argument. They even get an introduction to layout, concatenation and continuation once they use a long statement and need to make it readable.

Once comfortable with this method they are then less daunted when moving on to the Execute Method with its initially bewildering use of nested quotes.

Every place the OP used ME in this example is in CODE, not in a SQL Query. Read it again. There is no ME in the SQL part.

I included the note about "Me!" not working in the same paragraph that suggested the text box could simply be referred to directly rather than retreiving its value and concatenating. It was meant in this context and was appropriate since Me! does not work in the method I was explaining in that paragraph.
Read it again.

 

[patkeaveney]

If a field on a tab linked to the main form is changed on a form.
Will the subform on a tab linked to it become locked.

That appears to be what is happenning in this case.