Results 1 to 10 of 10
  1. #1
    melhado
    Guest

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

    **This is a continuation of a previous thread. To see the original posts, click on the "Full code is here" link below - Rory 14/09/2001**

    Good eyes, ironically, in the code I used as a basis for this one, I had it as vbOK.

    However, it didn't do what was expected -- it did give me back the ability to do breakpoints, and in doing so, I noticed something interesting when checking to see what variables were stored. It is only the first record of what appears to be the main table (rsMain). I even manually moved the records on the forms to other records, then ran the button -- it gave me the confirm message and then the forms shot back to the first record on the mainform and its related records! I ran it with breakpoints and saw that it is assigning variables to the first record values only (which would also be the same values in the rsTin recordset; yet, the Run number it assigned, was the one I input -- which had different 3 part common ID!

    I also got back the <With block..object variable not defined> message I had the other day, when hovering over the .NoMatch, and field names (!*) assigned to the bill-half-Pc variables and to anything that should be associated to "With rsTin" block. It is as if it cannot see it -- what would cause that? The table it is based on is in the same database, and it looks as if I declared it properly. <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=acc&Number=74306&page=& view=&sb=&o=&vc=1>Full code is here</A>, and the snippet, with comments in <font color=red>RED</font color=red> I refer to is:
    <font color=blue>
    With rsTin
    'Populate recordset, store low/high run numbers
    .MoveLast
    lngLast = !Run <font color=red>lngLast shows the right value from rsTin, as does lngFirst, but !Run gives the With block missing error.</font color=red>
    .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] = " & CLng(strInput)

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

    'find the record matching the input
    If MsgBox(strConfirm, 289, "CONFIRM REQUEST") = vbOK Then
    .FindFirst strCriteria<font color=red> 'strCriteria shows accurately, .FindFirst shows nothing</font color=red>
    End If 'confirm msg box?
    <font color=red>the variable below shows the value of the 1st record only, no matter what record it is on when called, or what run number is input. .NoMatch is giving the "with block missing" error message</font color=red>
    If Not .NoMatch Then
    strBill = !Bill_Num
    strHalf = !Bill_Half
    strPc = !PcNum
    </font color=blue>
    Any idea why this may be happening? Also, is it normal for the Dim statements to show values for the variables declared, when code is stopped like this? They are also showing the same values as the ones I mention -- first value of the main table, which brings up its related records!

    Any ideas what might be causing this unusual behavior. The master/child links are fairly straightforward, and I don't see any other code that may be conflicting. All the other controls seem to working as designed. I even enabled the control showing the "Run" and did a find, and it will work as long as the run I am searching for is part of the mainform record displayed. (<font color=red>Run is automatically incremented on a new record, so it is disabled and locked -- could this have anything to do with it?</font color=red>)

    Again, thank you for helping,

    'dave

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts

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

    Dave,
    Is there any chance you can post a zipped, stripped-down copy of your db? (If it's too big, you can email it to me) It may help me to figure out why the code isn't doing what I'd expect it to do!
    Incidentally, in debug the variables will show their assigned values wherever they appear in code. The Dim statements are not assigning them values though.
    Thanks.
    Regards,
    Rory
    Microsoft MVP - Excel.

  3. #3
    melhado
    Guest

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

    Db zipped is too big, so I did e-mail it to you, thank you. You will see, it is not much to strip, just the forms and code take a lot of space.

    Never noticed that the Dim statements in debug showed the values, never had to look this closely at every line either

    Once again, thank you

    'dave

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts

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

    I have received it and will take a look as and when I get time. Will post my answers here as usual.
    Regards,
    Rory
    Microsoft MVP - Excel.

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts

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

    Hi Dave,
    Now that I can see the form it all makes sense! The code was doing exactly what I expected, you simply couldn't see the record in question. I hadn't appreciated that your subform was also showing a one to many relationship and that the code therefore also needed to bookmark the correct record on the subform (I now understand why you were trying to do what you were doing initially!)
    You need something like this:
    <pre>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, rsSub As DAO.Recordset

    'first and last available run numbers, serve as guide
    Dim lngFirst As Long, lngLast As Long
    'store linking fields from rstTin to variables
    Dim strBill As String, strHalf As String, strPc As String
    '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 for SubForm's Run number to input #
    Dim strCriteria As String 'sub
    On Error GoTo Err_cmdFindRun_Click

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

    With rsTin
    'Populate recordset, store low/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] = " & CLng(strInput)

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

    'find the record matching the input
    If MsgBox(strConfirm, 289, "CONFIRM REQUEST") = vbOK Then
    .FindFirst strCriteria
    End If 'confirm msg box?
    If Not .NoMatch Then
    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

    'This seems to be redundant, if .NoMatch is True,
    'the above message appears and sub exits.
    'Else
    'MsgBox "Search Request Cancelled."
    'GoTo Exit_cmdFindRun_Click
    'End If
    End With 'rsTin

    '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 "[Bill_Num] = '" & strBill & "' AND [Bill_Half] = '" _
    & strHalf & "' AND [PcNum] = '" & strPc & "'"
    If Not .NoMatch Then
    Me.Bookmark = .Bookmark
    'if found, set focus to subform and clock # control
    Forms!frmL60Tin!sfTin.SetFocus
    Forms!frmL60Tin!sfTin.Form!txtOp.SetFocus

    Else
    'this message is appearing even when there is such a record.
    'It is coming up as soon as the confirmation message is OK'd.
    'Have tried it with subform focus code before the ELSE
    'and after setting rsMain to nothing -- no difference.
    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
    Set rsSub = Me.sfTin.Form.RecordsetClone
    With rsSub
    .FindFirst strCriteria
    Me.sfTin.Form.Bookmark = .Bookmark
    End With
    Set rsSub = 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
    </pre>

    I will investigate your GetLine60 code issue when I have more time.
    Hope that helps,
    Rory
    Regards,
    Rory
    Microsoft MVP - Excel.

  6. #6
    melhado
    Guest

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

    <hr><font color=d2691e>I hadn't appreciated that your subform was also showing a one to many relationship and that the code therefore also needed to bookmark the correct record on the subform.<hr>
    </font color=d2691e>
    I hadn't thought of that, I thought since it was the subform and Tin was its parent, that they would automatically link. It makes sense as a subform is really just a control, not a form. Thank you for pointing that out.
    <hr><font color=d2691e>I now understand why you were trying to do what you were doing initially!<hr>
    </font color=d2691e>
    Thank goodness someone does. I was beginning to doubt myself <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    <hr><font color=d2691e>Set rsSub = Me.sfTin.Form.RecordsetClone
    With rsSub
    .FindFirst strCriteria
    Me.sfTin.Form.Bookmark = .Bookmark
    End With
    Set rsSub = Nothing<hr>
    </font color=d2691e>
    Before I get started on it, I am not clear on what I am setting the rsSub to. I think I see what you are doing, finding the first "RUN" matching the strInput in the recordset underlying the subform of sfTin, however, sfTin is the control with the form that represents the table recordset, rsTin, so should I be referencing the control, sfCulo, that is the many side of the "Run" relationship -- <font color=blue>"Set rsSub=Me.sfTin.Form.sfCulo.Form.Bookmark"</font color=blue>? Or am I intending to match the table recordset find with that of the form's recordset, which will then bring up its subform's matching record?

    Thanks again for all the help,

    'dave

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts

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

    Dave,
    Just to clarify:
    The original code finds the three part ID for the Run number you input. This then finds one particular record for the main form and an associated set of records (including the one with the correct Run number) for the sfTIn subform. I had originally assumed that your subform showed all these records at once, either as a datasheet or a continuous form. However, since you then have another one to many relationship, as your subform has its own subform, your sfTin subform only shows one 'parent' record at a time. This therefore necessitates finding the correct record on your sfTin subform and moving to it to make it visible. So effectively what you have is:
    1. Main form matching the 3 part ID - only one record.
    2. sfTin subform has many records that match that 3 part ID and you can only see one at a time (because it too has a subform).
    3. The code therefore needs to find the record in the sfTin subform's recordset which has the correct run number. There is only one such record, though it has many records in its own sfCulo subform.
    I hope that makes some sense!?
    Regards,
    Rory
    Microsoft MVP - Excel.

  8. #8
    melhado
    Guest

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

    It makes sense. I was confusing myself on the fact that the table recordset was separate from the sfTin's rs. I never realized that its subform would block showing the record the pointer was on in the table. Never thought to look in the table to see where the pointer was, figuring that opening it would reset it, and having it open wouldn't show me the updates. Appreciate the clarification.
    <hr><font color=d2691e>I had originally assumed that your subform showed all these records at once, either as a datasheet or a continuous form. However, since you then have another one to many relationship, as your subform has its own subform, your sfTin subform only shows one 'parent' record at a time.<hr>
    </font color=d2691e>
    A picture is worth a thousand words. I couldn't have done a datasheet view if I tried. The sfTin is related to the Main form only in that its units are extruded from that unit, yet they are a unique product -- at this stage, its identity changes as do its specs. The need to maintain the relation in the reports and database is in the event there is a problem, traceability back to its parent (and earlier) needs to be maintained. I was able to use a continuous view on sfTin's subform, in that all the repeated testing is on the same unit. It is confusing to explain.

    BT W, it works now as planned. Thank you so much for helping me out on this and explaining how it worked.

    RE: the navigation buttons on the bottom of the sfTin. I am thinking that the new record is always available as I have planted the new Run number on the new record as the Current event, therefor the record is now dirty. Is this an accurate assumption? In the delivered front end, there are no navigation buttons, but my concern was that it may be indicative of another issue.

    As for the skipping I mentioned, I still have to see if that is happening. I see nothing in my cmdGetLine60 code that could cause it to sporadically generate the number/record, and then go to another new record and generate another. Still appreciate if you could look it over, in case I missed something.

    At one point I had Timer code set to refresh the form, so that the data acquisition entries could be read immediately; this was getting in the way of making any field required, as it could not always save without complaining. I have since found a different way to handle that. What may have been happening is that I wasn't filling anything in, so I saw a lot of records that were empty and worried.

    Once again, thanks so much

    'dave

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,896
    Thanks
    0
    Thanked 86 Times in 82 Posts

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

    Hi Dave,
    Your assumption appears to be correct - you're effectively editing the new record as soon as you move to it, so it will always be saved as a new record. You might want to consider populating the record from the cmdGetLine60 code if that should be the only way they can enter a new record?
    I can't see anything wrong with that code from my quick glance at it, certainly nothing that would cause double-entries.
    Hope that helps.
    Regards,
    Rory
    Microsoft MVP - Excel.

  10. #10
    melhado
    Guest

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

    OK, as long as it doesn't indicate something wrong.

    I did extensive testing, and it turns out I wasn't entering data in any of the fields, so it looked as if it was doing double duty. That and the Find Run and next strand buttons are the only way they can navigate, so it works.

    The only odd glitch is that if I try to make a field required, then once the form opens and populates the generated fields, it sporadically gives an error message about not being able to save the record because that field's property is Required. I cannot make it happen, nor does it happen due to any specific sequence -- I think I may be running afoul of some auto-refresh built into Access? Any way to test for that or kill it with code? If it causes problems I can take it out and trust the operator to remember to enter their clock number.

    Rory, Thanks for helping and taking a look at this as well.

    'dave

Posting Permissions

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