"franc sutherland" <franc.sutherland@googlemail.comwrote in message
news:26ca613c-aa5c-474d-bcde-6cb89c8ba5f0@k30g2000hse.googlegroups.com...
Quote:
Hello,
>
I am using Access 2003.
>
Is it possible to use string variables in the INSERT INTO statement?
>
I am using the INSERT INTO statement to add a long list of contacts to
a group by looping through the recordset (based on a linked
spreadsheet, 'tbl_group_import') and using the INSERT INTO statement
on each loop. I am using the rst.Fields.Item("FieldName") method to
assign a value to the string variables. However, when I run the code,
it prompts me for the variable values.
>
I am executing the SQL using DoCmd.RunSQL.
>
I would really appreciate any assistance.
>
Thanks,
>
Franc.
>
Here is the code;
>
Dim contact As Recordset
>
Dim title As String
Dim first_name As String
Dim insert_sql As String
Dim group_id As Integer
>
group_id = Forms![frm_group_view]![group_id]
>
Set contact = CurrentDb.OpenRecordset("tbl_group_import")
>
contact.MoveFirst
>
Do Until contact.EOF
>
title = contact.Fields.Item("contact_title")
first_name = contact.Fields.Item("contact_first_name")
>
insert_sql = "INSERT INTO
qry_group_member(group_member_group_id,contact_tit le,contact_first_name)"
& _
"VALUES (group_id,title,first_name)"
>
DoCmd.RunSQL insert_sql
>
contact.MoveNext
>
Loop
With your current code you will need to pass the values to the query.
Assuming there are no special characters in title or first_name.
"VALUES(" & group_id & ",""" & title & """,""" & first_name & """)"
Other choices
1) Save your query, converting the variables to parameters. Then pass the
values though the parameters.
2) Link to or import the spreadsheet and modify the query to append all the
records at once.
3) Open a recordset to your table to insert the records.
