Prev: Updating a Form Based on Updates to Subforms
Next: DLookup
From: milwhcky via AccessMonster.com on 23 Apr 2010 11:42
I am in the process of streamlining the process of generating shipping
documents in our existing Access database. Our 'Orders' form currently
features several buttons for the user to press to generate certain documents.
Different shipments often require a different set of documents.

To prevent the user from either forgetting to print a necessary document or
printing an unnecessary document, I am trying to use VBA code to make the
process happen automatically.

I'm doing this one document at a time, and I've found a glitch on my first
attempt. Hopefully, someone can provide me a solution...

A bill of lading document is needed for freight (but not ground) shipments.
Currently, a user clicks a button which runs a macro to open the
'BillofLadingForm'.

OpenForm...
Where Condition
[OrderID] = [Forms]![Orders]![OrderID]

SetValue...
Item
[Forms]![BillofLadingForm]![OrderID]
Expression
[Forms]![Orders]![OrderID]

The user enters more necessary information to the BillofLadingForm and clicks
a button to print the BillofLadingReport.

My attempt at making this work without having to click a button involved
adding code to the On Exit event of a certain field on the Orders form. The
field I chose for the On Exit event is the last field on the Orders form
involving information used on a bill of lading document.

To create a trigger for when the BillofLadingForm should open, I added a new
yes/no field to the ShippingCarrier table. That field was then added as a
hidden column of a combo box used to select the carrier on the Orders form.
I then converted the BillofLadingMacro to code and placed it in an If...Then
statement.

If [Forms]![Orders]![CarrierCombo].Column(2) = -1 Then
DoCmd.OpenForm "BillofLadingForm", acNormal, "", "[OrderID] = [Forms]![Orders]
![OrderID]", acEdit, acNormal
DoCmd.Maximize
[Forms]![BillofLadingForm]![OrderID] = [Forms]![Orders]![OrderID]
End If

With this code, the BillofLadingForm opens properly (and only when needed).
However, I usually (not always) get 'Type mismatch' errors after clicking the
button to print the BillofLadingReport.

I've since changed the code to run the macro instead of converting it, but I
get the same results...
If [Forms]![Orders]![CarrierCombo].Column(2) = -1 Then
DoCmd.RunMacro "BillofLadingMacro"
End If

Any ideas?

--
Message posted via http://www.accessmonster.com

From: Amy E. Baggott on 23 Apr 2010 12:25
You want to change

DoCmd.OpenForm "BillofLadingForm", acNormal, "", "[OrderID] =
[Forms]![Orders]![OrderID]", acEdit, acNormal

to

DoCmd.OpenForm "BillofLadingForm", acNormal, "", "[OrderID] = " &
[Forms]![Orders]![OrderID], acEdit, acNormal

--
Amy E. Baggott

"I''m going crazy and I''m taking all of you with me!" -- Linda Grayson


"milwhcky via AccessMonster.com" wrote:

> I am in the process of streamlining the process of generating shipping
> documents in our existing Access database. Our 'Orders' form currently
> features several buttons for the user to press to generate certain documents.
> Different shipments often require a different set of documents.
>
> To prevent the user from either forgetting to print a necessary document or
> printing an unnecessary document, I am trying to use VBA code to make the
> process happen automatically.
>
> I'm doing this one document at a time, and I've found a glitch on my first
> attempt. Hopefully, someone can provide me a solution...
>
> A bill of lading document is needed for freight (but not ground) shipments.
> Currently, a user clicks a button which runs a macro to open the
> 'BillofLadingForm'.
>
> OpenForm...
> Where Condition
> [OrderID] = [Forms]![Orders]![OrderID]
>
> SetValue...
> Item
> [Forms]![BillofLadingForm]![OrderID]
> Expression
> [Forms]![Orders]![OrderID]
>
> The user enters more necessary information to the BillofLadingForm and clicks
> a button to print the BillofLadingReport.
>
> My attempt at making this work without having to click a button involved
> adding code to the On Exit event of a certain field on the Orders form. The
> field I chose for the On Exit event is the last field on the Orders form
> involving information used on a bill of lading document.
>
> To create a trigger for when the BillofLadingForm should open, I added a new
> yes/no field to the ShippingCarrier table. That field was then added as a
> hidden column of a combo box used to select the carrier on the Orders form.
> I then converted the BillofLadingMacro to code and placed it in an If...Then
> statement.
>
> If [Forms]![Orders]![CarrierCombo].Column(2) = -1 Then
> DoCmd.OpenForm "BillofLadingForm", acNormal, "", "[OrderID] = [Forms]![Orders]
> ![OrderID]", acEdit, acNormal
> DoCmd.Maximize
> [Forms]![BillofLadingForm]![OrderID] = [Forms]![Orders]![OrderID]
> End If
>
> With this code, the BillofLadingForm opens properly (and only when needed).
> However, I usually (not always) get 'Type mismatch' errors after clicking the
> button to print the BillofLadingReport.
>
> I've since changed the code to run the macro instead of converting it, but I
> get the same results...
> If [Forms]![Orders]![CarrierCombo].Column(2) = -1 Then
> DoCmd.RunMacro "BillofLadingMacro"
> End If
>
> Any ideas?
>
> --
> Message posted via http://www.accessmonster.com
>
> .
>
From: milwhcky via AccessMonster.com on 23 Apr 2010 15:23
Thanks Amy!

Amy E. Baggott wrote:
>You want to change
>
>DoCmd.OpenForm "BillofLadingForm", acNormal, "", "[OrderID] =
>[Forms]![Orders]![OrderID]", acEdit, acNormal
>
>to
>
>DoCmd.OpenForm "BillofLadingForm", acNormal, "", "[OrderID] = " &
>[Forms]![Orders]![OrderID], acEdit, acNormal
>
>> I am in the process of streamlining the process of generating shipping
>> documents in our existing Access database. Our 'Orders' form currently
>[quoted text clipped - 54 lines]
>>
>> Any ideas?

--
Message posted via http://www.accessmonster.com

 | 
Pages: 1
Prev: Updating a Form Based on Updates to Subforms
Next: DLookup