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"
Forms![Claim Review]!lstbxGuideline.Requery
End Sub
Private Sub btnCancelGuideline_Click()
If Me.Dirty Then Me.Undo
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()
If lstbxGuideline.ListIndex > -1 Then
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