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

melhado · 2001-09-14 06:55

**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 RED I refer to is:

With rsTin
'Populate recordset, store low/high run numbers
.MoveLast
lngLast = !Run lngLast shows the right value from rsTin, as does lngFirst, but !Run gives the With block missing error.
.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 'strCriteria shows accurately, .FindFirst shows nothing
End If 'confirm msg box?
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
If Not .NoMatch Then
strBill = !Bill_Num
strHalf = !Bill_Half
strPc = !PcNum

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. (Run is automatically incremented on a new record, so it is disabled and locked -- could this have anything to do with it?)

Again, thank you for helping,

'dave

**rory** · 2001-09-14 06:58

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.

melhado · 2001-09-14 18:49

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

**rory** · 2001-09-15 08:24

I have received it and will take a look as and when I get time. Will post my answers here as usual.

**rory** · 2001-09-15 09:36

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

melhado · 2001-09-17 12:07

<hr>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>

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>I now understand why you were trying to do what you were doing initially!<hr>

Thank goodness someone does. I was beginning to doubt myself <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

<hr>Set rsSub = Me.sfTin.Form.RecordsetClone
With rsSub
.FindFirst strCriteria
Me.sfTin.Form.Bookmark = .Bookmark
End With
Set rsSub = Nothing<hr>

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 -- "Set rsSub=Me.sfTin.Form.sfCulo.Form.Bookmark"? 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

**rory** · 2001-09-17 12:33

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!?

melhado · 2001-09-17 14:00

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>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>

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

**rory** · 2001-09-17 14:58

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.

melhado · 2001-09-17 19:10

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

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

Thread Tools