Adding data from one form to another

[Debbiedo]

Working in MS Access 2003

I have one large table (Truck) that has at least four separate
delivery addresses per record.
(E.g. four fields, Deliver1, Delivery2, etc...)Also in this table are
text fields that have special instructions such as on what days these
addresses make deliveries. Theses days vary by address.

I need to separate out these addresses into a single record in a new
table (Location) and enter its delivery days. (Thus potentially, the
one record in the Truck table will result in 4 records in the Location
table)

I have the master form (TruckForm) designed so that the truck number,
delivery addresses, and special instructions are visible. I would like
the following capability: if the user clicks on Delivery1 button, it
opens up another form (LocationEntryForm), adds a new record, and
enters the current Truck.TruckNumber field into the current (or new)
Location.TruckNumber field and the current Truck.Delivery1 field into
the Location.Address field and displays in the LocationEntryForm. The
user then reads the special instructions for that delivery address and
determines which days the truck goes to that address and checks the
appropriate boxes in the LocationEntryForm. Once this delivery
location data is updated into the new table, the user refers back to
the truck form to transfer the next address. The user clicks on
Delivery2 button, which opens up the form (LocationEntryForm), adds a
new record, and enters the current Truck.TruckNumber field into the
current Location.TruckNumber field and the current Truck.Delivery2
field into the Location.Address field, etc... for all four Delivery
buttons.

My question is what is the code that I would use behind the delivery#
buttons to make the data transfer happen? Right now I just have a
macro running that opens up the LocationEntryForm and adds a new
record. I would like to increase these buttons's functionality by
being able to transfer the above data when clicked.

Any and all help is greatly appreciated.

Deb

 

[banem2]

Working in MS Access 2003

I have one large table (Truck) that has at least four separate
delivery addresses per record.
(E.g. four fields, Deliver1, Delivery2, etc...)Also in this table are
text fields that have special instructions such as on what days these
addresses make deliveries. Theses days vary by address.

I need to separate out these addresses into a single record in a new
table (Location) and enter its delivery days. (Thus potentially, the
one record in the Truck table will result in 4 records in the Location
table)

I have the master form (TruckForm) designed so that the truck number,
delivery addresses, and special instructions are visible. I would like
the following capability: if the user clicks on Delivery1 button, it
opens up another form (LocationEntryForm), adds a new record, and
enters the current Truck.TruckNumber field into the current (or new)
Location.TruckNumber field and the current Truck.Delivery1 field into
the Location.Address field and displays in the LocationEntryForm. The
user then reads the special instructions for that delivery address and
determines which days the truck goes to that address and checks the
appropriate boxes in the LocationEntryForm. Once this delivery
location data is updated into the new table, the user refers back to
the truck form to transfer the next address. The user clicks on
Delivery2 button, which opens up the form (LocationEntryForm), adds a
new record, and enters the current Truck.TruckNumber field into the
current Location.TruckNumber field and the current Truck.Delivery2
field into the Location.Address field, etc... for all four Delivery
buttons.

My question is what is the code that I would use behind the delivery#
buttons to make the data transfer happen? Right now I just have a
macro running that opens up the LocationEntryForm and adds a new
record. I would like to increase these buttons's functionality by
being able to transfer the above data when clicked.

Any and all help is greatly appreciated.

Deb

Hi Deb,

I think your DB design isn't design well. You need to consider to
avoid duplicating data (google for "rules of normalization" and
"normal forms"). I will suggest to extract Delivery data into new
table with all information required and regarding delivery data as you
have described and create 1-to-many relationship with main table. Then
you can use form with subform to populate data in related table.

However, if you want to stick with this bad designed DB, it is fairly
simple to copy data from open form record to second form record with
something like this:

Forms!frmSecondForm!txtDelivery = Forms!frmFirstForm!txtDelivery

Use this in OnClick event of the button. Both forms need to be opened
and to check that you can use IsLoaded function.

However I am suggesting to change DB design.

Regards,
Branislav Mihaljev

 

[Debbiedo]

Hi Deb,

I think your DB design isn't design well. You need to consider to
avoid duplicating data (google for "rules of normalization" and
"normal forms"). I will suggest to extract Delivery data into new
table with all information required and regarding delivery data as you
have described and create 1-to-many relationship with main table. Then
you can use form with subform to populate data in related table.

However, if you want to stick with this bad designed DB, it is fairly
simple to copy data from open form record to second form record with
something like this:

Forms!frmSecondForm!txtDelivery = Forms!frmFirstForm!txtDelivery

Use this in OnClick event of the button. Both forms need to be opened
and to check that you can use IsLoaded function.

However I am suggesting to change DB design.

Regards,
Branislav Mihaljev- Hide quoted text -

- Show quoted text -

I agree that the DB design is awful, unfortunately the software
application that I am using does not allow for joined tables, plus
each address needs to have its own record because I need to geo-code
each delivery address separately using the field "address" to geo-code
against.

Sorry, your code did not make much sense to me. I did not see any
field names in it.

Precisly, How do I code the following:

On click, OPEN form LocationEntryForm AND ADD new record AND COPY
Truck.TruckNumber to Location.TruckNumber AND COPY Truck.Copy
Truck.Delivery1 to Location.Address

There are 4 buttons. Each button does the same thing except the
Location.Address field gets loaded with different fields (Delivery1,
Delivery2, etc.) when clicked.

Thanks again

Deb

 

[Debbiedo]

Hi Deb,

I think your DB design isn't design well. You need to consider to
avoid duplicating data (google for "rules of normalization" and
"normal forms"). I will suggest to extract Delivery data into new
table with all information required and regarding delivery data as you
have described and create 1-to-many relationship with main table. Then
you can use form with subform to populate data in related table.

However, if you want to stick with this bad designed DB, it is fairly
simple to copy data from open form record to second form record with
something like this:

Forms!frmSecondForm!txtDelivery = Forms!frmFirstForm!txtDelivery

Use this in OnClick event of the button. Both forms need to be opened
and to check that you can use IsLoaded function.

However I am suggesting to change DB design.

Regards,
Branislav Mihaljev- Hide quoted text -

- Show quoted text -

I know this is not the best DB design but the software application I
am using does not allow joined tables. Plus I need to geo-code the
delivery addresses separately, thus they need to be in a single field
called address that I can geo-code against.

Your reponse did not make sense to me. I did not see where I was
writing one field to another field in different tables.

I want my buttons to do the following

On click ADD new record AND COPY field "Truck.TruckNumber" from
current record TO field "Location.Truck.Number" AND COPY
Truck.Delivery1 TO Location.Address AND OPEN LocationEntryForm.

LocationEntryForm would display just added record plus other blank
fields for that record I need to enter data into.

There are 4 buttons that do essentially the same thing except the
field "Truck.Delivery1" is changed to either Delivery2, 3 or 4.

How do I write the code to do this, please?

Many thanks

Deb

 

[banem2]

I know this is not the best DB design but the software application I
am using does not allow joined tables. Plus I need to geo-code the
delivery addresses separately, thus they need to be in a single field
called address that I can geo-code against.

Your reponse did not make sense to me. I did not see where I was
writing one field to another field in different tables.

I want my buttons to do the following

On click ADD new record AND COPY field "Truck.TruckNumber" from
current record TO field "Location.Truck.Number" AND COPY
Truck.Delivery1 TO Location.Address AND OPEN LocationEntryForm.

LocationEntryForm would display just added record plus other blank
fields for that record I need to enter data into.

There are 4 buttons that do essentially the same thing except the
field "Truck.Delivery1" is changed to either Delivery2, 3 or 4.

How do I write the code to do this, please?

Many thanks

Deb

Hi Deb,

First you need to open LocationEntryForm and then copy data:

DoCmd.OpenForm "LocationEntryForm",,,,acFormAdd
Forms!LocationEntryForm!TruckNumber = Forms!FirstFormName!TruckNumber
Forms!LocationEntryForm!Delivery1 = Forms!FirstFormName!Delivery1

Regards,
Branislav Mihaljev

 

[Debbiedo]

Hi Deb,

First you need to open LocationEntryForm and then copy data:

DoCmd.OpenForm "LocationEntryForm",,,,acFormAdd
Forms!LocationEntryForm!TruckNumber = Forms!FirstFormName!TruckNumber
Forms!LocationEntryForm!Delivery1 = Forms!FirstFormName!Delivery1

Regards,
Branislav Mihaljev- Hide quoted text -

- Show quoted text -

Thank you very much



Deb