Solved Add a SQL INSERT INTO command

[cybersardinha]

Hi,

I already got a lot of help in the forums and following the advice I have normalised my tables.

I'm trying to write some vba script to add new records to a table but it's failing and I can't figure out why.

This is the code:

Dim sCertID As String
Dim sPersonID As String
Dim sCertDate As Date

sCertID = "31"
sPersonID = Me.PersonID.Value
sCertDate = Me.CertDate.Value

Debug.Print sPersonID; " + "; sCertID; " + "; sCertDate

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "INSERT INTO tblPersonCert_INSERT_INTO_SQL(PersonID, CertID, CertDate) "
strSQL2 = "Values(" & sPersonID & ", " & sCertID & ", " & sCertDate & ")"

strSQL0 = strSQL1 & strSQL2

CurrentDb.Execute strSQL0

Debug.Print "Saved"

Any Ideas
Thanks

 

[theDBguy]

Hi. When you combine string variables together, be careful to make sure you have a space character between them.

 

[cybersardinha]

Hi. When you combine string variables together, be careful to make sure you have a space character between them.

Where is missing a space? I might be going blind for looking at this but I can't seem to find a missing space xD

 

[theDBguy]

For example:

String1 = "one"
String2 = "two"
String3 = String1 & String2 = "onetwo"

 

[cybersardinha]

I have a space at the end of the strSQL1 because of that

 

[theDBguy]

I have a space at the end of the strSQL1 because of that

That's good. If you didn't edit your post, I must be going blind. But earlier, this is what I saw.

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "INSERT INTO tblPersonCert (PersonID, CertID, CertDate)"
strSQL2 = "VALUES ('1','21','01/03/2020')"

strSQL0 = strSQL1 & strSQL2

CurrentDb.Execute strSQL0

Debug.Print "Saved"

Cheers!

 

[theDBguy]

Now, other than that, simply saying your code is "failing" doesn't give us enough details to offer a solution right away. Instead, we have to spend some time to ask you questions to help clarify your situation. Providing details like an error message would help speed up the process of finding a solution to your problem.

 

[cybersardinha]

Yes I edit it because i initially copied the wrong code xD

The actual one is:

Dim sCertID As String
Dim sPersonID As String
Dim sCertDate As Date

sCertID = "31"
sPersonID = Me.PersonID.Value
sCertDate = Me.CertDate.Value

Debug.Print sPersonID; " + "; sCertID; " + "; sCertDate

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "INSERT INTO tblPersonCert_INSERT_INTO_SQL(PersonID, CertID, CertDate) "
strSQL2 = "Values(" & sPersonID & ", " & sCertID & ", " & sCertDate & ")"

strSQL0 = strSQL1 & strSQL2

CurrentDb.Execute strSQL0

Debug.Print "Saved"

 

[cybersardinha]

I don't get an error message... on this code I get the first Debug.Print other than that nothing happens

This code is inside a Button Click event

 

[theDBguy]

Yes I edit it because i initially copied the wrong code xD

The actual one is:

Dim sCertID As String
Dim sPersonID As String
Dim sCertDate As Date

sCertID = "31"
sPersonID = Me.PersonID.Value
sCertDate = Me.CertDate.Value

Debug.Print sPersonID; " + "; sCertID; " + "; sCertDate

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "INSERT INTO tblPersonCert_INSERT_INTO_SQL(PersonID, CertID, CertDate) "
strSQL2 = "Values(" & sPersonID & ", " & sCertID & ", " & sCertDate & ")"

strSQL0 = strSQL1 & strSQL2

CurrentDb.Execute strSQL0

Debug.Print "Saved"

Okay, so now I'll have to ask you. What is the result of the Debug.Print line? Were you getting an error message? If so, what does it say? Thanks.

 

[theDBguy]

I don't get an error message... on this code I get the first Debug.Print other than that nothing happens

Oops, sorry, looks like out posts got crossed.

 

[theDBguy]

I don't get an error message... on this code I get the first Debug.Print other than that nothing happens

This code is inside a Button Click event

In that case, I'd recommend stepping through your code in debug mode. Do you know how to do that?

 

[cybersardinha]

No I now a bit of vba but it's all self learned from the internet and trial and error.

I've just added a second DebugPrint after "strSQL0 = strSQL1 & strSQL2" and i get:

INSERT INTO tblPersonCert_INSERT_INTO_SQL(PersonID, CertID, CertDate) Values(3, 31, 23/03/2022)

 

[cybersardinha]

I've added the database here for reference. It's on the frmEDIT form

 

[theDBguy]

No I now a bit of vba but it's all self learned from the internet and trial and error.

I've just added a second DebugPrint after "strSQL0 = strSQL1 & strSQL2" and i get:

INSERT INTO tblPersonCert_INSERT_INTO_SQL(PersonID, CertID, CertDate) Values(3, 31, 23/03/2022)

Okay. First thing, your SQL statement is saying to add some records to a table named "tblPersonCert_INSERT_INTO_SQL;" but when I looked at your db file, I didn't see any table with that name.

 

[cybersardinha]

that might be the problem then. i got that code from https://www.niftyaccess.com/insert-into-sql-statement/

 

[theDBguy]

that might be the problem then. i got that code from https://www.niftyaccess.com/insert-into-sql-statement/

LOL. I get into trouble sometimes too when I use copy and paste.

 

[cybersardinha]

So I managed to figure it out....

I had 2 Problems one was that I wasn't referring to the table correctly and second the date wasn'tformated.

The code that works for reference is:

Dim sCertID As String
Dim sPersonID As String
Dim dCertDate As Date

sCertID = "31"
sPersonID = Me.PersonID.Value
dCertDate = Me.CertDate.Value
sCertDate = Format$(dCertDate, "\#mm\/dd\/yyyy\#")

Debug.Print sPersonID; " + "; sCertID; " + "; sCertDate

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "INSERT INTO tblPersonCert (PersonID, CertID, CertDate) "
strSQL2 = "Values(" & sPersonID & ", " & sCertID & ", " & sCertDate & ")"

strSQL0 = strSQL1 & strSQL2

CurrentDb.Execute strSQL0

Debug.Print "Saved"

Thank you so much @theDBguy

 

[theDBguy]

So I managed to figure it out....

I had 2 Problems one was that I wasn't referring to the table correctly and second the date wasn'tformated.

The code that works for reference is:

Dim sCertID As String
Dim sPersonID As String
Dim dCertDate As Date

sCertID = "31"
sPersonID = Me.PersonID.Value
dCertDate = Me.CertDate.Value
sCertDate = Format$(dCertDate, "\#mm\/dd\/yyyy\#")

Debug.Print sPersonID; " + "; sCertID; " + "; sCertDate

Dim strSQL0 As String
Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "INSERT INTO tblPersonCert (PersonID, CertID, CertDate) "
strSQL2 = "Values(" & sPersonID & ", " & sCertID & ", " & sCertDate & ")"

strSQL0 = strSQL1 & strSQL2

CurrentDb.Execute strSQL0

Debug.Print "Saved"

Thank you so much @theDBguy

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.