For Each Loop and Locking Problem

[Bonhomme Septheure]

Hi, this is the first question I've posted. If I'm not using the right forum, please let me know.

I've created a For Each loop in the before update event of a subform to check whether any text box in the detail section contains data. If at least one text box is not null or empty, the record is saved.

After I've used the database, triggered this event, closed the form, and closed the database, the .laccdb file remains. When I try to reopen the database, I get an error message saying it's locked. I have to then go into Task Manager, end the MSACCESS.EXE process, and delete the .laccdb file before I can open my database again.

This isn't a split database. It's on my computer and I'm the only user.

I'm posting my code below. This problem only started after I created and ran the code. If someone could look at this and tell me how this code is causing this problem and what to do about it, I'd be grateful. Thanks!

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then 'Test if this is a new record.
  
  Dim ctl As Control
  Dim i As Integer
  i = 0
    For Each ctl In Me.Section(0).Controls 'Loop through the controls in the detail section.
  
        If ctl.ControlType = 109 Then 'Test each to see if it's a text box
            If IsNull(ctl) = False Or Len(ctl) > 0 Then 
                Exit For 'bail out of the loop and continue with the record update.
            End If
        End If
        i = i + 1 
    Next 
    
    If i = Me.Section(0).Controls.Count Then
        Me.Undo 
    End If
End If
End Sub

 

[spikepl]

Your code is unreadable as is: use the code brackets (Go Advanced, select the code, press "#")

1. If you added code and it causes an error, then comment it out. ( On the Edit Toolbar -in the code window, you have to make it visible first in View->Toolbars->Edit - there is a button that allows you to coment out the selected block) to verify that the error stems from here and not elsewhere.

2. If error stems from your new code, then comment-in small blocks until the error reappears. Then you have a clue.

 

[spikepl]

Hmmm .. I see things: In the code window, Debug->Compile

 

[Bonhomme Septheure]

Hi, spikepl

I went into the code window and compiled, then opened the form, triggered the event that runs the code, closed the form, closed the database, and had the same locking problem.

Are you seeing something specific in the code?

Thanks.

 

[spikepl]

I am stunned that the thing compiled. There is an extra End If at the end.
I also note that you do not Cancel the Update.

When you want to stop the save you need to say

Cancel=vbCancel

Probably this is what throws it off.

 

[Bonhomme Septheure]

(Sorry! My previous post was missing a line of code.)

Hi, spikepl

I'm afraid I can't find the extra End If. I tried putting the Cancel = vbCancel in as shown in the code below. I ran into the same problem.

If Me.NewRecord Then 'Test if this is a new record.
 
  Dim ctl As Control
  Dim i As Integer
  i = 0
    For Each ctl In Me.Section(0).Controls 'Loop through the controls in the detail section.
 
        If ctl.ControlType = 109 Then 'Test each to see if it's a text box
            If IsNull(ctl) = False Or Len(ctl) > 0 Then 
                Exit For 'bail out of the loop and continue with the record update.
            End If
        End If
        i = i + 1 
    Next 
 
    If i = Me.Section(0).Controls.Count Then
        Me.Undo 
        Cancel = vbCancel
    End If
End If

I noticed that if I commented out everything relating to my counter variable, i, it eliminated the problem. Unfortunately, it also meant the code no longer served its purpose. Any ideas why "i" could be causing this problem (no pun intended)?

 

[VilaRestal]

You could do it slightly differently:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then 'Test if this is a new record.
        Dim ctl As Control
        Dim bOK As Boolean
        For Each ctl In Me.Detail.Controls 'Loop through the controls in the detail section.
            If ctl.ControlType = acTextBox Then 'Test each to see if it's a text box
                If IsNull(ctl) = False Or Len(ctl) > 0 Then
                    bOK = True
                    Exit For 'bail out of the loop and continue with the record update.
                End If
            End If
        Next
        If Not bOK Then
            If MsgBox("No textboxes are filled in, the record will not be saved. Do you want to discard this new record?", vbYesNo) = vbYes Then
                Me.Undo
            Else
                Cancel = True
            End If
        End If
    End If
End Sub

Does that work?

 

[boblarson]

Okay, I have to ask this question -

If you are checking this only if it is a new record, then are you trying to make sure someone doesn't enter data and then delete it? Because if that isn't the case then the Form's Before Update event will only fire if something has been entered to create a new record. If you are trying to avoid someone putting something in and then immediately removing it, your code can be modified (simplified) to this:

    If Me.NewRecord Then    'Test if this is a new record.
        Dim ctl As Control
        [B][COLOR=red]Dim blnNoSave As Boolean[/COLOR][/B]
[B][COLOR=#ff0000][/COLOR][/B] 
        For Each ctl In Me[B][COLOR=red].Details.[/COLOR][/B]Controls    'Loop through the controls in the detail section.
            If ctl.ControlType = [B][COLOR=red]acTextBox[/COLOR][/B] Then    'Test each to see if it's a text box
                If Len([B][COLOR=red]ctl.value & vbNullString[/COLOR][/B]) > 0 Then
                    [B][COLOR=red]blnNoSave = False[/COLOR][/B]
                    Exit For    'bail out of the loop and continue with the record update.
                Else
                    [B][COLOR=red]blnNoSave = True
[/COLOR][/B]                End If
            End If
        Next

        [B][COLOR=red]If blnNoSave Then
[/COLOR][/B]            Cancel = True
            Me.Undo
        [B][COLOR=red]End If[/COLOR][/B]
    End If

The biggest thing is that you have to use ctl.Value because ctl does NOT have .Value as a default because not all controls have a .Value property.

 

[VilaRestal]

Almost Snap! Bob

As for the value thing, I wondered but I've tested mine and it didn't grumble.

 

[boblarson]

I've had it take issue before and so I always put the property I want when using a generic control object.

 

[VilaRestal]

True, but in this case the code only executes after checking the ctl is a textbox so it's ok.

But don't get me wrong I prefer putting .Value in always.

 

[Bonhomme Septheure]

Hi, Bob; Hi VilaRestal

Thanks for your code! I tried each and got mixed results.

First, some background regarding the form and subform:

I have a main form called frmContact used for finding contacts from a database and displaying information about each contact. The user enters information in unbound controls on the main form and clicks a search button. The on click event returns records in a subform called subContacts.

The on current event for each record in subContacts requeries another subform on the main form called subContactLocation that displays address, phone number, etc. This subform is on a page of a tab control.

subContactLocation is the form I'm having trouble with. If a user clicks or tabs into this subform, its before insert event fires and populates textboxes in the subform's header with values from subContacts for the current record in subContacts.

If the user doesn't enter any information in the text boxes in the detail section of subContactLocation or enters information in a text box or boxes and then deletes it, I'll end up with a record in my contact location table with information from the subform's header tying to a contact in the contact table, but no information regarding address, phone number, etc. To prevent this, I came up with the code you've seen here and put it in the before update event of subContactLocation.

If I use the code either of you provided, and trigger the before update event by clicking another page in the tab control, then close the form, followed by closing the database, the laccdb file goes away, and I don't have a locking problem.

If I do the above, but go to another record in subContacts before closing the form, or if I trigger the before update event by going to another record in SubContacts, then close the form, followed by closing the database, the laccdb file remains, and I have to go into Task Manager to end the MSACCESS.EXE process.

This is driving me nuts!

Any ideas?

Thanks.

 

[boblarson]

So why are you using the Before Insert event again? And what exactly is it trying to set on the subContactLocation subform?

 

[Bonhomme Septheure]

Hi, Bob

I'm using the BeforeInsert event of subContactLocation to populate three text boxes in the header of subContactLocation with information from subContacts. Here's the code:

Private Sub Form_BeforeInsert(Cancel As Integer)
'This event only fires if this is a new record
'If this is a new record, the following controls should be filled with information from subContacts,
'i.e. the subform showing the contact search results
Me![txtContactID] = Forms![frmContact]![subContacts]![txtContactID]
Me![txtProfessionalContactID] = Forms![frmContact]![subContacts]![txtProfContactID]
Me![txtEntityID] = Forms![frmContact]![subContacts]![txtEntityID]
End Sub

ContactID, ProfessionalContactID, and EntityID are key fields in the following tables, respectively:

tblContacts, containing people's names and other information about them
tblContactProfessional, containing information about a person's employment history
tblEntity, containing information about the company a person works for

subContactLocation writes this information to corresponding fields in tblContactProfessionalLocation allowing a link between the each record in the latter table and corresponding records in the former tables.

I use the BeforeInsert event because it will populate these text boxes when a new record is created.

Thanks!

 

[boblarson]

So, actually you should NOT be putting all of those there. You just need to have the subform's Master/Child property set to the correct field or set of fields which identifies a single record of the form above it in the hierarchy. No need to use the insert and it only complicates matter.

If you have your tables set up correctly you would very RARELY even need more than one field in the master/child property.

 

[VilaRestal]

Agreed with what Bob just said.

Also, the way I'd prefer to set values for new records is by setting the DefaultValue of those controls (if you must do it that way and not via Master/Child linking the subform to the parent). Access will only actually assign the default value if a new record is committed.

You could set their default values in the current event of subContacts and not have to do anything in the BeforeInsert event of subContactLocation.

 

[Bonhomme Septheure]

Hi, Bob

There are no bound controls on the main form, so I can't set the subform's master/child property to anything. The main form has several unbound text boxes the user fills in with search criteria to find a contact. When the user clicks the search button on the main form, the on click event runs code that assembles what the user entered for search criteria into a sql string. The code sets the sql string as the recordset for my subContacts subform that displays the results of the search.

My subContactLocation subform is on the main form as well and can't have a master/child relationship with the main form for the same reason.

As far as how I've got the tables set up, I'm stuck with what I've got without having to go back to square one.

Is my locking problem related to Access somehow not "letting go" of the previous record when I move to another record in subContacts?

Thanks!

 

[Pat Hartman]

If a user clicks or tabs into this subform, its before insert event fires

The BeforeInsert event ONLY fires if the form has already been dirtied. Do you have code in some other event - the current event that is dirtying the record? As a matter of good practice, you should have NO code that dirties the record before the user does. When you do, it leads to problems like the one you are trying to solve.

As to why the lock file remains? Have your permissions changed? There isn't anything you can do in code that prevents the lock file from being deleted. If you have compact on close set to yes, it may take a few minutes to delete the lock file or perhaps there really is someone else with the file open if it is on a network.

 

[Bonhomme Septheure]

Hello everyone,

Sorry to have taken so long to post how this turned out.

I finally got around the locking problem by creating a subform, "subContactLocationShell", that has no parent/child relationship to the main form, but which has a single text box which contains a ProfessionalContactID set by the on current event of the form showing all of the contact search results. subContactLocation is now a subform of subContactLocationShell and has a parent/child relationship based on ProfessionalContactID. Seems to work.

Thanks for everyone's help!