Showing single record from a subform (1 Viewer)

rlowden

rlowden
Joined
Aug 28, 2008
Messages
13
I am an amateur with no Visual Basic training, but I'm trying to learn. Help appreciated.

I have a standard form/subform with supporting tables in one-many relationship. The subform shows only name data for individuals from its supporting table (called tblMInfo); but I have another form based on that same table that shows additional information about each individual

I would like to insert a control on the subform record that when activated saves the current record and opens the other form (with the additional information), butshowing only the current record.

Not knowing Visual Basic I was able to create a Macro that opens the form with additional information, but I don't know how to filter it so that I get only the current record. It would also be nice to have the current record saved before the form with the additional information is opened.
 

Mike375

Registered User
Joined
Aug 28, 2008
Messages
2,545
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "CopySPPI", acNormal, "", "[NameNumber]=[Forms]![MasterForm]![SPPI]![NameNumber] And [RegPremium]=[Forms]![MasterForm]![SPPI]![RegPremium]", , acNormal

This is one of mine save as code from a macro. CopySPPI is the form being opened and SPPI is the subform. In a macro the first line is RunCommand and SaveRecord at the bottom of the macro screen. The Where for the OpenForm is NameNumber]=[Forms]![MasterForm]![SPPI]![NameNumber] And [RegPremium]=[Forms]![MasterForm]![SPPI]![RegPremium]",

In other word it is matching two fields. If I just used NameNumber then of course all records in the subform would be shown. Put the code pr macro as OnClick on one of the textboxes of the subform.

Another way to do this is to join two fields in a calculated field in a query.

NewFieldName:[Field1] & "" & [Field2] and then use that field for your Where.
 

rlowden

rlowden
Joined
Aug 28, 2008
Messages
13
To Mike357:

Thanks for your reply, but I just am not sure of the syntax in that "Where" statement. Assuming I use a macro, I just want to open a related form (with additional information) so that it points ONLY to the record I am currently viewing in a subform. If it helps to describe the syntax:

The subform is named frmMIInfo
The form I wish to open is frmMIInfoAdditional
They are both based on a table tblMIInfo
The indexing field the two forms share is MINumber

RE: the other issue - Thanks for the RunCommand-SaveRecord solution in the macro. Works great.

Many thanks for all help
 

Mike375

Registered User
Joined
Aug 28, 2008
Messages
2,545
In your Macro have OpenForm for the action line. At the bottom of the screen you will see the line for form name and so put the name of the form you wish to open.

Then below that you will see Where Condition.

Enter on the Where Condition line

[FirstFieldName of FormtoOpen]=[Forms]![MainFormName]![SubformName]![FirstMatching FieldName of Subform] And [SecondFieldName of FormtoOpen]=[Forms]![MainFormName]![SubformName]![SecondMatching FieldName of Subform]

If you only use an ID field for the Where Condition then you will get all records that match the selected record on the subform. So you need to select on at least two fields so that there is only one record.

Alternatively, as I said in the first post you can make a single field by joining two or more fields and then use that new field for the Where Condition. Another way would be to add an autonumber field to the table that supports the subform and form you wish to open.
 

rlowden

rlowden
Joined
Aug 28, 2008
Messages
13
Mike375,

Thanks again for your reply. I tried it with the correct syntax you provided, and yes. It now works. Beautiful.

You should know that my problem was not that I needed additional fields on which to select a unique record (the number I was using to match the forms was unique). The problem was that I did not know how to write the correct syntax. Your answer provided that, it turned out to be:

[MINumber]=[Forms]![frmMPInfo]![frmMIInfo]![MINumber]


Where MINumber is the unique identifier, frmMPInfo the parent form, and frmMIInfo the subform. I hadn't realized that I needed to include the parent form name in the argument. That is what solved the problem.

With Gratitude,
Bob
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom