Error 91 due to incorrect With Statement or Object Not Defined

[tallen]

Our IT staff will no longer support Microsoft Access. We were working in Access 2013 and now we have been upgraded to Microsoft Access 2016 until our company moves to Accela for our database needs. Surprisingly, our Access 2013 database still works in Access 2016 except when we try to run the following module. Can anyone help me re-write the module included in this post. We keep getting a 91 error code (Runtime error '91': Object variable or With block variable not set"). We have to keep this database running until we get a replacement database. I apologize if I am posting this incorrectly and I am open to guidance on how or where to post for help. We know it has something to do with Application.File search and our End With Syntax.

With Application.FileSearch
.NewSearch
.LookIn = pubPermitDocFolder 'a constant in GenMods module
.SearchSubFolders = False
.FileName = finddoc
.MatchTextExactly = True
.FileType = msoFileTypeWordDocuments
If .Execute() > 0 Then
pubCopyPermitDoc = finddoc
Else
pubCopyPermitDoc = Null
MsgBox ("An electronic version of the permit conditions is not available." & CR & "Please see your supervisor about creating one, or using an" & CR & "existing inspection format.")
Exit Sub
End If
End With
DoTheMerge:
Call dmerge(WordDoc, MergeQuery, SaveFolder)
Exit Sub
dfLettersPInspError:
MsgBox ("error # " & Err.Number & Err.Description)
Resume Next
End Sub

[NeoPa]

Hi Tallen.

You've done well enough with your post but we need some extra info.

Actually, let's start from the beginning. It looks like you haven't tried to compile this directly yet. If there's a run-time error of that type then it looks like it's actually a compile error which is more easily found and fixed by compiling before running.

To compile explicitly you need to open the VBA IDE (Use Alt-F11 from the main Access window.) then select Debug | Compile {Name of Project}. You should first make sure that all modules are set up properly to use explicit definitions for variables (Require Variable Declaration).

If that doesn't get you all the way there then post back with :

1. Whatever the compiler has told you as an error message (Number as well if you like but don't forget the message).
2. The line the compiler highlighted.
3. The full text of the whole procedure where the problem is found.

I had a look at the code you posted. There's no obvious error there. I suspect there's an unresolved If statement somewhere else that is causing this code to appear to be a problem. We'll know more with the full information.

[tallen]

Debugging/compiling was a little painful. I receive a "compile error: of Method or data member not found" on line 23 of the code.

Dim SearchCount As Integer
SearchCount = [CurrentSearchNo]
totcnt = rst.RecordCount
If totcnt = 0 Then
    MsgBox ("No matching records for parameter entered.  Start new search!")
    Me![cmdRESET].SetFocus
    rst.Close
    upd.Close
    Exit Function
End If
rst.MoveFirst
'MsgBox (rst.RecordCount)
If rst.BOF And rst.EOF Then
    MsgBox ("no transaction records")
    rst.Close
    upd.Close
    Exit Function
End If
Dim ReadCount As Integer
Do Until rst.EOF
    ReadCount = ReadCount + 1
    upd.Seek "=", rst![SortSeq]
    If upd.NoMatch Then
        MsgBox ("Unable to locate tmpEFSearch record")
        rst.Close
        upd.Close
        Exit Function
    End If

[tallen]

Hi NeoPa,

Thank you so much! I did correct twelve compile errors as a result of your guidance. However, when I attempt to run the code I originallly submitted in this post, I receive the error "#91Object variable or With block variable not set". The error again points to the original merge code as the source of error again when I debug. It does appear my with and end if sections of the code are missing some code, but I do not know how to resolve.

With Application.FileSearch
.NewSearch
.LookIn = pubPermitDocFolder 'a constant in GenMods module
.SearchSubFolders = False
.FileName = finddoc
.MatchTextExactly = True
.FileType = msoFileTypeWordDocuments
If .Execute() > 0 Then
pubCopyPermitDoc = finddoc
Else
pubCopyPermitDoc = Null
MsgBox ("An electronic version of the permit conditions is not available." & CR & "Please see your supervisor about creating one, or using an" & CR & "existing inspection format.")
Exit Sub
End If
End With
DoTheMerge:
Call dmerge(WordDoc, MergeQuery, SaveFolder)
Exit Sub
dfLettersPInspError:
MsgBox ("error # " & Err.Number & Err.Description)
Resume Next
End Sub

[twinnyfo]

Tallen,

As NeoPa requested, and I will do so again, please include the entire procedure that is giving you troubles. Also, if you declare any variables outside the procedure (like class-specific variables), please include those in the code.

Also, as a recommendation, you should learn to indent all your nested procedures as you have done in Post #3 above--it makes for much easier troubleshooting and following where you are trying to go.

Thanks.

[NeoPa]

Let me add to that post from TwinnyFo (which I endorse fully BTW) that you should also try to ensure all variables are Dimmed at the top of every procedure. Digging around to try to find Dim statements is just a waste of time.

Also, forget your originally posted code. We can do nothing based on that as there is no context provided. Instead post the code that's relevant now and let us know if there are any compile errors left. We never start to look for runtime errors until all compile errors are resolved. For that we need the code as requested here and earlier.

The advice in Debugging in VBA - 3) General Tips is particularly relevant to your situation. The first tip particularly.

Clear up as many errors as you can then post the code as explained earlier and the rest as explained in post #2 below (or above depending on your settings).

NB. I hope I've made it very clear that following instructions closely is very necessary. Until you do we are likely to be unable to help in any practical way.

[NeoPa]

Let me add that you shouldn't lose heart here. You started fairly well. You slipped up a little by failing to follow instructions adequately as carefully laid out for you. Nevertheless, that doesn't mean we aren't close. Frustratingly close in fact. Closer following of the instructions should make the job fairly straightforward I expect, and we'll have your code working in little time.

I can't guarantee it will do what you want as I have no idea how well you write code and how well you understand and translate logic into working code. Nevertheless I'm confident we can get the code to work as written. From there it's often pretty straightforward to add extra advice on making the code work better but that depends on what we see when we get there of course.

[tallen]

Hi NeoPao,

I never was able to get the following code to work. The file search no longer works in Access 2016 and I am marginal at best for programming. Maybe someone else out there knows how to correct the with string issue and a replacement for the filesearch command? I cannot imagine there is not Access 2016 standard code language for what used to be a common command, but I am finding it nowhere on the internet.

Private Sub lstLetters_Click()
Dim MergeQuery As String
Dim WordDoc As String
Dim SaveFolder As String
WordDoc = lstLetters.Column(0)
MergeQuery = lstLetters.Column(1)
SaveFolder = lstLetters.Column(2)
'ME:  September, 2008:  added Generic CMS in the following statement
If WordDoc = "Generic CMS" Then GoTo ProcessGeneric
If WordDoc = "Generic" Then GoTo ProcessGeneric
 
GoTo DoTheMerge
 
'original statement
'If WordDoc <> "Generic" Then GoTo DoTheMerge
ProcessGeneric:
If IsNull(Forms![fFacInsp01]![fFacInspSetup01].Form![ipPermNo]) Then
    MsgBox ("Permit# needed to merge Generic document.")
    Exit Sub
End If
If IsNull(Forms![fFacInsp01]![fFacInspSetup01].Form![ipARMS#]) Then
    MsgBox ("ARMS# needed to merge Generic document.")
    Exit Sub
End If
Dim finddoc As String
finddoc = Mid(Forms![fFacInsp01]![fFacInspSetup01].Form![ipPermNo], 3, 5) & Mid(Forms![fFacInsp01]![fFacInspSetup01].Form![ipPermNo], 9, 3) & Mid(Forms![fFacInsp01]![fFacInspSetup01].Form![ipPermNo], 13, 2) & " " & Mid(Forms![fFacInsp01]![fFacInspSetup01].Form![ipARMS#], 6, 3) & ".doc"
Dim CR As String
CR = Chr(13)
On Error GoTo dfLettersPInspError
With Application.FileSearch
    .NewSearch
    .LookIn = pubPermitDocFolder  'a constant in GenMods module
    .SearchSubFolders = False
    .FileName = finddoc
    .MatchTextExactly = True
    .FileType = msoFileTypeWordDocuments
    If .Execute() > 0 Then
        pubCopyPermitDoc = finddoc
    Else
        pubCopyPermitDoc = Null
        MsgBox ("An electronic version of the permit conditions is not available." & CR & "Please see your supervisor about creating one, or using an" & CR & "existing inspection format.")
        Exit Sub
    End If
End With
DoTheMerge:
Call dmerge(WordDoc, MergeQuery, SaveFolder)
Exit Sub
dfLettersPInspError:
MsgBox ("error # " & Err.Number & Err.Description)
Resume Next
End Sub

[NeoPa]

Let's start with something you should have included in your last post. Does this code compile?

As instructed earlier, that's something we need to know before we can proceed in any meaningful way.

I also notice that you don't seem to have bothered to follow other instructions such as moving all the Dim statements to the start of the procedure. It feels like we're fighting to get you to follow instructions and I'm really not sure why you would see it in your interest to allow that. I would expect you to see it as in your interest to co-operate as closely as possible. At the end of the day though, we're just here to help. If you don't want to take maximum advantage of that then only you can make the choice.

[tallen]

Thank you NeoPao,

I would like to take maximum advantage of the help on this site and Bytes has been excellent in past issues. I think what is missing in the conversation is "I am not a Programmer". I am a novice and the language spoken here is ALL foreign. Can you suggest another site I could visit which would provide beginner level assistance? I do not have a degree in computer programming. Even Dim is above my head. I may be on the wrong site. Please kindly advise where I should move my request for basic merge code development.

[NeoPa]

I'm afraid I can offer no advice on that score Tallen. I can't see this as an issue of where you post your question but surely as how you respond when anyone replies. This won't change depending on who you deal with. We're all human and will struggle to help you if you don't answer questions when we ask them.

I get that you aren't trying to be unhelpful, but I'm not sure you get that, much as we'd like to help you, this isn't possible when working in an information vacuum. It really isn't that we don't want to help. That doesn't help you much I know :-(

In a way you remind me of a number of people I've dealt with trying to do simple maths when they're convinced they can't. I'm sure they have everything they need brights-wise, but they've convinced themselves that it's all arcane knowledge and are nervous of even going there. Unfortunately they end up remaining convinced they simply can't do it - even though the steps themselves are actually very straightforward.

At this stage I can only really point you towards a tutorial site (Microsoft Access Tutorials (Strive4Peace)) and offer some well-meant advice.

If you do go somewhere else for help, try as hard as you can to follow and respond to their comments. Some may go over your head, and that's fine. Just let them know and I expect they'll be quite happy to express it differently for you. Have confidence in their desire to help you. Obviously some are more competent than others, but all are there to provide help where they can. Some are better at leading members gently towards what is needed. Others not so much. It seems I've failed here but there are more out there.

Good luck.