Access VBA ListBox Delete Item using qryDef

See more:

MS-Access

VBA

Hi, I have created a database to be a data entry tool for several users to enter information about an event. In two places in the datbase form the user will need to have the ability to add multiple records pertaining to one entity. The user should be able to add, edit, and delete these records and then continue in the form.

I am utilizing three forms.
1. A main form called [Claims Review]with listbox, add button, delete button
2. frmAddGuideline with bound record set to dataentry, add button, cancel button
3. frmEditGuideline with bound record not set to dataentry, update button, cancel button

Record source for form 2 and 3 is a query off table called tblGuideline. tblGuideline has 5 fields:
GuidelineID
ClaimID
Guideline
Entity
year

I can add a guideline perfectly using this code:
main form

Private Sub btnAddGuideline_Click()
DoCmd.OpenForm "frmAddGuideline", acNormal
End Sub

frmAddGuideline

Private Sub btnAddGuideline_Click()
DoCmd.Close acForm, "frmAddGuideline"
'refresh listbox from subform
Forms![Claim Review]!lstbxGuideline.Requery
End Sub

Private Sub btnCancelGuideline_Click()
'Delete changes to record
If Me.Dirty Then Me.Undo
'close form
DoCmd.Close acForm, "frmAddGuideline"
End Sub

Private Sub Form_Load()
Me.ClaimID = [Forms]![Claim Review]![ClaimID]
End Sub

Can't delete record!

Private Sub btnEditGuideline_Click()
'Nothing Selected
If lstbxGuideline.ListIndex > -1 Then

'Item Selected for update/delete
strGuideline = lstbxGuideline.ListIndex(Value)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strGuideline As String
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT * FROM tblGuideline "
strSQL = strSQL & "WHERE tblGuideline.GuidelineID =" & strGuideline & " "

db.QueryDefs.Delete ("qryEditGuidelinesItems")
Set qdf = db.CreateQueryDef("qryEditGuidelinesItems", strSQL)

DoCmd.OpenForm "frmEditGuideline", acNormal

Else
MsgBox "Error", "Select an Item in the listbox to Edit", vbOKOnly
Set db = Nothing
Set qdf = Nothing

End If
End Sub

frmEditGuideline

Private Sub btnCancelEditGuideline_Click()
'Delete changes to record
If Me.Dirty Then Me.Undo
'close form
DoCmd.Close acForm, "frmEditGuideline"
End Sub

Private Sub btnUpdateGuideline_Click()
Dim db As DAO.Database
Set db = CurrentDb
If Me.CheckDeleteGuideline = True Then
'Delete Record
Dim strSQL As String
Dim strGuideline As Variant
strGuideline = [Forms]![frmEditGuideline]![GuidelineID]
strSQL = "DELETE * FROM [tblGuideline] WHERE (tblGuideline.GuidelineID = " & strGuideline & ")"
db.Execute (strSQL), dbFailOnError
End If
'Close form, save record in tbl
DoCmd.Close acForm, "frmEditGuideline"
'refresh listbox from subform
Forms![Claim Review]!lstbxGuideline.Requery
End Sub

Private Sub Form_Load()
Me.ClaimID = [Forms]![Claim Review]![ClaimID]
End Sub

My problem is the listindex is not the value of the bound control.. please help!

Thanks,
Christina