Open Form with Join Key (1 Viewer)

[fenhow]

Hi, I have two tables each with a form.
They are set up as a one to many relationshiop

T1 PK is BTID auto number
T2 PK is ACQID auto number and it has BTID number

I joined T1 to T1 on BTID one to many, enforce etc..

Now, I have two forms created from these tables.
I have a button on T1 form that opens T2 form to add one or many records.

The issue I cannot seem to figure out is when I open T2 from T1 the BTID is always blank and I want it to populate with the BTID from T1 so the relate is done.

Can anyone help me?

Thanks.
Fen

 

[Minty]

Why not put form T2 on T1 as a sub form , if you drag it on in design view, it will automatically set up the Parent/Child links for you?

 

[fenhow]

Thanks, I want each form to be a pop up because of the workflow process.
Is what I want possible?
Thanks.
Fen

 

[Minty]

Have a look at the OpenArgs property - it allows you to pass an argument when opening the form.

You can then use
Me.OpenArgs on the newly opened form as a switch, value etc.

 

[fenhow]

ok thanks, so for now, how can I simply open the form and show the records that the BTID already matches?

 

[Minty]

I would check for existing records before opening the form, then set the form up appropriately.
So initially check for existing records, something like;

Dim iRecCount as Long
iRecCount = DCount ("BTID","T2","[BTID] = " & Me.[COLOR="Red"]YourBTIDControl [/COLOR])
IF iRecCount > 0 Then [COLOR="Green"]  'There are underlying records so open form in edit mode filtered to the BTID , still pass the BTID through as the open arg
     [/COLOR]
     DoCmd.OpenForm "frmT2", acNormal, ,"BTID =" & Me.[COLOR="red"]YourBTIDControl_Call_ID [/COLOR], acFormEdit, acWindowNormal,  Me.[COLOR="red"]YourBTIDControl_Call_ID[/COLOR]

ELSE      [COLOR="green"]' No records so open the form in add mode[/COLOR]

     DoCmd.OpenForm "frmT2", acNormal, , , acFormAdd, acWindowNormal,  Me.[COLOR="red"]YourBTIDControl_Call_ID[/COLOR]
END IF

Replace the red bit with your control name.

In the on load event of the frmT2 add

Me.[COLOR="red"]YourBTIDControl_Call_ID[/COLOR].DefaultValue = Me.Openargs

 

[fenhow]

Wow, that seems to work better than perfect! I cannot thank you enough!
Thank you, thank you , thank you.

Fen