Using a variable to change the record displayed in a form (1 Viewer)

[atrium]

My project opens with a parent form (with the first order summary details) and two subforms. The first subform shows the items ordered for the parent form Order. The second subform is a list of variables used to search the Orders table in the parent form.
(e.g. to search for a Order Number - The user enters a know Order Number and hit enter. The desired effect is the parent form and the first subform to return the order details for order number entered.
I don't want to use a query to extract only that record. I want all records still available (e.g. I might have 500 Orders and the one the user is search for is the 323 one.

I'm after just a quick way to skip to different records in the 500. I have string and date variables.

I'm sure there is a simple way to do this - can anyone help please.

 

[Cronk]

You say your second subform allows you to input a particular order number and set the parent form to display that.

How else do you want to "skip to different records in the 500"? I assume you want to apply some selection behavior. On what criteria? Possibilities would be order date or the entity placing the order. These could be controls on your second subform and provide a subset of those orders satisfying the criteria.

Or is there something I'm not understanding?

 

[atrium]

The second subform has eight fields that I can enter data and then on the On After Update event it goes to a Private sub

Private Sub SearchOrderNumber_AfterUpdate()

Dim FindOrderNumber As Long
FindOrderNumber = Val(Me.SearchOrderNumber)
DoCmd.GoToRecord acDataForm, "OrdersFrm", acGoTo, "[OrderNumber] = " & FindOrderNumber

The FindOrderNumber = 48103 (As I entered it)
acDataForm = 2
acGoTo = 4

The error I get (on the red shaded line) is Run Time error 2498
"An expression you entered is the wrong data type for one of the arguments."

I looked up the error messages but got no joy from a dozen so call solutions

 

[arnelgp]

You are using the Wrong command.
use DoCmd.FindRecord (provided that the Field you are searching is Visible on the Form).

 

[atrium]

I tried FindRecord and now I get a data type mismatch - Runt Time error 13

 

[arnelgp]

you must SetFocus on the Field/textbox you want to FindRecord:

Private Sub SearchOrderNumber_AfterUpdate()

Dim FindOrderNumber As Long
FindOrderNumber = Val(Me.SearchOrderNumber)
[Forms]![OrdersFrm].SetFocus
[Forms]![OrdersFrm]![OrderNumber].SetFocus
DoCmd.FindRecord FindOrderNumber

 

[atrium]

I Have tried the above and it comes back with

RT Error 2110 The system can't move the focus to the control OrderNumberFld

I then commented out the control setfocus lines and got

RT error 2046 The command or action 'FindRecord' isn't available now

 

[arnelgp]

if you will Google the Error number, you will understand what it means.
what is the Name of the Order Number textbox on OrdersFrm form?

 

[atrium]

OrderNumberFld

 

[arnelgp]

try this:

Private Sub SearchOrderNumber_AfterUpdate()

Dim FindOrderNumber As Long
FindOrderNumber = Val(Me.SearchOrderNumber)
[Forms]![OrdersFrm].SetFocus
[Forms]![OrdersFrm]![OrderNumberFld].SetFocus
DoCmd.FindRecord FindOrderNumber

 

[Pat Hartman]

I don't want to use a query to extract only that record. I want all records still available

You can only work with one record at a time, why not use the most efficient method? If your BE is Jet/ACE, it doesn't much matter but if you BE is SQL Server or you might move to SQL Server or some other RDBMS in the future, you are not taking advantage of making the server do the heavy lifting and are using poor client/server techniques. Most of my apps use SQL Server, DB2, Oracle, Sybase, etc as the BE. essentially, whatever RDBMS the client uses for his other applications. But even for the apps I build for ACE that will probably never be converted, I still use good client/server techniques. They don't hurt when the BE is Jet/ACE and they really help when the BE is an RDBMS. As a result, I can convert pretty much any application I build from ACE to SQL Server in an hour or two depending on how much testing I need to do. And this is possible only because I plan ahead.

If you know you are never going to have to convert the app, do whatever you want. If you think you might need to convert, use good client/server techniques from the get go.