Convert form to subform

[Ray Spackman]

Is it possible to change the slection type of a form from form to subform?

Having a problem passing the unique value of the first form to the second form when the relations are set properly so I was trying to make the second (unconnected, but related) form a subfrom and edit its linked fields.

 

[Galaxiom]

There is no intrinsic difference between a form and a subform. Simply place a subformcontrol onto a form and set the SourceObject Property of that control as the form you want it to display.

 

[Ray Spackman]

I was trying to prevent that and having to rereference my VBA coding. If that is what I must do, then so be it but take a look at the folllwoing info and tell me what you think please?

UPDATE: By chance that my original post was unclear, this is what is happening: I have 2 tables, 2 queries, and 2 forms. Each query is based on each table and each form is based on each query. Each form is set two popup and the second form pops up from a command button on the first form. So this is how it looks:

Table 1 Info:
Name: tblBowlerInformation
Fieldrimary Key: BowlerInfoID DataType:AutoNumber
Field:FirstName DataType:Text
Field:LastName DataType:Text

Table 2 Info:
Name:tblBowlerSpecs
Field:BowlerInfoID DataType:Number
FieldrimaryKey:BowlerSpecID DataType:Autonumber
Field:SpecDate DataTypeate/Time
Field:Rating DataType:Text

Relationship Info:
tblBowlerInformation.BowlerInfoID to tblBowlerSpecs.BowlerInfoID
One-to-Many Reinforced Integrity

Query 1 Info:
Name:qryBowlerInformation
Table:tblBowlerInformation
Field:BowlerInfoID
Field:FirstName
Field:LastName

Query 2 Info:
Name:qryBowlerSpecs
Field:BowlerinfoID
Field:BowlerSpecID
Field:SpecDate
Field:Rating

Form 1 Info:
Name:frmBowlerInformation
Control:BowlerInfoID
Control:FirstName
Control:LastName
Command:cmdOpenBowlerSpecs
VBA CODErivate Sub cmdBowlerSpecs_Click()
DoCmd.OpenForm "frmBowlerSpecs", , , "[BowlerInfoID] = " & Me.[BowlerInfoID]
End Sub

Form 2 Info:
Name:frmBowlerSpecs
Control:SpecDate
Control:Rating

When the command (cmdOpenBowlerSpec) is clicked, the correct form pops up and am able to input data.

After closing both forms and reopening frmBowlerInformation and the clicking the command button, the data that was just entered is gone. (It is not set to go to new record).

After opening the the table tble BowlerInformation and clicking on the + sign, it does not show the frmBowlerSpecs data related to that record.

However, when opening the table tblBowlerSpecs, the data is there but there is not any value in the related BowlerInfoId field which prevents it from showing in the form.

What are some factors that can be causing this. First time I have ever had this problem and have more tales, queries, and forms set up in the same manner that are doing the same thing.

As always, all help is appreciated and my thanks and appreciation in advance.

 

[Galaxiom]

The Conventional Sophisticated Coded Way:

Use the OpenArgs of the OpenForm command to pass the value you want entered in the related field to the new form. This value will become the OpenArgs property (as a string) of the called form.

In the BeforeInsert Event Procedure of the called form write the value from the OpenArgs property to the related field.

Further code is required if the form is ever opened stand alone to check the OpenArgs to ensure it isn't a Null String or this step can fail.

The Code-Free Alternative:

Put the called form as a subform on another (optionally) otherwise empty, unbound holding form. If desired, the selectors and other extraneous stuff can be removed from the holding form so it is just a blank form with a subform and looks completely normal.

Enter the full reference (via the Forms collection) to the related field on the calling form as the LinkMasterField of the subformcontrol and the related field on the subform as the LinkChildField.

When the holding form is opened its subform will load as though it is a subform of the calling form.

The only difference is that the subform will not immediately track the Current Record on the calling form as it would if it was on the calling form. However this is not an issue when you just need a popup entry or edit form based on the current record of the calling form.

If you do need the subform to track the movements on the calling form without it being closed it can be resynchronised by requerying the holding form or subform.

Alternatively place a textbox on the holding form and use it as the LinkMasterField of the subform. Enter its ControlSource as the field on the calling form. Resynchronise by requerying just the textbox.

This is technique useful if do not wish to requery the subform because you want the subform to behave as a Snapshot without including any new related records that may have been entered by other users after the form was opened.

The subform object itself can be reused as a standalone form (open it directly) or as a linked form to any of numerous other forms by opening it as a subform inside a different holder with the appropriate LinkFields.

 

[Ray Spackman]

Very helpful Glaxiom. The "Sphisticated Coded Way" sounds more like the route I would need to take. A couple questions though, In specifing the OpenArg for the form open event, as in the code I already have: DoCmd.OpenForm "frmBowlerSpecs", , , "[BowlerInfoID] = " & Me.[BowlerInfoID], don't I already have that Arg ("[BowlerInfoID] = " & Me.[BowlerInfoID]) or am I missing something. And, after I get the OpenArg, are saying that the same Arg needs to be placed in the BeforeInsert event of the form called. Lastly, the form called will be opening as a standalone, so can you give me an example of the code that will be needed to ensure the above steps work correctly?

 

[Galaxiom]

You have the WhereClause. This limits the RecordSource to those existing records which meet that criteria but has no effect on new records.

The OpenArgs argument of OpenForm is a Variant datatype. Whatever is placed in there is passed to the called form, appearing as the OpenArgs Property of the form. It can be used to pass just about anything with the limitation that it will become a string in the OpenArgs Property.

So refer to the field in the Current Record that you want to be passed to the form.

DoCmd.OpenForm "frmBowlerSpecs", , , "[BowlerInfoID] = " & Me.[BowlerInfoID], acDialog, Me.BowlerInfoID

(acDialog makes it a popup modal form forcing the focus to be locked to it until closed)

Then to get this value to appear in the new record, specify this in the BeforeInsert Event Procedure.

Me.BowlerInforID = Me.OpenArgs

If you want to be able to open the same form independently and enter the ID by some other means this will prevent an error that would be thrown if the OpenArgs Property was not present:

If Not IsNull(Me.OpenArgs) Then Me.BowlerInforID = Me.OpenArgs