Need Help With INSERT INTO In VBA For My DB

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:

Code:
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]
 
Enter Parameter Value for ExistingClientAddressesTable!File Number.....what have I done this time? :confused:

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

Code:
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?
 
I got a "Run-time error '3021': Reserved Error"
Is this because I don't currently have a record matching this ExistingClientMAX value?
 
No, you added that second criteria again. End after

"WHERE ProspectiveClientTable.[Prospective Number] = " & Me.[Prospective Number]
 
Ah, with the things that you added in red, it made it to where I don't have to have that condition right?
 
Correct; it's not a criteria, it's a value to be added to the record.
 
Now "Run-time error '3134': Syntax error in INSERT INTO statement." is being thrown for:

Code:
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:
 
Tip toeing in... Looks like a space was missing at tne of first line, I added it...

Code:
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 mentioned spaces earlier, plus try the method in post 25.
 
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!!
 
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])
 
Excellent! You can mark the thread solved by editing it and changing the prefix before the title.
 

Users who are viewing this thread

Back
Top Bottom