Locking Field Problems

[Nick F]

Pulling my hair out on this one. I am setting up a fish sampling database. I have a table that has a one to one relationship to 10 other tables. The reason I did this, there are 10 different sampling methods. Only one though can be applied to a single sampling event. Each method has slightly different data. If I put it all in one form, the data entry would be slow with so many extra fields. Therefore, I have a field in the first table labeled Methods with a dropdown list. When a user picks a method, the corresponding data form automatically pops up. I am trying to limit errors. So if a user clicks on the wrong method, I have written code into the close form button to delete that record if certain data is not found. What I am trying to do next, is lock the methods field in the first table if data is entered in the second table, thereby preventing any of the other 9 one to one relationship forms from being populated. The only way to unlock the method field would then be to delete the populated form. Here is my code.

Table 1
Private Sub Method_BeforeUpdate(Cancel As Integer)
'Electrofishing Form #1 in Methods Table
   If Me.Method = 1 Then
      Me.cmdElectrofishing.Visible = True
   End If
   If Me.Method = 1 Then
      DoCmd.OpenForm "Electrofishing", , , , acFormAdd, acDialog, Me.SamplingEventID
   End If
   'Temp_Logger Table #6 in Methods Table
   If Me.Method = 6 Then
      Me.cmdTempLogger.Visible = True
   End If
   If Me.Method = 6 Then
      DoCmd.OpenForm "Temp_Logger", , , , acFormAdd, acDialog, Me.SamplingEventID
   End If
   'Angling Form #7 in Methods Table
   If Me.Method = 7 Then
      Me.cmdAngling.Visible = True
   End If
   If Me.Method = 7 Then
      DoCmd.OpenForm "Angling", , , , acFormAdd, acDialog, Me.SamplingEventID
   End If
   'Gillnet Form #8 in Methods Table
   If Me.Method = 8 Then
      Me.cmdGillnet.Visible = True
   End If
   If Me.Method = 8 Then
      DoCmd.OpenForm "Gillnet", , , , acFormAdd, acDialog, Me.SamplingEventID
   End If
   'Seining Form #9 in Methods Table
   If Me.Method = 9 Then
      Me.cmdSeining.Visible = True
   End If
   If Me.Method = 9 Then
      DoCmd.OpenForm "Seining", , , , acFormAdd, acDialog, Me.SamplingEventID
   End If
   'Fyke_Net Form #10 in Methods Table
   If Me.Method = 10 Then
      Me.cmdFykeNet.Visible = True
   End If
   If Me.Method = 10 Then
      DoCmd.OpenForm "Fyke_Net", , , , acFormAdd, acDialog, Me.SamplingEventID
   End If
   'Minnow_Trap Form #11 in Methods Table
   If Me.Method = 11 Then
      Me.cmdMinnowTrap.Visible = True
   End If
   If Me.Method = 11 Then
      DoCmd.OpenForm "Minnow_Trap", , , , acFormAdd, acDialog, Me.SamplingEventID
   End If
   'Trotline Form #12 in Methods Table
   If Me.Method = 12 Then
      Me.cmdTrotLine.Visible = True
   End If
   If Me.Method = 12 Then
      DoCmd.OpenForm "Trotline", , , , acFormAdd, acDialog, Me.SamplingEventID
   End If
   'Hoop_Net Form #13 in Methods Table
   If Me.Method = 13 Then
      Me.cmdHoopNet.Visible = True
   End If
   If Me.Method = 13 Then
      DoCmd.OpenForm "Hoop_Net", , , , acFormAdd, acDialog, Me.SamplingEventID
   End If
   'Trammel_Net Form #14 in Methods Table
   If Me.Method = 14 Then
      Me.cmdTrammelNet.Visible = True
   End If
   If Me.Method = 14 Then
      DoCmd.OpenForm "Trammel_Net", , , , acFormAdd, acDialog, Me.SamplingEventID
   End If
   'Stream_Temp_Profile Form #20 in Methods Table
   If Me.Method = 20 Then
      Me.cmdStreamTempProfile.Visible = True
   End If
   If Me.Method = 20 Then
      DoCmd.OpenForm "Stream_Temp_Profile", , , , acFormAdd, acDialog, Me.SamplingEventID
   End If
   RunCommand acCmdSave
   DoEvents
End Sub

Table 2
Private Sub CloseForm_Click()
   Forms!Sampling_Event.Method.Locked = True
   If Me.StationLength = 0 Then
      DoCmd.RunCommand acCmdSelectRecord
      DoCmd.RunCommand acCmdDeleteRecord
      Forms!Sampling_Event.Method.Locked = False
   End If
   If Me.StationLength = 0 Then
      Forms!Sampling_Event.cmdElectrofishing.Visible = False
   End If
   DoCmd.Close
End Sub

When I run, I get an error message that says "You can't lock a control while it has unsaved changes". I thought I saved the field at the end with RunCommand. Help or ideas?



Cheers
Nick

[boblarson]

I think we need to fix your table structure first. You don't want to have just one table, for sure. But I think you just don't understand how you can set up the tables to be flexible enough for differences in the data and yet not have 10 tables (9 of which are, in essence, the same - albeit with some slightly different fields).

Can you post a copy of your relationships (a screenshot uploaded here and not to a third party websites)? Or just post a copy of the database with some bogus data just so we can see what you're attempting to do and we can make suggestions as to a more proper design.

[RuralGuy]

There is a Select Case structure that sometimes makes code easier to read and maintain.

Private Sub Method_BeforeUpdate(Cancel As Integer)
   Dim NextForm As String
   Select Case Me.Method
      Case 1
         'Electrofishing Form #1 in Methods Table
         Me.cmdElectrofishing.Visible = True
         NextForm = "Electrofishing"
      Case 6
         'Temp_Logger Table #6 in Methods Table
         Me.cmdTempLogger.Visible = True
         NextForm = "Temp_Logger"
      Case 7
         'Angling Form #7 in Methods Table
         Me.cmdAngling.Visible = True
         NextForm = "Angling"
      Case 8
         'Gillnet Form #8 in Methods Table
         Me.cmdGillnet.Visible = True
         NextForm = "Gillnet"
      Case 9
         'Seining Form #9 in Methods Table
         Me.cmdSeining.Visible = True
         NextForm = "Seining"
      Case 10
         'Fyke_Net Form #10 in Methods Table
         Me.cmdFykeNet.Visible = True
         NextForm = "Fyke_Net"
      Case 11
         'Minnow_Trap Form #11 in Methods Table
         Me.cmdMinnowTrap.Visible = True
         NextForm = "Minnow_Trap"
      Case 12
         'Trotline Form #12 in Methods Table
         Me.cmdTrotLine.Visible = True
         NextForm = "Trotline"
      Case 13
         'Hoop_Net Form #13 in Methods Table
         Me.cmdHoopNet.Visible = True
         NextForm = "Hoop_Net"
      Case 14
         'Trammel_Net Form #14 in Methods Table
         Me.cmdTrammelNet.Visible = True
         NextForm = "Trammel_Net"
      Case 20
         'Stream_Temp_Profile Form #20 in Methods Table
         Me.cmdStreamTempProfile.Visible = True
         NextForm = "Stream_Temp_Profile"
      Case Else
         '-- Do something else
   End Select
   DoCmd.OpenForm NextForm, , , , acFormAdd, acDialog, Me.SamplingEventID
   RunCommand acCmdSave
   DoEvents
End Sub

I don't know your form but I'm going to suggest you do not need the last two lines in this procedure and this procedure probably should be in the AfterUpdate event of the control instead of the BeforeUpdate event.

[Nick F]

Thanks Rural for the tips. New at this and don't know the tricks to keeping everything tidy.

[boblarson]

Thanks Rural for the tips. New at this and don't know the tricks to keeping everything tidy.

I still think that RG's code is just a band-aid fix that will eventually cause you great pain and anguish when you go to get the data back out.

Fix the table structure. Don't "band-aid" the problem.

[Nick F]

Here is part of my relationship table. There is some to the left but not too concerned about that. The stuff immediately to the right of the first table deals with methods. Upper right is the fish data. Lower right is additional information that is optional. Thanks.

[RuralGuy]

@Bob: I was only showing the OP a neater way to code. As of yet I made no judgement of *what* was being attempted. You may be correct and maybe we'll know when the OP posts the relationships.

[boblarson]

@Bob: I was only showing the OP a neater way to code. As of yet I made no judgement of *what* was being attempted. You may be correct and maybe we'll know when the OP posts the relationships.

Sorry, I didn't mean to imply that it was your fault about anything. I just didn't want the OP to get thinking that they could fix their problem by using something of that nature.

And to Nick F - Right away I can see your structure does need some help. Didn't CraigDolphin help you with a bunch of this? Did he actually suggest all of those tables with repeating fields?

[RuralGuy]

I agree with Bob on the structure and will bow out of this thread and let Bob take the lead.

[Nick F]

Bob,

Yes, Craig has helped me and has been a big help. No, he did not suggest all of those tables with repeating fields. I know it is probably wrong, but the way I was thinking of it in my head was the first table held information that was common to all sampling methods. Then I developed a table for each method containing information that is specific to the method. Granted, some of these fields are common between a few tables but not all. I did not know how to handle that or later on how to handle that in forms for data entry.

Cheers
Nick

[boblarson]

We'll work with you but it may be a bit of a task. I don't have a lot of time to devote to this but it is important to get it fixed before going any further. Don't worry about data input until the table structure is properly normalized. We can deal with that later.

[Nick F]

Here is my full relationship table.

[boblarson]

Can you post a copy of the database with the tables in it? I think it will be easier to analyze and post back suggested fixes.

[Nick F]

Can't seem to get it small enough. Have deleted all the forms. Extra tables and look up tables. Can't understand why it is still huge. Will work on it.

[Nick F]

It isn't populated with data yet either.