Should work but doesn't

[Valentine]

This was working last week and now it is not and I am stumped as to why. Nothing involving these fields have been changed except I removed clearing the boxes after the SQL runs.

Private Sub cmdComment_Click()

    Dim dbCurr As DAO.Database
    Dim strComment As String

    Set dbCurr = CurrentDb()
    strComment = "UPDATE Leave " _
               & "SET [Comments] = '" & Me.txtComment.Value & "' " _
               & "WHERE ((Leave.[Start Date]) = #" & Me.txtStart.Value & "# AND (Leave.[End Date]) = #" & Me.txtEnd.Value & "#);"
    DoCmd.SetWarnings False
    DoCmd.RunSQL (strComment)
    DoCmd.SetWarnings True


End Sub

 

[plog]

except I removed clearing the boxes after the SQL runs.

I don't really understand what that means, but I understand how to fix problems. Whatever you changed is the place to first look. Reverse whatever you did to see if your old method works. If so, that's the issue.

 

[CJ_London]

what does 'not working' mean?

suggest comment out your setwarnings and see if you get an error message
also debug.print strComment, see if the sql makes sense

Also possible your txtStart and txtEnd dates are not in US or SQL format (mm/dd/yyyy and yyyy-mm-dd respectively)

And why are you assigning dbCurr and then not using it?

 

[Valentine]

oh sorry that button is supposed to take the text in txt.Comment and put it in the Leave table where the dates match up to add comments to the leave transaction. Now to add more into this weirdness, it is working with EVERYONE except this 1 person.

 

[cheekybuddha]

it is working with EVERYONE except this 1 person.

What date format do they have set in their Regional Settings?

Did you follow CJ_London's advice re. removing your SetWarnings commands and adding a Debug.Print strComment so you can actually see what is trying to be executed?

 

[SHANEMAC51]

& "SET [Comments] = '" & Me.txtComment.Value & "' "

maybe there is a quotation mark or an apostrophe in the comment

 

[Valentine]

Yes the command works everything is as planned. Everyone in this database are using the same date formats I have a date selection box so its all standardized.

 

[cheekybuddha]

Did you follow CJ_London's advice re. removing your SetWarnings commands and adding a Debug.Print strComment so you can actually see what is trying to be executed?

 

[Valentine]

Yes, the command works everything is as planned except this one person.

 

[cheekybuddha]

So what is put output in the immediate Window (Ctrl+G) on the machine where it fails? What is the error message?

 

[jdraw]

Are you getting an error message? if so, post it here.

 

[Valentine]

no error message, I just input his data into the table directly

 

[cheekybuddha]

So what is put output in the immediate Window (Ctrl+G)

 

[MarkK]

By far the most reliable way to run SQL like this is to use a temporary parameterized QueryDef. Consider code like...

Private Sub cmdComment_Click()
    Const SQL As String = _
        "UPDATE Leave As t SET t.Comments = p0 " & _
        "WHERE t.[Start Date] = p1 AND t.[End Date] = p2"
        
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = Me.txtComment
        .Parameters(1) = Me.txtStart
        .Parameters(2) = Me.txtEnd
        .Execute dbFailOnError
        .Close
    End With
End Sub

Formats and delimiters for text and date data are handled automatically. Users can enter single and double quotes into their text data, and it doesn't break your SQL.

 

[jdraw]

So you gave up on the code/approach you posted in #1?

 

[The_Doc_Man]

OK, the question then is "what is special about the one person?"

Find a working machine and the non-working machine for these comparisions. Look for:

Different patch levels of Windows - have to use HELP >> ABOUT to find the windows version.

Different patch levels of Office - from the FILE tab in the ribbon, HELP >> ABOUT shows you the differences for Access. It is unlikely to be due to any other Office member.

Different regional settings for dates - look through the SETTINGS options.

Since references are supported through the registry and therefore can be machine-specific, check from VBA code pages TOOLS >> REFERENCES

How often do your users update their front-end file? If there is something out-of-date for this one user who failed to perform an update, you could be looking at a difference right there. ALSO, if the users have individual front-ends, see if this user got into the FILE >> OPTIONS >> CURRENT DATABASE settings.

 

[Gasman]

Was it working at the end of April, and not in May.? I would suspect dates, as they are not formatted, but 29/04/2022 cannot be taken any other way. 09/05/2022 however can.

 

[Gasman]

no error message, I just input his data into the table directly

?????

 

[NauticalGent]

By far the most reliable way to run SQL like this is to use a temporary parameterized QueryDef. Consider code like...

Private Sub cmdComment_Click()
    Const SQL As String = _
        "UPDATE Leave As t SET t.Comments = p0 " & _
        "WHERE t.[Start Date] = p1 AND t.[End Date] = p2"
       
    With CurrentDb.CreateQueryDef("", SQL)
        .Parameters(0) = Me.txtComment
        .Parameters(1) = Me.txtStart
        .Parameters(2) = Me.txtEnd
        .Execute dbFailOnError
        .Close
    End With
End Sub

Formats and delimiters for text and date data are handled automatically. Users can enter single and double quotes into their text data, and it doesn't break your SQL.

this saved my bacon today, thanks!

 

[MarkK]

Right on, glad you found it useful!
Cheers,