SQL Insert statements for a combobox

[lmangano]

I am building an issue log database. I have a combo box on a form that lists names of people that could be an OWNER for a particular issue. When someone is chosen from the combobox (i.e. when they trigger the CHANGE event), I need to insert a record into a table called Person_Issue_Involvement that isn't directly related to the form. This is what I've been doing, but I don't seem to be all the way there. Any suggestions? Can this even be done?

Private Sub cboOwner_Change()

Dim strOwnerID As String
Dim dbs As Database
Dim qdf As QueryDef

strOwnerID = Me![cboOwner].Column(1)
mdteSystemDt = Now()

Set dbs = CurrentDb

mstrSQL = "INSERT INTO [Person Issue Involvement] " & _
"(PERSON_ID, " & _
" ISSUE_ID, " & _
" ISSUE_ROLE_CD, " & _
" START_DT) " & _
"VALUES " & _
"('" & strOwnerID & "', " & _
"'" & Forms!frmInput!txtIssueID & "', " & _
"'Ownr', " & _
"'" & mdteSystemDt & "') "

Set qdf = dbs.CreateQueryDef("AddOwner", mstrSQL)

End Sub

 

[Robin]

Hello lmangano,

Yes this can certainly be done by using a little DAO code in the After Update event of the Combo Box. The code would look something like this.

Private Sub cboOwner_AfterUpdate()

On Error GoTo cboOwner_AfterUpdate_Err

Dim db As Database
Dim rs As Recordset
Dim ct As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Person_Issue_Involvement", DB_OPEN_DYNASET)

ct = "fldID = " & Me.txtID

rs.FindFirst ct
If Not rs.NoMatch Then
rs.Edit
rs("FieldName1") = Me.cboOwner.Column(1)
rs.Update
Else
'Do something
End If

Exit Sub

cboOwner_AfterUpdate_Err:
MsgBox Error$, 16, "Error"
Exit Sub
End Sub

If you need more help with this e-mail me to Robin@falconer.mtx.net

Regards
Robin.