Copy Record From Multiple Tables Based On CheckBoxes

[Shaunk23]

Hello. I Have a database with a table called ExpRecords. On this table there are multiple fields... You have Shipper, Shipper Address 1, Shipper Address 2, Shipper Phone etc... Then you have Receiver , Receiver Address 1 , Receiver Address 2 etc.

There are more... Like "load location" & then some various fields like Destination Country, Reference Number etc

I need to be able to Copy a shipment when creating new... My tables are linked by my companies reference number(AEC).

When i create a new shipment on the side of the form i have a "Duplicate Button" , a text-box where the user can enter the "AEC" to copy from & three check boxes..

The check boxes are Shipper / Receiver & Load Location

I need to be able to select those boxes.. Or two of three or all three & when command is pressed create a new record in my table, copying the Shipper(if selected) Receiver(If selected) etc. I have started a query Where im trying to get the results. In my query i have all fields selected & in the CRITERIA for anything that is relevant to shipper i have

Like IIf([Forms]![ExpNewShipment]![ExpCopyShipperCheck]=True,"Expr» [ExpRecords]![Type Of Shipper]","")


I dont understand the true/false part... IF the checkbox is selected then i need those results to be copied... If the check isnt selected then DONT copy them. Am i going about this the wrong way??

 

[Beetle]

Just to clarify, you should be using a query to combine existing data from different tables, then use that query as the basis for running a report, or exporting records, or whatever you're trying to do. You should not (in most cases) be taking existing data, combining it, then redundantly writing it to another table (it's not 100% clear that this is what you are doing, I'm just saying it's not good practice if you are).

Having said that, I don't quite understand the rest of what you're trying to do here. Maybe you could clarify a bit?

 

[Shaunk23]

Sure... Alot of times we have repeat customers. So.. they ship with me a few times.. They give me their Shipper Information / Receiver Information & the load address... When they come back the next time to ship rather then retyping all of the data again for a new shipment i need to be able to create a new shipment using the data from a prior shipment... Make sense?

 

[Shaunk23]

Lets work it one table at a time.. and forget the check boxes... So if i have a new shipment and click command after entering a 5 digit reference number in a text box -- i need it to go to the "old" record and get the info & create a new record using that.

 

[Shaunk23]

So here i have the SELECT Query... I have data selected from OLD AEC reference. Now how do i create a new record & Past this information in using the NEW aec number from another textbox


SELECT ExpRecords.AEC, ExpRecords.[Job Manager], ExpRecords.ShipmentType, ExpRecords.[Cargo Type], ExpRecords.[Aarid Service Type], ExpRecords.[Transport Mode], ExpRecords.[Destination Country], ExpRecords.[Payment Terms], ExpRecords.[Tracking Group], ExpRecords.[Type Of Shipper], ExpRecords.Shipper, ExpRecords.ShipperFullName, ExpRecords.[Shipper First Name], ExpRecords.[Shipper Last Name], ExpRecords.[Shipper Company], ExpRecords.[Shipper Full Address], ExpRecords.[Shipper Street Address 1], ExpRecords.[Shipper Street Address 2], ExpRecords.[Shipper City], ExpRecords.[Shipper State], ExpRecords.[Shipper ZipCode], ExpRecords.[Shipper Country], ExpRecords.[Shipper Phone Type 1], ExpRecords.[Shipper Phone Type 2], ExpRecords.[Shipper Phone Type 3], ExpRecords.[Shipper Phone 1], ExpRecords.[Shipper Phone 2], ExpRecords.[Shipper Phone 3], ExpRecords.[Shipper Email], ExpRecords.[Shipper Contact], ExpRecords.[Shipper Entity Type], ExpRecords.ShipperContactInfo, ExpRecords.[Shipper Entity], ExpRecords.Consignee, ExpRecords.[Type Of Consignee], ExpRecords.[Consignee Full Name], ExpRecords.[Consignee First Name], ExpRecords.[Consignee Last Name], ExpRecords.[Consignee Company], ExpRecords.[Consignee Full Address], ExpRecords.[Consignee Street Address 1], ExpRecords.[Consignee Street Address 2], ExpRecords.[Consignee City], ExpRecords.[Consignee State], ExpRecords.[Consignee ZipCode], ExpRecords.[Consignee Country], ExpRecords.ConsigneeCountryCode, ExpRecords.ConsigneePhoneType1, ExpRecords.ConsigneePhoneType2, ExpRecords.ConsigneePhoneType3, ExpRecords.ConsigneePhone1, ExpRecords.ConsigneePhone2, ExpRecords.ConsigneePhone3, ExpRecords.[Consignee Email], ExpRecords.[Consignee Contact], ExpRecords.[Consignee Contact Information], ExpRecords.[Full Notify Party], ExpRecords.[Notify Party 1], ExpRecords.[Notify Party 2], ExpRecords.[Notify Party 3], ExpRecords.[Notify Party 4], ExpRecords.[Load Location Name], ExpRecords.[Load Location Full Address], ExpRecords.[Load Location Address Line 1], ExpRecords.[Load Location Address Line 2], ExpRecords.[Load Location City], ExpRecords.[Load Location State], ExpRecords.[Load Location ZipCode], ExpRecords.LoadLocationPhoneType1, ExpRecords.LoadLocationPhoneType2, ExpRecords.LoadLocationPhoneType3, ExpRecords.[Load Location Phone 1], ExpRecords.[Load Location Phone 2], ExpRecords.[Load Location Phone 3], ExpRecords.[Load Location Email], ExpRecords.[Load Location Contact], ExpRecords.[Shipper Reference Number], ExpRecords.LoadLocationSource, ExpRecords.CargoLocationSource, ExpRecords.EquipPickupSource, ExpRecords.EquipReturnSource
FROM ExpRecords
WHERE (((ExpRecords.AEC)=[Forms]![ExpNewShipment]![CopyRecAECtext]));