Requery issues in Subform scenario (1 Viewer)

AnilBagga

Member
Local time
Today, 14:48
Joined
Apr 9, 2020
Messages
112
I have a form and subform for an Invoice entry. The main form has customer information and the sub form has item details

In the subform I need to enter the Header ID of the order which is pending for this customer (ConsigneeCode) specified in the header of this form. I fetch this data from a query - see screenshot 1, The criteria is ConsigneeCode entered in the header form.

I am not getting this Header ConsigneeCode to requery the ID used in the sub form! Therefore the data I get is of the first record. The Afterupdate codes are as below

How do I requery this. See screenshot no 2 for additional information where it shows the mismatch in names of customers. The drop down data is of the first record of the header.

Private Sub ConsigneeCode_AfterUpdate()
[Form]![InvDtlsfrm]![SOHdrID].Requery
Me.ConsigName = Me.ConsigneeCode.Column(1)
Me.Currency = Me.ConsigneeCode.Column(3)
[Form]![InvDtlsfrm]![SOHdrID] = ""
[Form]![InvDtlsfrm]![SONo] = ""
[Form]![InvDtlsfrm]![ItemCode] = ""
[Form]![InvDtlsfrm]![ItemName] = ""
[Form]![InvDtlsfrm]![SORate] = ""

End Sub

Private Sub DebtorCode_AfterUpdate()
ConsigneeCode.Requery
Me.DebtorName = Me.DebtorCode.Column(1)
ConsigneeCode = ""
ConsigName = ""

End Sub


Private Sub MewarUnit_AfterUpdate()
DebtorCode.Requery
DebtorCode = ""
DebtorName = ""
ConsigneeCode = ""
ConsigName = ""
 

Attachments

  • Screenshot2.png
    Screenshot2.png
    188.9 KB · Views: 19
  • Screenshot 1.png
    Screenshot 1.png
    170.2 KB · Views: 21

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 09:18
Joined
Jul 9, 2003
Messages
12,116
My guess is you are going about it the wrong way.

It appears you are thinking about it as a customer having an invoice.

Try thinking about it as just an invoice, forget about the customer for the moment.

So now you have a table that contains invoice details:- date, invoice number and customer.

You can now create a form based on this invoice table.

It is common for an invoice to contain a number of items and these are generally referred to as "line items" because each item will be presented on its own line in the invoice.

The way to handle this in MS Access is to have another table called "line items'" .

This line item table will contain Fields like ID, invoice number, item name, number of.

Create a form in datasheet view based on this line item table and drag the form on to your invoice form.

You now need to identify a relationship between the invoice form and the line item form a child/parent relationship which which will control the records associated with the invoice which are displayed in the line item subform.

There is a working example database provided by Microsoft called the Northwind database.

You you can download it here:-


It's not a production safe database as it has a couple of issues which I explain here:-

 
Last edited:

arnelgp

error reading drive A:
Local time
Today, 17:18
Joined
May 7, 2009
Messages
10,871
you are Far, far away.
you only need to add Link Master/Child Fields to your subform.
 

AnilBagga

Member
Local time
Today, 14:48
Joined
Apr 9, 2020
Messages
112
you are Far, far away.
you only need to add Link Master/Child Fields to your subform.

These linkages are in place

We first capture the Orders in SOHdr and SODtls - the mainform and Subform
We then capture the invoices in InvHdr and InvDtls

The main form and child form are linked in both cases. The Invoice forms in question are working fine. See linkages below. It is this requery linked to a field in another query which is a problem. The property sheet of the child form and the fields in Design view is as below. Each record of the Sub form has the ID of the main form.

To search for which items to look for in the invoice subform, we first need to locate the Order (which is identified by the SOHdrID which is of the orders table) from the Pending Order List. This is important as the orders for the customer could have maybe 50 but the Order which have pending items could be only 10 which are relevant for invoicing! Therefore SOdrID is the first cbo in the InvDtls form

In the next field of the Invdtls subform we select the ItemCode or the relevant record in the SODtls subform linked to to the SOHdrID .

The problem is that this SOHdrID is not requering the Consignee Code (captured in the InvHdr mainfrom) when looking for data in the pending Order Query

1598952784477.png


1598952928407.png


1598953212251.png

There are many tables and queries linked in this DB, that is why I am desiting from sharing the DB in the first instance. If necessary I will copy and do so

The DB has some confidential data too and adding test data will take time. I am hoping that by this post I will be able to convey the problem


1598952784477.png
 

Users who are viewing this thread

Top Bottom