使用qryDef访问VBA ListBox删除项目 [英] Access VBA ListBox Delete Item using qryDef
问题描述
您好，我创建了一个数据库作为数据输入工具，以供多个用户输入有关事件的信息.在datbase形式的两个位置，用户将需要具有添加与一个实体有关的多个记录的能力.用户应该能够添加，编辑和删除这些记录，然后继续表格.
我正在使用三种形式.
1.一个名为[Claims Review]的主窗体，带有列表框，添加按钮，删除按钮
2.将绑定记录设置为dataentry的frmAddGuideline，添加按钮，取消按钮
3.绑定记录未设置为dataentry的frmEditGuideline，更新按钮，取消按钮
表格2和表格3的记录源是一个名为tblGuideline的查询表. tblGuideline具有5个字段:
GuidelineID
ClaimID
准则
实体
年
我可以使用以下代码完美地添加准则:
主要形式
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
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
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
我的问题是listindex不是绑定控件的值.请帮助！
谢谢，
Christina
My problem is the listindex is not the value of the bound control.. please help!
Thanks,
Christina
推荐答案
自上次使用Access以来已有多年，但您是否尝试过使用:
Hi,
Years since I last used Access, but have you tried using:
strGuideline = lstbxGuideline.ItemData(lstbxGuideline.ListIndex)
如果列表框是多选版本，那么您将不得不遍历所有项目并检查Selected 属性是否为true.
If the list box is a multiselect version then you would have to loop through all items and check if the Selected property is true.
strGuideline = lstbxGuideline.ItemData(lstbxGuideline.ListIndex + 1 )
表单记录源设置为qdef"qryEditGuidelinesItems"
哇！
strGuideline = lstbxGuideline.ItemData(lstbxGuideline.ListIndex + 1)
form recordsource set to qdef "qryEditGuidelinesItems"
whew!
这篇关于使用qryDef访问VBA ListBox删除项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
