Subform not visible when it contains no records

[Sendeman]

Hi All,

I am creating a form for editing and adding data in an access 2007 database. I have a problem with a subform turning invisible if there are no records in it for the current record in the main form.

I'll first describe the part of the database in which the issue occurs:

The database has well-data in it. From many wells a set of samples has been taken for lab-analyses. The results of these analyses may be from samples taken from the groundwater, the soil or another type of matrix.

To be able to store this, I have a table with well data, and a table with analyses-data. These are related through the field BhId (Borehole ID). In the Analyses table there's a field called Matrix that tells where the sample has been taken (groundwater, soil, etc.).

This is how the form looks:
I have a main form built upon the well-data table. It contains a few tabs. On one of them, a subform is located that contains the analyses data. This subform contains tabs for a couple of different sets of analyses. The sets all are analyses for the well currently selected in the main form, but they have been filtered into different sets on the previously mentioned matrix field. Each matrix tab then contains a continuous subform with the related analyses.
It looks more or less like this:

 _________________________________________
|  Main form                              |
|  __________ ____ ____                   |
| | analyses |____|____|________________  |
| |     ______ ____ ____                | |
| |    | soil |____|____|___________    | |
| |    |                            |   | |
| |    |____________________________|   | |
| |_____________________________________| |
|_________________________________________|

Now, all this works well. I get the right analyses records in each matrix tab for the well selected in the main form. I can edit and add new records to the analyses table and the wells table.

There is one problem though: if a well does not have any associated analyses, the entire matrix tab does not show (or more likely: the subform in which the matrix tab is placed does not appear). Now, I do understand that there is nothing to edit, because there are no records. But I would like to be able to add new records to the analyses tabel for wells that have no analyses in them yet. I don't see a form for that now.

So, what can I do to solve this? How can I get the analyses subforms to show up empty when there are no records associated to the current well?

Any help will be greatly appreciated.

Best regards,
Martijn Senden.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[MajP]

Can you explain how the subform link to the main form?
If you are using a standard linked subform, then you should get the effect you desire. My guess is you are doing some sort of coding to filter the subforms, instead of using the native feature. Are the subforms based on updateable queries?

 

[dhookom]

It sounds like you have only two tables that might be record sources for these forms. Can we assume the main form is bound to the well data? I would expect the matrix subform(s) to be linked directly to the [Borehole ID] field from the main form.

Duane
Hook'D on Access
MS Access MVP

 

[Sendeman]

Hi,

Ok, some more info

The rowsource of the main form is the table with Well Data (the table is called "Location").

The rowsource for the subform inside the tab called "analyses" is this SQL-string:

SELECT Interval.IntervalId, Interval.BhId, Interval.IntervalTypeId, Interval.Depth1, Interval.Depth2, Interval.Value, Interval.Comment, Interval.X1, Interval.Y1, Interval.Z1, Interval.X2, Interval.Y2, Interval.Z2, Interval.DatMons, Interval.DatAnal, Interval.Top_PB, Interval.Top_Putdeksel, tblIntervaltypesAanwezig.Name, Location.Elevation FROM ([Interval] LEFT JOIN tblIntervaltypesAanwezig ON Interval.IntervalTypeId=tblIntervaltypesAanwezig.IntervalTypeId) LEFT JOIN Location ON Interval.BhId=Location.BhId ORDER BY Interval.BhId, Interval.Z1, tblIntervaltypesAanwezig.Name;

The table Interval contains the analyses data. The table tblIntervalTypeAanwezig is a code table containing the substances for which analyses have indeed been carried out (there is another code table with all substances that could potentially be analyzed). I mainly use this code table to extract the names of the substances.

The result of the latter rowsource query returns the analyses for all matrices. And it relates the Analyses to the wells through the BhId field.
In this subform a tab for each matrix resides. On each tab is a subform that is exactly the same as the sql-code above, except for the where clause:

WHERE Interval.IntervalId Is Not Null AND Interval.Matrix="Soil"

Of course, the string "Soil" would change for each tab to the matrix in question.

These subforms are all related to their parent through the field BhId. I made sure the fielde is present on each subform (invisible).

I hope this helps!

Please note that I am allowed to add new records in the subforms for wells that already contain analyses. I just can't when the well contains no analysis at all. I don't get to see any tabs at all.

I've no idea what could be the cause. I hope someone does!

Best regards,
Martijn.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[Sendeman]

Update:

I tried changing the rowsource for the top level subform (the one right below the main form, that is within the Analyses tab). I changed the rowsource into the table "Location", since the form doesn't contain any fields except for the invisible BhId field (used to bind on). This subform is (as I understand it) necessary to let the nested tabs work properly.

The change in rowsource resulted in subforms for each matrix in which I do get tabs and the option for new records, however, the subforms are now no longer filled with existing records. I thought maybe something had gone wrong with the binding.

In summary, I now have a form bound to the table Location. In this form there is a subform also bound to the table Location. This subform is related to the main form through the BhId field. This should always give one record as a result, since BhId in the table Location contains unique codes for each record.

I did not change the rowsource for the subforms for each matrix (which are located in the subform mentioned above). They are related to their parent subform through the BhId field (as they already were).

Somehow, I now do get the tabs for the matrices for all the wells, but I don't get the analyses records that are already in the Interval table (for none of the wells).

So, I feel I'm close, but I can't get it to work!

Any more ideas anyone? Thanks in advance for any help.

Best regards,
Martijn Senden.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[dhookom]

There should be no reason to have the Location table in the Record Source of the subform. The Elevation field should be visible in the main form.

Also, I would probably remove tblIntervaltypesAanwezig since the name of the type could be displayed using a combo box bound to the Interval.IntervalTypeId field.

I would not have multiple matrix subforms. I would have a combo box on the main form that selects the matrix value like "Soil". Then have the one matrix subform linked Parent/Child using the BhId and Matrix fields.



Duane
Hook'D on Access
MS Access MVP

 

[Sendeman]

Hi, thanks for your reply!

- I thought the subforms within the subforms would have to be bound to a field in their parent form, and therefore a rowsource with the required field (BhId) was required, in this case the table location. Isn't that true? How should I then bind the subforms nested within a subform that has no rowsource?
- The elevation field is visible in the main form.
- The user must be able to enter (multiple) records with analysis data for any of the matrices within each well. A combobox on the main form wouldn't help me achieve that. I have tried a combobox on a subform that would change the contents of a combobox with the possible substances (the possible substances differ for each matrix). Since I'm using a continuous form for the data entry of the analyses, this was problematic. When a different Matrix was chosen, the combobox with the substances updated for all of the records. Not what I wanted. I messed around with overlapping text boxes and VBA-code to mimic these two comboboxes working together, but it was a mess and I couln't get it working. I talked to the prospective users and I think the tabs for each matrix are the most intuitive way to go for them. It is very similar to the way they are entering data right now (in Excel).

So....

I guess I still don't know what causes the problems in either of the situations described above: at first I had all the records that are already present, but I didn't get any tabs if no analyses were present for a well. In the second attempt I always get the tabs, but the existing records do not show up at all.

So, what may be going on? I need to have both the existing records to show up and I want to be able to add new analysis records (whether there already are any for the current well or not).

Any more ideas? Thanks again!!

Regards, Martijn Senden.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -

 

[Sendeman]

Hmm....

I solved the issue! I unconsciously made a rather stupid mistake when changing the rowsource of the subform that contains the tabs to the table Location. I somehow set that subform to data entry view. That explains why I didn't see any records.

I now get the records that are already present and I get an empty new record if no analysis records are present yet for the current well. Great!

Thanks for all your efforts!

Best regards, Martijn Senden.

I love deadlines. I like the whooshing sound they make as they fly by.
- Douglas Adams -