Solved

MS Access Checkout Form

Posted on 2009-04-14
11
208 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:ghjeffeii
  • 6
  • 5
11 Comments
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24144371
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
0
 

Author Comment

by:ghjeffeii
ID: 24144406
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24144565
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
0
 

Author Comment

by:ghjeffeii
ID: 24144661
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24145191
Then what exactly are you looking for then?
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Accepted Solution

by:
ghjeffeii earned 0 total points
ID: 24154012
I want some sort of subform or text box that will house a running tally of the boxes that the user needs to check out.  The key here would be speed so that they can quickly see, & remove if necessary, all the boxes that are set to be checked out.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24154276
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24154315
Sample.

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

Author Comment

by:ghjeffeii
ID: 24163090
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?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24163948

"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
0
 

Author Comment

by:ghjeffeii
ID: 24164085
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
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now