Not too sure how to link master/child form when master is data entry

  • Thread starter Thread starter graeme34 via AccessMonster.com
  • Start date Start date
G

graeme34 via AccessMonster.com

Hi I have a Receive Invoice data entry form, on this form is a sub form that
has a query based control source, the query contains details of all records
of receipted orders.
The problem I have is I want the subform to show only the related records to
the Order number on the receive invoice record, yet I dont want to create an
occurance of a record in the receive invoice table until the user has
verified the delivery, i.e checked it against the subform. Therefore I cant
link the sub form to the master the way I normally would by showing a SQL
statement of the subforms control source query for each record in the
tblReceiveInvoice, as this record is not yet created. Is there anyway of
doingthis without having to create a temporary table. I have tried using,
[Forms]![frmReceiveInvoice]![txtPurchaseOrderNumber] in the WHERE clause of
the query but this didnt work.
Any help would be greatly appreciated
 
Using a query which references the
[Forms]![frmReceiveInvoice]![txtPurchaseOrderNumber] control as a parameter I
think all you need to do is requery the subform control in the
txtPurchaseOrderNumber control's AfterUpdate eevent procedure:

Me.YourSubformControl.Requery

Note that YourSubformControl is the name of the subform control on the
parent form, i.e. the control which houses the subform, not the name of the
underlying form object. The subform should then update as soon as a purchase
order number is entered in the parent form.

However, you could do it without the need for any parameter in the subform's
query or any code at all simply by entering txtPurchaseOrderNumber as the
LinkMasterFields property of the subform control and the name of the
PurchaseOrderNumber field in the subform's underlying recordset as the
LinkChildFields. You have to do this directly in the properties sheet of the
subform control; it can't be done through the dialogue which the 'build'
button (the one on the right with 3 dots) for these properties opens.

Ken Sheridan
Stafford, England
 
Hi I have a Receive Invoice data entry form, on this form is a sub form that
has a query based control source, the query contains details of all records
of receipted orders.
The problem I have is I want the subform to show only the related records to
the Order number on the receive invoice record, yet I dont want to create an
occurance of a record in the receive invoice table until the user has
verified the delivery, i.e checked it against the subform. Therefore I cant
link the sub form to the master the way I normally would by showing a SQL
statement of the subforms control source query for each record in the
tblReceiveInvoice, as this record is not yet created. Is there anyway of
doingthis without having to create a temporary table. I have tried using,
[Forms]![frmReceiveInvoice]![txtPurchaseOrderNumber] in the WHERE clause of
the query but this didnt work.
Any help would be greatly appreciated
Click to expand...

I'm sorry, this isn't making sense to me.

You don't want a record to exist in the subform, until you verify that
the record exists in the subform.

Could you explain? Do you perhaps need TWO subforms related to two
different tables? You could have the second subform's Enabled property
set to No until you've (somehow) verified the information in the first
subform, perhaps with a control button.

John W. Vinson[MVP]
 
Hi Ken
Thanks I took your advice and linked it directly to the textbox control
(txtPurchaseOrderNumber) on the master form and did away with the parameter
in the query this worked fine.
Thanks again ..
Ken said:
Using a query which references the
[Forms]![frmReceiveInvoice]![txtPurchaseOrderNumber] control as a parameter I
think all you need to do is requery the subform control in the
txtPurchaseOrderNumber control's AfterUpdate eevent procedure:

Me.YourSubformControl.Requery

Note that YourSubformControl is the name of the subform control on the
parent form, i.e. the control which houses the subform, not the name of the
underlying form object. The subform should then update as soon as a purchase
order number is entered in the parent form.

However, you could do it without the need for any parameter in the subform's
query or any code at all simply by entering txtPurchaseOrderNumber as the
LinkMasterFields property of the subform control and the name of the
PurchaseOrderNumber field in the subform's underlying recordset as the
LinkChildFields. You have to do this directly in the properties sheet of the
subform control; it can't be done through the dialogue which the 'build'
button (the one on the right with 3 dots) for these properties opens.

Ken Sheridan
Stafford, England
Hi I have a Receive Invoice data entry form, on this form is a sub form that
has a query based control source, the query contains details of all records
Click to expand...
[quoted text clipped - 10 lines]
the query but this didnt work.
Any help would be greatly appreciated
Click to expand...
Click to expand...
 
Hi John, sorry if I didnt explain it too cleary
what I have is a input form for entering invoices received, within this form
I wanted to put a subform that displayed all receipted invoices, this record
already exists. The problem I was having was linking this subform via its
PurchaseOrderNumber to the Master form when the Master form is not yet
created as it is a data entry form. The problem is now solved thanks to this
site, very helpful people your are on here :)
I didnt realise I could link the subform to the Master by using
txtPurchaseOrderNumber.
But apparantly you can and this solved the problem , thank you any
Graeme.
John said:
Hi I have a Receive Invoice data entry form, on this form is a sub form that
has a query based control source, the query contains details of all records
Click to expand...
[quoted text clipped - 10 lines]
the query but this didnt work.
Any help would be greatly appreciated
Click to expand...

I'm sorry, this isn't making sense to me.

You don't want a record to exist in the subform, until you verify that
the record exists in the subform.

Could you explain? Do you perhaps need TWO subforms related to two
different tables? You could have the second subform's Enabled property
set to No until you've (somehow) verified the information in the first
subform, perhaps with a control button.

John W. Vinson[MVP]
Click to expand...
 
Back
Top