inserting into dynamic table

[anasazii]

Hello everyone. I need help with an insert statement that is erroring on me, and I cannot figure out why.

First, I have created two tables in the code:

dbs.Execute "CREATE TABLE explic(ssn TEXT, type TEXT, st TEXT, num TEXT, exp DATETIME);"

dbs.Execute "CREATE TABLE personal(ssn TEXT, fn TEXT, ln TEXT, st1 TEXT, st2 TEXT, city TEXT, state TEXT, zip TEXT);"

When I insert into the first table, explic, everything goes ok, :

strSql = "Insert into explic VALUES('" & rst.Fields("ssn&quot & "'" & _
", 'State Medical License', '" & rst.Fields("med_license_state&quot & "'" & _
", '" & rst.Fields("med_license_number&quot & "', '" & rst.Fields("license_exp&quot & "');"
DoCmd.RunSQL (strSql)

However, when I try inserting into the other table, I get a compilation error. The weird thing is I have typed the statement twice and I am getting errors in different places in the statements. The uncommented code that is giving me an error is:

strSql = "Insert into personal VALUES('" & rst.Fields("ssn&quot & _
"', '" & rst.Fields("first_name&quot & "'" & _
", '" & rst.Fields("last_name&quot & "', '" & rst.Fields("street1&quot & _
"', '" & rstFields("street2&quot & "'" & _
", '" & rst.Fields("city&quot & "', '" & rst.Fields("state&quot & _
"', '" & rst.Fields("zip&quot & "'); "

It is giving me a "Sub or Function not defined" error at rst.Fields("street1&quot.
On the other (commented out) code that I have above it, typed pretty much the same, it is giving me the "sub or function not defined" error at rst.Fields("ssn&quot when I uncomment it.

Can anyone give me any insight as to what is causing this error? Is there something that I'm missing somewhere like a quote or anything like that? If you need any more info or code posted, let me know!

Thanks for any help that you can provide!!!
Janel

 

[dvrobin]

You had me stumped for a minute. When you get the error, it's highlighting the wrong item. The offending item is actually "rstFields("street2&quot ". Notice that you're missing a "." between rst and Fields.
Dave Robinder, MCSD
Programmer
Colorado DataScapes
Colorado Springs, CO
(719) 328-1111

 

[anasazii]

Dave,
Oh my god!!!
THANK YOU SO MUCH!!!!!!!
It always helps to have another pair of eyes!


Janel