Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    melhado
    Guest

    Bookmarks, table type recordsets and findfirst (2K, SR 1)

    Hi!

    Code below is meant to find a record in a child table, and when found, refresh the mainform. This would be analogous to having only the invoice number (many side)and needing to bring up the customer record (one side) which owns it. I figure that once it is found, requerying the mainform will make that happen. Both parent and child are based on tables and linked on a 3-part ID common to both; however, the input here uses a key unique to the child, which is the search criteria.

    It is pretty much the same code, with different criteria, I used on the mainform side of this same form, except that one is searching the one side and displaying the many -- like a typical form/subform relationship works -- and creating a new record in the child table as intended. However, it is not working the same way when I try it on the subform, especially with the Bookmark, which I want to have so that if nomatch is true, there is a record to return to. I have been trying unsuccessfully to make the bookmark = the current record of the form, but without luck. I commented the code with the runtime errors I was getting when I did breakpoints to stop the code. Running it normally just didn't do anything, nor did it do errors.

    Since I am not able to clone the form's recordset, as that would give me only the records that are part of the displayed mainform record, I opened a table type recordset based on the whole table underlying the subform/child, and set the search to look through that. I have had no luck setting the bookmark to the record displayed in the subform at the time they hit the button. The displayed record bears no relation to the sought after record, it is just a matter that it is there when they choose to do the search, so it is convenient to return to in the event the search is unsuccessful.

    Right now, I get the input box, the confirmation, then it dies. I don't even get the "NO Such Record" message if it doesn't exist, yet breakpointing the code, shows the no match to be true, it shows as false when the record exists. Either way, it is not moving the subform record. The button is on the subform whose underlying table is being searched.

    I think that it is the bookmark that is fouling me up, as the rest of the code should work as it did in the other one. Is this a bad assumption? Am I missing something else that may be causing the problem? Is there possibly a different way to search the child table and have it bring up this same form with the main, sub, sub 2, and sub 3 all displayed properly? There is data on the other forms the operator at this stage needs to see, and a report is to be printed at the end of the operation.

    I have been stuck on this for a couple of days and would really appreciate some feedback. I am almost out of hair to pull.
    <font color=blue>
    Private Sub cmdFindRun_Click()

    <font color=448800>'Operator enters desired Run number
    'of piece to be reworked after it has
    'been removed from machine for any
    'period of time.

    'once the run number has been identified,
    'and found, its parent record and related
    'subforms are brought into synch as well.</font color=448800>

    Dim db As DAO.Database
    Dim rsTin As DAO.Recordset
    <font color=448800>'first and last available run numbers, serve as guide</font color=448800>
    Dim lngFirst As Long
    Dim lngLast As Long
    <font color=448800>'Input run number, guide message for input box</font color=448800>
    Dim strInput As String, strGuide As String
    <font color=448800>'Msg advising non-existant number, confirm input</font color=448800>
    Dim strMsg As String, strConfirm As String
    <font color=448800>'Criteria match</font color=448800>
    Dim strCriteria As String
    <font color=448800>'Bookmark current position to return to if NoMatch is true
    'Dim varCurRec As Variant 'bookmark for current record
    'to return to if no match is true</font color=448800>

    Set db = CurrentDb
    Set rsTin = _
    db.OpenRecordset("tblTin", dbOpenSnapshot)

    With rsTin
    <font color=448800> 'Get low and high run numbers</font color=448800>
    .MoveLast
    lngLast = !Run
    .MoveFirst
    lngFirst = !Run

    <font color=448800>'Display high and low run numbers,
    'ask operator for desired run number</font color=448800>
    strGuide = "Please enter a valid RUN number" & vbCrLf _
    & "Between " & lngFirst & " and " & lngLast & "."
    strInput = Trim(InputBox(strGuide))
    If strInput = "" Then Exit Sub
    strCriteria = "[Run]=" & strInput & ""

    <font color=448800>'Store current record as bookmark</font color=448800>

    <font color=red>'this gave me a runtime error
    'Using the varCurRec gave me"Type Mismatch", most likely as the
    'value was "empty", this one is saying not a valid bookmark!
    Forms!frmL60Tin.sfTin.Form.Bookmark = rsTin.Bookmark</font color=red>

    <font color=448800>'Confirm entry with operator</font color=448800>
    strConfirm = "You Entered RUN NUMBER " & strInput & "." & vbCrLf & vbCrLf _
    & "Check your paperwork." & vbCrLf _
    & "If Incorrect, Cancel and Correct."

    If MsgBox(strConfirm, 289, "CONFIRM REQUEST") = vbYes Then

    <font color=448800> 'find the record matching the input</font color=448800>
    .FindFirst strCriteria
    End If <font color=448800>'end confirm msg box</font color=448800>

    If Not .NoMatch Then
    <font color=red>.Bookmark = Forms!frmL60Tin.sfTin.Form.Bookmark</font color=red>

    Else
    <font color=448800>'msgbox announcing record not found</font color=448800>
    strMsg = "Run Number " & strInput & " " & vbCrLf _
    & "Not Found in this database." & vbCrLf & vbCrLf _
    & "Please check with supervisor."
    MsgBox strMsg, 16, "!! NO SUCH RECORD EXISTS !!"

    End If <font color=448800>'not nomatch</font color=448800>
    End With

    Set rsTin = Nothing
    Set db = Nothing

    <font color=448800>'successful search on tblTin, bring up parent</font color=448800>
    Me.Parent.Requery

    Exit_cmdFindRun_Click:
    Exit Sub
    Err_cmdFindRun_Click:
    MsgBox Err.Description
    Resume Exit_cmdFindRun_Click

    End Sub
    </font color=blue>
    Thanks for any and all assistance.

    'dave

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bookmarks, table type recordsets and findfirst (2K, SR 1)

    <hr>I have had no luck setting the bookmark to the record displayed in the subform at the time they hit the button<hr>
    I don't understand what you're trying to do. Is the subform linked to the parent form using master/child links? If so, you can't do what you're trying to do at all. If not, how are you filtering the subform's recordset?

    If you want to move the record in the parent form, you'll have to use a clone of the parent form's recordset and do your find there, not just open a recordset on a table and try to use that to set a bookmark. What you're doing is trying to set a bookmark on an independent recordset to a bookmark in a subform. Neither of those have anything to do with the parent form.

    If you have a nomatch on a find, the bookmark doesn't change, so there isn't any need to try to save it first.
    Charlotte

  3. #3
    melhado
    Guest

    Re: Bookmarks, table type recordsets and findfirst (2K, SR 1)

    Hi Charlotte,

    Thanks for responding.
    <hr><font color=ff8c00>I don't understand what you're trying to do. Is the subform linked to the parent form using master/child links?<hr>
    </font color=ff8c00>
    Yes, the subform is linked to the parent form using master/child links on 3 fields, which uniquely identify the parent record. However the child I am trying to search on has another unique ID that is used from here on.
    <hr><font color=ff8c00>If you want to move the record in the parent form, you'll have to use a clone of the parent form's....<hr>
    </font color=ff8c00>
    Actually, I forst wanted to find the child record by opening the table, hoping to override the filter. If nomatch was true, I wanted it to return to the current record, that is what I was trying to set the bookmark to. After I found the record, then I was attempting to get the mainform to show that related record.
    <hr><font color=ff8c00>What you're doing is trying to set a bookmark on an independent recordset to a bookmark in a subform. Neither of those have anything to do with the parent form.<hr>
    </font color=ff8c00>
    Well put, I now see that my approach was destined for failure. <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    Let me ask, using the invoice/customer analogy, what approach would you use if you had just the invoice number and needed to find the customer it belonged to? How does one set the find to include parameters to match the linked fields between tables and then display the parent/subform to bring the records up together?

    If I move the code to the mainform, how do I take the operator input for the original 3-part ID that is linking them, and add a fourth input box to capture the "Run" ID on the child? Would I need to clone both parent and child recordsets-- forms or tables? -- to make my strCriteria work? It would have to be able to work from any record the user is on at the time, unless this is a job for a QBF? Or do I first find the parent, then ask the operator for the "Run" ID?
    i.e.,<font color=blue> <pseudo-code>
    from the parent recordset
    strSN=Trim(Input box 1...)
    strEnd=(inputbox2 ...) strPc =inputbox3...
    then from child recordset
    (this one is a number, so do I still do it as a string for the input box, or a number?) strRun=(input box 4) ...
    then concatenate them for my strCriteria...here is where I am not sure how to bring them both together
    strCriteria="[Bill_Num]='"&strSN&"' AND "[Bill_Half] = input2, AND "[PcNum]='"input 3.... all these are from the parent, how do I specify that "Run]="&strRun&"" comes from the child recordset?</font color=blue>

    I have done finds with one recordset declared and Me.Recordsetclone, but never 2 recordsets -- where do you suggest I do the subform clone recordset so that it is only filtered by the mainform record returned by the input1&2&3? Or do I need to find the main record first, then use something like another find routine with another input box, or will the GoToRecord to take the value of the "strRun" variable to move the subform record to the desired one?

    Thank you for your help

    'dave

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Bookmarks, table type recordsets and findfirst (2K, SR 1)

    Hi Dave,
    If I understand you correctly, I think what you want to do is find the relevant Run number in the recordset you've opened, then also store the values from this record that form the 3 part ID of your main form record. You can then use a recordsetclone of your main form's recordset to find the record that matches these 3 values and set the bookmark of the main form's recordset to the bookmark of the recordsetclone. If the Run number is not found at the start of the process, you simply exit the sub and you haven't gone anywhere.
    I hope that makes some sense?
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bookmarks, table type recordsets and findfirst (2K, SR 1)

    You can only sync parent-child forms one way. That's been explained before in other threads on this same question. The fact that there are keys in the child form that aren't in the parent recordset or that aren't part of the linking fields has nothing to do with it. The child recordset is limited by the link keys to those records that match the parent keys, period. That means that if you want to move to a different parent record, you must do it from the parent form's recordset. The only way I can think of to even approximate what you're trying to do is to have the parent form read the key value from the child form and then use that in a find on the parent form's recordset.

    However, your analogy doesn't make sense to me. If you have an invoice displayed then, by definition, you should already have the customer the invoice belongs to in the parent form. Otherwise, there's something very strange about your forms or the way you've linked them. In normal usage, the child form can only display records related to the parent form, so you can't have an invoice displayed that relates to a different parent record.
    Charlotte

  6. #6
    melhado
    Guest

    Re: Bookmarks, table type recordsets and findfirst (2K, SR 1)

    Makes absolutely perfect sense, Rory. It appears to be exactly what I want to do. Getting there, however, is a different story. I'd appreciate a bit of detail on how to go about that. I have done this kind of find with one recordset that is a mainform, and then gone to a new record in the sub. Although it sounds similar, it seems a bit more complicated.
    <hr><font color=ff8c00>find the relevant Run number in the recordset you've opened, then also store the values from this record that form the 3 part ID of your main form record.<hr>
    </font color=ff8c00>
    OK, so opening the table type recordset was the right first move? How do I store the linked values from that record is what is eluding me.
    <hr><font color=ff8c00>You can then use a recordsetclone of your main form's recordset to find the record that matches these 3 values and set the bookmark of the main form's recordset to the bookmark of the recordsetclone<hr>
    </font color=ff8c00>
    When do I clone the mainform's recordset, and it sounds as if I'd be better off running the code from the mainform -- it is currently running from the subform. Then I could do the Me.recordsetclone instead of needing the fully qualified identifier? Would it possible for you to copy my <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=73265&page=& view=&sb=&o=&vc=1#Post73265>code in this post</A> and modify the relevant areas, or use it to illustrate what you are saying? You have the idea of what I am trying to do perfectly, I would appreciate learning how to do it.
    <hr><font color=ff8c00>If the Run number is not found at the start of the process, you simply exit the sub and you haven't gone anywhere<hr>
    </font color=ff8c00>
    That works, exiting the sub and just giving them a msg box that says the record doesn't exist.

    Appreciate the feedback. Thanks

    'dave

    edited to activate link -- Charlotte

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Bookmarks, table type recordsets and findfirst (2K, SR 1)

    OK, it would be somthing like the following. I've called your ID fields Id1, ID2 and ID3 and assumed they're all numeric (you'll need to add quotes if any of yours are text fields)
    Private Sub cmdFindRun_Click()

    'Operator enters desired Run number
    'of piece to be reworked after it has
    'been removed from machine for any
    'period of time.

    'once the run number has been identified,
    'and found, its parent record and related
    'subforms are brought into synch as well.

    Dim db As DAO.Database
    Dim rsTin As DAO.Recordset, rstClone as DAO.Recordset
    'first and last available run numbers, serve as guide
    Dim lngFirst As Long
    Dim lngLast As Long
    Dim lngID1 as Long, lngID2 as Long, lngID3 as Long
    'Input run number, guide message for input box
    Dim strInput As String, strGuide As String
    'Msg advising non-existant number, confirm input
    Dim strMsg As String, strConfirm As String
    'Criteria match
    Dim strCriteria As String

    Set db = CurrentDb
    Set rsTin = _
    db.OpenRecordset("tblTin", dbOpenSnapshot)

    With rsTin
    'Get low and high run numbers
    .MoveLast
    lngLast = !Run
    .MoveFirst
    lngFirst = !Run

    'Display high and low run numbers,
    'ask operator for desired run number
    strGuide = "Please enter a valid RUN number" & vbCrLf _
    & "Between " & lngFirst & " and " & lngLast & "."
    strInput = Trim(InputBox(strGuide))
    If strInput = "" Then goto Exit_cmdFindRun_Click
    strCriteria = "[Run]=" & strInput
    'Confirm entry with operator
    strConfirm = "You Entered RUN NUMBER " & strInput & "." & vbCrLf & vbCrLf _
    & "Check your paperwork." & vbCrLf _
    & "If Incorrect, Cancel and Correct."

    If MsgBox(strConfirm, 289, "CONFIRM REQUEST") = vbYes Then

    'find the record matching the input
    .FindFirst strCriteria
    End If 'end confirm msg box

    If Not .NoMatch Then
    lngID1 = !ID1
    lngID2 = !ID2
    lngID3 = !ID3
    Else
    'msgbox announcing record not found
    strMsg = "Run Number " & strInput & " " & vbCrLf _
    & "Not Found in this database." & vbCrLf & vbCrLf _
    & "Please check with supervisor."
    MsgBox strMsg, 16, "!! NO SUCH RECORD EXISTS !!"
    goto Exit_cmdFindRun_Click
    End If 'not nomatch
    End With
    set rstClone = Me.Recordsetclone
    with rstclone
    .findfirst "[ID1] = " & lngID1 & " AND [ID2] = " & lngID2 & " AND [ID3] = " & lngID3
    If not .NoMatch then
    me.bookmark = .Bookmark
    else
    msgbox "There is no parent record associated with this number!"
    end if
    end with
    set rstclone = nothing


    Exit_cmdFindRun_Click:
    Set rsTin = Nothing
    Set db = Nothing
    Exit Sub
    Err_cmdFindRun_Click:
    MsgBox Err.Description
    Resume Exit_cmdFindRun_Click

    End Sub
    I hope that gives you the idea (but please note that I've just been typing it straight in so I can't guarantee it's 100% accurate!)
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    melhado
    Guest

    Re: Bookmarks, table type recordsets and findfirst (2K, SR 1)

    You make it look so simple <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I will be working on it this afternoon, but wanted to thank you and clarify my own understanding of how it works.

    I never would have thought that after the strCriteria find is successful, to then assign the 3 linking fields to variables, instead of to a bookmark. If that find is unsuccessful, then it exits gracefully, which is what it should do.

    If I understand it correctly (bear with me, I like to know why), because the recordset is still open, they are held along with the results of the find on the table recordset, meaning the pointer is still on that record in the table, as you are not closing that rst until Exit. I suppose that acts as a placeholder for the subform record, while I clone the mainform and search it on the three linked fields? When the find is successful on the mainform, it and specified subform are displayed together? Am I understanding it correctly? Its exactly what I wanted. ("Me.recordsetclone" indicates it makes more sense to put this button on the mainform)

    I have yet to keep 2 recordsets open like this in one piece of code. A nifty learning experience, I thank you.
    <hr><font color=ff8c00>I hope that gives you the idea (but please note that I've just been typing it straight in so I can't guarantee it's 100% accurate!)<hr>
    </font color=ff8c00>
    It is accurate enough for me to work with, if my understanding is correct. The IDs are strings, but I can handle that, I also like the second message box if a parent isn't found, which means someone has been playing in the tables sandbox.

    Thank you for getting me on the right road.

    'dave

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Bookmarks, table type recordsets and findfirst (2K, SR 1)

    Actually, because the values are assigned to variables, you could destroy the recordset straight away - you would just need to make sure that it was destroyed in all possible branches of your If clause. Doing it the way I did was partly laziness (I didn't have to change as much of the structure of your original code) and partly because you only need to destroy it in one place in the code - you just use GOTOs to branch to the exit procedure, which is also used by your error handler. It should hopefully mean that if your code bombs out unexpectedly, you're not left with an open recordset.
    Hope that helps and enjoy your playing!
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    melhado
    Guest

    Re: Bookmarks, table type recordsets and findfirst (2K, SR 1)

    OK, so once they are stored in variables, they sit until the End Sub? What I am still not clear on is how the find (results of the strInput and strCriteria) from the child table is brought together with the parent. It is not apparent to me where that value is being stored so that when the 3 part ID calls the parent, they come up together. Or does the pointer from the findfirst on the child recordset just sit in the record identified?

    I realize that I will also have to set the focus to the subform at the end of the code, if the search is successful, but I think I will enjoy working with it this afternoon. Learned a lot here, thanks

    'dave

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Bookmarks, table type recordsets and findfirst (2K, SR 1)

    You're basically pulling the parent ID details from the record you find in the first recordset you open (the RUN number you searched for) and then finding the parent record that has those 3 IDs. Because the subform is already linked by these IDs to the main form, Access takes care of finding the relevant subform records.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    melhado
    Guest

    Re: Bookmarks, table type recordsets and findfirst (2K, SR 1)

    Now I understand. It was what Access is doing behind the scenes that I wasn't able to see.

    Thanks again,

    'dave

  13. #13
    melhado
    Guest

    Re: Bookmarks, table type recordsets and findfirst (2K, SR 1)

    Well, I apparently am not doing something right. Here is the code I put together, using what you told me with what I have fpr messages and variables. The variables for the linking fields are strings, so I added the single and double quotes where needed and made the 3 part ID a variable. I put the button on the mainform. when I compile, I get no complaints, but when it runs, it goes nowhere -- I get the input box OK, and then it does nothing. Put breakpoint on the bookmark line and that is when I hover over the commands and have detailed in <font color=red>RED</font color=red> the problems I noted.

    I tried quotes around the string variables to no avail. The "Object block.." error on the .noMatch has me baffled. Do I need to redim the variable, did I somehow end it prematurely? And the fact that it shows the right run number and the wrong variable values further confuses me.

    One point may be that the "Run" number being input is a long integer; but when I try to dim the input variable as long, instead of string, I get type mismatch errors. Could this be the problem? How do I get around it? That is the only number, the linking values are string.

    What am I missing? <img src=/S/help.gif border=0 alt=help width=23 height=15>

    Appreciate your feedback

    Private Sub cmdFindRun_Click()
    'Operator enters desired Run number
    'of piece to be reworked after it has
    'been removed from machine for any
    'period of time.

    'once the run number has been identified,
    'and found, its parent record and related
    'subforms are brought into synch as well.

    'set Declarations of recordsets and storage variables
    Dim db As DAO.Database
    Dim rsTin As DAO.Recordset, rsMain As DAO.Recordset
    Dim strBill As String, strHalf As String, strPc As String
    Dim strCriteria As String 'sub Criteria match
    Dim strMain As String 'main criteria match

    'first and last available run numbers, serve as guide
    Dim lngFirst As Long
    Dim lngLast As Long

    'Input run number, guide message for input box
    Dim strInput As String, strGuide As String

    'Msg advising non-existant number, confirm input
    Dim strMsg As String, strConfirm As String

    'assign variables
    Set db = CurrentDb
    Set rsTin = _
    db.OpenRecordset("tblTin", dbOpenSnapshot)

    With rsTin
    'Get low and high run numbers
    .MoveLast
    lngLast = !Run
    .MoveFirst
    lngFirst = !Run

    'Display high and low run numbers,
    'ask operator for desired run number
    strGuide = "Please enter a valid RUN number" & vbCrLf _
    & "Between " & lngFirst & " and " & lngLast & "."
    strInput = Trim(InputBox(strGuide))
    If strInput = "" Then GoTo Exit_cmdFindRun_Click
    strCriteria = "[Run]=" & strInput & ""

    'Confirm entry with operator
    strConfirm = "You Entered RUN NUMBER " & strInput & "." & vbCrLf & vbCrLf _
    & "Check your paperwork." & vbCrLf _
    & "If Incorrect, Cancel and Correct."

    If MsgBox(strConfirm, 289, "CONFIRM REQUEST") = vbYes Then

    'find the record matching the input
    .FindFirst strCriteria <font color=red> it shows the right Run number entered</font color=red>
    End If 'end confirm msg box

    <font color=red>' When I did the breakpoint and mouse over the .NoMatch, it is telling me "Object block or With block variable not set ", yet it the with rstin is set above. I cannot see where I might have prematurely ended it.???</font color=red>
    If Not .NoMatch Then
    <font color=red>The following are showing only the record the main form is on at the time the button is executed. It is not changing, even though the strInput above is the correct entry, and has different values. I tried quotes, but the compiler complained.</font color=red>
    strBill = !Bill_Num
    strHalf = !Bill_Half
    strPc = !PcNum

    Else
    'msgbox announcing record not found
    strMsg = "Run Number " & strInput & " " & vbCrLf _
    & "Not Found in this database." & vbCrLf & vbCrLf _
    & "Please check with supervisor."
    MsgBox strMsg, 16, "!! NO SUCH RECORD EXISTS !!"

    'exit gracefully, like the lady on TV says, "goodbye!"
    GoTo Exit_cmdFindRun_Click

    End If 'not nomatch
    End With

    'match to parent
    Set rsMain = Me.RecordsetClone
    'set criteria for matching parent
    strMain = "Bill_Num ='" & strBill & _
    "' AND Bill_Half ='" & strHalf & _
    "' AND PcNum = '" & strPc & "'"

    With rsMain
    .FindFirst strMain
    If Not .NoMatch Then
    Me.Bookmark = .Bookmark
    Else
    MsgBox "There is no billet record " & vbCrLf _
    & "associated with this Run number." & vbCrLf & vbCrLf _
    & "Please inform supervisor of incident."
    End If
    End With
    Set rsMain = Nothing

    'gracefully exit, rs closed here so if code bombs,
    'we are not left with an open recordset.
    Exit_cmdFindRun_Click:
    Set rsTin = Nothing
    Set db = Nothing
    Exit Sub
    Err_cmdFindRun_Click:
    MsgBox Err.Description
    Resume Exit_cmdFindRun_Click

    End Sub

    Thanks again,

    'dave

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Bookmarks, table type recordsets and findfirst (2K, SR 1)

    You don't appear to have any error handling in this routine, so an unhandled error in your code could be resetting your variables. I'd suggest stepping through the code and watching the rsTin and the db variable to see when they get reset.
    Charlotte

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Bookmarks, table type recordsets and findfirst (2K, SR 1)

    Hi,
    First thoughts:
    1. As Charlotte said, add On Error Goto Err_cmdFindRun_Click at the start of the procedure
    2. Try changing
    strCriteria = "[Run]=" & strInput & ""
    to
    strCriteria = "[Run] = " & CLng(strInput)
    and see if that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

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
  •