Solved Problem with subform combo box requery (1 Viewer)

[CupKat]

Hi, access newbie and first time poster here!

I have a form for entering invoice information. In the main form you choose a client from a combo box, and this filters options in a combo box in the subform to only show contractors currently placed with that client. After trying various ways of applying an AfterUpdate event to make this happen, all seemed to be well at first glance when using this line of code as an AfterUpdate event on the main form combo box:

Forms!formname!subformname.Form!comboboxname.requery

It would correctly show only the relevant contractors in the subform. I entered a handful of invoices to test this, and still all seemed well. They all happened to be related to the same client (Client1). The problem revealed itself only after adding invoices relating to a different client (Client2). This information still displayed correctly, but when going back to previous invoices relating to Client1, the contractor section had gone blank and clicking on the dropdown now showed contractors related to Client2 rather than Client1, even though Client1 was still selected in the main form.

I hope my problem makes sense, and if anyone could point me in the right direction I’d be very grateful!

 

[bob fitz]

Can you show us the SQL statement used for the Row Source property or the SQL statement of the query used for the Rowsource property of the combo box.

 

[CupKat]

The row source for the main form combo box is:
SELECT Tbl02_Clients.ID_Clients, Tbl02_Clients.Trading_Name FROM Tbl02_Clients ORDER BY Tbl02_Clients.Trading_Name;

and the subform combo box:
SELECT [qry04_ClientINVICES_SUB4_Contracts].[ID_Clients], [qry04_ClientINVICES_SUB4_Contracts].[Trading_Name], [qry04_ClientINVICES_SUB4_Contracts].[ContractsKey], [qry04_ClientINVICES_SUB4_Contracts].[Client_Rate], [qry04_ClientINVICES_SUB4_Contracts].[ID_CONTRACTS] FROM qry04_ClientINVICES_SUB4_Contracts ORDER BY [Trading_Name];

 

[bob fitz]

Can you show us the SQL statement of the query "qry04_ClientINVICES_SUB4_Contracts].[ID_Clients". I'm looking for the WHERE clause that determines which records are returned.

 

[CupKat]

Can you show us the SQL statement of the query "qry04_ClientINVICES_SUB4_Contracts].[ID_Clients". I'm looking for the WHERE clause that determines which records are returned.

SELECT Tbl02_Clients.ID_Clients, Tbl02_Clients.Trading_Name, Tbl01_CONTRACTS.ID_CONTRACTS, Tbl01_CONTRACTS.ContractsKey, Tbl01_CONTRACTS.Client_Rate
FROM Tbl02_Clients INNER JOIN Tbl01_CONTRACTS ON Tbl02_Clients.ID_Clients = Tbl01_CONTRACTS.Client_Trading_Name
WHERE (((Tbl02_Clients.ID_Clients)=[Forms]![FrmClientINVOICES_MAIN_4]![ClientID]));

 

[bob fitz]

can you post a copy of the db. with just a few fictitious records to show the problem

 

[bob fitz]

Hi, access newbie and first time poster here!

I have a form for entering invoice information. In the main form you choose a client from a combo box, and this filters options in a combo box in the subform to only show contractors currently placed with that client. After trying various ways of applying an AfterUpdate event to make this happen, all seemed to be well at first glance when using this line of code as an AfterUpdate event on the main form combo box:

Forms!formname!subformname.Form!comboboxname.requery

It would correctly show only the relevant contractors in the subform. I entered a handful of invoices to test this, and still all seemed well. They all happened to be related to the same client (Client1). The problem revealed itself only after adding invoices relating to a different client (Client2). This information still displayed correctly, but when going back to previous invoices relating to Client1, the contractor section had gone blank and clicking on the dropdown now showed contractors related to Client2 rather than Client1, even though Client1 was still selected in the main form.

I hope my problem makes sense, and if anyone could point me in the right direction I’d be very grateful!

Are you using the actual name of the form or the name of the subform control?

 

[CupKat]

Will a series of screenshots do?
Adding first invoice, all good:

Second invoice, still all good:

Going back to view first invoice, contract dropdown now blank:

Clicking first invoice subform dropdown now gives options based on most recently entered main form client:

If I go back and re-select the client in the main form for the first invoice, the correct contractor again appears on that subform, but the contracts section in the second invoice now appears blank. So it seems that whatever invoice I'm on, the subform shows options based on the most recent selection in the main form. Does that make sense?

 

[CupKat]

Are you using the actual name of the form or the name of the subform control?

Forms!FrmClientINVOICES_MAIN_4!FrmClientINVOICES_SUB_4.Form!Contracts_Combo4.Requery

 

[bob fitz]

If you post the copy db I/we may be able to fix it

I suspect that "FrmClientINVOICES_SUB_4" is the name of the sub form but what is the name of the subform control on the main form that is displaying the form

 

[CupKat]

If you post the copy db I/we may be able to fix it

I suspect that "FrmClientINVOICES_SUB_4" is the name of the sub form but what is the name of the subform control on the main form that is displaying the form

I have attempted to do a copy attached below, hopefully it has what is needed!

 

[bob fitz]

Try the following in the OnCurrent event of the main form:

Forms!FrmClientINVOICES_MAIN_4!FrmClientINVOICES_SUB_4.Form!Contracts_Combo4.Requery

 

[CupKat]

Try the following in the OnCurrent event of the main form:

Forms!FrmClientINVOICES_MAIN_4!FrmClientINVOICES_SUB_4.Form!Contracts_Combo4.Requery

That did it! Thank you ever so much for taking the time to help!

 

[bob fitz]

That did it! Thank you ever so much for taking the time to help!

You're welcome. Always glad to help if I can.
I usually find it easier to find the problem if I can get my hands on the actual db

 

[CupKat]

You're welcome. Always glad to help if I can.
I usually find it easier to find the problem if I can get my hands on the actual db

That provided a bonus learning opportunity - I now know that I can copy just the structure of the database without any of the records - I was worried I was going to have to go through and delete all sensitive information in order to share it! Very useful in case I need to ask for help another time