Insert Into statement not inserting

[david123]

I'm using the following code to insert data into two fields (Position and Courses for Position) into a table (PositionCoursestbl) from two list boxes.

However, when I make my selection from the listboxes and run the code, nothing happens

Here's the code:

For Each varItem In Me.List8.ItemsSelected
strItem = strItem & ",'" & Me.List8.ItemData(varItem) & "'"
Next varItem
If Len(strItem) <> 0 Then
strItem = Right(strItem, Len(strItem) - 1)
strItem = (" & strItem & ")
End If

For Each varItem In Me.List4.ItemsSelected
strLocation = strLocation & ",'" & Me.List4.ItemData(varItem) & "'"
Next varItem
If Len(strLocation) <> 0 Then
strLocation = Right(strLocation, Len(strLocation) - 1)
strLocation = (" & strLocation & ")
End If

strSQL = "INSERT INTO PositionCoursestbl ([Position], [Courses for Position])" & _
"VALUES ('" & strItem & "', '" & strLocation & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
Response = acDataErrAdded
End Sub


Any help would be great

Thanks,
David

 

[DCrake]

Are you trying to store multi values into a single field. That what it looks like. If that is the case then you need to normalise your tables and have a parent child between them to store multi values.

David

 

[david123]

Are you trying to store multi values into a single field. That what it looks like. If that is the case then you need to normalise your tables and have a parent child between them to store multi values.

David

I have a Positions table and a Courses table between the PositionsCoursestbl, so I don't know if normalization is the problem.

There are two list boxes on my form, one showing the data from the Positions table and one from the Courses table. What I'd like to do is have the user select the Positions they'd like to add courses to via the Courses listbox. This data would be stored in the PositionsCoursestbl

 

[DCrake]

If you comment out the set warnings line what error is occuring?

 

[david123]

If you comment out the set warnings line what error is occuring?

It's saying "Microsoft Access can't append all records in the append query" and it says "...and it didn't add 1 record(s) due to the table due to key violations"

 

[DCrake]

This usually implies that you have duplicate keys PK or FK in the insert. If you try selecting 1 item from 1 list and none from the other does it work?

Try selecting 1 from each list does it work?
Try selecting 2 form each list does it work?

You need to think that this type of thing will be going on in real time so you need to plan for this. also try and select 1 item that you know already exists. does this error?

Test, Test, Test, Deploy

David

 

[david123]

This usually implies that you have duplicate keys PK or FK in the insert. If you try selecting 1 item from 1 list and none from the other does it work?

Try selecting 1 from each list does it work?
Try selecting 2 form each list does it work?

You need to think that this type of thing will be going on in real time so you need to plan for this. also try and select 1 item that you know already exists. does this error?

Test, Test, Test, Deploy

David

I removed the enforce referential integrity on the two relationships between the three tables, picked one item from each list and ran it. it worked, but it inserted "strItem" and "strLocation" into the Position and Courses Required for Position fields, respectively. The reason the error was coming up was because there was no "strItem" and "strLocation" in the positions and courses tables.