append record to table with two 1º keys with listbox

[badinjector]

I am trying to add records to a table that has two primary keys. There are several tables in the db, but the ones of interest are:
tblCompetencies with compid and comptext as fields
tblCourses with courseid and coursename as fields
tblmatched with compid and courseid as key fields
I'm new to access and i have tried several things. Ideally, for quick data entry i would like to have a list box populated with courses and the user can select multiple courses, then click a command button that will append the record(s) (a competency paired with a course, or several pairs added at the same time). The form has a dropdown for the competency. I'm a little confused as how to do this.Some thoughts I had:

' Loop through the selected items in the list box and build a text string
If Me!Lstcourses.ItemsSelected.Count > 0 Then
For Each varItem In Me!Lstcourses.ItemsSelected
strCriteria = strCriteria & "tblcourses.courseid = " & Chr(34) & Me!Lstcourses.ItemData(varItem) & Chr(34) & "OR "

Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 4)
' ok i think i'm setting the strCriteria above to tblcourses.courseid="#selected" or is this setting it to text??? i did get a type mismatch trying variations of this

Else

MsgBox "Select An Item From The List First"

Exit Sub

End If

strSQL = "INSERT INTO tblmatched [courseid, compid]
ok thats all i get thats of any use..
any help greatly appreciated

 

[Remou]

You want to select a number of courses and insert these along with one competency into tblmatched, is that correct?

If so, you will need to run the SQL in the loop through items selected. This is how I would see it:

[tt]For Each itm ...
strSQL = "INSERT INTO tblmatched (courseid, compid) "
& "Values ('" _
& Me!lstCourses.ItemData(varItem) & "','" _
& Me!CompetencyControlName & "')"
db.Execute ... [/tt]

I have included single quotes (') because you imply that they are needed. If courseid and compid are numeric, the single quotes need to be removed.

 

[badinjector]

ok remou..thats seems to work for the most part.i have some small annoyances.Here is the code i have entered;(apologies in advance for my lack of vba)I have collected this from a variety of sources hoping to make it work.I put the fields and command button on an unbound form.

Private Sub Command9_Click()
On Error GoTo Err_Handler
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("qrymatched")
' Loop through the selected items in the list box and build a text string

If Me!Lstcourses.ItemsSelected.Count > 0 Then

For Each varItem In Me!Lstcourses.ItemsSelected
strCriteria = strCriteria & "tblcourses.courseid = " & Chr(34) & Me!Lstcourses.ItemData(varItem) & Chr(34) & "OR "

strCriteria = Left(strCriteria, Len(strCriteria) - 4)

strSQL = "INSERT INTO tblmatched (courseid, compid) " & "Values ('" & Me!Lstcourses.ItemData(varItem) & "','" & Me!cbocomp & "')"

Next varItem

Else

MsgBox "Must Select An Item From The List First"

Exit Sub

End If


' Apply the new SQL statement to the query
qdf.SQL = strSQL
' Open the query
DoCmd.SetWarnings False
DoCmd.OpenQuery "qrymatched"
DoCmd.SetWarnings True
' Empty the memory
Set db = Nothing
Set qdf = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Handler

End Sub

The records do get added to the table, except if I select more than one course from the list and assign it to the competency from the drop down and if one of those course-comp combinations already exist, none of the course-comp pairs will get added, but i dont get an error message (i think i screwed up the error msg handling after too many cut and paste operations)saying what happened.The error msg will appear if i run the append query on its own. Also, the table "tblmatched" doesnt seem to give updated data unless it is closed and reopened. I'm not sure if my form should be bound or not.Unbound i cant modify any records. I put a subform on the main form to show those records already entered, but it doesnt refresh after adding new records either.Sorry to be a pain.

 

[Remou]

There is a whole bunch of stuff here that you do not need and some stuff that you need is missing. Here is a suggestion for checking if the pair exists.

Private Sub Command9_Click()
On Error GoTo Err_Handler
' Declare variables
Dim db As DAO.Database
Dim varItem As Variant
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()

' Loop through the selected items in the list box and build a text string

 If Me!Lstcourses.ItemsSelected.Count > 0 Then

     For Each varItem In Me!Lstcourses.ItemsSelected
        If IsNull(DlookUp("courseid","tblmatched","courseid='" _
           & Me!Lstcourses.ItemData(varItem) & "' And compid='" _
           & Me!cbocomp & "'")) Then

           strSQL = "INSERT INTO tblmatched (courseid, compid) " _
           & "Values ('" & Me!Lstcourses.ItemData(varItem) _
           &  "','" & Me!cbocomp & "')"
    
           db.Execute strSQL 
        Else
           Msgox "Already exists"
        End If
      Next varItem

 Else

     MsgBox "Must Select An Item From The List First"

Exit Sub

End If

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description
Resume Exit_Handler

End Sub

 

[badinjector]

Wow thats awesome.I had some trouble with the dlookup statement (type mismatch) so i tried this(i changed some field names/tables in a redesign )

If IsNull(DLookup("courseid", "tblmatchedoutcomes", "courseid=" & Me!lstcourses.ItemData(varItem) & " And compmodoutcomeid=" & Me!cbocompetencies & "")) Then

and it seems to work (dlookup returns numbers now instead of strings i think) I will try to put an explanation in the message box to the tune of "You have already entered 'compid'(i will try to put the related compid text here) with the course 'courseid' (as course text)"

Thanks so much for the help.I worked on this for many hours and have learned a lot from your posts.