SQL insert into query linked to a list box control on a form

Question

I would like to build an append query which takes a value selected in a list box (lstSSCItaly) and joins it to the field 'Team' in table 'tblTeams'. Upon doing so, I would like the query to link the two (the control and the table) and insert the value in the field 'team' in the table 'tblTeams' into the field 'Group' in table 'tblDependencies'. At present, I am getting a run time error 3134 'syntax error with INSERT INTO. My query is as follows:

Private Sub lstSSCItaly_DblClick(Cancel As Integer)

Dim sql_GET As String

sql_GET = "INSERT INTO tblDependencies01(group) SELECT team FROM tblteams WHERE '" & lstSSCItaly & "' = team"
Application.DoCmd.RunSQL (sql_GET)

End Sub

you are locking for insert or update? you cant use where in insert statement — Khurram Ali, Dec 24 '14 at 14:28
In short, i would like to create a new record in table tblDependencies01 and paste the value in my list box (lstSSCItaly) to the field 'Group' in the new record created. — Marchese Il Chihuahua, Dec 24 '14 at 14:37
Include detail about your tblDependencies01 i mean its columns also show your code for getting values from list box or something else — Khurram Ali, Dec 24 '14 at 14:41
Sorry I am not sure what you mean. Within table 'tblDependencies01' there is a column called 'Group'. Upon double clicking on my listbox 'lstSSCItaly' i would like the following to happen: (1) a new record is created to table 'tblDependencies01' and (2) the value selected in list box 'lstSSCItaly' gets appended to the field 'Group' in table 'tblDependencies01'. — Marchese Il Chihuahua, Dec 24 '14 at 14:43
@i do not understand your second poing what do you mean by `append to the field 'group'` — Khurram Ali, Dec 24 '14 at 15:08
I would like the value in the list box ('lstSSCItaly') to be added to the new record in the field 'group' — Marchese Il Chihuahua, Dec 24 '14 at 15:10
The SQL doesn't seem right. So make a new Access query (not in VBA) with SQL `INSERT INTO tblDependencies01(group) SELECT team FROM tblteams WHERE team = 'Colombia';`. Fiddle with that until you get what you want, then migrate to VBA. http://stackoverflow.com/a/1099570/122139 — Smandoli, Dec 24 '14 at 16:56

score 0 · Accepted Answer · edited Dec 24 '14 at 16:52

0

Insert statements have to be in the format:

INSERT INTO table(field1, field2...) VALUES("a", "b",...)

or

INSERT INTO table (SELECT FROM table2 where something = 1)

Set is only used in updates.

It's difficult to come up with the exact syntax without more information on the table structures, relationships and data.

You could do the following but not sure if its what you're after:

INSERT INTO tblDependencies01(group) 
SELECT '" & lstSSCItaly & "' + team FROM tblteams

edited Dec 24 '14 at 16:52

Smandoli

6,919
3
49
83

answered Dec 24 '14 at 14:43

Mark Davies

736
1
7
26

I would have done a comment but my reputation is not high enough. Add more details and I can probably come up with a better solution – Mark Davies Dec 24 '14 at 14:44
Mark, i tried the following but am getting the same syntax error. Is it possible that the '+' sign you put is correct? sql_GET = "INSERT INTO tblDependencies01(group) SELECT '" & lstSSCItaly & "' + team FROM tblteams" Application.DoCmd.RunSQL (sql_GET) – Marchese Il Chihuahua Dec 24 '14 at 14:48
This is not related to the problem – Khurram Ali Dec 24 '14 at 15:10
It's been a while since I've done access but just tried a simple concatination and this worked for me: INSERT INTO tblDependencies01 ( [group] ) SELECT "xxxx"+"yyyy" AS Expr1; The group bit will need square brackets around to qualify it (its a reserved word so probably should name it something else) – Mark Davies Dec 24 '14 at 15:58

SQL insert into query linked to a list box control on a form

1 Answers1