Results 1 to 2 of 2
2004-12-24, 15:50 #1
- Join Date
- Feb 2002
- Thanked 0 Times in 0 Posts
User friendly parameters (Access 2000)
Edited by HansV to remove duplicated text and unnecessary line ends.
How can i make a user friendly way of entering the parameters in the code for the items to be deleted? I have in mind the following code:
Public Function DeleteBulbs()
Dim bas As String
bas = " delete * from tblBulbs where Bulbid In (2,4,20)"
I would prefer the user to choose by himself which bulbs to delete, for example 2,4,20. I imagine the possibility of building a special table, and somehow to tie up the figures entered in this table ,on a form,with my module. Is there a prefered way to do it in Access?
2004-12-24, 16:13 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: User friendly parameters (Access 2000)
You could use a multi-select list box on a form
- Create a new form; leave its Record Source blank.
- Place a list box on the form, and name it lboBulbID
- Set the Row Source Type property of the list box to Table/Query.
- Set the Row Source to
SELECT DISTINCT BulbID FROM tblBulbs
- Set the Multi Select property to Single or Extended. Single means that you can click items one by one; Extended means that you can select items the same way you can select files in Windows Explorer, using the mouse in combination with the Shift and Ctrl keys.
- Put a command button on the form and name it cmdDelete.
- Set the caption of the button to "Delete selected items" or something similar.
- Create an On Click event procedure for the button:
Private Sub cmdDelete_Click()
Dim strWhere As String
Dim strSQL As String
Dim varItem As Variant
' Check that something has been selected
If Me.lboBulbID.ItemsSelected.Count = 0 Then
MsgBox "Please select one or more items.", vbExclamation
' Loop through selected items
For Each varItem In Me.lboBulbID.ItemsSelected
strWhere = strWhere & "," & Me.lboBulbID.ItemData(varItem)
' Get rid of first comma
strWhere = Mid(strWhere, 2)
' SQL string
strSQL = "DELETE * FROM tblBulbs WHERE BulbID In (" & strWhere & ")"
' For testing only: display SQL string
' Execute SQL string