Close form open another pointing to same record

[Dirt Bike]

I have 2 forms with charts looking at different things like sales for one and
profit margin for the other. I want to close the one form and open the other
with a record pointer that carries the record with it. In other words, when
I point to a specific client in a profit $$ view I want to mash a button and
close that form and open the form with the margin graphed pointing to the
same client (dance with what brung me).
I am very much a VBA newbie and need alot of hand holding with regards to
VBA coding.....obviously, otherwise I would know the answer to the question I
am asking...
Thank you very much for the help

 

[fredg]

I have 2 forms with charts looking at different things like sales for one and
profit margin for the other. I want to close the one form and open the other
with a record pointer that carries the record with it. In other words, when
I point to a specific client in a profit $$ view I want to mash a button and
close that form and open the form with the margin graphed pointing to the
same client (dance with what brung me).
I am very much a VBA newbie and need alot of hand holding with regards to
VBA coding.....obviously, otherwise I would know the answer to the question I
am asking...
Thank you very much for the help

Look at the arguments in the OpenForm method in VBA help.
The 4th argument is a Where clause (without the word Where).
Also look up, in VBA help:
Restrict data to a subset of records

DoCmd.OpenForm "Form2", , , "[RecordID] = " & Me.[RecordID]
DoCmd.Close acForm, "Form1"

The above assumes [RecordID] is a Number datatype.

 

[Dirt Bike]

Look at the arguments in the OpenForm method in VBA help.
The 4th argument is a Where clause (without the word Where).
Also look up, in VBA help:
Restrict data to a subset of records

DoCmd.OpenForm "Form2", , , "[RecordID] = " & Me.[RecordID]
DoCmd.Close acForm, "Form1"

The above assumes [RecordID] is a Number datatype.

Fred Thank you for your help!!

No joy however...

When I create a button to test and put:
Private Sub Command31_Click()

On Error GoTo Err_Command31_Click

Dim RecordID As Single
DoCmd.OpenForm "CM3", , , "[RecordID] = " & Me.[RecordID]
Exit_Command31_Click:
Exit Sub
Err_Command31_Click:
MsgBox Err.Description
Resume Exit_Command31_Click

I get "MS Access cannot find the field "|" referenced in your expression.

I examined the help screens your refered me to but my level of VBA is so low
that it wasn't much help.

 

[Albert D. Kallal]

Dim RecordID As Single < this is not needed at all....


DoCmd.OpenForm "CM3", , , "[RecordID] = " & Me.[RecordID]

In the above:

[ReocrdID] =

RecordID must be a legitimate column (field) in the TARGET forms datasource.

The default in ms-access for the primary key is usually "ID". So, replace
ReocrdID in the above with the column name of the primary key used in your
table.


Me.[RecordID]

Replace the above with the name of the current text box, or field name that
holds the primary key (id) of the current record).

You only need the one line of code to open the 2nd form. You don't need to
declare that variable RecordID. This recordid value is to come from the
forms data source.

DoCmd.OpenForm "CM3", , , "[RecordID] = " & Me.[RecordID]

If the form allows editing of data BEFORE you launch the 2nd form, then you
need to force a disk write..

Thus, you need:

if me.Dirty = true then me.Dirty = false

DoCmd.OpenForm "CM3", , , "[RecordID] = " & Me.[RecordID]

the me.Dirty = false forces a disk write of the forms data. As mentioned,
you not need to do this unless the form allows editing of data...