Getting "file count not be found" when executing SQL (1 Viewer)

[tmyers]

This is the first I have tried using an input box, so I have a strong feeling I am using it incorrectly.

I am trying to use the input box to allow the user to set a value for a field. In this case, naming a copy/revision of the form in which the button was clicked. I think take the user defined value and store it in a string. I then use that string in an update statement to set the value of a field, but when I try to execute the SQL, I get a run time error stating the file could not be found.

My code is:

Dim strinput
Dim strsql

Call CreateRevisions

    strinput = InputBox(prompt:="Name of revision")
    
        If Not IsNull(strinput) Then
            'DoCmd.SetWarnings False
            strsql = " UPDATE tblJobDetails.RevisionName"
            strsql = strsql & " SET tblJobDetails.RevisionName = '" & strinput & "'"
            strsql = strsql & " WHERE (((tblJobDetails.JobID)=[Forms]![JobQuote]![JobID]));"
            'DoCmd.RunSQL strsql
            CurrentDb.Execute strsql
            'docmd.setwarnings true
        End If

Everything shows the correct values when hovering over them, but I get that weird error then I try to run the SQL statement. I tried both versions shown to run it, as well as tried debug.print, but nothing.

Am I possibly using the input box incorrectly?

 

[Minty]

I think it should simply be

strsql = " UPDATE  tblJobDetails "
 strsql =   strsql & " SET  RevisionName = '" & strinput & "'"
 strsql =   strsql & " Where JobID = " & [Forms]![JobQuote]![JobID]

Don't add the field name to the table statement.
And when you are writing SQL you can miss out a million of the brackets in simple WHERE clauses.

 

[tmyers]

It seems the slightly more versed I get in VBA, the worse I get.
I went from being bad at defining things, to having the trouble of OVER defining things.

Having making that simple change, it worked.

I do have another simple question though. When should I use docmd vs currentdb.execute? Do each have their own purpose/uses? The only time I have run into a situation where I HAD to use a specific one was when I defined my db as DAO. I was then forced to use docmd, as currentdb.execute would not work.

 

[Minty]

Generally, I always use currentdb.execute as it will give me errors if there are any but suppresses the inbuilt warnings about action queries affecting records. (So no need to set warnings off, which hides all error messages, which can be dangerous)

I'm not sure about your statement regarding DAO recordsets, I've never experienced that, so maybe something else was afoot?