Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    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)"
    CurrentDb.Execute bas
    End Function

    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    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
    Me.lboBulbID.SetFocus
    Exit Sub
    End If

    ' Loop through selected items
    For Each varItem In Me.lboBulbID.ItemsSelected
    strWhere = strWhere & "," & Me.lboBulbID.ItemData(varItem)
    Next 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
    MsgBox strSQL

    ' Execute SQL string
    CurrentDb.Execute strSQL
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •