Requery Subform

[Johnnie Pearson]

I am new to MS-Access, but not to programming in general.

I have a main form and two sub-forms:
The main form is frmLTSMain. Subform frmDataApps is related to frmLTSMain; subform frmLTSDataLoads is related to frmDataApps.

When I move the record pointer in frmLTSMain, the correct records are shown in both subforms initially. However, when I move the record pointer in subform frmDataApps, subform frmLTSDataLoads does not requery. I created custom queries to populate both subforms, so that I can hid the unneeded primay key fields.

I thought by establishing referential integrity between the main form and the subforms, the record pointer movement would be handled by Access.

I've tried all variants of syntax that I can find on the web and none of them work. Any help would be appreciated.

** Edit **

[NeoPa]

So, it seems you have a main form, a sub-form, and a sub-sub-form. Not a main form with two sub-forms.

Please describe the structure fully with steps you've taken so far to establish the linking between the forms. I expect we would also need a clear description of the tables/queries involved in your forms, with related info on how they link together.

Without this information there is very little question to work with.

[Johnnie Pearson]

Correct, I have a main form a sub-form and a sub-sub form.

The main form is frmLTSMain
frmLTSMain is parent to child frmDataApps
frmDataApps is parent to child frmLTSDataLoads

I placed the below code in the On Current event in form frmDataApps thinking it would requery its child form. I am also not sure if I'm using the correct event or if I'm placing it in the correct location.

Private Sub Form_Current()
  [Forms]![frmLTSMain].[Form]![sbfrmLTSDataLoads].Requery
End Sub

When I run the above line, the error is:

Run-time error '2465':

Microsoft Access can't find field 'sbfrmLoadFieldLocation' referenced in your expression.

[Johnnie Pearson]

As a follow-up, I've attached a picture of the form. When it first loads, everything is OK. However, looking at the sub-form, if I move the record pointer from record RequestNo 001 to record RequestNo 002, the sub-sub-form is not refreshed as I thought it would be, based on the relationships that I've set.

** Edit **

[NeoPa]

When I say to describe the structure fully I don't mean simply to indicate which forms are connected in your head to which other forms. I mean that to should specify which properties of which forms were set in which way to define the structure that reflects how you think they should connect together.

I believe the Form_Current() code you have posted should be unnecessary. I won't dwell on that as splitting a thread into different problems is never a good idea (nor is it allowed). I will merely mention that the reference is not correct. I can't say much at this time as I don't know enough about your setup, but more help on that can be found at Referring to Items on a Sub-Form.

I'm confident that the solution to your issues can be found in the design and setup of your forms. This is what we still need to know about if we're to help you further.

[Johnnie Pearson]

The reference page was useful information, but didn't really address my issue; and I'm not sure I understand your question.

I've attached a sample DB. There is one record in the main form, seven records in the sub-form, and one record in the sub-sub-form. No matter which record I select in the sub-form, the sub-sub-form shows the same record. Again, I thought the relationships that I’ve established would case the record pointer to move appropriately.

I'm trying to fix this for a person who knows less than I do about Access, so it’s a case of the blind leading the blind. In the course of 'fixing' the problem, I deleted all 'unnecessary' objects and renamed the remaining objects to add some form of consistency in naming conventions. As a result, if you open the DB, you'll get an error message about a missing form. I don’t think that has any impact on my real problem.

[NeoPa]

It's normally good etiquette to wait to be asked before posting a database, as that generally involves more time and effort than contributors are offering. In this case though, as you seem to have got as far as you can with explanations, and you're relatively new, and have clearly made an effort to explain as well as you can, I'll make an exception to my usual rule of simply ignoring posted databases that weren't requested, and look at it for you. I'm sorry to say though, that as it's now 01:00 I may not get around to looking at it until tomorrow for you. Answering simple posts is much less time-consuming than dealing with a posted database. Pop back in tomorrow and see if I've had time to check this out for you (or simply wait for the notification if that's how you interact with the site).

[NeoPa]

I managed to take a look at it today.

I was quite surprised to find that almost all the information provided was inaccurate in one way or another. With such an overload of issues to look at I wasn't in a position to list all of them. I would just suggest in future that you take a little more care when posting information. It is critical when asking for help, and it's not generally appreciated to find oneself wasting time on incorrect information.

I think I was able to recognise most of what I needed though. What struck me as the root of your problem was one of the items where you'd reported something different from what I found was actually the case. Instead of a main form, with a subform which itself had a further subform, I found a main form that had two separate subforms on it.

From what you've already posted I would guess you already understand enough to realise that what you are hoping for is not possible when set up as it is. Of course, subforms are only available to a form when it is displayed in one of the Form views, and not when displayed as a Datasheet (as you have them).

What you have :

 _________
|    A    |
|_________|
 _|_   _|_
| A1| | A2|
|___| |___|

What you need :

 _______
|   A   |
|_______|
 ___|___
|   A1  |
|_______|
 ___|___
|   A1i |
|_______|

[Johnnie Pearson]

I truly appreciate your efforts and any false information that I provided was a result of a lack of knowledge and nothing more. It is clear that I am a novice with Access. I’ve developed on other platforms for over 15 years, so I figured there had to be more than one way to “skin this cat.” I purchased a book, did some research, and solved this problem by creating a sub-datasheet; thus eliminating what I called the “sub-sub-form.” Again thanks for your help. I promise not to trouble you again.

[NeoPa]

Johnnie, My comments were not based on expectations of an Access person. They were based on expectations of an approach when asking for help. Perhaps I laid it on a bit thick, for which I apologise, but it really shouldn't be too much to expect a member to expend a little effort to ensure what they post accurately reflects the problem they are describing. I'm not talking a few minor inconsistencies here. Many things I found to be different from what you described.

In truth, I can live with that with a new poster, but would be disappointed were I to find the same situation in future threads from you (hence my comments). Alternatively, if you choose to have no more dealings with me, I can respoect that too.