Solved INSERT Query not returning errors - yet not inserting into table

[Ryan142]

Hi guys

I posted earlier today for some help with INSERT Queries and thanks to the help of @Uncle Gizmo I managed to get it all good. However, I'm creating another form for inserting data, but this one doesn't seem to be working correctly. No errors are given but no change to the database? It's a bit peculiar anyway here's my code

The Function:

Private Function fAddRec(FirstName As String, LastName As String, HouseNumber As Integer, StreetName As String, City As String, PostCode As String, MobileNum As String, Email As String, CardNum As String, ExpiryDate As String, SecurityCode As Integer, Remarks As String)

    'Add Text Delimiters - Chr(34) = "
    FirstName = Chr(34) & FirstName & Chr(34)
    LastName = Chr(34) & LastName & Chr(34)
    StreetName = Chr(34) & StreetName & Chr(34)
    City = Chr(34) & City & Chr(34)
    PostCode = Chr(34) & PostCode & Chr(34)
    MobileNum = Chr(34) & MobileNum & Chr(34)
    Email = Chr(34) & Email & Chr(34)
    CardNum = Chr(34) & CardNum & Chr(34)
    Remarks = Chr(34) & Remarks & Chr(34)
    
    'Add Date Delimiters - Chr(35) = #
    ExpiryDate = Chr(35) & ExpiryDate & Chr(35)
    
    Dim strSQL0 As String
    Dim strSQL1 As String
    Dim strSQL2 As String
    
    strSQL1 = "INSERT INTO tblUserDetails(FirstName, LastName, HouseNumber, StreetName, City, PostCode, MobileNum, Email, CardNum, ExpiryDate, SecurityCode, Remarks) "
    strSQL2 = "Values(" & FirstName & ", " & LastName & ", " & HouseNumber & ", " & StreetName & ", " & City & ", " & PostCode & ", " & MobileNum & ", " & Email & ", " & CardNum & ", " & ExpiryDate & ", " & SecurityCode & ", " & Remarks & ")"
    
    strSQL0 = strSQL1 & strSQL2
    
    CurrentDb.Execute strSQL0

End Function      'fAddRec

The Execution Code:

Private Sub btnCreate_Click()

    SQLInsert = fAddRec(Me.txtForename, Me.txtSurname, Me.txtHouseNum, Me.txtStreet, Me.txtCity, Me.txtPostCode, Me.txtMobileNum, Me.txtEmail, Me.txtCardNum, Me.txtExpiry, Me.txtSecurity, Me.txtRemarks)

End Sub

Thanks, Ryan

 

[theDBguy]

Hi Ryan. Add this line at the bottom of your function and post the result from the Immediate Window.

Debug.Print strSQL0

 

[Ryan142]

Hi Ryan. Add this line at the bottom of your function and post the result from the Immediate Window.

Debug.Print strSQL0

Will do thanks

 

[Ryan142]

Hi Ryan. Add this line at the bottom of your function and post the result from the Immediate Window.

Debug.Print strSQL0

Do you mean the event function for activating the fAddRec() function or that one itself?

 

[Gasman]

Do you mean the event function for activating the fAddRec() function or that one itself?

PMFJI,
The function itself.?
The variable is not going to be available to the calling function.?

 

[Ryan142]

PMFJI,
The function itself.?
The variable is not going to be available to the calling function.?

Thanks I just wanted to check.

 

[theDBguy]

Do you mean the event function for activating the fAddRec() function or that one itself?

Hi. Basically, in the code you posted above, I am saying to insert the bolded line below:

...
    strSQL0 = strSQL1 & strSQL2

    Debug.Print strSQL0

    CurrentDb.Execute strSQL0

End Function      'fAddRec

When you run your function, it will show the query SQL statement in the Immediate Window (Ctrl+G). Copy and paste what you get there in your reply to this post.

 

[Ryan142]

Hi Ryan. Add this line at the bottom of your function and post the result from the Immediate Window.

Debug.Print strSQL0

It doesn't make return anything no window or anything?

 

[theDBguy]

It doesn't make return anything no window or anything?

Hi. You may have missed my last reply. Basically, after you run the function, hit Ctrl+G.

PS. Either that, or change the code to this instead:

MsgBox strSQL0

 

[Ryan142]

Hi. You may have missed my last reply. Basically, after you run the function, hit Ctrl+G.

Ah sure my bad will do

 

[Uncle Gizmo]

The Execution Code:

Private Sub btnCreate_Click()

    SQLInsert = fAddRec(Me.txtForename, Me.txtSurname, Me.txtHouseNum, Me.txtStreet, Me.txtCity, Me.txtPostCode, Me.txtMobileNum, Me.txtEmail, Me.txtCardNum, Me.txtExpiry, Me.txtSecurity, Me.txtRemarks)

End Sub

Try this:-

Private Sub btnCreate_Click()

   Call fAddRec(Me.txtForename, Me.txtSurname, Me.txtHouseNum, Me.txtStreet, Me.txtCity, Me.txtPostCode, Me.txtMobileNum, Me.txtEmail, Me.txtCardNum, Me.txtExpiry, Me.txtSecurity, Me.txtRemarks)

End Sub

 

[Ryan142]

Hi. You may have missed my last reply. Basically, after you run the function, hit Ctrl+G.

PS. Either that, or change the code to this instead:

MsgBox strSQL0

It just opens up the vba editor I feel like I'm doing something wrong

 

[Ryan142]

Try this:-

Private Sub btnCreate_Click()

   Call fAddRec(Me.txtForename, Me.txtSurname, Me.txtHouseNum, Me.txtStreet, Me.txtCity, Me.txtPostCode, Me.txtMobileNum, Me.txtEmail, Me.txtCardNum, Me.txtExpiry, Me.txtSecurity, Me.txtRemarks)

End Sub

No joy I'm afraid

 

[Gasman]

Make sure the Immediate window is open, select view then select that window.
Else change to MSGBOX as theDBguy suggested

 

[theDBguy]

It just opens up the vba editor I feel like I'm doing something wrong

Hi. Did you try using MsgBox strSQL0 instead?

Edit: Oops, sorry for the duplicate info.

 

[Deleted member 147267]

Never mind. I missed something.
Instead I'll say where do you expect to see the results, in a table or on a form? You're not requerying a form so likely won't be seen there. If in a table and that table is already open, you need to refresh the view.

 

[Ryan142]

Hi. You may have missed my last reply. Basically, after you run the function, hit Ctrl+G.

PS. Either that, or change the code to this instead:

MsgBox strSQL0

I changed it to MsgBox strSQL0 as you said and I got a pop up window returning this: "INSERT INTO tblUserDetails(FirstName, LastName, HouseNumber, StreetName, City, PostCode, MobileNum, Email, CardNum, ExpiryDate, SecurityCode, Remarks) Values("Test", "Name", 14, "Generic Road", "Generic City", "1AB 2BC", "12345678900", "generic@email.com", "1111222233334444", #14/05/2020#, 111, "Test")

 

[theDBguy]

I changed it to MsgBox strSQL0 as you said and I got a pop up window returning this: "INSERT INTO tblUserDetails(FirstName, LastName, HouseNumber, StreetName, City, PostCode, MobileNum, Email, CardNum, ExpiryDate, SecurityCode, Remarks) Values("Test", "Name", 14, "Generic Road", "Generic City", "1AB 2BC", "12345678900", "generic@email.com", "1111222233334444", #14/05/2020#, 111, "Test"

Hi. Thanks. If that's all you got back, it looks like you're missing a closing parens for your VALUES list.

Edit: I just took a quick look back at your code from your original post, and the closing parens seems to be there.

 

[Ryan142]

Hi. Thanks. If that's all you got back, it looks like you're missing a closing parens for your VALUES list.

Edit: I just took a quick look back at your code from your original post, and the closing parens seems to be there.

Apologies that's my bad as I had to type it out since can't highlight it. But the parens are there

 

[theDBguy]

I changed it to MsgBox strSQL0 as you said and I got a pop up window returning this: "INSERT INTO tblUserDetails(FirstName, LastName, HouseNumber, StreetName, City, PostCode, MobileNum, Email, CardNum, ExpiryDate, SecurityCode, Remarks) Values("Test", "Name", 14, "Generic Road", "Generic City", "1AB 2BC", "12345678900", "generic@email.com", "1111222233334444", #14/05/2020#, 111, "Test")

Okay, if you can get the Debug.Print to work, you could copy and paste the resulting SQL statement into the Query Designer and try to Run it from there. If there's any error with it, you should get a message. Barring that, you could try changing the last line of your code into this:

CurrentDb.Execute strSQL0, dbFailOnError

If you run it again with that new code, let us know if you get an error message.