Elegant solution to a multi-table datasheet?

[Matter]

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

Hey that's pretty cool! That trick allows me to lay the form out just like the paper log, and then expand the additional data when needed. Sweet!

 

[vbaInet]

Yes, it's very useful. I use for hiding/unhiding a search pane.

Have you managed to find a solution to your hole problem or do you still need some ideas?

 

[Matter]

Have you managed to find a solution to your hole problem or do you still need some ideas?

Hi vbaInet,

sorry for the late response; when I tried to get on over the weekend the website was all fubar for unknown reasons.

Anyway, I did get it all tip-top, thanks for asking: just added a goto-first-record to the on-open of the subform and everything's spiffy! Since this new form is so awesome, I've been toying with getting the code to pass the SUB1ID fk to the mineralization, alt, etc. if the sub-lithology record is selected. I think it shouldn't be so difficult, but I haven't put the time in yet as I've been busy with other things. Thanks again!

m

 

[vbaInet]

Hello there,

I'm pleased that you are quite happy with your solutions.

You're most welcome. You know what to do if you get stuck.

Cheerios!

 

[Matter]

Hi Again,

I implemented your awesome form-hiding functions, but at some point I decided to make a show/hide button for the header. Since the header won't let me resize it unless I make all of the individual buttons small and moved to the top, I decided it was easier to make the entire thing not visible, and then have three fields shown at the top of the body for identification. The problem comes when un-hiding the header part: Access gives an error:

Runtime error '2455':

You entered an expression that has an invalid reference to the property Form/Report.

Clicking "Debug" highlights the first line of your code:

Forms!RC_Log!frmSubStruct.Form.Filter = "[MAINID] = " & Me.MAINID

Any ideas? I've tried debugging the heck out of it for a week with no luck. Tonight I'll strip all of the confidential data out of the base and post it, in case I introduced the error. Thanks again for your help!

 

[vbaInet]

What header are you referring to, the main form or the subform?

 

[Matter]

In the Form header of the simplified log form.

I'm not positive, but I think it has something to do with reloading the data with the focus inside of the body instead of the header, maybe calling the refresh-script out of order?

 

[vbaInet]

Can't picture what could be going on unless I saw the database.

In the meantime check the order of your code:

Make Visible, then Filter

 

[wstech]

Aloha,

i was able to implement this code into my dBase, very nice by the way, I am trying to get the code to work on three subforms, i have tried several rewrites, can close and open the forms I can get them to move but i can't get them to go back to the original set up. I am sure it is is in the way the code is ordered but i can't find it, any ideas or suggestions would be great.

Thanks,
Wes

 

[Matter]

Aloha!

i was able to implement this code into my dBase, very nice by the way, I am trying to get the code to work on three subforms,

Which part of the code are you trying to get running? There are 2 parts; one that passes PK and FK to forms based on the selection in a datasheet subform, and the second which implements show and hide buttons to clean up the form.

You can post your code and we'll look at it to see if anything stands out.

 

[wstech]

I am trying to get the show/hide code to work, i was able to get the code with a few changes to work for 2 subform. Here is the code that I tried for 3 subforms: (not sure I am on right track)

Private Sub cmdSquad1Shutter_Click()
Dim space1Between As Integer
Dim space2Between As Integer
Dim org1top As Integer
Dim org2Top As Integer

If cmdSquad1Shutter.Caption = "-" Then
cmdSquad1Shutter.SetFocus
sfrmCalendarSquad1.Visible = False
space1Between = lblSquad2.Top - sfrmCalendarSquad1.Top - sfrmCalendarSquad1.Height
org1top = lblSquad2.Top
lblSquad2.Top = lblSquad1.Top + lblSquad1.Height + space1Between
cmdSquad2Shutter.Top = cmdSquad2Shutter.Top - (org1top - lblSquad2.Top)
sfrmCalendarSquad2.Top = lblSquad2.Top + lblSquad2.Height ' Or sfrmCalendarSquad2.top = sfrmCalendarSquad2s.top - (origtop - lblSquad2.top) (not tried but should work)
cmdSquad1Shutter.Caption = "+"

cmdSquad2Shutter.Caption = "-"
cmdSquad2Shutter.SetFocus
sfrmCalendarSquad2.Visible = True
space2Between = lblSquad3.Top - sfrmCalendarSquad2.Top - sfrmCalendarSquad2.Height
org2Top = lblSquad3.Top
lblSquad3.Top = lblSquad2.Top + lblSquad2.Height + space2Between
cmdSquad3Shutter.Top = cmdSquad3Shutter.Top - (org2Top - lblSquad3.Top)
sfrmCalendarSquad3.Top = lblSquad3.Top + lblSquad3.Height

cmdSquad3Shutter.Caption = "-"
cmdSquad3Shutter.SetFocus
sfrmCalendarSquad3.Visible = True

Else
space1Between = lblSquad2.Top - lblSquad1.Top - lblSquad1.Height
org1top = lblSquad2.Top
lblSquad2.Top = sfrmCalendarSquad1.Top + sfrmCalendarSquad1.Height + space1Between
cmdSquad2Shutter.Top = cmdSquad2Shutter.Top + (lblSquad2.Top - org1top)
sfrmCalendarSquad2.Top = lblSquad2.Top + lblSquad2.Height

space2Between = lblSquad3.Top - lblSquad2.Top - lblSquad2.Height
org2Top = lblSquad3.Top
lblSquad3.Top = sfrmCalendarSquad2.Top + sfrmCalendarSquad2.Height + space2Between
cmdSquad3Shutter.Top = cmdSquad3Shutter.Top + (lblSquad3.Top - org2Top)
sfrmCalendarSquad3.Top = lblSquad3.Top + lblSquad3.Height

sfrmCalendarSquad1.Visible = True
cmdSquad1Shutter.SetFocus ' Maybe setfocus to the Mineral id or comment or other field in Mineral subform.
cmdSquad1Shutter.Caption = "-"
End If
End Sub

Private Sub cmdSquad2Shutter_Click()
Dim space1Between As Integer
Dim space2Between As Integer
Dim org1top As Integer
Dim org2Top As Integer

If cmdSquad2Shutter.Caption = "-" Then
cmdSquad2Shutter.SetFocus
sfrmCalendarSquad2.Visible = False
space2Between = lblSquad3.Top - sfrmCalendarSquad2.Top - sfrmCalendarSquad2.Height
org2Top = lblSquad3.Top
lblSquad3.Top = lblSquad2.Top + lblSquad2.Height + space2Between
cmdSquad3Shutter.Top = cmdSquad3Shutter.Top - (org2Top - lblSquad3.Top)
sfrmCalendarSquad3.Top = lblSquad3.Top + lblSquad3.Height
cmdSquad2Shutter.Caption = "+"

cmdSquad1Shutter.Caption = "-"
cmdSquad1Shutter.SetFocus
sfrmCalendarSquad1.Visible = True
space1Between = lblSquad2.Top - sfrmCalendarSquad1.Top - sfrmCalendarSquad1.Height
org1top = lblSquad2.Top
lblSquad2.Top = lblSquad1.Top + lblSquad1.Height + space1Between
cmdSquad2Shutter.Top = cmdSquad2Shutter.Top - (org1top - lblSquad2.Top)
sfrmCalendarSquad2.Top = lblSquad2.Top + lblSquad2.Height
Else
space2Between = lblSquad3.Top - lblSquad2.Top - lblSquad2.Height
org2Top = lblSquad3.Top
lblSquad3.Top = sfrmCalendarSquad2.Top + sfrmCalendarSquad2.Height + space2Between
cmdSquad3Shutter.Top = cmdSquad3Shutter.Top + (lblSquad3.Top - org2Top)
sfrmCalendarSquad3.Top = lblSquad3.Top + lblSquad3.Height

sfrmCalendarSquad2.Visible = True
cmdSquad2Shutter.SetFocus
cmdSquad2Shutter.Caption = "-"
End If

End Sub

Private Sub cmdSquad3Shutter_Click()
Dim space1Between As Integer
Dim space2Between As Integer
Dim org1top As Integer
Dim org2Top As Integer

If cmdSquad3Shutter.Caption = "-" Then
cmdSquad3Shutter.SetFocus
sfrmCalendarSquad3.Visible = False
cmdSquad3Shutter.Caption = "+"
Else
sfrmCalendarSquad3.Visible = True
cmdSquad3Shutter.SetFocus
cmdSquad3Shutter.Caption = "-"
End If

End Sub

 

[Matter]

Hey, sorry for the late reply. Have you made any progress? I've looked at your code, and it doesn't look very different from mine, so perhaps the problem is elsewhere?

Are you getting any errors? Try putting a break halfway through the VB compiler and see what the values for space1Between are, etc.

Sorry I can't be more helpful!

 

[wstech]

I had it working and then i fooled around with it and broke. I will get it fixed and post the code, been busy with work.

 

[gemma-the-husky]

can I make a point? - at the beginning you noted that databases grow, which is fair enough.

However, if the table design was correctly normalised then adding extra functionality shouldnt involve jumping through hoops. The fact that you say that you need to add a number of fields to other tables, seems to indicate to me that there are some underlying design issues, which invariably lead to difficulties in modifying/adding features.

I would be inclined to examine the table structure at some point.

 

[wstech]

Thanks for the advice, I have gone through the tables and stripped out all the extra stuff. Now I am trying to put together a display that meets with the design requirements.

 

[wstech]

here is what I came up with for three Subforms:

Option Compare Database
Option Explicit

Private Sub cmdSquad1Shutter_Click()
Dim space1Between As Integer
Dim space2Between As Integer
Dim orig1Top As Integer
Dim orig2Top As Integer

If cmdSquad1Shutter.Caption = "-" Then
cmdSquad1Shutter.SetFocus
sfrmCalendarSquad1.Visible = False

space1Between = lblSquad2.Top - sfrmCalendarSquad1.Top - sfrmCalendarSquad1.Height
space2Between = lblSquad3.Top - sfrmCalendarSquad2.Top - sfrmCalendarSquad2.Height

orig1Top = lblSquad2.Top
lblSquad2.Top = lblSquad1.Top + lblSquad1.Height + space1Between
cmdSquad2Shutter.Top = cmdSquad2Shutter.Top - (orig1Top - lblSquad2.Top)
sfrmCalendarSquad2.Top = lblSquad2.Top + lblSquad2.Height

orig2Top = lblSquad3.Top
lblSquad3.Top = sfrmCalendarSquad2.Top + sfrmCalendarSquad2.Height + space2Between
cmdSquad3Shutter.Top = cmdSquad3Shutter.Top - (orig2Top - lblSquad3.Top)
sfrmCalendarSquad3.Top = lblSquad3.Top + lblSquad2.Height
cmdSquad1Shutter.Caption = "+"

Else
space1Between = lblSquad2.Top - lblSquad1.Top - lblSquad1.Height
space2Between = lblSquad3.Top - lblSquad2.Top - lblSquad2.Height

orig2Top = lblSquad3.Top
lblSquad3.Top = sfrmCalendarSquad2.Top + sfrmCalendarSquad2.Height + space2Between
cmdSquad3Shutter.Top = cmdSquad3Shutter.Top + (lblSquad3.Top - orig2Top)
sfrmCalendarSquad3.Top = lblSquad3.Top + lblSquad2.Height

orig1Top = lblSquad2.Top
lblSquad2.Top = sfrmCalendarSquad1.Top + sfrmCalendarSquad1.Height + space1Between
cmdSquad2Shutter.Top = cmdSquad2Shutter.Top + (lblSquad2.Top - orig1Top)
sfrmCalendarSquad2.Top = lblSquad2.Top + lblSquad1.Height

sfrmCalendarSquad1.Visible = True
cmdSquad1Shutter.SetFocus
cmdSquad1Shutter.Caption = "-"
End If
End Sub

Private Sub cmdSquad2Shutter_Click()
Dim space1Between As Integer
Dim space2Between As Integer
Dim orig1Top As Integer
Dim orig2Top As Integer

If cmdSquad2Shutter.Caption = "-" Then
cmdSquad2Shutter.SetFocus
sfrmCalendarSquad2.Visible = False

space2Between = lblSquad3.Top - sfrmCalendarSquad2.Top - sfrmCalendarSquad2.Height

orig2Top = lblSquad3.Top
lblSquad3.Top = lblSquad2.Top + lblSquad2.Height + space2Between
cmdSquad3Shutter.Top = cmdSquad3Shutter.Top - (orig2Top - lblSquad3.Top)

sfrmCalendarSquad3.Top = lblSquad3.Top + lblSquad3.Height
cmdSquad2Shutter.Caption = "+"
Else
space2Between = lblSquad3.Top - lblSquad2.Top - lblSquad2.Height
orig2Top = lblSquad3.Top
lblSquad3.Top = sfrmCalendarSquad2.Top + sfrmCalendarSquad2.Height + space2Between
cmdSquad3Shutter.Top = cmdSquad3Shutter.Top + (lblSquad3.Top - orig2Top)
sfrmCalendarSquad3.Top = lblSquad3.Top + lblSquad3.Height

sfrmCalendarSquad2.Visible = True
cmdSquad2Shutter.SetFocus
cmdSquad2Shutter.Caption = "-"
End If

End Sub

Private Sub cmdSquad3Shutter_Click()
Dim space1Between As Integer
Dim space2Between As Integer
Dim org1top As Integer
Dim org2Top As Integer

If cmdSquad3Shutter.Caption = "-" Then
cmdSquad3Shutter.SetFocus
sfrmCalendarSquad3.Visible = False
cmdSquad3Shutter.Caption = "+"
Else
sfrmCalendarSquad3.Visible = True
cmdSquad3Shutter.SetFocus
cmdSquad3Shutter.Caption = "-"
End If



End Sub

now that I got it working I set it to work with a side menu that open s and closes the SubForms, I changed the it to a public Sub and can call it from it the menu and the labels, i will post the code when if finish it.

 

[vbaInet]

Phew!! You don't need to post your code because the barebones is in the database and to be honest it can be done even more effeciently by creating a function for all.

 

[wstech]

I am not proficient with functions, so I had to do it with code, if you se a better way please let me know.

 

[vbaInet]

It would be hard to explain. But one idea would be to create an array of subform control names (i.e. the subform controls present on the form) and for everytime you collapse or expand, you would delete that subform control name from the array, and for each array item perform a check of its TOP property to see if the distance between the form above it (if it applies) and its TOP is NOT a certain twips. You would arrange the array items in such a way that the check is done Top-Down.