using a subform for data entry

[graeme34 via AccessMonster.com]

Hi,
I am trying to use a subform for entering order details, the master form
(sales order)
has 5 control boxes, (account index, sales order Number, order date,
customers order number and delivery mode). the child form has seven control
boxes, six of which are based on a select query the other being a calculated
field. One of the control boxes on the subform is also sales order number.
What I am wanting to do is make the subform order number equal to the master
order number. I thought Access automatically did this am I going wrong
somewhere, is a fault with my query, on which the subform is based? Here is
the query..
SELECT tblSalesOrderLine.SalesOrderNumber, tblSalesOrderLine.[Product Code],
tblProduct.Description, tblSalesOrderLine.QuantityOrdered, tblSalesOrderLine.
Price, tblSalesOrderLine.VATRate
FROM tblSalesOrder INNER JOIN (tblProduct INNER JOIN tblSalesOrderLine ON
tblProduct.[Product Code]=tblSalesOrderLine.[Product Code]) ON tblSalesOrder.
SalesOrderNumber=tblSalesOrderLine.SalesOrderNumber;
Any suggestions would be greatly appreciated.
Thank you!

 

[Rick Brandt]

Hi,
I am trying to use a subform for entering order details, the master
form (sales order)
has 5 control boxes, (account index, sales order Number, order date,
customers order number and delivery mode). the child form has seven
control boxes, six of which are based on a select query the other
being a calculated field. One of the control boxes on the subform is
also sales order number. What I am wanting to do is make the subform
order number equal to the master order number. I thought Access
automatically did this am I going wrong somewhere, is a fault with my
query, on which the subform is based? Here is the query..
SELECT tblSalesOrderLine.SalesOrderNumber, tblSalesOrderLine.[Product
Code], tblProduct.Description, tblSalesOrderLine.QuantityOrdered,
tblSalesOrderLine. Price, tblSalesOrderLine.VATRate
FROM tblSalesOrder INNER JOIN (tblProduct INNER JOIN
tblSalesOrderLine ON tblProduct.[Product
Code]=tblSalesOrderLine.[Product Code]) ON tblSalesOrder.
SalesOrderNumber=tblSalesOrderLine.SalesOrderNumber;
Any suggestions would be greatly appreciated.
Thank you!

No need for the subform's query to include anything from the master table so
take that out. All you need to do is set the MasterLink and ChildLink
properties on the subform control to the OrderNumber field in each table.

That will not only automatically filter the subform to matching records it will
also automatically propogate that value to new records created in the subform.

 

[graeme34 via AccessMonster.com]

Hi Rick
Thanks for your interest...I am quite new to this so please bear with me.
I tried what you said and took the sales order number out of the query, now
when I try and link the master and child forms through the sales order number
I am getting incompatible data types as the child sales order number has now
become binary type.
Yet if I make the control source on the child form = [Parent]!
[txtSalesOrderNumber], this works until I try to go to the next line. Because
the parent form has never been saved the child form has no related record,
breaking the referential entegrity rules. Any suggestions where I am going
wrong??
Thanks again.

Hi,
I am trying to use a subform for entering order details, the master

[quoted text clipped - 16 lines]

Any suggestions would be greatly appreciated.
Thank you!

No need for the subform's query to include anything from the master table so
take that out. All you need to do is set the MasterLink and ChildLink
properties on the subform control to the OrderNumber field in each table.

That will not only automatically filter the subform to matching records it will
also automatically propogate that value to new records created in the subform.

 

[Rick Brandt]

Hi Rick
Thanks for your interest...I am quite new to this so please bear with
me.
I tried what you said and took the sales order number out of the
query, now when I try and link the master and child forms through the
sales order number I am getting incompatible data types as the child
sales order number has now become binary type.
Yet if I make the control source on the child form = [Parent]!
[txtSalesOrderNumber], this works until I try to go to the next line.
Because the parent form has never been saved the child form has no
related record, breaking the referential entegrity rules. Any
suggestions where I am going wrong??

Lets step back you should have an OrderNumber field in both tables that are both
the same data type. Do you?

If you do then a subform bound directly to the items table embedded in a main
form bound directly to the order table should work when both MasterLink and
ChildLink have an entry of SalesOrderNumber (assuming this is the name of the
order numebr field in both tables).

I have no idea what you mean by "as the child sales order number has now become
binary type".

If the tables are related on this field then they have to be the same type. In
fact if you had a proper relationship set up between the two tables Access would
normally fill in the proper values for MasterLink and ChildLink automatically
when the subform is created.

 

[graeme34 via AccessMonster.com]

Hi Rick

I'll start at the beginning. I have a sales table and a sales detail table on
a one to many relationship.
The primary key on the sales table is Sales order Number and on the detail
table it is a composite key of Sales order number and Product code. I dont
know what has happened in the child form but each time I try to link the
forms in the subform field linker the data type of the Sales order number on
the child form is showing as type binary???
Have I miss understood you when you said the subforms query does not need
anything from the master table. Because when I take out the tblSalesOrderLine.
SalesOrderNumber out of the query that is when the data type changes to
binary. Yet when it is there it is of type long Integer the same as the
master table. I try to link the two in the subform field linker. But the link
is still not updating the child form Order number to match the master tables.
What should the control source of the child table be?? As it is defaulting to
subfrmSales (sub from name), is this right?

Thanks again for your interest Nick...much appreciated

Hi,
I am trying to use a subform for entering order details, the master

[quoted text clipped - 16 lines]

Any suggestions would be greatly appreciated.
Thank you!

No need for the subform's query to include anything from the master table so
take that out. All you need to do is set the MasterLink and ChildLink
properties on the subform control to the OrderNumber field in each table.

That will not only automatically filter the subform to matching records it will
also automatically propogate that value to new records created in the subform.

 

[graeme34 via AccessMonster.com]

If this helps at all here is the new query I have based the subform on:

SELECT tblSalesOrderLine.SalesOrderNumber, tblSalesOrderLine.[Product Code],
tblProduct.Description, tblSalesOrderLine.QuantityOrdered, tblSalesOrderLine.
Price, tblSalesOrderLine.VATRate

FROM tblProduct INNER JOIN tblSalesOrderLine ON tblProduct.[Product Code] =
tblSalesOrderLine.[Product Code];

Hi Rick

I'll start at the beginning. I have a sales table and a sales detail table on
a one to many relationship.
The primary key on the sales table is Sales order Number and on the detail
table it is a composite key of Sales order number and Product code. I dont
know what has happened in the child form but each time I try to link the
forms in the subform field linker the data type of the Sales order number on
the child form is showing as type binary???
Have I miss understood you when you said the subforms query does not need
anything from the master table. Because when I take out the tblSalesOrderLine.
SalesOrderNumber out of the query that is when the data type changes to
binary. Yet when it is there it is of type long Integer the same as the
master table. I try to link the two in the subform field linker. But the link
is still not updating the child form Order number to match the master tables.
What should the control source of the child table be?? As it is defaulting to
subfrmSales (sub from name), is this right?

Thanks again for your interest Nick...much appreciated
[quoted text clipped - 8 lines]

That will not only automatically filter the subform to matching records it will
also automatically propogate that value to new records created in the subform.

 

[graeme34 via AccessMonster.com]

It seems to be working now, the only small problem I have is the value on the
child order number is null until I enter a vaule into the other composite key
on the child form (product code) after updating this then the order number is
equal to the master order number. Is there anything I can do with the before
update property of the child order number so it is equal to the master order
number as soon as the master form is opened??

If this helps at all here is the new query I have based the subform on:

SELECT tblSalesOrderLine.SalesOrderNumber, tblSalesOrderLine.[Product Code],
tblProduct.Description, tblSalesOrderLine.QuantityOrdered, tblSalesOrderLine.
Price, tblSalesOrderLine.VATRate

FROM tblProduct INNER JOIN tblSalesOrderLine ON tblProduct.[Product Code] =
tblSalesOrderLine.[Product Code];

Hi Rick

[quoted text clipped - 21 lines]

 

[Rick Brandt]

It seems to be working now, the only small problem I have is the
value on the child order number is null until I enter a vaule into
the other composite key on the child form (product code) after
updating this then the order number is equal to the master order
number. Is there anything I can do with the before update property of
the child order number so it is equal to the master order number as
soon as the master form is opened??

You could set the default value to Forms!SalesForm!OrderNumber, but really
it's not necessary. In most cases the related field in the subform is not
even displayed since the user knows that it is the same as the parent.