but i keep getting
Missing semicolon (;) at end of SQL statement.

Any ideas?

Private Sub cmdAddAll_Click() Dim strSQL As String Dim i As Integer Dim lngAgentID As Long Call cmdDelAll_Click strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) VALUES " For i = lstAllAgents.ListCount To 1 Step -1 lngAgentID = lstAllAgents.Column(0, i - 1) strSQL = strSQL & "(" & lngAgentID & ", " & locID & "), " Next i strSQL = Left(strSQL, Len(strSQL) - 2) & ";" CurrentDb.Execute strSQL Call populateLocsAgents Call populateAllAgentsEnd Sub

INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc)
SELECT 488 AS fldAGent, 0 AS fldLoc
UNION ALL
SELECT 274 AS fldAGent, 0 AS fldLoc
UNION ALL
SELECT 315 AS fldAGent, 0 AS fldLoc
UNION ALL et cetera

0

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) "
For i = lstAllAgents.ListCount To 1 Step -1
lngAgentID = lstAllAgents.Column(0, i - 1)
strSQL = strSQL & " select " & lngAgentID & ", " & locID
if i <> 1 then strSQL = strSQL & " union all "
Next i

you end with 1 not 0... anyways, see what is the result...

0

APD TorontoAuthor Commented:

Sorry, long day, i will never be 0

now i get:
Syntax error (missing operator) in query expression '0 UNION ALL SELECT 274'.

my string is:
?STRSQL
INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) SELECT 488, 0 UNION ALL SELECT 274, 0 UNION ALL SELECT 315, 0 ... UNION ALL SELECT 3, 0

strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) ("
For i = lstAllAgents.ListCount To 1 Step -1
lngAgentID = lstAllAgents.Column(0, i - 1)
strSQL = strSQL & " select " & lngAgentID & ", " & locID
if i <> 1 then strSQL = strSQL & " union all " else strSQL = strSQL & ")"
' strSQL = strSQL & iif(i=1, ")", " union all ")
Next i

0

APD TorontoAuthor Commented:

INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) (SELECT 488, 0 UNION ALL SELECT 274, 0 ...UNION ALL SELECT 3, 0)

Syntax error in INSERT INTO statement.

0

APD TorontoAuthor Commented:

wgat about INSERT INTO...

fldAgent = SELECT fldAgentID WHERE fldArchived = False

ok :) this is getting strange but I tried with a local access

insert into roles (roleid, rolename)
select a,b from (
select 10 as a, 'A' as b from dual
union all select 20, 'B' from dual
union all select 30,'C' from dual
)

works fine... but I have a dual table with one column and one record dual(id) : 1 record = 1
so, add a table dual, with one column(int) and add one row into it

then try this and see if you get

strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) select a,b from ("
For i = lstAllAgents.ListCount To 1 Step -1
lngAgentID = lstAllAgents.Column(0, i - 1)
strSQL = strSQL & iif(i=1, " select " & lngAgentID & " as a, " & locID & " as b from dual ", " union all select " & lngAgentID & ", " & locID & " from dual ")
Next i

you should get:

INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) SELECT 488 as a, 0 as b from dual UNION ALL SELECT 274, 0 from dual ...UNION ALL SELECT 3, 0 from dual)

strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) select a,b from ("
For i = lstAllAgents.ListCount To 1 Step -1
lngAgentID = lstAllAgents.Column(0, i - 1)
strSQL = strSQL & iif(i=1, " select " & lngAgentID & " as a, " & locID & " as b from dual ", " union all select " & lngAgentID & ", " & locID & " from dual)")
Next i

insert into roles (roleid, rolename)
select a,b from (
select 10 as a, 'A' as b from dual
union all select 20, 'B' from dual
union all select 30,'C' from dual
)

0

APD TorontoAuthor Commented:

are you able to send me a sample? also, do i anbsolutely need dual?

0

APD TorontoAuthor Commented:

INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) select a,b from ( union all select 488, 162 from dual) union all select 274, 162 from dual) ... union all select 410, 162 from dual) select 3 as a, 162 as b from dual

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:

Or even simpler:

For i = lstAllAgents.ListCount To 1 Step -1 CurrentDB.Execute "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) VALUES(" & lstAllAgents.Column(0, i - 1) & "," & LocID & ")"Next i

For i = lstAllAgents.ListCount To 1 Step -1

lngAgentID = lstAllAgents.Column(0, i - 1)

strSQL = "INSERT INTO tblAgentLocAssoc (fldAgent, fldLoc) VALUES (" & lngAgentID & ", " & locID & "), "

CurrentDb.Execute strSQL

Next i