Need Help With INSERT INTO In VBA For My DB

[pbaldy]

Sorry, I didn't look at the SQL statements closely enough. You're inserting a new record, and I think you want the max ID you got at the beginning as one of the field values (right?). More like this, additions in red:

PTEAddressInfo = "INSERT INTO ExistingClientAddressesTable([COLOR="Red"][File Number],[/COLOR] [Street],[City],[State/Province],[Country],[Zip Code])" & _
                          "SELECT [COLOR="red"]" & ExistingClientMAX  & ",[/COLOR] Address, City, State, Country, ZIP " & _
                          "FROM [ProspectiveClientTable] " & _
                          "WHERE ProspectiveClientTable.[Prospective Number] = " & Me.[Prospective Number]

 

[mboyde]

Enter Parameter Value for ExistingClientAddressesTable!File Number.....what have I done this time?

My RE-updated code for the lines throwing the parameter inquiry.

PTEAddressInfo = "INSERT INTO ExistingClientAddressesTable([File Number],[Street],[City],[State/Province],[Country],[Zip Code])" & _
                                    "SELECT " & ExistingClientMAX & ",Address, City, State, Country, ZIP " & _
                                    "FROM [ProspectiveClientTable] " & _
                                    "WHERE ProspectiveClientTable.[Prospective Number] = " & Me.[Prospective Number] & "" & _
                                    "AND ExistingClientAddressesTable![File Number] = " & ExistingClientMAX

Is it because I used the Exclamation instead of the Period?

 

[mboyde]

I got a "Run-time error '3021': Reserved Error"
Is this because I don't currently have a record matching this ExistingClientMAX value?

 

[pbaldy]

No, you added that second criteria again. End after

"WHERE ProspectiveClientTable.[Prospective Number] = " & Me.[Prospective Number]

 

[pbaldy]

Here's a commonly used method to debug SQL in code:

http://www.baldyweb.com/ImmediateWindow.htm

 

[mboyde]

Ah, with the things that you added in red, it made it to where I don't have to have that condition right?

 

[pbaldy]

Correct; it's not a criteria, it's a value to be added to the record.

 

[mboyde]

Now "Run-time error '3134': Syntax error in INSERT INTO statement." is being thrown for:

PTECallerContactInfo = "INSERT INTO ExistingClientContactInformationTable([File Number],[Relationship To Client],[Last Name],[First Name],[Home Number],[Cell Number],[Email]" & _
                                            "SELECT " & ExistingClientMAX & ",[Caller's Relationship to FN],[Caller's Last Name],[Caller's First Name],[Caller's Home Number],[Caller's Cell Number],[Caller's Email]" & _
                                            "FROM [ProspectiveClientTable]" & _
                                            "WHERE ProspectiveClientTable.[Prospective Number] = " & Me.[Prospective Number] & " ;"
DoCmd.RunSQL PTECallerContactInfo

I've checked and everything looks okay to me. Maybe I overlooked something? :banghead:

 

[GinaWhipp]

Tip toeing in... Looks like a space was missing at tne of first line, I added it...

PTECallerContactInfo = "INSERT INTO ExistingClientContactInformationTable([File Number],[Relationship To Client],[Last Name],[First Name],[Home Number],[Cell Number],[Email] " & _
"SELECT " & ExistingClientMAX & ",[Caller's Relationship to FN],[Caller's Last Name],[Caller's First Name],[Caller's Home Number],[Caller's Cell Number],[Caller's Email] " & _
"FROM [ProspectiveClientTable]" & _
"WHERE ProspectiveClientTable.[Prospective Number] = " & Me.[Prospective Number] & " ;"
DoCmd.RunSQL PTECallerContactInfo

 

[pbaldy]

I mentioned spaces earlier, plus try the method in post 25.

 

[mboyde]

I'm going to take an 8-hour nap, and I'll be back to debug/give thanks/show the issue is resolved in the morning! Thank you for all your help! So far, so good though!!

 

[mboyde]

So, everything is resolved now! I feel more confident on how SQL works in VBA! Thank you SOOOOOO much!! This question is now RESOLVED! (I don't know how to mark that [if that's a thing])

 

[pbaldy]

Excellent! You can mark the thread solved by editing it and changing the prefix before the title.

 

[mboyde]

Got it! Thank you yet again!