open form to specific record number

  • Thread starter Thread starter Joel
  • Start date Start date
J

Joel

TIA:

I have form1 based on a recordset. I have a button on this form that opens
another form (form2 based on the same recordset). Is there a way to have
form2 open to the same record in form1 when the button is clicked.

something like:

set recordset = current recordset
recnum=recordset.recordnumber
docmd.open form "form2", where recordset.recordnumber=recnum

Help with any details is appreciated.
thanks, Joel
 
TIA:

I have form1 based on a recordset. I have a button on this form that opens
another form (form2 based on the same recordset). Is there a way to have
form2 open to the same record in form1 when the button is clicked.

something like:

set recordset = current recordset
recnum=recordset.recordnumber
docmd.open form "form2", where recordset.recordnumber=recnum

Help with any details is appreciated.
thanks, Joel
Click to expand...

If you look at VBA help on the OpenForm method you will see that the
method has several arguments, one of which is a Where argument.

DoCmd.OpenForm "Form2", , , "[RecordID] = " & Me.[RecordID on form]

The above assumes the RecordID field in the table is a Number
datatype.

In addition to looking in VBA help on the OpenForm method, also look
up Where Clause + Restrict data to a subset of records.
 
fredg said:
TIA:

I have form1 based on a recordset. I have a button on this form
that opens another form (form2 based on the same recordset). Is
there a way to have form2 open to the same record in form1 when the
button is clicked.

something like:

set recordset = current recordset
recnum=recordset.recordnumber
docmd.open form "form2", where recordset.recordnumber=recnum

Help with any details is appreciated.
thanks, Joel
Click to expand...

If you look at VBA help on the OpenForm method you will see that the
method has several arguments, one of which is a Where argument.

DoCmd.OpenForm "Form2", , , "[RecordID] = " & Me.[RecordID on form]

The above assumes the RecordID field in the table is a Number
datatype.

In addition to looking in VBA help on the OpenForm method, also look
up Where Clause + Restrict data to a subset of records.
Click to expand...

Note also that a record number is meaningless to ACCESS. It is only a
temporary number and depends on the current sort order and nuumber of
records in a query.

I'm not sure why you need to open a second form with the same information
but a tabbed form would make this a lot easier,
 
Thanks Mike and Fred:

Here's the issue...I am working on a huge database that some else developed.
To Fred: Unfortunately there is no unique id in the recordset so the where
clause will not work.

To Mike: I agree a tabed form would be the best But there are already 14
tabs in the form i am working with. The reason for the second form is to
show additional data and calculations which just won't fit on the current
form and adding 14 more tabs is not what the user wants...so I created a copy
of the forms and have a button to click to open the form that has additional
data with calculations.

So I have what I have. Is there a way to set a bookmark for the current
record in form1 so that when form2 is opened (based on the same recordset) it
opens to the bookmark??

Thanks, Joel

Mike Painter said:
fredg said:
TIA:

I have form1 based on a recordset. I have a button on this form
that opens another form (form2 based on the same recordset). Is
there a way to have form2 open to the same record in form1 when the
button is clicked.

something like:

set recordset = current recordset
recnum=recordset.recordnumber
docmd.open form "form2", where recordset.recordnumber=recnum

Help with any details is appreciated.
thanks, Joel
Click to expand...

If you look at VBA help on the OpenForm method you will see that the
method has several arguments, one of which is a Where argument.

DoCmd.OpenForm "Form2", , , "[RecordID] = " & Me.[RecordID on form]

The above assumes the RecordID field in the table is a Number
datatype.

In addition to looking in VBA help on the OpenForm method, also look
up Where Clause + Restrict data to a subset of records.
Click to expand...

Note also that a record number is meaningless to ACCESS. It is only a
temporary number and depends on the current sort order and nuumber of
records in a query.

I'm not sure why you need to open a second form with the same information
but a tabbed form would make this a lot easier,
Click to expand...
 
Joel said:
Thanks Mike and Fred:

Here's the issue...I am working on a huge database that some else
developed. To Fred: Unfortunately there is no unique id in the
recordset so the where clause will not work.

To Mike: I agree a tabed form would be the best But there are
already 14 tabs in the form i am working with. The reason for the
second form is to show additional data and calculations which just
won't fit on the current form and adding 14 more tabs is not what the
user wants...so I created a copy of the forms and have a button to
click to open the form that has additional data with calculations.

So I have what I have. Is there a way to set a bookmark for the
current record in form1 so that when form2 is opened (based on the
same recordset) it opens to the bookmark??
Click to expand...

There must be something unique to identify the record. Adding a key field
would be the best solution.

Note that if you have 14 tabs and still can't show all of a record, the
chances are that this is a badly designed database and should be split into
several tables.
There is a wizard that will examine this although if you have fields that
hold similar items (part 1, part 2, part 3,...) it is a dead giveaway.
If you are working for somebody and just took this over, now is the time to
tell them that, in writing.
 
Thanks Mike:

I have realized all along that with a unique a key things would be
"easy"...I was just wondering about other possibilities through code as I
have asked about. such as the bookmark approach.

Thanks for your help.

Joel
 
Back
Top