Using Access 2002. Main form based on table, two subforms: one as a datasheet of the same table, one as a datasheet of documents matching each record in that table (stored in a related table). Note: main table is for folders, related table is for documents.
Subform synching issue: Both subforms synch fine with the main form and change records as I navigate. The problem is that the datasheet for showing the folders in the main table only display a single row when linked to the main form. When not linked to the main form, of course the synch is absent.
I found one workaround for this but it didn't work. (Workaround tested: place a textbox on the main form whose control source is the indexed field, then link the difficult subform to that "control" instead of the field itself.)
Goal: To show all folders in datasheet view (or similar) and show all documents for selected folder (also in separate datasheet view).
I attempted to use a listbox to simulate the datasheet for the folders. This works somewhat, but will not allow me to use the listbox to cause the main form to navigate among the folders. In fact, I cannot click select in the listbox at all. It does however, show all folders as desired, and does select whatever folder record is current in main form navigation.
In either scenario described above, the main form opens/loads with editing, deletions and additions set to No (with a command button to turn them on). I set them all to Yes temporarily just to see if it would make a difference, but unfortunately it did not.
Any suggestions toward achieving the goal stated above? Could either be revising what I'm doing, or creating a new form/subform configuration altogether.
If I understand correstly, you're looking for a way to synchronize the second subform (let's call it SF_2) when another row is selected in the first subform (SF_1). Here's a solution:
1. In the module of the main (parent) form:
Option Compare Database
Private m_SF1Loaded As Boolean
Private m_SF2Loaded As Boolean
Public Sub RowChanged(ByVal Sender As String, Optional ByVal NewRow As Variant)
Static varNewRow As Variant
Select Case Sender
m_SF1Loaded = True
If m_SF2Loaded = True Then
varNewRow = NewRow
m_SF2Loaded = True
If Not IsEmpty(varNewRow) Then
Private Sub SynchSF2(ByVal NewRow As Variant)
Const c_Filter As String = "Field1 Like '@1*'" ' Replace by the proper filter expression.
If m_SF2Loaded = True Then
Me.Child_2.Form.Filter = Replace(c_Filter, "@1", NewRow)
Me.Child_2.Form.FilterOn = True
2. In the module of SF_1:
Option Compare Database
Private Sub Form_Current()
' Replace Field1 by the name of the control used for the synch.
Me.Parent.RowChanged Me.Name, Me.Field1.Value
3. In the module of SF_2:
Option Compare Database
Private Sub Form_Current()
Hi Sinndho, Thanks for the response. Since my VBA programming skills are very limited, I barely understand (portions of) the routines you provided. Of course, I don't always understand code I test or use. At this point, I don't understand how to try to apply these routines.
In my original post, I somewhat described two scenarios: one with two subforms as two datasheets, and the other as two subforms as a listbox and a datasheet. In the first of these two scenarios, there are not two rows displayed in the first datasheet in order to select one to synch with a record in the second datasheet, as linking reduces the first datasheet's display to a single row. In the second scenario, the first subform is a listbox rather than a datasheet, but although multiple rows are displayed, I cannot click into the listbox at all. Anyway, I'm unclear how to utilize the code posted.
If you can provide a little information on which scenario (or a 3rd different scenario), and what code applies to what, I'll make a copy of the database and attempt implementing the code.
Please note that in both scenarios I described, both subforms synch with and follow the navigation of the main form, and thus are indirectly synched with each other. It's just that in one, I have a first subform with a datasheet that only has one record displayed, and in the other the listbox is synched but is not useful for navigating the main form through the recordset.
screenshot images for both scenarios can be viewed at: DCOUCH-post
Note in both that the MainFolder field appears in the Main Form, and in each subform.
• The Main Form is based on tblMainFolderONLY with MainForm as primary key.
• The TOP area is a listbox with Control Source as MainFolder and a Row Source as a 5-field SELECT statement based on the same table.
• The BOTTOM subform (datasheet) has tblDOCUMENTSonly as Record Source...and is linked by its MainFolder field to the MainFolder "field" in the table underlying the Main Form.
• (same as Scenario1): The Main Form is based on tblMainFolderONLY with MainForm as primary key.
• The TOP subform (datasheet) has a 5-field SELECT statement based on the same table...and is linked by its MainFolder field to a MainFolder textbox "control" on the Main Form (upper right).
• (same as Scenario1): The BOTTOM subform (datasheet) has tblDOCUMENTSonly as Record Source...and is linked by its MainFolder field to the MainFolder "field" in the table underlying the Main Form.
Editing, deletions and additions are OFF.
(Misc. Info not directly related to this form: This Main Form is opened from the primary Main Form, with a similar configuration in Single Form View...which shows MainFolder field along with its four description fields one record at a time, and has a different but almost identical lower subform displaying documents linked to follow MainFolder navigation. Editing is mainly done on yet another secondary Main Form, allowing folders, location/descriptions and documents to be added, deleted or edited.)
I don't understand how your project is organized. Usually, you create an interface with a main (Parent) form and a subform to be able to handle One-to-Many relationships: The main form shows records from the One table and for each row in this table there is one or several rows in the Many table that are displayed in the subform in datasheet view.
A problem occurs with this schema when there are 3 tables involded in the relationships. For instance a table holds customers data and a second table holds Purchases data. The relationship is One (Customers) to Many (Purchases). Now you have a third table that holds Items data. An purchase can concern more than one item, so there is a One (Purchases) to Many (Items) relationship between both tables.
When you add a second subform to display the items related to one purchase, it's not easy to synch this subform with the subform displaying the purchases. I thought that that was the situation you described with one row in tblMainFolderONLY related to several rows in a Folders table and one row in this Folders table related to several rows in a Tables folder. The schematic code I provided can solve this kind of problem.
From what you describe, why do you need a listbox or a subform in datasheet view if it's to display one row only?
Hi Sinndho, Understandable that my configuration seems odd. Although items, contacts and medications are setup in this same database, each of these are on separate tabs, and their forms and routines are not related to those for documents. The form on the tab for documents is the primary main form for that purpose.
For documents, there are two tables, related to each other: tblMainFoldersONLY is "1", and tblDOCUMENTSonly is "many". On the main form mentioned above, documents are displayed in a subform on the main form, with the main form (on tab) bound to tblMainFolderONLY. This tab-resident form and subform display correctly with the main form in single form view, and the documents subform in datasheet view. (Note that I am not speaking of the form we're discussing, but the one that calls it...because you mentioned "items".) This main form calls two other forms and closes while they are open. The first is the edit form. The second is the datasheet form we've been discussing and looking at (including the screen shots provided). The edit form also works correctly.
A further note on relationships is that tblMainFolderONLY holds unique records for folders, and also includes 4 other fields. 3 of those other fields also have their own tables where unique DocumentCategory, RoomCabinet and Drawer are stored. (the 4th field is for misc. notes and is not related or existent elsewhere) Each of the 3 related fields are in 1 to many relationships with their respective fields in tblMainFolderONLY.
I've added a screen shot of the relationships window at the top of the page I provided with images (same link, but shown here now as DCOUCH-post-images): DCOUCH-post-images
In the relationships window screen shot, the top and middle sections are not document-related, and are not involved in what we are discussing at all. The lower section shows first the primary 1 to many relationship between the main form and the documents subform tables, and also shows the last 3 many to 1 relationships for the 3 folder-related descriptors which have separate unique tables. (i.e.-a main folder can only exist in one unique category, in one specific room and cabinet, and in one specific drawer per assignment)
So, in the form we are discussing, which is the only form open, this folder-based form is the parent. The goal or intention is for it to display all 5 fields of tblMainFolderONLY as rows, and show ALL rows of that table...not one row. Then, also displayed is the documents subform in datasheet view, related to the main form by relationship as the "many" side, and by linking properties by child on MainFolder field, and by master on txtMainFolderDocsLinkingAlternate (a control on the main form displaying the MainFolder field).
If you look at my two scenario screen shots, the first (with the listbox) shows what I want the form to look like; however, it was not my original intention to use a listbox. The second scenario screen shot shows a copy of the same form using a datasheet instead of a listbox. That is what is preferred; however, I have been unable to get the datasheet to display ALL of the rows while the subform datasheet is bound (which is why I tried to use a listbox, playing around with whatever might work). It is not intended to keep both of these. It is only intended to meet the goal of having a display approximately like the first scenario, but which will let me click on a row, and have the main form navigate to whatever row I've clicked on.
At present, with the listbox, I cannot click to highlight a row at all, nevertheless navigate that way. And with the second scenario using a datasheet, clicking is pointless as it refuses to display more than one row. That leaves me needing to use only the preset navigation arrows to navigate through the folders. So neither scenario does what I want. Both scenarios do however, track along with the main form navigation arrows when I use them (i.e.-the top listbox or datasheet highlights or changes, and the bottom documents datasheet displays the documents related to whichever folder is highlighted in the listbox scenario, or displayed as a single row in the datasheet scenario). But I don't want it to display only a single row...but ALL rows of folder records.
Thanks for taking the time to look at it. I can rebuild a form from scratch if needed, if it will meet the goal criteria stated. Just not very good at it. Fortunately, it's for personal use.
I did try to interpret the code you provided, changing to my subform names and field name...but with no success. Although I've written some very basic filters, I was at a loss as what to use for a filter expression (tried this also).
Whether I understand all the code or not, it is at least clear that...
1. Removing the child/master linking causes the datasheet subform to display all records, and some sort of code to synch the two subforms is needed to use instead of the built-in linking.
My problem would seem to be that, even if code were used, if that code employed a filter, the datasheet subform would display only the records (in this case one record) allowed by the filter, and thus I would pretty much be at square one. In short, synching the two subforms to the main form has never been the problem...but only getting the first subform to display all records like the second subform does. I did try using a query rather than the table, but had the same results (although perhaps I needed some special form and format query?).
I'm thinking that rather than synching the first and second subform, I should take a look at synching the first subform with the main form without using child/master properties, as the second subform is already synched to the main form. With that configuration, both subforms would match.
Anyway, I monitor and refresh this thread periodically to see if any new information is available.
I had failed to follow correctly a technique tried earlier (found elsewhere). But now got it working as desired. Basic concept:
• Unbound main form
• On main form, a textbox field with Control Source as...
• When setting up subforms, use a name that is different from its Source Object
• Subform being used as a master (other subform follows navigation of this form) is not linked using Link Child Field & Link Master Field properties...and is based on the table both subforms are to follow navigation of...is set to default to Datasheet View
• Subform being used as related detail (follows navigation of the first subform) is linked on Link Child Field to the primary key field which is related to the first subform's underlying table...and is linked to the name of the textbox field (above) which uses an expression for its Control Source...and is set to default to Datasheet View.