Solved Open form to specific record but allow record navigation.

[ddewald]

Hello all. I have a form called "EditVendor" that allows you to edit a vendors details and I have a button called "SaveAndClose" that closes the form and opens up another form and goes to the same record. Heres the code (I know my names are sloppy):

DoCmd.OpenForm "frm_Vendors", , , "[VendorID]=" & Nz(Me![VendorID], 0)

It works great, but it of course filters the opened form and it wont allow you to flip through records in Navigation. I know if I just click the filtered button it will allow it but the people using the form will not. Any work arounds out there?

 

[vba_php]

if you want to go to a specific record when you open a form automatically, why can't you just use the docmd.gotorecord action in code?

 

[ddewald]

Is there a way to build that command into the openform command?

 

[ddewald]

Figured it out. May be a bit sloppy but I put this code into the forms Resize event.

Dim intID As Integer
Dim rs As Object
intID = Me.VendorID
Me.FilterOn = False
Set rs = Me.RecordsetClone
With rs
.FindFirst "[VendorID]=" & intID
Me.Bookmark = .Bookmark
End With
rs.Close

Removed the filter while staying on the same record.

 

[vba_php]

well good. mark this thread as solved then if you would. thanks!

 

[isladogs]

Can I suggest a different approach. Open the form to the full recordset.
Use a combo to select a specific record and set FilterOn=True.
Next to the combo have a button to clear the filter and show all records again

 

[ddewald]

Ah, good idea. I'll give it a go. Thanks!

 

[MajP]

You can also pass in the filer via openargs in the domcd.openform if opened ACDIALOG.
Then in the forms load event

if not trim(me.openargs & "") = "" then
me.recordset.findfirst "SomeField = " & me.openargs 'wrap in single quotes if a string
end if

If not opened acdialog then after the openform simply move to that record.
forms("calledformname").recordset.findfirst "somefield = " & some value 'wrap in single quotes if text.

 

[Pat Hartman]

Just FYI, if your BE is Jet/ACE what you are doing doesn't matter but since almost all of my apps are either designed for SQL Server (or other RDBMS) or get upsized later, I NEVER use form filters since that eliminates any benefit you get from using a "real" RDBMS as your BE and is the primary reason why most people are appalled by how slow their formerly speedy app is when they switch from Jet/ACE to SQL Server.

If you are using SQL Server, let us know and I'll be more specific on how I handle this.