Stopping Wrong Shipments

[Dan Ford via AccessMonster.com]

First time poster - been lurking for some time. Great site.



I have a database to control shipping process and create Bill of lading.
We have had issues in the past where the shipping clerks have loaded more
than one customers freight into the container. We have a few customers
where this is okay, but as a rule, it is not. I want to stop this by
validating the customer number being loaded.

Shipping is now scanning a 2D PDF bar code on each package as they load it.
The fields on bcode are Customer(num), Shipper(num), CaseNumber(text), and
CustOrdNumber(text).

The master form (BillofLading) captures date, bill of lading (autonumber +
PK), container#, carrier. The subform (BillofLadingSForm) captures the
bcode info above with CaseNumber as PK.

Customer table (tblCustomer) has field Commingle (Y/N) to ID when customers
can be loaded together.

How can I write a DLookup on BeforeUpdate field Customer of the subform
where if the previous record [Customer] for the bill of lading being loaded
does not match the current record [Customer] and the tblCustomer.Commingle
= false, then it will generate a message stating that the customers cannot
be commingled?

I am good with queries and macros, but a little bit green with VB. I know
what to do, just not quite sure how to do it.

Any help would be greatly appreciated.

 

[Arvin Meyer]

Easier, and much faster than DLookup.

Set a form level variable:

Public blnComingle As Boolean

Fill it when you open the form with the first comingle value. As you move
through the records, check it and set a textbox to true or false depending
upon the match:

Sub Form_Current()
If Me!Comingle = blnComingle Then
Me.chkComingle = True ' unbound checkbox
Else
Me.chkComingle = False
End If

blnComingle = Me!Comingle ' reset variable
End Sub

Now each record will read the one before it. The first record will always be
false (as it should be, since nothing comes before it)
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Dan Ford via AccessMonster.com]

Thanks Arvin, but now I have had to make a change of strategy. There are
too many variables on when customers can be shipped with each other (it
helps when you talk with Shipping and Receiving!)

In any event, the thinking now is that if I assign a "destination" field on
each customer which has a relationship with the destination table, I can
now identify which customers go to individual destinations.

So now the thinking is, if a customer is scanned from the bcode, and each
customer has the identical destination field in their customer table, then
they can go together.

Which brings us back to how does the program generate an error message if
the customer scanned does not go to the same destination as the customer on
the first and/or previous subform record?

Are we back to DLookups?

Dan
cdford[AT]comcast.net

 

[Arvin Meyer]

So now the thinking is, if a customer is scanned from the bcode, and each
customer has the identical destination field in their customer table, then
they can go together.

Which brings us back to how does the program generate an error message if
the customer scanned does not go to the same destination as the customer on
the first and/or previous subform record?

Are we back to DLookups?

No, you can handle it the same way. What I'd do though is build a
destination code fields and sort on that for your shipments first, even
before you pick your orders. That will build more "sets" of deliveries.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Dan Ford via AccessMonster.com]

Arvin,

Okay, so I am more than a little green on VB code. <Swoosh! Right over my
head!> Working with your original thought and changing from comingle field
to destination field, I still need some help here.

Where does the form level variable "Public blnDestination as Boolean" go?
As a module? On the form itself? Customer Textbox? <Swoosh>

Sorry if I am asking you to hold my hand on this one, but I am willing to
be student if you are willing to be teacher.

Dan

 

[Arvin Meyer]

Dim your variable in the Declarations section of the form. Set it in your
procedure to make sure it is initialized with your default (True or False)

You can also do it in the Declarations section of a standard module.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access