CODEWITHSUNDEEP
vbams-access
Shadyjunior
Shadyjunior

Reputation: 457

Access: Listbox selection add to table

So my database is pretty simple, a listbox "ListItems" which contains a bunch of different products in which the user selects and enters a quantity of how many were made. Which works perfectly, however to aid the user from an efficiency standpoint a search box was added to prevent the user having to find the right product from 50 thousand items.

When the user types the top record is selected; 

Private Sub txtSearchItems_Change()
        ListItems.Requery

        If Me.ListItems.ListCount > 0 Then
            Me.ListItems.Selected(0) = True
        End If
End Sub

However, if the user does not physically click the selected line in the list box, when the following code is ran, the item will be added to the table without an item;

    Dim RST As DAO.Recordset

    Set RST = CurrentDb.OpenRecordset("select * from UptimeItems")

    If IsNull(txtQuantityProd) Or txtQuantityProd = "" Then
        MsgBox "Please enter a quantity produced.", vbExclamation
        txtQuantityProd.SetFocus
        End
    End If

    '\\Checkbox For If is Press
    Check95.Value = False

    If DLookup("[IsPress]", "Machines", "[MachineName] = Location") = -1 Then
        If DLookup("[MoreThan1Blank]", "LintelInfo", "[No] = ListItems") = -1 Then
            Check95.Value = True
        End If
    End If
    '//

    With RST
        .AddNew
        !RecordID = RecordID
        !Product = ListItems
        !Quantity = txtQuantityProd
        !MoreThan1Blank = Check95
        .Update
    End With

    RST.Close
    Set RST = Nothing

    Forms![UptimeAARecord]![UptimeItems].Requery
    Call ClearFields

enter image description here

as you can see the top record the user did not select the row in the listbox so no data was added other than the quantity, but with the "Box 100 900mm" the user did, thus the data was added.

How can I fix this?

Upvotes: 0

Views: 291

Answers (1)

Erik A
Erik A

Reputation: 32642

If your list box multi-select property is set to None, you can select the item by setting the value instead:

Private Sub txtSearchItems_Change()
    ListItems.Requery
    With Me.ListItems
        If .ListCount > 0 Then
            .Value = .Column(.BoundColumn, 0)
        End If
    End With
End Sub

This ensures the value property has a valid value when you use it in further code.

If you allow for multiple selections, your code to add entries probably requires further adjustment.

Upvotes: 1

Related Questions