Opening form at at a specific record

[momon]

How would i do this.

I have a form with a select query as the record source.
Each record in the query is differentiated from the other records by the

AND [ITEM NUMBER]

The

AND [ITEM NUMBER] are entered by the user at the main switchboard. The user clicks to open a certain form which has a query as the record source.

The criteria for the query is the

. So the form opens with a given

at [ITEM NUMBER] = 1.
There are many [ITEM NUMBER]s corresponding to each

.

Now my question is: How would I open the from at a specific [ITEM NUMBER]?

If I specify the query to look for both the

AND [ITEM NUMBER], I get one specific record. This is NOT what i want.

-----------------

For example,

= 0001 has 4 items.
The user enters at the main switch board,

= 0001 and [ITEM NUMBER] = 3.

I want to open the form at

= 0001 and [ITEM NUMBER] = 3. Meanwhile, the user can still press the previous record button to go back to [ITEM NUMBER] = 2 or press the next record button to go forward to [ITEM NUMBER] = 4



Thanks

 

[jdegeorge]

You should base the form on a query pointing to the

from the main switchboard. In the query,

the field critiera should be

[forms]![SWITCHBOARD FORM NAME]!

On the same CRITERIA LINE, under [ITEM NUMBER] enter "3"

Then, on a 2nd CRITERIA LINE for

re-enter

[forms]![SWITCHBOARD FORM NAME]!

What this does is give you a query with ITEM #3 at the top and then all other items for this QUOTE.

Hope this helps.

Jim "Get it right the first time, that's the main thing..."

 

[momon]

hmm... for some reason the post above doesn't seem to work.

I have in the query, [forms]![SWITCHBOARD FORM NAME]!

under the

field.

However, I want the form to display a certain [ITEM NUMBER] for the given

. There are multiple [ITEM NUMBER]s corresponding to each

.
-----
For example,

= 0001 has 4 items.
The user enters at the main switch board,

= 0001 and [ITEM NUMBER] = 3 (arbitrarily selected, it could have equally well been 1, 2, or 4).

I want to open the form at

= 0001 and [ITEM NUMBER] = 3 (corresponding to the [ITEM NUMBER] specified in the switchboard by the user). Meanwhile, the user can still press the previous record button to go back to [ITEM NUMBER] = 2 or press the next record button to go forward to [ITEM NUMBER] = 4

NOTE:
I do NOT want to specify a criteria for both

and [ITEM NUMBER] since this will produce one record, and not enable the user to scroll to the other [ITEM NUMBER]s for a given quote number.

 

[jdegeorge]

If you set 2 lines of criteria as suggested, you will get what you're looking for.

Respond to this by pasting in the actual SQL from your query, and give me the basic table layout. I'll play around with it for you and send it back.

Jim "Get it right the first time, that's the main thing..."

 

[momon]

thanks for your help jim.

ok here is the table layout... i'll try to explain things the best i can.

there are two tables in the upper query window.
They are: ORDER and WSPECS
Between these two tables there are 'joins' between:
1)

in the ORDER table and

in the WSPECS table
2) [ITEM NUMBER] in the ORDER table and [ITEM NUMBER] in the WSPECS table

In the main switchboard, the two text fields,

AND [ITEM NUMBER] are bound to the WSPECS table.

-------

so basically here is what my query screen looks like
______________ ______________
|ORDER | | WSPECS
|QUOTE NUMBER |------ |QUOTE NUMBER
|ITEM NUMBER |-------| ITEm NUMBER
|... etc | |... etc
--------------- ---------------

----------------------------------------------------------

Field QUOTE NUMBER ITEM NUMBER
Table ORDER ORDER
Sort
Show yes yes
Criteria [Forms]![Start]!

Or

NOTE: Start is the name of the switchboard form.

I also tried this suggested by JIm

Field QUOTE NUMBER ITEM NUMBER
Table ORDER ORDER
Sort
Show yes yes
Criteria [Forms]![Start]!

3
OR [Forms]![Start]!

but it doesn't seem to work...

also... i tried this

Field QUOTE NUMBER ITEM NUMBER
Table ORDER ORDER
Sort
Show yes yes
Criteria [Forms]![Start]!

[Forms]![Start]![ITEM NUMBER]
OR [Forms]![Start]!

 

[jdegeorge]

Having your criteria for

be [Forms]![Start]!

and for [ITEM NUMBER] be [Forms]![Start]![ITEM NUMBER] gets you only the records where both numbers equal the iput items. You know this already.

After going crazy with this for the past 2 hours, I'm almost positive that your query needs only 1 criteria for

= [Forms]![Start]!

. This leaves the problem of getting your form to display the exact ITEM number, which involves some "sort" or "record pointing" code that I don't know.

I'm sure that one of the wizards who frequent this site will be able to help you much better than I can.

I'm at a loss. Sorry to have wasted your time and to have gotten your hopes up.

I flagged this for email notification because if this is ever solved, I'd really like to know the solution.

Good luck!

Jim :-(
"Get it right the first time, that's the main thing..."

 

[momon]

hey man... need not be sorry... you were generous enough to try and listen to my problem... and i am greatly thankful...


i'll tell you if it ever gets resolved...
thanks again for your help!