Solved Sql statement syntax with variables (1 Viewer)

ClaraBarton

Registered User.
Local time
Yesterday, 23:08
Joined
Oct 14, 2019
Messages
427
Good morning. I went to bed with this problem and received no enlightenment overnight. Why doesn't this work:
Code:
strSql = "INSERT INTO tblItemDetail ([DocNo], [fDrawerID], [fItemID]) " & _
                "VALUES (" & intDoc & "," & intDrawer & "," & intNewID & ");"
With CurrentDb
            .Execute strSql
End With

?strSql
INSERT INTO tblItemDetail ([DocNo], [fDrawerID], [fItemID]) VALUES (0,0,0);
?intDrawer
 1
?intDoc
 2
?intNewID
 188
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:08
Joined
May 7, 2009
Messages
19,169
are there autonumber fields in the query?
autonumber seeds starts at 1 and increment by 1?

sorry for the answer.
all those code from immediate window?

did you set the variables before you construct strSQL?
 

ClaraBarton

Registered User.
Local time
Yesterday, 23:08
Joined
Oct 14, 2019
Messages
427
I want this to create a new record so there would be an autonumber of DetailID. Is that the problem?
I use a recordset to create a new record and this for the detail tied to ItemID
Code:
intDrawer = PopupValue("popDrawer", "lstLocation")
Set rst = Me.RecordsetClone
strFind = "[InUse] = False And [fDrawerID] =" & intDrawer
    
    If rst.RecordCount <> 0 Then
        rst.MoveLast
        rst.MoveFirst
    End If
    
    rst.FindFirst strFind
        If Not rst.NoMatch Then
           Me.Bookmark = rst.Bookmark
        Else
            'If there are no empty records, tack them on the end
            intDoc = Nz(DMax("DocNo", "qryItems1", "[fDrawerID]=" & intDrawer), 0)
            intDoc = intDoc + 1
'            Me.fDrawerID = intDrawer
                      
                With rst
                    .AddNew
                       ![Modified] = Date
                       intNewID = ![ItemID]
                    .Update
                End With
            rst.Bookmark = rst.LastModified
        End If
      
       With CurrentDb
            .Execute strSql
       End With
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:08
Joined
May 7, 2009
Messages
19,169
you use findfirst and if it did not find anything it will be on the EOF.
but you have
intNewID = ![ItemID]
!ItemID here can be 0, since you there is no match.

there is also conflict here.
you are setting this:

Me.fDrawerID = intDrawer (editing the Form)

while Adding new record (through vba)?
you add the code inside .Addnew.

another review:

after .addnew, you also have .execute strSQL (is this also an append?)

if you are working with form, avoid using SQL statement since
you have an In-memory copy of your table (same table).
you do it all in form or vba using it's recordset(clone).
 
Last edited:

ClaraBarton

Registered User.
Local time
Yesterday, 23:08
Joined
Oct 14, 2019
Messages
427
sorry... that line is commented out. Should have edited my work better
 

ClaraBarton

Registered User.
Local time
Yesterday, 23:08
Joined
Oct 14, 2019
Messages
427
My variables all have values as shown in the first post. The Sql statement is to add a new record to the Detail table after the .Add New puts one in the Item table.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:08
Joined
May 7, 2009
Messages
19,169
intNewID = ![ItemID]
you are in .EOF here, so ![ItemID] can be 0.
maybe change it to:
Code:
                With rst
                    .AddNew
                       ![Modified] = Date
                    .Update
                    .bookmark = .lastmodified
                    intNewID = ![ItemID]
                End With
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:08
Joined
May 7, 2009
Messages
19,169
My variables all have values as shown in the first post.
you already debug.print the strSql and it shows the Values (0, 0, 0).
i think you need to Re-build strSQL again, prior to .Execute.

when you build strSQL on the early of the code, the only way
to get it updated is to re-build it.
 

ClaraBarton

Registered User.
Local time
Yesterday, 23:08
Joined
Oct 14, 2019
Messages
427
YOU WERE SO RIGHT! I thought the string was built when it executed. It WORKS! Thank you so much.
 

Users who are viewing this thread

Top Bottom