Open a form, Go to specific record without filtering the form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

frenchie70 - 28 Feb 2008 22:49 GMT

I have a continuous form that i use to filter particular records on a unique
ID (FNID). On this continuous form i have a button that opens another form
allowing the user to edit the specific record. The second form opens and is
filtered on the selected record using the following code:
On Error GoTo Err_cmdHistory_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frm_Footnote"
stLinkCriteria = "[FNID]=" & Me![FNID]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_cmdHistory_Click:
Exit Sub
Err_cmdHistory_Click:
MsgBox Err.Description
Resume Exit_cmdHistory_Click
End sub

Can i essentially do the same thing, but have the form go to the selected
record and still have all of the other records available for searching? It's
like I need a combination of the GoToRecord and FindRecord functions, but
cannot figure out how to do this. I'm fairly new to database development and
have learned a lot by looking at these community boards. Any help would be
greatly appreciated.

Reply to this Message

fredg - 29 Feb 2008 00:32 GMT

> I have a continuous form that i use to filter particular records on a unique
> ID (FNID). On this continuous form i have a button that opens another form
[quoted text clipped - 19 lines]
> have learned a lot by looking at these community boards. Any help would be
> greatly appreciated.

Pass the current [FNID] to the newly opened form using the OpenArgs
argument:

DoCmd.OpenForm "frm_Footnote", , , , , , Me![FNID]

Then in the "frm_Footnote" Load event:

If Not IsNull(Me.OpenArgs) Then
Me![FNID].SetFocus
DoCmd.FindRecord Me.OpenArgs, acEntire, , acSearchAll, , acCurrent
End If

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

frenchie70 - 29 Feb 2008 14:35 GMT

Thank you for the quick response. I've changed my code to reflect your
suggestions, but I may have missed something because it still opens the form
to the first record and not the desired record or the one the old code would
filter directly to. Incidentally, I was referencing the wrong control or
field and have corrected it (FNNum). The correct field is an indexed and
unique text field and i have adjusted the code to reflect that as well.
Here's what i have:

On the frm_footnote Load event

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.FNNum.SetFocus
DoCmd.FindRecord Me.OpenArgs, acEntire, , acSearchAll, , acCurrent
End If
End Sub

On Error GoTo Err_cmdHistory_Click

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frm_Footnote"
stLinkCriteria = "[FNNum] = '" & Me.FNNum & "'"
DoCmd.OpenForm stDocName, , , , , , stLinkCriteria
Exit_cmdHistory_Click:
Exit Sub
Err_cmdHistory_Click:
MsgBox Err.Description
Resume Exit_cmdHistory_Click
End Sub

Do i need to change any other settings to get this to work? should i have
something in the FindFirst option of the DoCmd.FindRecord? Thanks again.

> > I have a continuous form that i use to filter particular records on a unique
> > ID (FNID). On this continuous form i have a button that opens another form
[quoted text clipped - 31 lines]
> DoCmd.FindRecord Me.OpenArgs, acEntire, , acSearchAll, , acCurrent
> End If

Reply to this Message

fredg - 29 Feb 2008 17:10 GMT

> Thank you for the quick response. I've changed my code to reflect your
> suggestions, but I may have missed something because it still opens the form
[quoted text clipped - 65 lines]
>> DoCmd.FindRecord Me.OpenArgs, acEntire, , acSearchAll, , acCurrent
>> End If

You seemed to have somehow combined 2 different procedures.

Code the first Form's event that you use to open the second form:

Private Sub cmdHistory_Click()
On Error GoTo Err_cmdHistory_Click

Dim stDocName As String
stDocName = "frm_Footnote"
DoCmd.OpenForm stDocName, , , , , , Me![FNNum]
Exit_cmdHistory_Click:
Exit Sub
Err_cmdHistory_Click:
MsgBox Err.Description
Resume Exit_cmdHistory_Click
End Sub

Do not add to or change the above code except to change the name of
the event if I have that wrong.

Code the second Form's Load event:

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
Me.FNNum.SetFocus
DoCmd.FindRecord Me.OpenArgs, acEntire, , acSearchAll, , acCurrent
End If
End Sub

Then your code in the second form's Load event should work just fine.
It works for me.

The above assumes that [FNNum] contains a unique value that will
identify the correct record.

Signature

Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Reply to this Message

frenchie70 - 29 Feb 2008 17:45 GMT

That did it. Thank you for your patience and help.

> > Thank you for the quick response. I've changed my code to reflect your
> > suggestions, but I may have missed something because it still opens the form
[quoted text clipped - 100 lines]
> The above assumes that [FNNum] contains a unique value that will
> identify the correct record.

Reply to this Message