Run Select Query in sub-form (1 Viewer)

[kilburfi]

Hi - I have a main form (let's call it MAINFORM), attached to that I have a sub-form (let's call it SUBFORM).

In the SUBFORM there is a combo box which gets its list from a select query (lets call it QUERY1). I have a "ON CLICK" action to run the query.

When I load MAINFORM and click on the drop-down button for the first time this works absolutely fine.

When I then move to another record on the SUBFORM and click on the drop-down button it just keeps the list entries from the last time and doesn't refresh with the "ON CLICK" action in SUBFORM.

How do I make the query run every time I click on the drop-down button in the SUBFORM?

Thanks - Fiona.

 

[CJ_London]

sounds like it is a problem with your query.

Is your subform single or continous view?

Better if you show your code - both vba behind the button and the sql for the rowsource if it is not determined in the vba

 

[kilburfi]

The query works and the code I am using to run again is:

DoCmd.OpenQuery "Prospect K contacts - 09-01-14 Query", acViewNormal
DoCmd.Close acQuery, "Prospect K contacts - 09-01-14 Query"

I did it like this because I don't actually want to view the results of the query I just want them to populate the drop-down box.

As the query works the first time I think the problem lies with making it re-query every time I click on the button (I assume it is more a case of where to put the code - which even to tie it to and should this be on the main form or the sub form)

My sub-form is a datasheet.

The query is to list contacts at a customer site depending on the customer number that is in the main form. If I go to a different customer record and then run the query manually it works fine but it just doesn't refresh the list again in the sub-form.

I hope that makes sense!

 

[kilburfi]

that should have read "event" not "even"

 

[CJ_London]

OK, either I don't understand or you are doing it wrong

Am I right in my understanding that you have or want:

A mainform (MAINFORM) populated with a customer
A subform (SUBFORM) populated with a list of contacts for that customer

I'm not sure where your query comes in or what the significance of the site is - is the site related to a combo on the main form?

 

[kilburfi]

OK - I will try to explain better.

My main form has a customer number amongst other fields.

The sub form populates with the further info pertaining to that customer number (from a different table which is linked to the main by Customer Number) and the user is allowed to add a record pertaining to that customer in the sub-form.

One of the fields on the sub-form is called "Employee". The employee information is held in a third table in the database.

I have a query which populates the "Employee" field with only employees relevant to that customer number and the query works fine on a stand-alone basis. (It picks the customer number from the main form and runs the query based on that number).

The process is:

1) Load main form and search for a customer.
2) Click on Add in the sub-form to add a new customer contact.
3) The last field in this sub-form is called "Employee" and is a combo from a third table. Click the drop down box and the employees that show are populated by a query so you can select the correct employee. The query is run using the "On Click" routine attached to that field of the sub-form.

This works great the first time and populates correctly.

If you then move on to do the next customer contact and select a different customer it all works fine until you get to the "Employee" combo box which, when clicked still shows the same employees as it did for the previous record.

This looks like the query runs the very first time you click on the combo field but doesn't run on any subsequent records (so the "On Click" is not being run every time it is clicked).

Is it my reference to the control in the sub-form which is incorrect (perhaps it is ok putting it in the "On-Click" property for the first time but perhaps the second time I need to be specific with the location of the control (as I feel that I need to actually code it for subsequent instances - rather than rely on the "On-Click" again or "After Update, Before Update, etc).

I hope this makes more sense!

If you then

 

[CJ_London]

So to clarify you are saying the recordsource of your combo is dependant on the value of some field in the customer table? i.e. the customer number?

If this is the case you need to requery the employee control - suggest you do it on the main form current event and the code would be something like

mySubFormControlName.Form.EmployeeCtrl.Requery

 

[kilburfi]

Thank - I ran the query again (tried a few attempts at different code but got there in the end). The key code that I have just put in that made the whole thing work was to Refresh the subform. That then updated the control with the query results. Thanks so much for your help.