Synching subforms after query

[renrenren]

Hi,
I have the following code that is supposed to make sure that when I move through different subforms, the record is retained via the ID primary key so I am still looking at the same record.
This works totally fine when I just open the "Navigation_Form" as it shows all records but when I want to view the results of a query, it retains the record but loses the filtered query when I go to a subform.

E.g, when the query loads in "Navigation_Form", record 1 of 20 from the query is shown (this has the ID of say 100)
when I move to record with the ID of 102, which is the 2nd record in the query and then move to a subform, record 102 is shown but out of 3000 as thats the total number of records in the database.

Ps I am using txtCurrentID to store the ID number as Navigation_Form is just a form to hold the subforms, the subfroms actually hold the ID and txtSearchQuery to save the query to apply to the subforms

Any help would be grateful.

Thanks

Code in Navigation_Form
Option Compare Database
Dim bQueryApplied As Boolean

Private Sub Form_Activate()
On Error GoTo ErrorHandler

If Not bQueryApplied Then
If Not IsNull(Me.txtSearchQuery) And Me.txtSearchQuery <> "" Then
If Me.NavigationSubform.Form.RecordSource <> Me.txtSearchQuery Then
Me.NavigationSubform.Form.RecordSource = Me.txtSearchQuery
Me.NavigationSubform.Form.Requery
End If
Else
If Me.NavigationSubform.Form.RecordSource <> "SELECT * FROM Coredata" Then
Me.NavigationSubform.Form.RecordSource = "SELECT * FROM Coredata"
Me.NavigationSubform.Form.Requery
End If
End If
bQueryApplied = True
End If

Exit Sub

ErrorHandler:
MsgBox "Error in Form_Activate: " & Err.Description, vbCritical
End Sub

Private Sub Form_Unload(Cancel As Integer)
Me.txtSearchQuery = Null
bQueryApplied = False
End Sub

Code in subforms
Option Compare Database

Private Sub Form_Current()
If CurrentProject.AllForms("Navigation_form").IsLoaded Then
Forms!Navigation_form!txtCurrentID = Me.ID
End If
End Sub

Private Sub Form_Load()
If CurrentProject.AllForms("Navigation_form").IsLoaded Then
Dim storedID As Variant
storedID = Forms!Navigation_form!txtCurrentID
If Not IsNull(storedID) Then
If Me.Recordset.NoMatch Or Me.Recordset!ID <> storedID Then
Me.Recordset.FindFirst "ID = " & storedID
End If
End If
End If
End Sub

 

[June7]

Should post code in a CODE block to retain indentation and readability.

Have you step-debugged? Could you provide db for analysis?

Comparison to "SELECT * FROM Coredata" might never be equal to RecordSource SQL because it lacks semicolon at end of SQL string. Access tends to add them. Why bother with this comparison? Just set the RecordSource. Otherwise, safer to use LIKE and wildcard or InStr() function.

 

[renrenren]

Sorry, not sure how to post a block.
The from coreData is there so that when I open the navigation form it shows all records and have the function to jump between subforms in the navigation from.
If there is an easier way to be able to sync the subforms so I always see the same record when I access any subform in the navigation from that might help

 

[June7]

Click the </> icon on post editor toolbar.

I am not really understanding what you are doing. Why would subforms need to "sync"? Why would different subforms have same data?
That's why I asked if you could provide db.

 

[renrenren]

Option Compare Database
Dim bQueryApplied As Boolean

Private Sub Form_Activate()
On Error GoTo ErrorHandler

If Not bQueryApplied Then
If Not IsNull(Me.txtSearchQuery) And Me.txtSearchQuery <> "" Then
If Me.NavigationSubform.Form.RecordSource <> Me.txtSearchQuery Then
Me.NavigationSubform.Form.RecordSource = Me.txtSearchQuery
Me.NavigationSubform.Form.Requery
End If
Else
If Me.NavigationSubform.Form.RecordSource <> "SELECT * FROM Coredata" Then
Me.NavigationSubform.Form.RecordSource = "SELECT * FROM Coredata"
Me.NavigationSubform.Form.Requery
End If
End If
bQueryApplied = True
End If

Exit Sub

ErrorHandler:
MsgBox "Error in Form_Activate: " & Err.Description, vbCritical
End Sub

Private Sub Form_Unload(Cancel As Integer)
Me.txtSearchQuery = Null
bQueryApplied = False
End Sub

Code in subforms
Option Compare Database

Private Sub Form_Current()
If CurrentProject.AllForms("Navigation_form").IsLoaded Then
Forms!Navigation_form!txtCurrentID = Me.ID
End If
End Sub

Private Sub Form_Load()
If CurrentProject.AllForms("Navigation_form").IsLoaded Then
Dim storedID As Variant
storedID = Forms!Navigation_form!txtCurrentID
If Not IsNull(storedID) Then
If Me.Recordset.NoMatch Or Me.Recordset!ID <> storedID Then
Me.Recordset.FindFirst "ID = " & storedID
End If
End If
End If
End Sub

 

[renrenren]

I have around 7 subforms in a navigation form that show all different pieces of data all about the same companies.
There is one with core data, another with their capabilities etc.
Using the current code, if I open navigation form I can view any records core data and when I switch to their capabilities I still see the same record as the ID numbers are synced.
But if I use a query that opens in the navigation from, when it opens I see record 1 of 50, which happens to be record number 150.
if i then move to another subform, I see the same record, e.g 150 but the query has been reset, so I see all records

 

[arnelgp]

Navigation Subform only holds 1 query/table at a time. Any filtering will be lost when changing Source Object of the navigation subform.

 

[renrenren]

Navigation Subform only holds 1 query/table at a time. Any filtering will be lost when changing Source Object of the navigation subform.

Only needs to have one.

What I want to happen is.
1.Create query with button push.
2. Display query results in navigation form
4. Move up records in core data subform.
5. Switch to another form
6. Still view the same record that I was viewing in core data.

Points 1-6 currently work fine when I just open the navigation form.
But only points 1-4 work when navigation form is opened by a query

 

[LarryE]

If all of your subforms can share a common field with the navigation form, then you can link them all using Master/Child fields

 

[renrenren]

If all of your subforms can share a common field with the navigation form, then you can link them all using Master/Child fields

Yes an ID, all linked to a primary key in another table.

Because the subforms are dynamically updated in the navigation form, I think I might need to create a query as there is no option to just add master/child links

 

[June7]

Right, NavigationSubform container on NavigationForm object does not have master/child links. Considering primary purpose of NavigationForm to dynamically display different datasets - one at a time - that don't have shared parent, the main form commonly doesn't even have a RecordSource.
An alternative is to emulate NavigationForm with a normal form/subform structure.

I am still rather lost on the "switch to another form" and "still view the same record" concept. Never really addressed my questions in post 4.

Second posted code lacks indentation formatting. Is this actually how it is in your module?

 

[Edgar_]

Clicking a navigation button will close the current form in the navigation subform and open the target form associated with the button clicked. Navigation buttons have a Where clause too. Keep that in mind.

1.Create query with button push.
2. Display query results in navigation form
4. Move up records in core data subform.
5. Switch to another form
6. Still view the same record that I was viewing in core data.

You have a textbox holding the current record in your main form. You also have a textbox where you are entering a RecordSource, which is weird because textboxes are typically for writing search terms not entire queries. By the way, the code you posted seems to be from two or more forms, you did not address that and it's making us confused.

I'll have to guess your game here. So let's say you have in main form:
1. A textbox to hold the current record
2. A textbox to write queries, for some reason, instead of search terms
3. A navigation form with 7 buttons

The idea is that, when you search from the textbox for queries, one subform by the name "Coredata" is filtered and when you enter that subform, depending on the record you select, the rest of the forms should load the same filtered recordset, but go to the record selected in Coredata.

If so, then simply filter each subform on load based on the textbox to write queries and then FindFirst. There is no need to check if the Navigation form is loaded if those forms are open only through the Navigation form.

Not knowing more, I can't recommend a better approach.

 

[Gasman]

Option Compare Database
Dim bQueryApplied As Boolean

Private Sub Form_Activate()
On Error GoTo ErrorHandler

If Not bQueryApplied Then
If Not IsNull(Me.txtSearchQuery) And Me.txtSearchQuery <> "" Then
If Me.NavigationSubform.Form.RecordSource <> Me.txtSearchQuery Then
Me.NavigationSubform.Form.RecordSource = Me.txtSearchQuery
Me.NavigationSubform.Form.Requery
End If
Else
If Me.NavigationSubform.Form.RecordSource <> "SELECT * FROM Coredata" Then
Me.NavigationSubform.Form.RecordSource = "SELECT * FROM Coredata"
Me.NavigationSubform.Form.Requery
End If
End If
bQueryApplied = True
End If

Exit Sub

ErrorHandler:
MsgBox "Error in Form_Activate: " & Err.Description, vbCritical
End Sub

Private Sub Form_Unload(Cancel As Integer)
Me.txtSearchQuery = Null
bQueryApplied = False
End Sub

Code in subforms
Option Compare Database

Private Sub Form_Current()
If CurrentProject.AllForms("Navigation_form").IsLoaded Then
Forms!Navigation_form!txtCurrentID = Me.ID
End If
End Sub

Private Sub Form_Load()
If CurrentProject.AllForms("Navigation_form").IsLoaded Then
Dim storedID As Variant
storedID = Forms!Navigation_form!txtCurrentID
If Not IsNull(storedID) Then
If Me.Recordset.NoMatch Or Me.Recordset!ID <> storedID Then
Me.Recordset.FindFirst "ID = " & storedID
End If
End If
End If
End Sub

Do not copy the code from here, as you have already lost indentation by not using code tags in the first place.Copy the code again from your DB. I do hope you are using indentation as well?

 

[renrenren]

Ok, sorry all, I inherited this database, not not sure why the code is the way it is.

So just to confirm.
There was an attempt to use a Excel workbook in access. The work book was so big the database was created with linked tables, primary key being ID.
A navigation form was created with the drag and drop feature and they want to make sure that when in the navigation form, if you move records, say from record 1 to 10, if you then click a subform you still see record 10.
This works fine with the posted code only when you view the navigation form in isolation. If you open the form via a query, the query is correctly shown in the first subform, but when you move to another subform it keeps the same record (based on ID) but its no longer filtered as per the query.

The code might be wrong, so it may need totally rewriting, which I'm not capable of.

I have been looking at making a form solely to hold a tab control, linked by the ID, to subforms to have the same effect, but can the results of a query be displayed in subforms in a tab control.

Thanks

 

[renrenren]

Actually I might try and push the id and company name from the query into the id and company name in the main form, which in theory should show the correct records in the subforms of the tab control

 

[Gasman]

I have never used a navigation form, not available in my last version of Access, and no need now.
I would just use a standard form and subform layout.
Then just use the standard linked fields.

 

[LarryE]

I have never used a navigation form, not available in my last version of Access, and no need now.
I would just use a standard form and subform layout.
Then just use the standard linked fields.

I think the OP is confusing terms. I think they are using a standard form and just naming it a "Navigation" form because it has sub-forms and queries as record sources.

 

[LarryE]

Ok, sorry all, I inherited this database, not not sure why the code is the way it is.

So just to confirm.
There was an attempt to use a Excel workbook in access. The work book was so big the database was created with linked tables, primary key being ID.
A navigation form was created with the drag and drop feature and they want to make sure that when in the navigation form, if you move records, say from record 1 to 10, if you then click a subform you still see record 10.
This works fine with the posted code only when you view the navigation form in isolation. If you open the form via a query, the query is correctly shown in the first subform, but when you move to another subform it keeps the same record (based on ID) but its no longer filtered as per the query.

The code might be wrong, so it may need totally rewriting, which I'm not capable of.

I have been looking at making a form solely to hold a tab control, linked by the ID, to subforms to have the same effect, but can the results of a query be displayed in subforms in a tab control.

Thanks

I think you will be much better off if you can attach the file and let someone look at it and recommend a course of action. From what you have said, you might be going down a road to nowhere if you inherited a system you don't know how to maintain or fix. The table and relationship structure is the most important thing.

 

[renrenren]

Thanks for your help.
I have fixed the issue now by creating main form with subforms in tabs and pushing the results of query into the main form.

 

[Pat Hartman]

Let's try to summarize. Sounds like you were using an actual Access Navigation Form so please confirm to help others who find this thread. That type of form is normally just a menuing method. It is not a "main form" bound to a "parent" record which is what you replaced it with and now you have a main form with a tab control and on each tab page, you have a subform. All the subforms have master/child links set to allow the main form to sync the subforms.

When you use an actual Access Navigation form you have an unbound main form with navigation options. The navigation options load ONE SINGLE subform at a time. When you choose a different navigation option, the previous subform is unloaded and is replaced with the chosen option. So, the first subform which may be what you were calling the "main form" cannot be used to control the other "sub forms" because it is not always present and so the master/child links. There are methods you can use to sync forms this way when using an actual Navigation form. They entail using hidden unbound controls on the unbound Navigation form. When the "main form" filters, the filter values are placed in the hidden unbound controls and then all the other forms are bound to queries which use the hidden unbound controls as arguments.

The concept of the Access Navigation form is just that. A form that is used as a menuing facility. the concept is not a parent form with related child forms. So, your current solution will be far better suited to your needs.

There is nothing inherently wrong with an Access Navigation form. You just have to understand how it works and realize that there is never more than ONE subform open at any time so they can't "talk" to each other. I've been using Access long enough to have created my own version of this form since it was first introduced in 2007 or maybe even 2010 and so I never adopted it. I just use my own custom forms. Plus, I actually prefer my own version of the old Switchboard as a navigation tool since it is data bound and therefore easy to expand/change. Rearranging the switchboard means changing the Switchboard Items table and no code is required.