By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,760 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

Error 91 due to incorrect With Statement or Object Not Defined

P: 12
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.

Expand|Select|Wrap|Line Numbers
  1. With Application.FileSearch
  2. .NewSearch
  3. .LookIn = pubPermitDocFolder 'a constant in GenMods module
  4. .SearchSubFolders = False
  5. .FileName = finddoc
  6. .MatchTextExactly = True
  7. .FileType = msoFileTypeWordDocuments
  8. If .Execute() > 0 Then
  9. pubCopyPermitDoc = finddoc
  10. Else
  11. pubCopyPermitDoc = Null
  12. 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.")
  13. Exit Sub
  14. End If
  15. End With
  16. DoTheMerge:
  17. Call dmerge(WordDoc, MergeQuery, SaveFolder)
  18. Exit Sub
  19. dfLettersPInspError:
  20. MsgBox ("error # " & Err.Number & Err.Description)
  21. Resume Next
  22. End Sub
3 Weeks Ago #1

✓ answered by 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.

Share this Question
Share on Google+
10 Replies


NeoPa
Expert Mod 15k+
P: 31,419
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.
3 Weeks Ago #2

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

Expand|Select|Wrap|Line Numbers
  1. Dim SearchCount As Integer
  2. SearchCount = [CurrentSearchNo]
  3. totcnt = rst.RecordCount
  4. If totcnt = 0 Then
  5.     MsgBox ("No matching records for parameter entered.  Start new search!")
  6.     Me![cmdRESET].SetFocus
  7.     rst.Close
  8.     upd.Close
  9.     Exit Function
  10. End If
  11. rst.MoveFirst
  12. 'MsgBox (rst.RecordCount)
  13. If rst.BOF And rst.EOF Then
  14.     MsgBox ("no transaction records")
  15.     rst.Close
  16.     upd.Close
  17.     Exit Function
  18. End If
  19. Dim ReadCount As Integer
  20. Do Until rst.EOF
  21.     ReadCount = ReadCount + 1
  22.     upd.Seek "=", rst![SortSeq]
  23.     If upd.NoMatch Then
  24.         MsgBox ("Unable to locate tmpEFSearch record")
  25.         rst.Close
  26.         upd.Close
  27.         Exit Function
  28.     End If
2 Weeks Ago #3

P: 12
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.

Expand|Select|Wrap|Line Numbers
  1. With Application.FileSearch
  2. .NewSearch
  3. .LookIn = pubPermitDocFolder 'a constant in GenMods module
  4. .SearchSubFolders = False
  5. .FileName = finddoc
  6. .MatchTextExactly = True
  7. .FileType = msoFileTypeWordDocuments
  8. If .Execute() > 0 Then
  9. pubCopyPermitDoc = finddoc
  10. Else
  11. pubCopyPermitDoc = Null
  12. 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.")
  13. Exit Sub
  14. End If
  15. End With
  16. DoTheMerge:
  17. Call dmerge(WordDoc, MergeQuery, SaveFolder)
  18. Exit Sub
  19. dfLettersPInspError:
  20. MsgBox ("error # " & Err.Number & Err.Description)
  21. Resume Next
  22. End Sub
2 Weeks Ago #4

twinnyfo
Expert Mod 2.5K+
P: 3,205
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.
2 Weeks Ago #5

NeoPa
Expert Mod 15k+
P: 31,419
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.
2 Weeks Ago #6

NeoPa
Expert Mod 15k+
P: 31,419
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.
2 Weeks Ago #7

P: 12
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub lstLetters_Click()
  2. Dim MergeQuery As String
  3. Dim WordDoc As String
  4. Dim SaveFolder As String
  5. WordDoc = lstLetters.Column(0)
  6. MergeQuery = lstLetters.Column(1)
  7. SaveFolder = lstLetters.Column(2)
  8. 'ME:  September, 2008:  added Generic CMS in the following statement
  9. If WordDoc = "Generic CMS" Then GoTo ProcessGeneric
  10. If WordDoc = "Generic" Then GoTo ProcessGeneric
  11.  
  12. GoTo DoTheMerge
  13.  
  14. 'original statement
  15. 'If WordDoc <> "Generic" Then GoTo DoTheMerge
  16. ProcessGeneric:
  17. If IsNull(Forms![fFacInsp01]![fFacInspSetup01].Form![ipPermNo]) Then
  18.     MsgBox ("Permit# needed to merge Generic document.")
  19.     Exit Sub
  20. End If
  21. If IsNull(Forms![fFacInsp01]![fFacInspSetup01].Form![ipARMS#]) Then
  22.     MsgBox ("ARMS# needed to merge Generic document.")
  23.     Exit Sub
  24. End If
  25. Dim finddoc As String
  26. 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"
  27. Dim CR As String
  28. CR = Chr(13)
  29. On Error GoTo dfLettersPInspError
  30. With Application.FileSearch
  31.     .NewSearch
  32.     .LookIn = pubPermitDocFolder  'a constant in GenMods module
  33.     .SearchSubFolders = False
  34.     .FileName = finddoc
  35.     .MatchTextExactly = True
  36.     .FileType = msoFileTypeWordDocuments
  37.     If .Execute() > 0 Then
  38.         pubCopyPermitDoc = finddoc
  39.     Else
  40.         pubCopyPermitDoc = Null
  41.         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.")
  42.         Exit Sub
  43.     End If
  44. End With
  45. DoTheMerge:
  46. Call dmerge(WordDoc, MergeQuery, SaveFolder)
  47. Exit Sub
  48. dfLettersPInspError:
  49. MsgBox ("error # " & Err.Number & Err.Description)
  50. Resume Next
  51. End Sub
2 Weeks Ago #8

NeoPa
Expert Mod 15k+
P: 31,419
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.
2 Weeks Ago #9

P: 12
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.
1 Week Ago #10

NeoPa
Expert Mod 15k+
P: 31,419
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.
1 Week Ago #11

Post your reply

Sign in to post your reply or Sign up for a free account.