how to fix enter parameter values in the Insert into statement?

  • Thread starter Thread starter Fuzuy
  • Start date Start date
F

Fuzuy

Hi, All:

I am using insert into statement to populate a Table, however, the
table has to use one field change with the change of multiselect
listbox value, the other would be an ID from that Form.

I have a difficulty to pass these two fields change through a for loop
to Insert Into Statement, anyone has a solution to help this out???

Thanks in advance!!!!

Coding is listed in the following , hope my question is clear.

( the IID, tem both are correct in the for loop through, but I get a
prompt each time of loop "enter parameter value" for tem and IID
separately, at the Insert Into Statement)

For Each vItm In Me!Dwgs.ItemsSelected

IID = Me![ID#]
tem = [Forms]![frmdwg_spe_cal_dsheet]!Dwgs.ItemData(vItm)

ssq = "INSERT INTO try ( Dwgs_no, [ID#], [Doc#], title, Disc1, Disc2,
Disc3, Disc4, critreq ) "
ssq = ssq & " SELECT tem AS Dwgs_no, [ID#], [Doc#], title, Disc1,
Disc2, Disc3, Disc4, critreq "
ssq = ssq & " FROM Dwg_Spe_Cal_Dsheet WHERE Dwg_Spe_Cal_Dsheet.
[ID# ]= IID"

DoCmd.RunSQL ssq

Next vItm
 
Hi Fuzuy,

tem and IID are variables (containing values that you want to use), so you
need to build the SQL string with them as variables concatenated into the
string, rather than as literals within the string. Try:
ssq = ssq & " SELECT '" & tem & "' AS Dwgs_no, [ID#], [Doc#], title,
Disc1, Disc2, Disc3, Disc4, critreq "
for the second line (assuming that tem is a string - if it's a number, omit
the single-quote delimiters; also, if tem itself contains a single-quote
character, you'll need to use a pair of double-quote characters as the
delimiter, rather than a single-quote character), and
ssq = ssq & " FROM Dwg_Spe_Cal_Dsheet WHERE Dwg_Spe_Cal_Dsheet.[ID# ]=
" & IID & ";"
for the third line (assuming that IID is a number - if it's text, add
single-quote delimiters). I've also included a closing semicolon here; it's
not strictly necessary, but it won't hurt, and it gives the place to put the
text delimiter if it's needed.

HTH,

Rob
 
Back
Top