Questionable "Object Variable or With Block variable not set" Error (1 Viewer)

S

stevenblanc

Registered User.
Local time
Today, 11:32
Joined
Jun 27, 2011
Messages
103
Hi folks,

Looking for a little help here. I have a form that allows my users to select a customer from a list and inputs that customerID into whichever parent form they call it from. This works fine for 90 percent of my forms, however, I am getting the following error on one form:

Run-time error '91'

Object Variable or With Block variable not set.
Click to expand...

The body of the code is as follows with the text in bold being the error line:
Code: 
        With Forms(txtParentForm)
            If .DataEntry = True Then
                .DataEntry = False
                .AllowAdditions = False
            End If
            
            [B][U][COLOR="Red"].Recordset.FindFirst strLinkCriteria[/COLOR][/U][/B]
            If .Recordset.NoMatch Then MsgBox "The selected record no longer exists."
            .txtFocus.SetFocus
            .txtCustomerID.Enabled = False
        End With

At this point in time the variables included are set as follows (revealed by hovering over the variables):
Code: 
txtParentForm = "frmAccounting_ReceiptEntry" [COLOR="SeaGreen"]'This is actually set by a me.form.name and works I don't get the error for other forms.[/COLOR]
strLinkCriteria = "[CustomerID]='2DAUTO01'"

So I am certain that the With Block does in fact have an object (frmAccounting_ReceiptEntry).

Any ideas would be greatly appreciated.

Cheers,


Steven
 
spikepl

spikepl

Eledittingent Beliped
Local time
Today, 20:32
Joined
Nov 3, 2010
Messages
6,142
I am certain that it in fact does not have an object - Access seldom invents things.

The answer is likely in the missing context - are you using forms/subforms.?

What is
Forms(txtParentForm) ? Is it a form or a subform? Is it open?

You can test it with
If Forms(txtParentForm) Is Nothing Then
'form doesn't exist
End IF
 
S

stevenblanc

Registered User.
Local time
Today, 11:32
Joined
Jun 27, 2011
Messages
103
The form is open otherwise it would not make it through the first three lines of the with statement... I think my brain just clicked into gear: that particular form is unbound and as such cannot do record navigation... problem probably solved.
 
boblarson

boblarson

Smeghead
Local time
Today, 11:32
Joined
Jan 12, 2001
Messages
32,059
Also, you should probably use a RecordsetClone to search and set the value instead of the actual recordset.
 
S

stevenblanc

Registered User.
Local time
Today, 11:32
Joined
Jun 27, 2011
Messages
103
Bob,

What is the danger of navigating the recordset?

Also, resolved, if anyone is interested, here's the full code for the sub:

Code: 
Private Sub OpenCustomerReview(ListIndex As String)
Dim strLinkCriteria As String
 
    strLinkCriteria = "[CustomerID]=" & "'" & Me.lstCustomers.Column(0) & "'"

    If CurrentProject.AllForms(txtParentForm).IsLoaded Then
    
        With Forms(txtParentForm)
            If .DataEntry = True Then
                .DataEntry = False
                .AllowAdditions = False
            End If
            
            If .RecordSource <> "" Then
                .Recordset.FindFirst strLinkCriteria
                If .Recordset.NoMatch Then MsgBox "The selected record no longer exists."
            Else
                Forms(txtParentForm).txtCustomerID.Value = Me.lstCustomers.Column(0)
            End If
            
                .txtFocus.SetFocus
                .txtCustomerID.Enabled = False
            
        End With
        
    End If
    
    Form_Close
End Sub
 
Last edited:
boblarson

boblarson

Smeghead
Local time
Today, 11:32
Joined
Jan 12, 2001
Messages
32,059
stevenblanc said:
Bob,

What is the danger of navigating the recordset?

Also, resolved, if anyone is interested, here's the full code for the sub:

Code: 
Private Sub OpenCustomerReview(ListIndex As String)
Dim strLinkCriteria As String
 
    strLinkCriteria = "[CustomerID]=" & "'" & Me.lstCustomers.Column(0) & "'"
 
    If CurrentProject.AllForms(txtParentForm).IsLoaded Then
 
        With Forms(txtParentForm)
            If .DataEntry = True Then
                .DataEntry = False
                .AllowAdditions = False
            End If
 
            If .RecordSource <> "" Then
                .Recordset.FindFirst strLinkCriteria
                If .Recordset.NoMatch Then MsgBox "The selected record no longer exists."
            Else
                Forms(txtParentForm).txtCustomerID.Value = Me.lstCustomers.Column(0)
            End If
 
                .txtFocus.SetFocus
                .txtCustomerID.Enabled = False
 
        End With
 
    End If
 
    Form_Close
End Sub
Click to expand...
If you use the recordset object the visible records in the form are affected immediately instead of if something is found. You can navigate back and forth within a clone without affecting the displayed records in a form and then you can set the bookmark of the recordset to the bookmark of the recordsetclone when it has found what it was looking for. But it is your choice. I just don't like affecting something until I actually want to do something with it.
 
You must log in or register to reply here.

Similar threads

M
Solved Object variable or With block variable not set Error
Replies
10
Views
623
Momma
M
M
Solved Object variable or with block variable not set
Replies
7
Views
269
theDBguy
theDBguy
NauticalGent
What System or Hidden table(s) (if any) holds the properties for the current db?
Replies
15
Views
719
isladogs
isladogs
C
Object Invalid or No Longer Set Error
Replies
6
Views
209
MajP
MajP
G
Error in a line of vba code
Replies
5
Views
567
theDBguy
theDBguy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom