Requerying the data in a subform after making a selection from the main form

[RuralGuy]

So the SubForm should only contain records for one job?

 

[RuralGuy]

The relationships I can't seem to get to work are linking the queries to the tables.

The relationships are defined between tables and the queries take care of themselves.

 

[Tophan]

That's correct

For instance for one month we would bill job #20200501 for Valuation #1. So in the Description field of the subform I would type Valuation #1

Next month I would create another invoice for the same job but that would be for Valuation #2 (this is just an example - the valuations might not be carried out monthly - sometimes we would bill for other works aside from a valuation)

 

[RuralGuy]

Why do you have the [Job Details] table included in the [Billing Name] query? It does not look like it is doing anything.

 

[Tophan]

Even though the Job Details table is seen in the Billing Name query design view I realised I didn't need to use any of the fields in Job Details to achieve the results I wanted from the query - deleting shouldn't affect the query.

 

[RuralGuy]

Actually, including the [Job Number] field in the [Billing Name] query would allow you to place that value in a hidden control on your MainForm that could then be used as a 2nd parameter for the LinkMaster/ChildFields properties of the SubFormControl. This would probably get you the results for which you have been looking.

 

[Tophan]

Ok...I think I am officially lost

 

[RuralGuy]

Easier to just show you.

 

[Tophan]

THANK YOU THANK YOU THANK YOU

This is how I wanted the form to work - just gotta figure out how you did that

Seriously - thank you very much for your patience and assistance - I can now move forward with this database and hopefully won't encounter any more major snags.

 

[RuralGuy]

Glad we could help. Have fun.

 

[Tophan]

Hi RuralGuy,

Just wanted to give you an update. I was working again on the database last night and realised that even though the solution you gave me is working, there are some Clients that we open several jobs for in one year so I really wanted to filter the job number/name by the Client selected.

I tried again this morning with the code and was successful

Following your suggestion with removing the lookup fields from the tables - I created the Invoice & Invoice Details subform and inserted the following codes:

Private Sub Form_Load()
Forms![Invoice]![Invoice Details Subform].Form![Combo12].Requery
End Sub


Private Sub Combo12_GotFocus()
[Combo12].Requery
End Sub

[Combo12] looks up the job number in the Job Select Query so I only see the jobs for the Client selected in the "Bill To" combo box in the Invoice table.

I am so happy.

Thanks again for all your help

 

[RuralGuy]

You are certainly welcome and thanks for the update.