transfer data from main form to subform

[Gazza]

If i enter a part no into a text box(PN1) on the main form it enters the data
into a textbox(PN2) on a subform.
Im using a continuous form on the subform and the master/child link is the
Part No text box.
is there a way to lock the first item to the first line and then be able to
enter a second item to display on the next line.
I have made the size of the continuous form to one line in my subform but i
am only able to enter one part no into the subform because when i setfocus to
the PN1 textbox on the main form it clears the first line.

thanks

 

[Sprinks]

Hi, Gazza.

You don't give enough general information about what your main form is, but
normally a subform is for detail records about or related to a main form
record. The linking field, shown once in the main form will automatically be
entered in the linking field in the subform. So the answer to your question
is No.

If you are creating an order, for example, that might have multiple part
numbers ordered, link by the OrderNumber instead. Otherwise, post more
generic information about your application and we'll try to help.

Sprinks

 

[Gazza]

Ok thanks sprinks here goes.

The main forms recordsource is the product details table and customer
details table.

The subforms recordsourse is the product details table.

My main form(PC1) opens up with the customer details entered and sts focus
on the part no text box(PN1).I enter a part no(primary key) into the text box
and it displays the info of the record(part no, description, price etc) from
a table(Product Details) and then sets focus on the quantity text box(Q1) on
the main form.
when i enter a quantity it re-sets the focus back to the part no text box on
the main form but it clears the line from the subform.

The subform is set to a continuous form and i have tried setting the
dataentry properties of both main form to yes and no amd subform to yes and
no.

Hope thats enough info to help.

thanks

 

[Sprinks]

Gazza,

Hmm. Are you saying the main form's RecordSource property is a query of the
product details table and customer tables? If this is so, and you're linking
on the ProductID field, you'll never be able to enter more than one detail
record. But I don't understand what the form is meant to do--is it an order
entry for a customer? I understand the details that you've given about your
forms, but please describe what the form's generic purpose is.

Sprinks

 

[Gazza]

sorry i realised i never enter that info when i re-read it.
the form is basically a price check form.
When a customer phones up for a price on some items we enter the customer id
which sets the discounts on the price for that particular customer so when i
enter the part no the price displayed is the price for that customer.
I dont need to save the info as it is only needed for a price on a list of
items and its easier than entereing the discount for every individual item
that a customer asks for.

 

[Gazza]

the forms recordsource is the query of the two tables.

 

[Sprinks]

OK.

You didn't say, but I'm going to guess that your main form's RecordSource is
the Customer table, which includes a discount field which applies to all
items purchased by the customer, not a specific discount for each item.

If the form opens up to this customer, presumably this form is being
launched from some other form on which you've selected the customer.

If this is so, I can think of only two possibilities for the observed
behavior of clearing the line from the subform after entering the quantity:

1) The subform is sized to display only one record, and the record isn't
gone, it's just scrolled out of the way. What happens if you arrow up? Does
the record come back?
2) There is code in the Qty's or in the subform's OnCurrent event procedure
that is indeed clearing the line. Open the subform in Design view, show the
properties (View, Properties), select the event tab, and click on the Qty
field. Are any [Event Procedure]'s visible? If so, click on it, then the
ellipsis to the right, and select Code Builder. Cut and paste the code in a
subsequent post. Do the same for the form; click on the little square at the
top left corner of the window to display the form properties.

Sprinks


Sprinks

 

[Gazza]

ok are you ready to be totally confused.

the form is based on the customer details table (now) and it does open from
another form so you presumed correctly.

As for the two reasons the only code i have for the textboxes is on the main
form,
in the on got focus event of the part no text box i have me.PN1=null and
me.Q1=null
Even if i remove that code i still cant enter another part no underneath, it
just overwrites the first part no.
The subform is a contiuous form so i assume it should do what i want.

OK.

You didn't say, but I'm going to guess that your main form's RecordSource is
the Customer table, which includes a discount field which applies to all
items purchased by the customer, not a specific discount for each item.

If the form opens up to this customer, presumably this form is being
launched from some other form on which you've selected the customer.

If this is so, I can think of only two possibilities for the observed
behavior of clearing the line from the subform after entering the quantity:

1) The subform is sized to display only one record, and the record isn't
gone, it's just scrolled out of the way. What happens if you arrow up? Does
the record come back?
2) There is code in the Qty's or in the subform's OnCurrent event procedure
that is indeed clearing the line. Open the subform in Design view, show the
properties (View, Properties), select the event tab, and click on the Qty
field. Are any [Event Procedure]'s visible? If so, click on it, then the
ellipsis to the right, and select Code Builder. Cut and paste the code in a
subsequent post. Do the same for the form; click on the little square at the
top left corner of the window to display the form properties.

Sprinks


Sprinks

sorry i realised i never enter that info when i re-read it.
the form is basically a price check form.
When a customer phones up for a price on some items we enter the customer id
which sets the discounts on the price for that particular customer so when i
enter the part no the price displayed is the price for that customer.
I dont need to save the info as it is only needed for a price on a list of
items and its easier than entereing the discount for every individual item
that a customer asks for.

 

[Sprinks]

Gazza,

I don't often do this, but we don't seem to be getting anywhere. Please
email the application to me, or better yet copy it to a new database (Start a
new database, then File, Get External Data, Import) and delete all but a few
of the records.

If you copy, please be sure to test that it works as the original before you
send it.

Send it to the following, removing the capitalized letters:
[email protected]

ok are you ready to be totally confused.

the form is based on the customer details table (now) and it does open from
another form so you presumed correctly.

As for the two reasons the only code i have for the textboxes is on the main
form,
in the on got focus event of the part no text box i have me.PN1=null and
me.Q1=null
Even if i remove that code i still cant enter another part no underneath, it
just overwrites the first part no.
The subform is a contiuous form so i assume it should do what i want.

OK.

You didn't say, but I'm going to guess that your main form's RecordSource is
the Customer table, which includes a discount field which applies to all
items purchased by the customer, not a specific discount for each item.

If the form opens up to this customer, presumably this form is being
launched from some other form on which you've selected the customer.

If this is so, I can think of only two possibilities for the observed
behavior of clearing the line from the subform after entering the quantity:

1) The subform is sized to display only one record, and the record isn't
gone, it's just scrolled out of the way. What happens if you arrow up? Does
the record come back?
2) There is code in the Qty's or in the subform's OnCurrent event procedure
that is indeed clearing the line. Open the subform in Design view, show the
properties (View, Properties), select the event tab, and click on the Qty
field. Are any [Event Procedure]'s visible? If so, click on it, then the
ellipsis to the right, and select Code Builder. Cut and paste the code in a
subsequent post. Do the same for the form; click on the little square at the
top left corner of the window to display the form properties.

Sprinks


Sprinks

sorry i realised i never enter that info when i re-read it.
the form is basically a price check form.
When a customer phones up for a price on some items we enter the customer id
which sets the discounts on the price for that particular customer so when i
enter the part no the price displayed is the price for that customer.
I dont need to save the info as it is only needed for a price on a list of
items and its easier than entereing the discount for every individual item
that a customer asks for.


:

Gazza,

Hmm. Are you saying the main form's RecordSource property is a query of the
product details table and customer tables? If this is so, and you're linking
on the ProductID field, you'll never be able to enter more than one detail
record. But I don't understand what the form is meant to do--is it an order
entry for a customer? I understand the details that you've given about your
forms, but please describe what the form's generic purpose is.

Sprinks

:

Ok thanks sprinks here goes.

The main forms recordsource is the product details table and customer
details table.

The subforms recordsourse is the product details table.

My main form(PC1) opens up with the customer details entered and sts focus
on the part no text box(PN1).I enter a part no(primary key) into the text box
and it displays the info of the record(part no, description, price etc) from
a table(Product Details) and then sets focus on the quantity text box(Q1) on
the main form.
when i enter a quantity it re-sets the focus back to the part no text box on
the main form but it clears the line from the subform.

The subform is set to a continuous form and i have tried setting the
dataentry properties of both main form to yes and no amd subform to yes and
no.

Hope thats enough info to help.

thanks



:

Hi, Gazza.

You don't give enough general information about what your main form is, but
normally a subform is for detail records about or related to a main form
record. The linking field, shown once in the main form will automatically be
entered in the linking field in the subform. So the answer to your question
is No.

If you are creating an order, for example, that might have multiple part
numbers ordered, link by the OrderNumber instead. Otherwise, post more
generic information about your application and we'll try to help.

Sprinks

 

[Sprinks]

Hi, Gazza.

The reason you can only add one record in the subform is that the subform is
currently linked on Part No, which is a field in the RecordSource of the
subform, and an unbound control on the main form. Whatever part number has
been entered in the footer is the only one that will show up in the subform.

This is a very unusual way to use a subform. Normally, a subform is linked
on a field common to the respective record sources of the main and the
subform. Before advising you of a way to allow multiple part numbers,
though, I'll need to understand your application better.

You mentioned in a previous that entering the CustomerID, presumably on the
previous form, sets the discount applicable to that customer. But there is
no Discount field in your Customer Details table. There is, however, a
Discount Code field in the Product Details table that leads me to believe
that the discount is product-specific. Both of these are hard to understand,
because in my experience, discount is related to an order quantity.

So please explain in detail how discounts are to be applied. For example,
are they related to a specific product, a specific order quantity, a specific
customer? Or a combination thereof?

Once I understand your intent, I'll be able to recommend a solution.

Sprinks

 

[Gazza]

thanks for the advice.

Basically the discount is set on a product group(see product details
table).But some customers will have a different discount for a product
group.That was going to be my next problem to tackle.

This form is only used when customers phone in for a price query on a number
of items then the salespeople can open up form1 and type in the customer id
and press the enter key.This in turn will open up the other form so i can
enter a number of part numbers and it will give me a total price according to
the specific customer.I know there is a few other bits that i have to add to
the form (just my own weird way of doing things)but i can sort that out
afterwards unless you actually need this done beforehand.

hope this is enough and thanks again

 

[Sprinks]

Hi, Gazza.

OK.

My approach would be create a continuous subform with a dummy table,
consisting of Part No and Quantity fields as its RecordSource. Place a combo
box with the RowSource set to the identical query statement as you had for
your subform's RecordSource, and a textbox for the quantity. You could then
add multiple part numbers directly into the dummy table. All other fields
can be displayed using the combo box' Column property.

For example, to display the Description (the 2nd column in the query), set
an unbound textbox to

=YourComboBox.Column(1)

Be sure to set the combo box' Bound Column to 1, the Column Count to 6, the
LimittoList property to Yes, and the ControlSource to the Part No field in
the dummy table.

The combo box will limit the selections to parts in your Product table, and
you can make any calculations you need, displaying them in other textboxes.

Hope that helps.

Sprinks

 

[Gazza]

sorry but i dont quite understand what you mean by a dummy table.
have i got to create another table with only the two fields(part no and
quantity)
or a query based on the product details table with only the 2 fields selected.

 

[Gazza]

Sorry i got it.
not checked my mail today.
just what i needed thanks very much for your help