asked on

MS Access Checkout Form

I'm working on a database that will track boxes in, out, and back into a warehouse - much like a library. I need a form that will check the boxes out. Right now, I have a combo box (cmbBox) that will allow users to select boxes that are currently available ('In' status), a command button (cmdAdd) that will add the selected item from cmbBox to a text box (txtBoxesAwaitingCheckout), another combo box (cmbCheckOutTo) that lists employees to whom the box will be checked out and finally a command button (cmdCheckOut) that actually runs the SQL update statement to check the boxes out ('Out' status). This works, however since I'm forced to use the BoxID to identify the box in the tblBoxes table for checkout, the user will see this number instead of the BoxNumber field with which they'll be familiar. I was considering adding the BoxNumber field to the txtBoxesAwaitingCheckout event, but then the SQL statement that I've created will no longer work. I was also considering using a subform, but I wanted some feedback before I actually went to the trouble of creating one. Am I on the right track here?

Thanks,

Gary

Private Sub cmdCheckOut_Click()
    Dim strSQL As String
          
    'Update box status in tblBoxes for selected boxes to show "Out"
    strSQL = "UPDATE tblBoxes" & vbCrLf & _
            "SET Status = 'Out'" & vbCrLf & _
            "WHERE BoxID IN (" & Me.txtBoxesAwaitingCheckout & ")"
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    Debug.Print strSQL
    
    Me.txtBoxesAwaitingCheckout.Value = ""
    cmbCheckOutTo.Value = ""
    cmdCheckOut.Enabled = False
    Me.Requery
    Me.Refresh
End Sub
 
Private Sub cmdAdd_Click()
    If IsNull(Me.txtBoxesAwaitingCheckout) Or Me.txtBoxesAwaitingCheckout = "" Then
        Me.txtBoxesAwaitingCheckout = Me.cmbBox
    Else: Me.txtBoxesAwaitingCheckout = Me.txtBoxesAwaitingCheckout & ", " & vbCrLf & Me.cmbBox
    End If
   
    If IsNull(cmbCheckOutTo) Or cmbCheckOutTo = "" _
        Or IsNull(txtBoxesAwaitingCheckout) Or txtBoxesAwaitingCheckout = "" Then
        cmdCheckOut.Enabled = False
    Else: cmdCheckOut.Enabled = True
    End If
End Sub
 
Private Sub cmdRemoveAll_Click()
    Me.txtBoxesAwaitingCheckout = ""
    cmdCheckOut.Enabled = False
End Sub
 
Private Sub cmbCheckOutTo_AfterUpdate()
    If IsNull(cmbCheckOutTo) Or cmbCheckOutTo = "" _
        Or IsNull(txtBoxesAwaitingCheckout) Or txtBoxesAwaitingCheckout = "" Then
        cmdCheckOut.Enabled = False
    Else: cmdCheckOut.Enabled = True
    End If
End Sub
 
Private Sub txtBoxesAwaitingCheckout_AfterUpdate()
        If IsNull(cmbCheckOutTo) Or cmbCheckOutTo = "" Then
        If IsNull(txtBoxesAwaitingCheckout) Or txtBoxesAwaitingCheckout = "" Then
            cmdAdd.Enabled = False
        End If
    Else:
        cmdAdd.Enabled
    End If
End Sub

Open in new window

Jeffrey Coachman

ghjeffeii

Your Combobox that displays the Boxes should have two Columns:
The BoxID and The BoxName

- Set the RowSource Property of the combobox to a query or SQL statement that will display the BoxID and The BoxName
- Set the Column Count Property of the combobox to: 2
- Set the BoundColumn Property of the combobox to: 1 (This is the BoxID)
Set the Column Widths Property of the combobox to: 0,1
(This will hide the Box ID, and display the BoxName.)

JeffCoachman

ghjeffeii

ASKER

Thanks for your response Jeff. I do, already have the combo box that lists the 'In' boxes set up as you have listed. What I'm worried about is with the text box only listing the BoxID, once the user has added multiple boxes to the text box there isn't a good way for them to run back through it to confirm that they have all they right boxes listed before finally performing the check out.

Jeffrey Coachman

Then, IMHO, you should consider using a Listbox instead of a combobox.

This way you can deep six the whole "add multiple boxes to the text box" code.
And actual SEE the highlited values as you select them in the listbox.
;-)

Here is a sample I just did for another similar question.

JeffCoachman
AccessProductsBasicSimpleMultiSe.mdb

ghjeffeii

ASKER

Thanks for the attached. That's a possibility, but w/ our warehouse having over 10,000 boxes, I'm not sure that'll work that well.

Jeffrey Coachman

Then what exactly are you looking for then?

ASKER CERTIFIED SOLUTION

ghjeffeii

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

Then how about two listboxes.
When you select a value from one, you can "move" in to the other listbox.
Thus one listbox displays a list of all the selected boxes.

JeffCoachman

Jeffrey Coachman

Sample.

Is something like this acceptable?
Access-Basic-SampleMoveSelectedI.mdb

ghjeffeii

ASKER

That's a pretty cool form and would probably work. I ended up creating a dedicated table to the check out process w/ a subform based on a query for that table. As the user adds items, the subform fills up & it retrieves pallet, rack & other misc. information.

How might a couple of queries look for updating (changing status) & inserting (for history) based on the items selected in your form?

Jeffrey Coachman

"How might a couple of queries look for updating (changing status) & inserting (for history) based on the items selected in your form?"

Well, if you have decided to use your own design to solve this issuse, is this even relevent?

Jeff

ghjeffeii

ASKER

I'm just trying to weigh your solution against mine to see which one would work better, easier. No big deal - thanks for your time.

Gary