Elegant solution to a multi-table datasheet?

[Matter]

Hi All,

Sorry for the long winded post, but hopefully the attached images will help illustrate the problem:

I'm currently developing and adding features to a rather complex database that has scaled well past its original use (isn't that most databases?); a geologic database with hundreds of thousands of records, which are split into many tables with complex relationships.

I've been asked to add a new form for adding in a slightly different type of data, but which does not require addition or restructuring of existing tables.

This form will be used to enter data that has been recorded on paper into the database (see "Paper Form.pdf"). The detailed data entry form ("detailed form.png") is too cumbersome for fast data entry, and so I have started devising a new form ("new form.png"), but have hit a road block integrating all of the tables into one datasheet. The requirements is that the "FROM", "TO", and "HOLE-ID" values would need to be entered once, and then replicated to several tables, while allowing editing across those tables for other fields (see "relationships.png") A query works fine for pulling the data out, but I cannot add new records, which is the goal.

Ideally, I would like to accomplish this without having to code new export queries and macros (a daunting task), and allow the new data to be integrated into the existing data structure.

So what do you think? Do-able? Or messy and cumbersome? Unfortunately I'm on a short deadline, so a dirty hack will have to suffice until I can come up with an elegant solution to the problem, but it would be nice to get off on the correct foot.

 

[Matter]

I've been doing some research, and I may be able to accomplish something using subforms... However I am having difficulty getting it to grab the from-to from the parent table and replicating it to the other ones. Anyone have any experience with this? I might be able to do it on a onfocus or something...

http://office.microsoft.com/en-us/access/HA102668001033.aspx

 

[vbaInet]

Your form looks very cumbersome, lots going on there. I was comparing your form with the paper form and my advise would be to try to make it look similar to that or the other way round.

Not quite sure what your question is though?

 

[Matter]

Hi vbaInet, the thing is that the table-infrastructure is already in place. Sure, I could make a new table that exactly matches the form, but then a geologist would only be able to put one alteration, mineralization, etc per depth record. By having the tables separate, it allows 1 meter mark to represent a greater number of individual observations...

The top part of the New Form is not the issue, it's the bottom part I'm trying to work with.

The first step (I think) is linking the main-geology datasheet to the sub-geology datasheet. The two of them will share 4 fields: From, To, Hole-id, and mainid. The values that need to be put into the sub-geology form would be rock code, percent, etc. Unfortunately, thus far it appears that using a sub-datasheet will only link on 2 of the 4 fields I want to link on... so the rest might be accomplished using VB?

 

[vbaInet]

Hi vbaInet, the thing is that the table-infrastructure is already in place. Sure, I could make a new table that exactly matches the form, but then a geologist would only be able to put one alteration, mineralization, etc per depth record. By having the tables separate, it allows 1 meter mark to represent a greater number of individual observations...

The top part of the New Form is not the issue, it's the bottom part I'm trying to work with.

The first step (I think) is linking the main-geology datasheet to the sub-geology datasheet. The two of them will share 4 fields: From, To, Hole-id, and mainid. The values that need to be put into the sub-geology form would be rock code, percent, etc. Unfortunately, thus far it appears that using a sub-datasheet will only link on 2 of the 4 fields I want to link on... so the rest might be accomplished using VB?

I get you now. Open up the properties of the subform and under the Data tab select your main form as the Source Object property. Then in the Link Master Fields property select the ID on the main form that links the subform, and under Link Child Fields select the same ID that links to the main form (the FK of the main form).

 

[Matter]

I get you now. Open up the properties of the subform and under the Data tab select your main form as the Source Object property. Then in the Link Master Fields property select the ID on the main form that links the subform, and under Link Child Fields select the same ID that links to the main form (the FK of the main form).

Hmmm I'm not so sure I get you there.

I've been working a sub-datasheet method that is dirty but might work for starting data entry today: see the screenshot.

The way it's set up now is:
the main form is linked to the first subform via HOLE-ID.
The first subform is linked to the ones below it using HOLE-ID plus MAINID

What is the FK that you are speaking of?

I think the child fields are set up OK, but I can only link 3 I think. See the 2nd screenshot.

Thanks for the help!

 

[Matter]

Non-elegant solution to the problem.

OK; well I thought I was going to be able to do it using a split form as a subform, but the split form wasn't functioning as a sub-form, and after reading this:
http://www.access-programmers.co.uk/forums/showthread.php?t=133594

I decided to try something else. My next thought was to put the header information in the form header of the split form, but this conflicts with the way the tables are set up in the database, and additionally the split forms won't keep the header above the datasheet, so I was forced to split the header off into a pop-up, and keep them in two separate forms.

The result works, but is not ideal. If you can see a better way of doing this, let me know, but for now it is functional.

Thanks again for the ideas!

 

[vbaInet]

Re: Non-elegant solution to the problem.

OK; well I thought I was going to be able to do it using a split form as a subform, but the split form wasn't functioning as a sub-form, and after reading this:
http://www.access-programmers.co.uk/forums/showthread.php?t=133594

I decided to try something else. My next thought was to put the header information in the form header of the split form, but this conflicts with the way the tables are set up in the database, and additionally the split forms won't keep the header above the datasheet, so I was forced to split the header off into a pop-up, and keep them in two separate forms.

The result works, but is not ideal. If you can see a better way of doing this, let me know, but for now it is functional.

Thanks again for the ideas!

I think your solution is a combination of the "dirty_hack" and your "newform.png". Have a look at my attachment and tell me your thoughts.

 

[Matter]

Re: Non-elegant solution to the problem.

Have a look at my attachment and tell me your thoughts.

Yeah, that is effectively what is set up. In my database, HOLE-ID (your HOLE-ID_FK) groups all of the holes together, and MAINID allows detail to be added to depth intervals (your LithoID_FK).

The problem I ran into is that I can't use a split form as a sub-form, so I can't have the header info (HOLE-ID) together on the same form as the MAINID linked values. The split form does the job OK because it allows more than one of the alteration/min/struct/etc per lithology entry, but it would be a bit nicer if everything was in one place, and even nicer if I could keep them on one line.

Aesthetically I liked using subdatasheet lines, but unfortunately you can only keep them in a hierarchy:

Litho 1
> Litho 2
>> Structures
>>> Alteration

works, but

Litho 1
> Litho 2
> Structures
> Alteration

does not.

This becomes a problem in cases when a geologist wants to enter a structure, but does not have a secondary lithology to note, for example:

if Litho 2 is null

Litho 1
> Litho 2 (null)
>> Structures (not available for data entry)
>>> Alteration (not available for data entry)


So, in the end the split form is the best option I think, It's just a bit visually cumbersome. I also had to pull a trick with hiding a pop-up form that had the HOLE-ID stored in in it (from the drillhole header) in order to make it the default value (FK) for new records being created within the split form... Also a really dirty hack. I saw some solutions using VB modules, but I realized that bug after I had already deployed the new front end, and I needed a quick fix. (Bad practice, I know, but I'm under the gun a bit)

Anyway, thanks again for helping me brainstorm, and if you think of another solution I would really appreciate it!

Cheers
m

 

[vbaInet]

I still haven't gotten a clear picture of how everything should be interlinked. Are Veins & Structures, Mineralization, Alteration and Samples all subs of Lithology? What is the hierarchical structure between these five?

 

[Matter]

I still haven't gotten a clear picture of how everything should be interlinked. Are Veins & Structures, Mineralization, Alteration and Samples all subs of Lithology? What is the hierarchical structure between these five?

The database is set up to allow a geologist to generalize, and then get progressively more detailed such that whatever level of detail is required can be pulled out of the database and used in GIS / AutoCAD, etc depending on who is doing the analyzing.

It works like this:

The veins/structures, mineralization, and alteration tables all provide additional details about the main lithology, but are not required and thus are not kept in the same table, as there can be 1 or 1000 per lithology interval (litho 1). Litho 2 is actually a direct subset of litho 1, which can also have entries in the veins/structures, mineralization, and alteration tables to provide more detail and so on and so on. It's a super complex beast, but it thus far has handled everything we've thrown at it. Perhaps this screenshot of the backend relationships is the most informative:

 

[vbaInet]

The database is set up to allow a geologist to generalize, and then get progressively more detailed such that whatever level of detail is required can be pulled out of the database and used in GIS / AutoCAD, etc depending on who is doing the analyzing.

It works like this:

The veins/structures, mineralization, and alteration tables all provide additional details about the main lithology, but are not required and thus are not kept in the same table, as there can be 1 or 1000 per lithology interval (litho 1). Litho 2 is actually a direct subset of litho 1, which can also have entries in the veins/structures, mineralization, and alteration tables to provide more detail and so on and so on. It's a super complex beast, but it thus far has handled everything we've thrown at it. Perhaps this screenshot of the backend relationships is the most informative:

I've got you now. I've got some form design ideas for you so I will draw it up and send it in a few minutes. My other diagram fits exactly what you've just described but I'm not sure if that's how you've set up your tables? Why do you use MainID instead of LithologyID? Or is that what you called your ID field in the Lithology table?

For Litho1 and Litho2, why not create a list of values within a field called LitholoygyType and the two values would be Litho1 and Litho2. So during data entry it will become a drop down list with those two fields.

 

[vbaInet]

Lots of webs in that relationships diagarm Am I right to say that the DrillHole_Geol_Main table represents the Lithology side of things? I have attached my thoughts on how you could design this. The four boxes represent subforms. To get around being able to get all the fields you require for your form, you could go about it this way.

Following my diagram, for the main form access create a query that will include all the fields listed on the main form plus those in Lithology only. Since you have lots of fields already on the main form, instead of selecting individual records, just select all records from the tables by double clicking the asterisk. On the Lithology subform, you should include the foreign key that links to the main form plus all the fields that Mineralization, Samples (etc...) will contain. Let me know your thoughts.

 

[Matter]

Am I right to say that the DrillHole_Geol_Main table represents the Lithology side of things?

Yup: I was simplifying the cryptic names earlier.

Following my diagram, for the main form access create a query that will include all the fields listed on the main form plus those in Lithology only.
Since you have lots of fields already on the main form‚ [...] On the Lithology subform, you should include the foreign key that links to the main form plus all the fields that Mineralization, Samples (etc...) will contain. Let me know your thoughts.

I think this would work, but wouldn't it would show every single alteration, structure, etc. for an entire hole at once? If so, it is asking for data entry errors.

In the split form, if you select one record from the GEOL_MAIN table, it will automatically filter the 5 sub-forms to only show the related records for that interval. Your solution allows for a more aesthetic look to the form (very desirable) but wouldn't we would need VB code to filter the other 5? This is what I came up with just thinking it through, but I'll try it and get back to you in a bit.

Thanks!

 

[vbaInet]

Yup: I was simplifying the cryptic names earlier.



I think this would work, but wouldn't it would show every single alteration, structure, etc. for an entire hole at once? If so, it is asking for data entry errors.

In the split form, if you select one record from the GEOL_MAIN table, it will automatically filter the 5 sub-forms to only show the related records for that interval. Your solution allows for a more aesthetic look to the form (very desirable) but wouldn't we would need VB code to filter the other 5? This is what I came up with just thinking it through, but I'll try it and get back to you in a bit.

Thanks!

To solve that you would link each of your four subforms to Lithology via Main_ID. I think you mentioned that earlier? Lithology will be a subform of Hole_ID's form, and the other four forms will be subforms of Lithology. All these subforms (however) will be placed on the main form like I drew or as you wish.

For you to have the four subforms, you will need to create forms for each of them. Ensure that each one of them have the Main_ID field as part of the record source. Set the Default View (under the Format tab) of the form to Datasheet. This would work, don't you think?

See attached a test database I just did. Ignore the relationships but have a look at the code on both events. Move between records on the main form, or move do so in Lithology. This might be what you need. The trick is how to reference a parent form from child form, and vice versa. Let me know if you were able to incorporate this into your db.

 

[Matter]

See attached a test database I just did. Ignore the relationships but have a look at the code on both events. Move between records on the main form, or move do so in Lithology. This might be what you need. The trick is how to reference a parent form from child form, and vice versa. Let me know if you were able to incorporate this into your db.

Wow, thanks for putting in the time to make such a nice example! I have my form set up in a way that I think will work, but I'm stumbling over the code:

Private Sub Form_Current()
    [B]If tblLithoID.Value <> "" And IsNull(tblLithoID) = False  Then[/B]
        Forms!frmMain!frmSubMineral.Form.Filter = "[LithoID_FK] = "

I noticed that in your Lithology table, you keep the PK named differently than you do your FKs in the linked fields. With that line of code, it sounds like that is a prerequisite, yes? If so that could be a deal braker, as there are 100,000s of records already in this database, and making such a change is out of the question. My VBA-fu is not strong, so I might be misreading it... Your test database is pretty much exactly what I'm aiming for though, so thanks!

Cheers,
m

 

[vbaInet]

Private Sub Form_Current()
    [B]If tblLithoID.Value <> "" And IsNull(tblLithoID) = False  Then[/B]
        Forms!frmMain!frmSubMineral.Form.Filter = "[LithoID_FK] = "

I noticed that in your Lithology table, you keep the PK named differently than you do your FKs in the linked fields. With that line of code, it sounds like that is a prerequisite, yes?[/quote]


The naming convention is just preference. I use LithoID_FK so that when I'm creating queries or building an sql string I can different easily differentiate between a foreign and primary key. Don't worry much about the setup, it was just a quick database I setup so I can test my code.

Once you've added the code, go back to the properties view of your main form and delete that "[Event Procedure]" line in the On Current. This just means the code will still remain but it will not be run until you set that event to run, which is what this code does:

Forms!frmMain!frmLithology.Form.OnCurrent = "[Event Procedure]"

Few questions then. Does it compile? What errors do you get? And where did you place your subforms? A diagram would do.

Tell me the names of one of your subforms, the name of the Lithology form and that of the Main form.

 

[Matter]

Few questions then. Does it compile? What errors do you get? And where did you place your subforms?

Sorry it took me so long to get back, I've had my hands tied with a lot of other projects, so I just finished the changes last night. I needed to remove whitespace from my form names in order to get your script to work (I decided it was better to eliminate the bad-practice rather than define the form names as variables), so after a bit of modification to my database, this code worked fine:

   Private Sub Form_Current()
    If MAINID.Value  "" And IsNull(MAINID) = False Then
        Forms!RC_Log!RC_structures_subdatasheet.Form.Filter = "[MAINID] = " & Me.MAINID
        Forms!RC_Log!RC_alteration_subdatasheet.Form.Filter = "[MAINID] = " & Me.MAINID
        Forms!RC_Log!RC_mineralization_subdatasheet.Form.Filter = "[MAINID] = " & Me.MAINID
        Forms!RC_Log!RC_structures_subdatasheet.Form.FilterOn = True
        Forms!RC_Log!RC_alteration_subdatasheet.Form.FilterOn = True
        Forms!RC_Log!RC_mineralization_subdatasheet.Form.FilterOn = True
   End If
End Sub


 Private Sub Form_Open(Cancel As Integer)
    Forms!RC_Log!RC_litho_MAIN_datasheet.Form.onCurrent = "[Event Procedure]"
End Sub

Unfortunately when new records are being created in the sub forms, it does not use the filtered MAINID as the default value; To remedy this I used

 =[Forms]![RC_Log]![RC_litho_MAIN_datasheet].[Form]![MAINID]

as the default value for these fields, and then hid the columns in the datasheets. When the form first opens, all of the records for the entire hole are visible, as filtering doesn't start until a record is selected; this might allow a user to circumvent the default value and therefore create an orphaned record. Do you know a code snippet for auto-selecting the first hole in the list on form_open?

Re: the DB structure, it is set up the exact same that your trial database was. Sweet!

Thanks again!

 

[vbaInet]

Sounds like you've been working hard on your database. Great job!

If I got some time I will implement a nice design trick which I think you will find useful.

Are your holes sorted in order?

 

[vbaInet]

Here you go. See if you can spot the new feauture. It's more of aesthetics than logic.