Multiple Datasheet View subform tabs requery issues

[gottnoskill]

So my problem here is something I cannot find a work around to. I have plenty of forms setup in the same way with subtle differences, but there is a common problem in all of them.



Each form has at least two subform tabs in them. Each subform is displayed in Datasheet view. One is made for daily inspection sign offs and the other for daily inventory sign offs. Each datasheet view has entries for all days of the current month. For those who sign-off daily there are no issues, but for supervisory staff I am coming across a problem

Supervisory staff are allowed to look at previous months if necessary. You type in the month and year into text boxes and click a button that will run the desired month and year within the where statement to pull up the desired results for both inspection and inventory sheets.

The code for the button looks something like this.

Form_subForm_UnitInventory.Form.RecordSource = "SELECT * FROM tbl_Inventory " & A1Filter
Form_subForm_UnitInventory.Form.Requery

Form_subForm_UnitInspection.Form.RecordSource = "SELECT * FROM tbl_Inspection " & A1Filter
Form_subForm_UnitInspection.Form.Requery

The A1Filter has an if statement that runs the different month and year if it is placed within the text boxes.

The form on load is tabbed on the inspection datasheetview for the subform. When running the code while the inspection tab is open the datasheet requeries properly with the desired month and year for review. When I tab over to the inventory tab it will show the desired month and year correctly as well, but when I attempt to requery on the inventory sheet it will not requery correctly. No matter how many times I requery nothing happens.

If I were to on load tab over to the inventory tab and run the code while the inventory datasheet is open then it will requery correctly... but now the inspection tab will not requery correctly. I was thinking that it had something to do with my code, but now I think it is something with the way Access handles datasheet view for subforms, because If have omitted both recordsource and requery for either inspection or inventory and tested it the same issue occurs. I found a weird way to 'reset' the problem and here it is.

If I requery with the inspection tab open, to make the inventory tab work I must:

1. goto inventory tab
2. go back to inspection tab
3. go back again to inventory tab and then run the code.

This works 100% of the time. Vice Versa if I run the requery first on inventory tab I must do the 3 tab switch to make inspection tab work.

I feel like requery doesnt work correctly when you run the requery on one subform datasheet view and change to the other. Is there anyway I can alleviate this problem?

[RuralGuy]

Let's check a couple of things first. The button is on the MainForm, correct? The two SubFormControls are named subForm_UnitInventory and subForm_UnitInspection, correct?

[gottnoskill]

Yessir! The button is cmd_setDate and the Navigation target names are subForm_UnitInventory and subForm_UnitInspection. Not sure if this is necessary but too much info can't be bad. I never changed the name for the tabs themselves so if that is needed then they are NavigationButton18 and NavigationButton19

[RuralGuy]

Comment out the existing code and try this:

'Form_subForm_UnitInventory.Form.RecordSource = "SELECT * FROM tbl_Inventory " & A1Filter
 'Form_subForm_UnitInventory.Form.Requery

 'Form_subForm_UnitInspection.Form.RecordSource = "SELECT * FROM tbl_Inspection " & A1Filter
 'Form_subForm_UnitInspection.Form.Requery

subForm_UnitInventory.Form.RecordSource = "SELECT * FROM tbl_Inventory " & A1Filter
subForm_UnitInspection.Form.RecordSource = "SELECT * FROM tbl_Inspection " & A1Filter

See if that code performs the same. I'm pretty sure replacing the RecordSource will force a requery on its own.

[gottnoskill]

I've attempted this before and still get the same unwanted results, and I'm sorry I've explained the way this is setup a little wrong. the subform_UnitInventory and subForm_UnitInspection forms are in a subform of its own called nav_UINSP_UINV.

nav_UINSP_UINV is used as a subform on the 'master form' pM_Nav_AUnits.

So pM_Nav_AUnits
Has a subform called nav_UINSP_UINV which has subforms > subForm_UnitInspection & > subFForm_UnitInventory

[RuralGuy]

Maybe this link will assist with the syntax: http://access.mvps.org/access/forms/frm0031.htm

[gottnoskill]

I use this page quite a lot trying to figure out how to target certain controls in subforms and things of that nature. But when I attempt to use the syntax in this case would be

On the MainForm

Me!Subform1.Form!Subform2.Form.Recordsource

This would look like

Me!nav_UINSP_UINV.Form!subForm_UnitInventory.Form. RecordSource = "SELECT * FROM tbl_Inventory " & A1Filter

But I get this error.

Run-time error '2465':

Microsoft Access can't find the field 'nav_UINSP_UINV' referred to in your expression.

The only way I could find my way around this problem was using Using 'Form_' to select the desired form.

[RuralGuy]

Are you certain the 1st SubFormControl is named 'nav_UINSP_UINV'? Change its name and see if you still get the same error.

[gottnoskill]

I just tried to change the name to 4 different things. UINSP_UINV, UINV, abc, subformTest

and i still get the same error.

is the way I wrote the code wrong?

[RuralGuy]

Is this the error you keepn getting:
Microsoft Access can't find the field 'nav_UINSP_UINV' referred to in your expression.
...in spite of changing the name of the first SubFormControl?
Are there any other controls on the 1st SubFormControl you could try and reference?

[gottnoskill]

I feel dumb. I found the problem. As with most things I cant figure it out until I post it on a forum.

Me![NavigationSubform].Form![NavigationSubform].Form.RecordSource = "SELECT * FROM tbl_Inspection " & A1Filter

This pretty much fixes all the problems I was having.

I wasn't thinking and using the actual form name to try and make this work when infact I needed the subform CONTROL.

Apparently directly calling the form the way I was doing just didn't allow for subforms to be requeried properly for whatever reason.

Anyways thank you for leading me to find out this issue!

[RuralGuy]

Excellent! Thanks for posting back with your success and solution.