Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run Time Error 91 (A2K)

    Hi,
    I posted this question under a different thread, but I thought that I maybe should post a separate question. I've worked with this since yesterday and I'm just stumped.

    In my frmSurvey, at various different times and when doing different actions I keep getting this Run Time Error 91: Object Variable or with block variable not set. Jim has helped me tremendously in implementing this code, and though I am starting to understand most of it, I can't figure out what's triggering this error. Not only does it occur on different commands, sometimes it happens, sometimes it doesn't (for instance if I just open and close the form nothing seems wrong...but if I do anything within the form this error starts popping up), and when I look in the VBE it's not breaking on the same line of code each time.

    I've tried compiling the project but that did not solve the problem. I've tried to research why this is happening, but I'm just stumped at the variation of the same error.

    If anyone has any ideas, I would greatly appreciate it! If you need more details or need me to post the nonworking code directly, please let me know! I'm just sort of at a standstill until I figure this out, or at least the logic behind it.

    Thanks so much!
    Sarah
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Run Time Error 91 (A2K)

    Do you know what command it bombs on?

  3. #3
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 91 (A2K)

    Thanks for your reply Pat,

    It's quite a few so this might be pretty long.

    If I open and close the form without doing anything, it closes with no problems. As I was sitting here pondering, I realized that no code bombs until I try cmdBackup which first comes up with Run Time Error 3021 (Either BOF or EOF is true, or the current record has been deleted. Requested operation requires a current record). I didn't mention this error earlier but now I think it might be related so cmdBackup gets Error 3021 and bombs here:
    --------------------
    Private Sub poseQuestion()

    strQID = rstQ!strqnum **BREAK**
    strSql = "SELECT * FROM tblResponselist WHERE strqnum='" & strQID & "' ORDER BY strrnum"
    rstR.Open strSql, con, adOpenKeyset, adLockReadOnly, adCmdText
    nResp = rstR.RecordCount
    If nResp = 0 Then
    MsgBox "No Responses available for Question" & strQID, , "Survey Error"
    GoTo nextQuestion
    End If
    rstR.MoveFirst ' Position to first response
    Me.lblQuestion.Caption = rstQ!strquest ' Load question to form
    Me.lblnumber.Caption = rstQ!strqnum
    ' Initialize response controls
    For i = 1 To 11
    Me("chkResponse" & i).Value = False
    Me("chkResponse" & i).Visible = False
    Me("lblResponse" & i).Visible = False
    Me("lblqnum" & i).Visible = False
    Next i
    ' Load response texts and set visible as needed
    For i = 1 To nResp
    Me("lblResponse" & i).Visible = True
    Me("lblResponse" & i).Caption = rstR!strresp
    Me("chkResponse" & i).Visible = True
    Me("lblqnum" & i).Visible = True
    Me("lblqnum" & i).Caption = rstR!strrnum
    rstR.MoveNext
    Next i
    nextQuestion:
    rstR.Close ' Close response table
    rstQ.MoveNext ' Advance question table to next

    End Sub
    -------------------------------
    Now at this point, if I close the VBE and attempt to click cmdBackup again, then the Error 91 appears:
    -------------------------------
    Private Sub cmdBackup_Click()
    Dim rstDA As ADODB.Recordset

    If Not rstQ.BOF Then **BREAK** ' Test if a Beginning of File
    rstQ.MovePrevious
    rstQ.MovePrevious ' If not, backup one question
    Set rstDA = rstA.Clone ' Build clone recordset of answer table
    If rstDA.RecordCount > 0 Then
    rstDA.MoveFirst ' See if any answers exist for new current question
    strQID = rstQ!strqnum
    rstDA.Find "strqnum='" & strQID & "'"
    If rstDA.RecordCount > 0 Then ' If so,
    rstDA.MoveFirst ' position to first answer
    While Not rstDA.EOF ' and delete all prior answers
    rstDA.Delete
    rstDA.MoveNext
    Wend
    rstDA.Close ' Close clone recordset
    End If
    End If
    Else
    MsgBox "Already at start of question list", , "Survey Error"
    End If
    poseQuestion ' Go pose prior question

    End Sub

    ----------------------
    Now when I try cmdQuit I get Error 91 here:
    -----------------------
    Private Sub Form_Close()
    rstA.Close
    rstQ.Close
    Set rstA = Nothing
    Set rstR = Nothing
    Set rstQ = Nothing
    con.Close
    Set con = Nothing

    End Sub
    ---------------------------
    I also got the error earlier for cmdAccept and also in PrivateSub pose question (at a different point then mentioned above), but I can't get that to replicate right now and this is a handful as is.

    Much appreciation
    Sarah

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Run Time Error 91 (A2K)

    <P ID="edit" class=small>(Edited by patt on 15-Apr-04 14:06. poseQuestion added instruction)</P>The 3021 comes up when you first enter the form and it bombs in the Backup event on the following instruction:
    strQID = rstQ!strqnum
    To overcome this I put an instruction between the 2 MovePrevious instructions.

    When you exit out of the VBE editor it seems to have set rstA to nothing and when you then Accept at this point it bombs with a 91 error on the following:
    getResponse:
    rstA.AddNew

    I also had to put a test at the start of the PoseQuestion sub, that is shown in bold:

    Private Sub poseQuestion()
    If rstQ.BOF Then Exit Sub
    strQID = rstQ!strqnum


    I would not have done this whole survwy this way at all, one reason is that when you go backwards or forwards you don't see anything you have ticked previously.

    If you want my opinion on how I would have done it, just ask.

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 91 (A2K)

    Hi Sarah,

    I see you are still working away, was just thinking about the structure after Pat's comment and wondering how you are going to use the data etc or analysis the data? ( which I am not sure how critical this is - but I assume given you are capturing it you want to do something with it?)

    At the moment you don't have a unique way of identifying each survey from what I can see, which will mean that you can't recall a questionnaire as such if you need to go back and validate it.

    Also the other thing is that that the structure allows for a user to multi enter all questions. (are you intending on creating validation rules for each question?) For example a user can add 2 response to Q3 about temperature, which if you want to do any analysis with could cause a problem, especially as you don't know this has happened given there is no unique key for each survey.

    Suppose I'm just asking what your goal is with the data, as you may need to take the above into account before you progress to far down the track.

    If you want I could have a look and see what I have floating around from the previous survey stuff I have done...

    Cheers
    Tony

  6. #6
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 91 (A2K)

    Pat,
    I appreciate your help and of course I would be most interested in your opinion!
    Thanks,
    Sarah

  7. #7
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 91 (A2K)

    Hi Tony,
    I'm learning as I go on this project, so there's features I know I need, but I just am trying to work on it one step at a time. For instance, yes I do want a way to define each survey and then further define each survey by date, data collector, etc. The info I'm working with right now is all the same survey all from the same collector, so I sort of let those features slide in order to get going. However, in a cardboard box by my feet is another survey from multiple data collectors that will have to be dealt with.
    The head of the project has grand ideas of using the data in SPSS or SAS and using it in ArcView GIS as well, but beyond collection and basic analyization, I'm not positive that there is a true blue defined goal. It's basically survey data on features of parks to see what characteristics are more condusive to physical activity.
    I noted also that some questions will need to be blocked to only have one answer while some allow for multiple responses....I also need a way to define what is stored when a check box is selected, and a way if they select Other/Detail (98) to have a little dialog box pop up so they can enter in what they need (I made a little form frm detail but haven't gone far with it)...plus the things I mentioned earlier about specific surveys, dates, parks, collectors, etc.
    Any opinions or things you've got floating around are as always much appreciated. I've just been tackling one obstacle at a time so as to not get too overwhelmed/discouraged/throw the computer out of my 8th floor window. <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    Thanks so much. Just having dialogue with you all helps immensely!
    Sarah

  8. #8
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 91 (A2K)

    Pat,

    I, for one, am very interested in your proposed solution. I learned long ago that there is never one best way to solve a problem in programming....

    -- Jim

  9. #9
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 91 (A2K)

    Hi Sarah,

    Understand a little more about where/why your at the point you are now. Suppose I would have started at the other end and configured the db to work the other way first. In that I would have start a unique record level for each survey and then built downwards to the questionnaire level. But of course there are many ways to skin a cat as they say.

    If it would help I have a db that might give you some ideas. It was used for a research project and contains various methods including the binary, lookup and a complex lookup sequence based over multiple visits against one person. Suppose you need to find what is the most comfortable way for you to deal with the data, this may give you some idea's and at least provide another way to control user options etc. The design itself is totally different to what you have been working with as it uses bound forms. (I just find them quicker to deal with and easier to control for things like this)

    Not sure how your Geocoding works depending on where you are? I assume that potentially the same as the mesh blocking etc that I've worked with. The db was configured to capture address components so we could run it against a mesh block procedure after the data capture etc also. (the mesh blocking wasn't done in the db but on a server so the actually procedures are not in the db) but would also work as a means of feeding to arcview potentially.

    Only issues is that I can't get down under 100k without structural changes and don't have time to do that, also it may make it harder for you to see how it all works as it is fully functional now. (212K zipped) So therefore if you want to have a look you can email me. (my email address is on my profile) and I'll forward.

    Suppose all I can suggest is to take a quick step back now and review what you need. Jim's method is effective but will require a lot more programming potentially to achieve what I suspect you actually need in the long run. If you are 'learning' as you say above it will therefore consume time coming to grips with it, but that can also be a good way to learn. However Access when used it it's simplest form using bound controls is very quick, effective and can provide the same results without as much code cutting. The choice of course is ultimately yours...

    Cheers
    Tony

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Run Time Error 91 (A2K)

    Hi Jim
    As you say, there is usually more than one way to skin a cat. What I don't like about your solution (don't get me wrong here, it works so I cannot knock it) is that it takes a lot of code to do the job.

    I would have 5 basic tables as follows:
    1. SetQuestions This table holds a list of questions that form the basis of all questionaires. It ends up to be a static table of questions, one that rarely changes.
    2. SetResponses This table holds the list of responses to the questions supplied in 1. above. Again, this is a static list of responses.
    3. Survey This is a table where the description, date, etc of each survey is entered. This would be the table that the survey form is based upon.
    4. Questions This is the table that the first subform is based on, on the form described in 3. above.
    5. Responses This is the table that the second subform is based on, on the form described in 3. above.

    The form would have the Survey table as it's Recordsource. Once the survey details have been completed, the SetQuestions table is copied into the Questions table based upon the survey ID (this is the link) in the Survey table. The table SetResponses is then copied into the Responses table, and this is based upon the Questions table (it's ID field as the link).
    The subforms would be requeried to populate the subforms, then it's straight data entry into the Responses subform.

    One of the up-sides into doing it this way is the small amount of code required (copying 2 tables).
    Another up-side is that Access looks after all record updating with all responses kept in the Responses table reflecting on what the user has chosen.

    This is another way to approach this challenge. It would be interesting to see what other loungers would do.

  11. #11
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Salem, Oregon, USA
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 91 (A2K)

    I didn't stick w/ the code long enough to figure out how exactly it traced throuhg, but the error that you are getting I often get if I have referred to some object (recordset, database) that I failed to set -- usually, a typo or a change in name or a turd left over from code I might have pasted. It's possible w/ your rstQ, rstA, rstR, etc. that somewhere along the line (or several places along the line) you are calling code that refers to a recordset that doesn't exist yet. Just a thought! -- thx, Pat

  12. #12
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 91 (A2K)

    Hi Tony,

    The larger issue here is that this grant is sort of heading into unknown territory, so to speak, for the people who are in charge of the project. This is the first project of it's type and so even the top dogs are pretty much changing things as they go along. No definite methodology is in place.

    When I was hired they were like hey we have all the data we need in Access, we just don't know how to build a form. I think if you look a few weeks or a month or so back early on when I was asking questions, Hans neatly referred to what they had as a metadata table, and I worked first on figuring out some normalization and moved forwards from there. The project leaders, to roughly quote, just want the data in Access, but to be absolutely 100% honest, I'm not sure that they are certain what they want to do with it once it's in there. They also, again roughly quoting, want it to work with ArcView. No details, that's that.

    So, from my end, in terms of the relationship with ArcView, I just want, at this point, to make sure that all my fields, etc., fit parameters for GIS tables, but not delve too much further in that area right now. I deal with that aspect almost as a separate project. In fact there is a stand alone GIS dataset in the works from GPS points we took one afternoon in the whole park, but I digress.

    I really like Jim's method because I am enjoying the challenges that come from learning the code. However, I do appreciate any further ideas that you and Pat or anyone else here has regarding their opinion on their favorite/best way to do this. I have had some help today working out the runtime errors, from Pat and others, but haven't yet had a chance to try anything but later tonight I'll post the changes and remark again in a bit.

    My goal is to by Sunday evening have a system that will allow me to proceed in entering the initial one survey with one surveyor data...and have all the info that I need safely tucked in tblAnswers (or whatever it may be called by then), regroup and see how I can more efficiently build this database to handle multiple surveyors and multiple surveys. So as I see it, there's a few more immediate things I need to do once the runtime errors are solved: figure out if I can assign values to checkboxes (so instead of storing as X I can store a certain checkbox as 98 if the label is "other" or 97 if "completed", etc.) to be stored in the table, store correct labels for the checkbox values as another field in the table, and when a certain value is stored (in this case 98) I want a dialog box to open where the user can enter in the detail info (up to 255 characters) and have that saved in the correct field in tblAnswers.

    If I'm totally off base in proceeding in this manner, feel free to tell me so!!!

    I'll send you an email and I'm interested in seeing the db you have!

    Muchas gracias,
    Sarah

  13. #13
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 91 (A2K)

    Hi Pat,
    I don't know if it was included in my zip file but I did at one point start working on a tblInfo which held the data like data, surveyor name, etc. I've just never worked with subforms before so wasn't sure exactly how to proceed in connecting tblinfo, tblQuestions, and tblResponseList and then where would my answers be stored? Please forgive me if it's an elementary question. I will fairly soon, have to deal with multiple surveyor IDs so this is something I've wondered how I'd do.

    If you have a minute do you think you might go into some more detail about what you're talking about here?

    Appreciatively,
    Sarah

  14. #14
    Star Lounger
    Join Date
    May 2002
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run Time Error 91 (A2K)

    Hi Pat,
    Thanks for your thought. To my unknowledgeable eye it seemed to me that it would occur if the recordset wasn't set quite right as you said. But just the variety of the error and the multitude of lines on which it is breaking really threw me for a loop. Like I posted to Tony, I think I have some potential fixes that I will try to implement and post on later.

  15. #15
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Run Time Error 91 (A2K)

    <P ID="edit" class=small>(Edited by patt on 18-Apr-04 09:31. Warning re overring your database)</P>Rather than try to describe what I was trying to say I have included a database that shows what I was tring to describe.

    Hope you find it helpful, either way let me know what you think.

    BTW don't override your database with this one, it only includes the stuff I needed to show you.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •