Run-Time Error 3197 (please read anyway) (1 Viewer)

psyc0tic1

Access Moron
Local time
Today, 15:05
Joined
Jul 10, 2017
Messages
360
I know there have been many threads regarding this error but I have narrowed down the culprit but do not know what needs to be fixed. This only happens to one of my forms in tab control but it is complicated.

History...
Tab control main form (frm_home)
Form on a tab having issues (frm_labtestinput) with 3 subforms (frm_usbinput, frm_usbinput2, and frm_usbinput3) with showstretch

The code for frm_labtestinput
Code:
Option Compare Database
Option Explicit

Private blnGood As Boolean

Private Sub cmdLabQuestion_Click()
    DoCmd.OpenForm "frm_newlabrecordhowto", , , , , acDialog
End Sub

Private Sub cmdNewLabRecord_Click()
    DoCmd.OpenForm "frm_newlabrecord", , , , , acDialog
End Sub

Private Sub cmdLabAttachments_Click()
    DoCmd.OpenForm "frm_labattachments", , , , , acDialog
End Sub

Private Sub Form_Load()
    On Error Resume Next
    Dim HasUSB As Long
    
    HasUSB = DLookup("HasUSB", "tbl_parts", "ID = " & Me.Part_Number)
    If HasUSB = 2 Then
      ShowStretch
    ElseIf HasUSB = 1 Then
      ShowStretch2
    ElseIf HasUSB = 11 Then
      ShowStretch3
    Else
      HideShrink
    End If
End Sub

Private Sub ShowStretch()

    Me.InsideHeight = 10000
    
    Me.UsbInput1.Visible = True
    Me.USBInput2.Visible = False
    Me.USBInput3.Visible = False
    Me.UsbInput1.Height = 7600
    Me.UpdateRecord.Top = 9500

End Sub

Private Sub ShowStretch2()

    Me.InsideHeight = 3000
    
    Me.UsbInput1.Visible = False
    Me.USBInput2.Visible = True
    Me.USBInput3.Visible = False
    Me.USBInput2.Height = 4700
    Me.UpdateRecord.Top = 6460

End Sub

Private Sub ShowStretch3()

    Me.InsideHeight = 10000
    
    Me.UsbInput1.Visible = False
    Me.USBInput2.Visible = False
    Me.USBInput3.Visible = True
    Me.USBInput3.Height = 7600
    Me.UpdateRecord.Top = 9500

End Sub

Private Sub HideShrink()

    Me.InsideHeight = 7560

    Me.UsbInput1.Visible = False
    Me.USBInput2.Visible = False
    Me.USBInput3.Visible = False
    Me.UsbInput1.Height = 0
    Me.USBInput2.Height = 0
    Me.UpdateRecord.Top = 1800
    
End Sub

Private Sub cboGoToRecord_AfterUpdate()
    On Error Resume Next
    Dim rst As Object
    Dim HasUSB As Long
    
    Set rst = Me.RecordsetClone
    rst.FindFirst "AuditID = " & Me.cboGoToRecord.Value
    Me.Bookmark = rst.Bookmark
     
    HasUSB = DLookup("HasUSB", "tbl_parts", "ID = " & Me.Part_Number)
    If HasUSB = 2 Then
      ShowStretch
    ElseIf HasUSB = 1 Then
      ShowStretch2
    ElseIf HasUSB = 11 Then
      ShowStretch3
    Else
      HideShrink
    End If
End Sub

Private Sub Form_Current()
    Me.cboGoToRecord.Value = Me.AuditID.Value
End Sub

Private Sub UpdateRecord_Click()
    Dim strMsg As String
    On Error Resume Next
    blnGood = True
    
    If (validate) Then
        Me.Recordset.Edit
        Me.Recordset.Fields("status").Value = "Complete"
        Me.Recordset.Fields("LabInspectorUserID").Value = Credentials.UserId
        Me.Recordset.Fields("LabUpdate").Value = Date
        Me.Recordset.Update
        If Me.CurrentRecord < Me.Recordset.RecordCount Then
            Me.Recordset.MoveNext
        Else
            Me.Recordset.MoveFirst
        End If
    Else
        strMsg = "All Fields are required."
        Call MsgBox(Prompt:=strMsg, Title:="Before Update")
    End If
        Application.Echo False
        DoCmd.Close
        DoCmd.OpenForm "frm_home"
        Form_frm_home.Lab_Test_Input_Form.SetFocus
        Application.Echo True
    blnGood = False
End Sub

Private Function validate() As Boolean
    validate = True
    If (IsNull(Me.LabTestDate.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalFunctBad.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalFunctTested.Value)) Then
        validate = False
    End If
    If (IsNull(Me.TotalFunctGood.Value)) Then
        validate = False
    End If
    If (Credentials.UserId = 0 Or IsNull(Credentials.UserId)) Then
        validate = False
    End If
End Function

This code represents what is on each of the 3 subforms... pretty much the same:
Code:
Option Compare Database
Option Explicit

Private blnGood As Boolean

Private Sub Part55MinReading1_2_KeyDown(KeyCode As Integer, Shift As Integer)
    If (KeyCode = vbKeyTab And Shift = 0) Then
        KeyCode = 0
        Me.InitReading1.SetFocus
        Me.Parent!UpdateRecord.SetFocus
    End If
End Sub

When a record is present on frm_labtestinput that does not require one of the subforms to appear... no errors when I click the submit button (UpdateRecord_Click)

When a record is present that requires one of the subforms to show... if you skip editing any data in the subform and click the submit button (UpdateRecord_Click)... no errors... no issues... but if you edit anything in one of the subforms and click the submit button I get error 3197... but... if I tab from the last control on the subform to the submit button (UpdateRecord_Click)... no error.

I have tested this every which way I can and it comes down to something in the code that is missing.

Can anyone see what is missing in the code to cause this? It used to work but now I have to use On Error Resume Next to stop the error popup but it requires the form be submitted twice to update the record.
 

missinglinq

AWF VIP
Local time
Today, 16:05
Joined
Jun 20, 2003
Messages
6,423
Well, 30+ members have read this post and not responded, and I'm guessing that, just like myself, they can't begin to understand your scenario, as posted.

But, in general, this error usually pops because of one of these two reasons

  • The app is in a multi-user environment and is not split into a Front End/Back End configuration, and more than one user is active
  • The Form, and one or more Subforms, are based, at least partially, on the same Table

Are either of these reasons present, here?

Linq ;0)>
 

psyc0tic1

Access Moron
Local time
Today, 15:05
Joined
Jul 10, 2017
Messages
360
Well, 30+ members have read this post and not responded, and I'm guessing that, just like myself, they can't begin to understand your scenario, as posted.

But, in general, this error usually pops because of one of these two reasons

  • The app is in a multi-user environment and is not split into a Front End/Back End configuration, and more than one user is active
  • The Form, and one or more Subforms, are based, at least partially, on the same Table

Are either of these reasons present, here?

Linq ;0)>

Sorry... After all the threads regarding my database... I didnt think I needed to explain those details again.

Database is split.
Only me in it at the time... No other users.
The form and sub forms are using the same table.

This worked before but for some reason isnt anymore but I think I have discovered part of the solution.

I am in the process of creating another table to house all of the fields that the sub forms use and create the relationships.

The reason is that it seems the sub forms and the parent form are trying to update the table at the same time.

I will fix it... Even though this worked fine last week as it was.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:05
Joined
Feb 28, 2001
Messages
27,001
Part of the problem is that the stuff you posted is almost totally unrelated to this error, with one exception.

Code:
Private Sub cmdLabQuestion_Click()
Private Sub cmdNewLabRecord_Click()
Private Sub cmdLabAttachments_Click()
Private Sub Form_Load()
Private Sub ShowStretch()
Private Sub ShowStretch2()
Private Sub ShowStretch3()
Private Sub HideShrink()
Private Sub cboGoToRecord_AfterUpdate()
Private Sub Form_Current()
Private Sub UpdateRecord_Click()
Private Function validate() As Boolean
Private Sub Part55MinReading1_2_KeyDown(KeyCode As Integer, Shift As Integer)

Out of ALL of those items, ONLY the UpdateRecord_Click() has the ability to update anything, and that error is about updating a busy record. NONE of the other items CAN modify any record directly. They are all about modifying controls.

There are three forms you could be opening (based on the button-click events) and one could imagine that any two of the forms could interfere with each other. That would depend on the Form_Open, Form_Load, and Form_Current events that we can't see for those forms. Your description isn't that helpful, even on the follow-up.

The reason you didn't get much response is that you posted too much stuff that was irrelevant and probably ended up confusing people.

The idea that two forms (one parent, one sub) are open to the same table at the same time seems illogical somehow. The only time I would ever consider this normal is if there is a self-relation in that table that drives both forms. I.e. if this were an ancestry database (as a contrived example), you might have a (literal) parent/child relationship between two records in the same table of people, in which case the sub and parent form would be talking to the same table but different records.

To have the parent and child forms talking to the same record at the same time is a design flaw. What could POSSIBLY be in the table that requires a child form when the parent form is already looking at the table?

As to why we didn't remember your previous posts and thus didn't remember your previous comments about your DB structure, most of us have to be able to read the problem really quick. The more research we have to do, the more likely we are to leave it for another more energetic person. Each question, to get the best results, should be self-contained OR you should include a link to some other thread where you have the information we need to understand your issue better.
 

psyc0tic1

Access Moron
Local time
Today, 15:05
Joined
Jul 10, 2017
Messages
360
Scolded for not posting enough information and too much information at the same time
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:05
Joined
Feb 28, 2001
Messages
27,001
Perhaps a BIT of a scold based on TMI and misdirecting information, but that doesn't stop us from wanting to help you. It MIGHT stop us from understanding the problem.

The key to this is for us to understand WHY you believe that the form and the sub-form should both be based on the same table at the same time, because TYPICALLY a sub-form is based on a child table of that which drives the parent form. I suspect your issue stems from a collision between parent and sub because both could theoretically do updates at the same time (the parent's update being implied by something done in the sub). And that would trigger the 3197 error.
 

psyc0tic1

Access Moron
Local time
Today, 15:05
Joined
Jul 10, 2017
Messages
360
Perhaps a BIT of a scold based on TMI and misdirecting information, but that doesn't stop us from wanting to help you. It MIGHT stop us from understanding the problem.

The key to this is for us to understand WHY you believe that the form and the sub-form should both be based on the same table at the same time, because TYPICALLY a sub-form is based on a child table of that which drives the parent form. I suspect your issue stems from a collision between parent and sub because both could theoretically do updates at the same time (the parent's update being implied by something done in the sub). And that would trigger the 3197 error.

Well back in Post #3 I explained that I discovered the reason and was making the new table to solve it.

As for the question of WHY I believe that the form and sub-form should both be based on the same table...

Originally my database was one table and three forms.
Data table that held all fields
form 1 create a new record
Form 2 update part of that newly created record
Form 3 complete the remaining information needing update on the record.

Since then it has grown into having lookup tables and many more functions but the biggest change was to form 3... all of the fields that I ended up creating the sub-forms for didn't need to be seen for every record so I started the showstretch project that Ridders hooked me up with. Thus where the sub-forms came from and my not knowing I needed to now split all of the fields these sub-forms contained into another table.

The answer to why my database was incorrectly designed was... inexperience.

The confusion was the fact that this poor design was working and then wasn't. It may have been broke all along as I am a keyboard user and I used the tab key to navigate through the fields on the forms in testing... using the tab key didn't reveal this issue. Other people are clickers and when I was accidentally using the mouse to navigate I discovered the issue.

Also strange that out of the 30 or so users I have for this database... nobody mentioned seeing this issue either.

So as I said back in post #3... I am making the "child table" and am confident this will solve the problem.

Since this thread is so confusing... mods please delete it
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:05
Joined
Feb 28, 2001
Messages
27,001
Actually, don't do a child table.

Now that you explained the purpose of your three forms, may I suggest that you look into a tab control? This is similar in SOME ways to a sub-form, but ALL controls on the same form are always available to VBA and to each other. Yet they are not bound by the Access behavior that says "if you update the sub, you have to update the parent under certain circumstances". I believe the latter concept is your source of trouble.

In a tab control, you essentially get several blank pages that count as part of the same form (so one and ONLY one table reference, no conflicts involved). You put your main data in an area outside the tabs, or perhaps on the default first tab. But you can put details of some parts of the main record on one tab and your "remaining information" on a second tab.

These tabs LOOK like sub-forms - but aren't. And an update performed from the form catches ALL bound controls on ALL tabs at the same time. One fell swoop and it gets EVERYTHING. And the best part is, if you don't want to look at the other tabs, you don't have to. But it is trivial to switch tabs to see that extra data if that is what you need at a given moment.

I've had as many as ten tabs for one of my more complex forms where the complexity came from having a lot of combo boxes, more than would fit in the "flat" version of the form. But the difference is: tabs on one form and no conflicts; or a form and sub-form with lots of room for conflict.

Mods, please DO NOT delete the thread until psyc0tic1 has a chance to look over this answer. I think it may help clarify the situation and also clarify my intention to help once we can agree on what is actually needed.
 

psyc0tic1

Access Moron
Local time
Today, 15:05
Joined
Jul 10, 2017
Messages
360
Actually, don't do a child table.

Now that you explained the purpose of your three forms, may I suggest that you look into a tab control? This is similar in SOME ways to a sub-form, but ALL controls on the same form are always available to VBA and to each other. Yet they are not bound by the Access behavior that says "if you update the sub, you have to update the parent under certain circumstances". I believe the latter concept is your source of trouble.

In a tab control, you essentially get several blank pages that count as part of the same form (so one and ONLY one table reference, no conflicts involved). You put your main data in an area outside the tabs, or perhaps on the default first tab. But you can put details of some parts of the main record on one tab and your "remaining information" on a second tab.

These tabs LOOK like sub-forms - but aren't. And an update performed from the form catches ALL bound controls on ALL tabs at the same time. One fell swoop and it gets EVERYTHING. And the best part is, if you don't want to look at the other tabs, you don't have to. But it is trivial to switch tabs to see that extra data if that is what you need at a given moment.

I've had as many as ten tabs for one of my more complex forms where the complexity came from having a lot of combo boxes, more than would fit in the "flat" version of the form. But the difference is: tabs on one form and no conflicts; or a form and sub-form with lots of room for conflict.

Mods, please DO NOT delete the thread until psyc0tic1 has a chance to look over this answer. I think it may help clarify the situation and also clarify my intention to help once we can agree on what is actually needed.

If I understand what you are suggesting... my database already uses tab control for the forms (including form #3). Are you suggesting making the 3 sub-forms used on form #3 tab controls themselves?

If that is what you are saying... I am not sure how to implement that considering the nature of how I have the sub-forms set up currently.

Currently form #3 has a combo-box on it that lists records in the database with a status of "Waiting on Lab Testing" and the user can choose which record they need to update.

Now... of those records... not all have the need for any of the 3 sub-forms and if one is chosen that doesn't... none of the sub-forms appear and they can update the record with the default data on the parent form and done.

If a record is chosen that requires one of the sub-forms... the appropriate one will appear and that data can be added along with the default data from the parent form and done.

I do not know how to retain this functionality if those sub-forms are tab controls on the parent form. The reason for the showing or not showing of the sub-forms is to eliminate the chance that a user updates incorrect data.

I am intrigued by the idea though.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:05
Joined
Feb 28, 2001
Messages
27,001
In the form OnCurrent routine, you would simply have an extensive routine that decides what controls on what tabs need to be enabled. Like I said earlier, ALL of the controls on ALL of the tabs are available for VBA.

Once you create a new record (your form #1) OR once you select a record for updating (your form #3) there should be NO DIFFERENCE on the editing abilities on your record other than asserting any default values that are necessary for a "virgin" record. We are talking about something that you describe as other than a parent/child situation. That is why I question the need for sub-forms.

If I am seemingly pointing you in a direction that makes no sense to you, then it is because there is some factor at work here that isn't clear. I just think that having three separate forms that potentially could work on the same record is taking the "Divide and Conquer" strategy a little TOO far.

In the end analysis, it is your vision and your problem. All I can do is offer advice based on what I see. And I have stated my opinion on having three forms simultaneously touch the same record independently or semi-dependently.
 

psyc0tic1

Access Moron
Local time
Today, 15:05
Joined
Jul 10, 2017
Messages
360
If I am seemingly pointing you in a direction that makes no sense to you, then it is because there is some factor at work here that isn't clear. I just think that having three separate forms that potentially could work on the same record is taking the "Divide and Conquer" strategy a little TOO far.

I think the misunderstanding may be my lack of explanation. There are not 3 subforms trying to update the same record at the same time. Depending on situations... the 3 subforms may not be used at all... depending on other situations... only one subform OR the other OR the other will be trying to add information to the same table as the parent form but never all three at the same time.

I am going to explore the tab control in lieu of the subforms to see if I can make the showstretch work with them because all 3 of the current subforms are of different physical size and I like the parent form growing and shrinking in the different scenarios.
 

psyc0tic1

Access Moron
Local time
Today, 15:05
Joined
Jul 10, 2017
Messages
360
In the mean-time... the child table worked to solve the 3197 error
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 15:05
Joined
Feb 28, 2001
Messages
27,001
Glad that you are at least able to make some progress if the TAB control experiments don't work out. And I understand that dynamic sizing looks "spiffy" if you can make it work, and further that TAB controls tend to not grow or shrink too well.

In fact, they WILL prevent you from shrinking the form. You cannot shrink a form's Height in such a way that the lowest control on the form would have no place to be even if it is not visible at the time. And TAB controls DO indeed take up some room.
 

Users who are viewing this thread

Top Bottom