Continuous Forms (Access 2007): Select Specific Record 2

[kjv1611]

This is at least somewhat related to:
thread702-1553189

However, it's not exactly the same question, so I started a new thread.

I thought it would be something fairly simple. I want to select a specific record in the Form's recordset, and I have a specific reason for doing so.

One of the things I'm doing in the form with one particular button (same form as the other thread) is highlighting and "approving" - setting a checkbox field value to True/Yes or unchecking the same checkbox.

In that process, if there are enough records so that the form has to be scrolled to see them all, if the user were to scroll to the bottom, and "approve" a record, currently, the form's focus shoots to the top of the form.

Therefore, I wanted to add in a line of code (or however many lines required) to select the same record that was just approved.

To do so, I took a note from this thread:

http://www.tek-tips.com/viewthread.cfm?qid=1392955

And put a text box in the header, and am setting the value in that box to the ID field for the "approved" record, with the idea that I could then set the focus on or select that given record after the Form.Requery command. But no, for some reason, every thing I've tried causes that portion to error out.

Here's what I have so far:

Private Sub cmdApprove_Click()

    DoCmd.RunCommand acCmdSelectRecord
    txtLast = Me.ImportID
    If txtFileName = vbNullString Then Exit Sub
    If Me.Approved = True Then
        Me.Approved = False
    Else
        Me.Approved = True
    End If
    Form.Requery
    
'And below are the different methods I've tried - basically trying the same thing, but trying different options, none of which seem to work - at least for me in this instance.

'The line, "txtLast.SetFocus", is to set focus to the comparable text box first, b/c one of the error messages was telling me I couldn't refer to an object that doesn't have focus...

'    txtLast.SetFocus
    
'    DoCmd.RunCommand acCmdSelectRecord
'    Forms!frmRandom!ImportID.SetFocus
    'Forms!Files!FileID.SetFocus
'    DoCmd.FindRecord txtLast, acAnywhere, , , , acAll
    'DoCmd.FindRecord txtLast, acAnywhere, False, acSearchAll, , acAll, True
    'DoCmd.FindRecord txtLast.Text, acAnywhere, , acSearchAll, , acCurrent
    'DoCmd.FindRecord txtLast
End Sub

If anyone looks at this, can follow it at all, and make a suggestion, I'm all ears.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

Why requery the form when you update a bound field ????

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

That's how I'm getting the form to show the "highlight" or "unhighlight" correctly.

I've got conditional formatting on the text boxes for each record. The conditional formatting looks at a flag, labeled "approved" which is a yes/no field to determine whether to add the highlighting (fill color) to the text boxes.

So, this is what happens:
[ol][li]User clicks "approve"[/li]
[li]"approved" value in the table is changed to True/"Yes"[/li]
[li]Conditional formatting only sees the change (at least, so far) if the form is re-queried.[/li]
[li]Conditional formatting highlights the now "approved" record's text boxes.[/li]
[li]If the record was already "approved", then it now is de-highlighted after beign "unapproved."[/li]
[/ol]

The reason I did it this way, was b/c I saw the references to using a text box in the header for the record ID selected, but that doesn't really do what I need, since I could have more than one record "approved" at a single time.

I hope that explanation helps a little in understanding my probably insane logic.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

What happens if you replace this:
Form.Requery
with this ?
Me.Dirty = False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Doesn't change anything. Of course, the table is still updated, but the form doesn't change - so I cannot see the change taking place via the conditional formatting.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

And this ?
Me.Recalc

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

That did it!

Thanks, PHV!

Now it works correctly - without the Form.Requery, and thus not necessitating using the extra text box to attenpt to reselect the correct record!

I'll have to read up on how ReCalc works in comparrison with the others.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

And for the record, in case anyone reads this in the future and skips over something. The part that worked was:

[blue]Me.Recalc[/blue]

Which could also be typed:

[blue]Form.Recalc[/blue]

As far as I know.

--

"If to err is human, then I must be some kind of human!" -Me

 

[GKChesterton]

Star! Star for [tt][purple]recalc![/purple] [/tt] -- a function that is inadequately referenced in MS Access 2003 help files, and gets no mention in my 3 Wrox books. I think I've even posted problems here on TT for which this was the answer, though I didn't learn it that way. (Mind you, my presentation might be at fault.)

[purple]If we knew what it was we were doing, it would not be called
research [blue]database development[/blue], would it? [tab]-- Albert Einstein[/purple]​