Setting a boolean field using code

[Keith Nichols]

Hi,
This might be an odd one as I can't find anything relevant in the forum.

I have a boolean Field, Archive, in my table, tbl_prj_Details, and the form fdlg_Prj_details is based on this table. Records marked as "archive" are excluded by the form's sql. On the form is a command button, cmd_Archive.

The on-click event of cmd_Archive runs a message box asking users to confirm archiving. If yes is selected I want the boolean field in the table to be set to yes. The form is then re-queried and the archived record disappears from view.

I was able to get several versions of this functionality working using check boxes on my form, but I was hoping to reduce things to 2 clicks, Archive & confirm.

Me![tbl_Prj_Details].Archive = True

and a host of variations on this theme did not work. I know that this is, or should be, something relatively simple, but as a self taught newbie, I can't figure it right now. Any help appreciated.

Regards,

Keith.

 

[Rich]

Something like
Private Sub txtArch_Click()
On Error GoTo Err_Archive_Click
Dim Msg, Style, Title, Response, MyString

Beep
Msg = "You are about to archive this customer and related properties are you sure you wish to continue? "
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Confirm Archive" '
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE Addresses LEFT JOIN etc.
CanClose = True
DoCmd.Close
DoCmd.SetWarnings True
Exit Sub
Else
Me.Undo

End If
Err_Archive_Click:
Exit Sub



MsgBox Error$
Resume Err_Archive_Click

End Sub

 

[Keith Nichols]

Hi Rich,

I feel I am geting close but I'm still tripping on the update. Can you make any suggestions as to where I'm going wrong? I get "Error 3144 Syntax error in update statement" when I run the code you gave with the sql line below:

DoCmd.RunSQL "UPDATE tbl_prj_Details"
Archive = True

Regards,

Keith

 

[Rich]

The easiest way is to build an update Query and then convert it to SQL and paste that into your vba statement

 

[Keith Nichols]

The easiest way is to build an update Query and then convert it to SQL and paste that into your vba statement

What an excellent tip. Many thanks.

Regards,

Keith.

 

[Keith Nichols]

All records updating rather than just current

OK. I have generated the SQL as per the method described. Now my problem is that the routine updates all records in the table rather than the current record of the form.

Private Sub cmd_Archive_Click()
On Error GoTo Err_Handler

Dim Msg, Style, Title, Response, MyString

Beep
Msg = "You are about to archive this project and related properties.    " & Chr(13) & Chr(13) & "Are you sure you wish to continue? "
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Confirm Archive" '
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
DoCmd.SetWarnings False

[COLOR="Teal"]'This must be where the problem lies[/COLOR]
[COLOR="DarkRed"]DoCmd.RunSQL "UPDATE tbl_Prj_Details SET tbl_Prj_Details.Archive = Yes;"[/COLOR]

DoCmd.SetWarnings True
Exit Sub
Else
Me.Undo

End If

Exit_Here:
    Exit Sub

Err_Handler:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Here
    
End Sub

Thanks in advance,

Keith.

 

[JimmyK]

You need to set the query to only update that particular record in the database. Something like:

UPDATE tbl_Prj_Details SET tbl_Prj_Details.Archive = Yes where yourfieldname = 'whatever'

 

[Keith Nichols]

Back to square 1 - Use a checkbox

Hi,

I was unable to work out how to set the boolean field in my table for the current record only.

In the end, I reverted to a check box on my form and a messge box in the AfterUpdate event. Now when a user checks the archive box, the message box asks them to confirm or cancel. This triggers a requery or undo: the 2 clicks of my original requirement. This is really the functionality I wanted all along so I'm happy with that.

My only remaining niggle is that the check box does not show a tick when you click. To get round this irritation I hide it in the code, which necessetates moving the focus away from the check box. Essentialy I am using the check box like a button so I would prefer a button. Hey ho. If any readers can see how to use a button I would be grateful for a heads up.

Private Sub chk_Archive_AfterUpdate()
On Error GoTo Err_Handler

Dim Msg, Style, Title, Response, MyString

    txt_Project_Title.SetFocus 'Move focus away from checkbox
    chk_Archive.Visible = False 'Hide chk_Archive
    Beep

'Define messagebox
    Msg = "You are about to archive this project and related properties.    " & Chr(13) & Chr(13) & "Are you sure you wish to continue? "
    Style = vbYesNo + vbExclamation + vbDefaultButton1
    Title = "Confirm Archive" '

'Check which button clicked
    Response = MsgBox(Msg, Style, Title)
        If Response = vbYes Then
            Me.Requery  'Current project removed from recordset
            chk_Archive.Visible = True 'Show chk_Archive
        Else
            Me.Undo                     'chk_Archive unchecked
            chk_Archive.Visible = True  'Show chk_Archive
        End If

Exit_Here:
    Exit Sub

Err_Handler:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_Here
    
End Sub

Regards,

Keith.

 

[Keith Nichols]

UPDATE tbl_Prj_Details SET tbl_Prj_Details.Archive = Yes where yourfieldname = 'whatever'

Hi Jimmy,

Thanks for the response - It looks like we were writing at the same time.

The PK of the records selected by the form is "Project_ID" which is an autonumber. I presume that I need to declare a variable and pass the Project_id for the current record to the sql statement. Not too sure about how to do this.

Is the code below heading in the right direction?

dim Prj_ID as string

Prj_ID = current record Project_ID

UPDATE tbl_Prj_Details SET tbl_Prj_Details.Archive = Yes where Project_ID = Prj_ID

Regards,

Keith.

 

[Keith Nichols]

Syntax errors in SQL

I feel I'm getting close!

I created an update query based on the Project_ID field of the form which checks the archive box for the appropriate project. This works fine.

My problems now is pasting this into the VBA

SQL of query:

UPDATE tbl_Prj_Details SET tbl_Prj_Details.Archive = Yes
WHERE (((tbl_Prj_Details.Project_ID)=[forms]![fdlg_Prj_details]![project_ID]));

Pasted in VBA:

Dim str As String
[COLOR="Red"]str= UPDATE tbl_Prj_Details SET tbl_Prj_Details.Archive = Yes
WHERE (((tbl_Prj_Details.Project_ID)=[forms]![fdlg_Prj_details]![project_ID]));[/COLOR]
DoCmd.RunSQL str

What do I need to do to the sql statement to allow VBA to read & run it?


Regards,

 

[Banana]

It should be str= "SQL blah blah blah " _
& "More SQL blah blah blah"

The _ tells VBA that code is more than one line long whereas the & concentates the SQL string together (SQL string has to be one long string)

But if you want to pass the criteria from from to the SQL, then it should be

str="SQL blah blah" _
& "More SQL Blah blah Where Criteria= " & Me.ControlName & ")"

 

[Keith Nichols]

Hi Banana,

Thanks for the heads-up. With the code below, I now get a "3085 - undefined function 'YesWhere' in expression" error. I must be closer as it doesn't go red when I write it!

Any idea what aditinal punctuation or re-jigging of the code is needed?

Private Sub cmd_Archive_Click()
On Error GoTo Err_Handler

Dim Msg, Style, Title, Response, MyString
Dim str As String
str = "UPDATE tbl_Prj_Details SET tbl_Prj_Details.Archive = Yes" _
& "WHERE (((tbl_Prj_Details.Project_ID)=[forms]![fdlg_Prj_details]![project_ID]));"

Beep
Msg = "You are about to archive this project and related properties.    " & Chr(13) & Chr(13) & "Are you sure you wish to continue? "
Style = vbYesNo + vbExclamation + vbDefaultButton1
Title = "Confirm Archive" '
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
DoCmd.SetWarnings False
DoCmd.RunSQL str
DoCmd.SetWarnings True
Exit Sub
Else
Me.Undo

End If

Exit_Here:
    Exit Sub

Regards,

Keith.

 

[Banana]

I'm not sure what 3085 error is, but are you referring to a control on form as a criteria?

If that is the case, you can't really have it as a part of SQL string; As I said above, your criteria should be-

WHERE Criteria = " & Me.ControlName & ";))"

Edit- I realized now- You're getting error because it's reading SQL as ".... YesWhere..." because you have it as

...Yes" _
& "Where....

which VBA will translate into "....YesWhere...."

It should have a space before the "" like this

...Yes " _
& "Where...

which translates correclty into "... Yes WHERE..."

 

[Keith Nichols]

Works a treat!

Thanks Banana.

The space after the "yes" was the stumbling block. I added a requery in the code and now it all works perfectly.

Thanks once again to yourself & Rich for getting this working, and of course for the education.

Regards,

Keith.