Copy Shipping details on form to billing details subform

[Ultraviolet47]

Hi all

I have frm_shipping based on tbl_shipping where there are address
fields. I also have a subform, frm_billing based on tbl_billing in this
that's for billing details, as they may not be the same.

I would like a button so that I can automatically copy the details from
frm_shipping to sub form frm_billing if they are the same.

Following suggestions on here I've tried insert into and things like
Frm_billing![Ad1]=Frm_shipping![Ad1], etc but no luck?

Can someone point me int he right direction please? Using Access 2003

Greatly appreciated, thank you.

 

[Guest]

I'd suggest assigning the values of the various address controls on the
frm_shipping parent form to the DefaultValue properties of the corresponding
controls on the subform. If you want to automate this put the code in the
Current event procedure of the parent form AND in its AfterInsert event
procedure. If you want to do it manually put it in the Click event procedure
of a button on the parent form. The code would be something like this:

Me.sfrBillings.Form.Ad1.DefaultValue = """" & Me.Ad1 & """"
Me.sfrBillings.Form.Ad2.DefaultValue = """" & Me.Ad2 & """"
Me.sfrBillings.Form.Ad3.DefaultValue = """" & Me.Ad3 & """"
Me.sfrBillings.Form.Ad4.DefaultValue = """" & Me.Ad4 & """"

A few things you need to realize:

1. sfrBilling is the name of the subform *control* on the frm_shipping
parent form, i.e. the control which houses the subform, not its underlying
form object, frm_billing (unless the control and its underlying form object
have the same name of course).

2. Assigning a value as the DefaultValue property of a control only sets
the value which will be automatically entered when you add a new record in
the subform. Although the values will show in a blank record in the subform
the record won't begin to be created until you add other data or change any
of the default values.

3. If you use a button to set the default values manually the values you
set in one shipping record will carry over when you move to another shipping
record while the form is still open, until you click the button again.
Automatic assignment of the values in the Current and AfterInsert event
procedures of the parent form would probably be a better strategy therefore.

4. The DefaultValue property is always a string expression, regardless of
the underlying data type of the field in question, which is why the values
are wrapped in quote characters in the above code with the two sets of """".
A double set of quotes within a string delimited by quotes is interpreted by
Access as a literal quotes character. Often these quotes aren't absolutely
necessary, but can sometimes be crucial in circumstances where you might not
expect them to be needed (date/time values for instance), so its good
programming practice to always include them in your code.

Ken Sheridan
Stafford, England

Hi all

I have frm_shipping based on tbl_shipping where there are address
fields. I also have a subform, frm_billing based on tbl_billing in this
that's for billing details, as they may not be the same.

I would like a button so that I can automatically copy the details from
frm_shipping to sub form frm_billing if they are the same.

Following suggestions on here I've tried insert into and things like
Frm_billing![Ad1]=Frm_shipping![Ad1], etc but no luck?

Can someone point me int he right direction please? Using Access 2003

Greatly appreciated, thank you.

 

[Ultraviolet47]

Hi

That works great, thanks. Two questions though:

I need it to copy the contents of a combo box as well (e.g if they
selected "2" in main form combo box, enter this in subform combobox. Is
this possible, as I kept getting errors, even though it can copy the
other contents fine?

Can you please explain about the "Automatic assignment of the values in
the Current and AfterInsert event procedures" ? I need to use the
manual button method, and as you say, this keeps the record in the
boxes until clicked twice, and I would like to rectify this, but
AfterInstert, etc out of my depth!

I have been looking for this code for ages-thank you!

 

[Ultraviolet47]

OK-now I have a strange problem! If I enter the details in the shipping
form and billing subform by hand, it saves them to the relevant tables.

However, if I enter the details in the shipping form and then click
transfer with the code, it transfers the data over to the subform, but
when I click save, it doesn't save the subform data to the billing
table, but does save the shipping form data to shippng table?

Is it something to do with my link child fields, etc?

Thanks

 

[Guest]

The reason its not saving the record was actually explained in my original
reply. Its because setting the DefaultValue of a control doesn't initiate a
new record, but merely puts values into the controls on the form. A new
record is only initiated if you either change one of those values or add new
data in another field on the form. If you want to save the subform record
you'll need to add another line of code to the procedure to do this. The way
to initiate a new record without changing a default value or adding new data
to another control is simply to set the value of one of the control's to its
existing value, so you could put this line after the others in the procedure:

Me.sfrBillings.Form.Ad1 = Me.Ad1

It only has to be done with one of the controls not all of them. If you do
this the new record added to the subform will be saved. You'll notice
however that the subform will now have the same values showing in its next
new record.

If you are only adding one record to the subform for the current parent
form's record, and you want it automatically saved unless you 'undo' it (but
still allowing you to make changes before its saved), then a better strategy
would be to set the Value property of each control in the subform. This is
the controls' default property (not to be confused with its DefaultValue
property) so you don't need to specifically refer to it, merely to the
control, you don't need to wrap the value in quotes, and you don't need the
extra line to 'dirty' the form. You should first move the subform to a new
record, however, or you'll overwrite any existing subform record which is its
current record. You can avoid repetition of the reference to the subform's
Form property by setting an object variable (frm below) to point to the
subform's underlying form. The code would thus go like this:

Dim frm As Form

Set frm = Me.sfrBillings.Form

Me. sfrBillings.SetFocus
DoCmd.GoToRecord Record:=acNewRec

frm.Ad1 = Me.Ad1
frm.Ad2 = Me.Ad2
frm.Ad3 = Me.Ad3
frm.Ad4 = Me.Ad4

As far as your other question is concerned, if you use this alternative
method and set the Value of each control rather than its DefaultValue
property you can automate this by putting the code in the AfterInsert event
procedure of the parent form so the values are passed to the subform
automatically when you enter a new parent record. To do this select the form
in design view by clicking on the little black square in its top left corner.
In the properties sheet select the After Insert event property on the Event
tab. Click on the 'build' button; that's the one on the right with 3 dots.
Select Code Builder in the next dialogue and OK out of it. The VBA window
will open at the form's AfterInsert event procedure. Enter the code as new
lines between the two lines which will be already in place.

Using the alternative code above don't put the code in the form's Current
event procedure, but if you use the original code plus the extra line, then
you should also put the same code in the form's Current event procedure by
following the same procedure after selecting that event property in the
form's properties sheet.

Ken Sheridan
Stafford, England

 

[John Vinson]

However, if I enter the details in the shipping form and then click
transfer with the code, it transfers the data over to the subform, but
when I click save, it doesn't save the subform data to the billing
table, but does save the shipping form data to shippng table?

Is it something to do with my link child fields, etc?

Please post your code.

John W. Vinson[MVP]

 

[brenda]

I'm working on a project that needs the same type code but from Subform
to Subform, I can get this to work on main form to subform. Is it
possible?? When I use the code below I get a data member not found on
the "from" data.

I'm using Access 2000.