Macros opening Data Entry Forms and copying Data.

[RobertSmith101]

Hi!

Ted here, I need some help with a form and some macros.

Here is what I have I have a data entry form (frmNewOrder)where I enter the customerID then I go to a combo box which says Air Freight or Sea Freight what I would like to do is that when Air Freight is selected have the Air Freight Data Entry form open (frmAirFreightordr) and then copy the customerID to the field that says CustomerID in the Air Freight, and Sea Freight is selected then it opens the Sea Freight Form (frmSeaFreightOrdr) and copies the CustomerID that I input to the CargoDetails into the customerID field (which is related in both forms)

I can't have one big form

This is because there are three seperate tables

one for Cargo Details

one for Sea Freight and

one for Air Freight

as one order for cargo can only be sent through one means of transport (Ie Spare parts for a refigerator can only go go either by shipping or plane)

One the Cargo Form first of all on that form I input the CustomerID

Then what I want to happen from the drop down menu when I select the the attribute Sea Freight the form for Sea Freight Details will popup and the same would happen when I select the Air Freight just the Air Freight Form would pop up.

When either of these forms pops up I want it to have the CustomerID from the Cargo Form to copied onto whichever form pops up (Sea Freight or Air Freight)'s CustomerID field.

Could you please help me with this? I would greatly appreciate some help thanks,

Ted!

 

[RobertSmith101]

Anyone know?

 

[Ziggy]

My first thought is:

can you not have the Air freight and Sea Freight data as one table? You would have a field for "FreightType" for example, set up another table to define your freight type and then make it a lookup from the freight table.

This way your only dealing with the one table and all the customer info already included. Another way is to use the "option" group on a form to make the selection.

ziggy

 

[RobertSmith101]

It may be a good idea to have one form however there is one problem the Air Freight Table and Sea Freight Table have different attributes and I need to be done like this sorry.

 

[Ziggy]

Hi Rob,

I've read your post over and over and I keep coming up with the same idea,

This how I picture it...

You should have a customer table which will generate your Customer ID (or you can specify your own).

Then I gather the Cargo table would apply to both Sea and Air freight, and in effect is your "Order Header"


Then 2 forms: The Cargo table is the main part of each form with the Seafreight as the "Subform" on one and the "Airfreight" a subform on the other. These are the Order details to the Cargo form.


This way you start by Choosing either Airfreight or Sea freight forms from the start. Each form has a Lookup to the customer table, by selecting the customer Id this way you are connecting it to the order so there is no need to perform any copy/paste as you ask.

The order details (Sea or Air) are related to the Cargo table by the order ID
With Queries you can pull it together for your reports.

Sorry I don't mean to re-arrange your design, I'm just giving you an Idea of how to solve your problem, Often you can start out with too many fields in a table and there by restricting your flexibility.


Ziggy