Linking forms - Oh GOD please help!

[Emma]

I have tried a couple of methods to get these forms linked together. I
finally got the subform to be updateable but I still can't seem to get it
linked to my main form via the Job_ID. I have a presentation on this due
tomorrow. . . . God help me!!

This is the code I have on my main form option button that is supposed to
open one of two subforms (thus far). In case 1: I tried to link via a bound
text box. In case 2 I tried to link via the openArgs. Neither of them works.

Select Case Form_Tbl_Master_Jobs!Frame_Contract_Type
MyOpenArgs=Me!cbo_Account_No&"-"&Me!Job_ID
Case 1
DoCmd.OpenForm "frm_tbl_contracts_Int", acNormal, , Me!Job_ID =
frm_tbl_Contracts_Int.txt_Job_ID, acFormAdd, acWindowNormal
Case 2
DoCmd.OpenForm "frm_tbl_contracts_Biopsy", acNormal, , , acFormEdit,
acWindowNormal, MyOpenArgs
Case Else
DoCmd.GoToControl "comments"
End Select
End Sub


In Case 1 I get an "Object required" error. What am I doing wrong?


The code for Case 2 in my linked form is as follows:
Me!txt_Job_ID=split(MyOpenArgs)(1)
Me!txt_Account_No = Format(split(OpenArgs, "-")(0),"00000")

I am not getting any data in the txt_Account_No field or the txt_Job_ID field.

What I need is for the Job_ID and the cbo_Account_No field data to come over
to a new record in the linked frm_tbl_contracts_Int!txt_Job_ID and the
frm_tbl_contracts_Int!txt_Account_No.

 

[KARL DEWEY]

Use a query as your record source and make sure you include JOB_ID field or
whatever field you have in common between the main form and the subform.

 

[Douglas J. Steele]

In your first case, you need quotes around the field name in your Where
condition:

DoCmd.OpenForm "frm_tbl_contracts_Int", acNormal, , "Me!Job_ID = " &
Forms!frm_tbl_Contracts_Int!txt_Job_ID, acFormAdd, acWindowNormal

That assumes that Job_ID is a numeric field. If it's text, you need quotes
as well:

DoCmd.OpenForm "frm_tbl_contracts_Int", acNormal, , "Me!Job_ID = '" &
Forms!frm_tbl_Contracts_Int!txt_Job_ID & "'", acFormAdd, acWindowNormal

Note that I changed frm_tbl_Contracts_Int.txt_Job_ID to
Forms!frm_tbl_Contracts_Int!txt_Job_ID, assuming that you're trying to refer
to a control on another form.

In your second case, Me!txt_Job_ID=split(MyOpenArgs)(1) is missing the "-"
to indicate that's the separator (, is assumed by default). Also, you need
to refer to Me.OpenArgs, not MyOpenArgs in both cases. Try:

If IsNull(Me.OpenArgs) = False Then
Me!txt_Job_ID=Split(Me.OpenArgs, "-")(1)
Me!txt_Account_No = Format(Split(Me.OpenArgs, "-")(0),"00000")
End If

The fact that you're not getting any errors implies to me that you haven't
set Access to require that all variables be declared. If you're using Access
97 or earlier, there's a Require Variable Declaration checkbox on the
Modules tab under Tools | Options. In Access 2000 and newer, it's on the
Editor tab under Tools | Options when you're in the VB Editor.

 

[Emma]

Okay, for Case 1, I've added the quotes as follows:

DoCmd.OpenForm "Frm_Tbl_Contracts_Int", acNormal, , "Me!Job_ID = '" &
Forms!Frm_Tbl_Contracts_Int!txt_Job_ID & "'", acFormAdd, acWindowNormal

and the form opens up ok. However, The job ID is not coming overy from the
main form. What do I need to do to get the Job ID and account number over to
the main form.

 

[Douglas J. Steele]

What is Frm_Tbl_Contracts_Int? Is it the name of the subform? If so, that's
not how you refer to it.

Your main form has a control on it that contains the form that's being used
as a subform. If you added the subform by dragging the form onto the main
form, that control should have the same name as the form that was dragged,
but if you added the subform by selecting the Subform control from the
Toolbox, it'll probably be named something like Child0. To refer to a
control on a subform, you need to use:

Form!NameOfMainForm!NameOfSubformControl.Form!NameOfControlOnSubform

See http://www.mvps.org/access/forms/frm0031.htm at "The Access Web" for
more details.

 

[Emma]

frm_tbl_Contracts_Int is a form that is supposed to pop up when a particular
selection is made from an option box on my main form. It is not a subform
per se, but is supposed to be linked to the main form via the Job_ID.

 

[Douglas J. Steele]

I'm starting to get confused.

What forms do you have? Which form contains the code DoCmd.OpenForm
"Frm_Tbl_Contracts_Int", ....?

 

[Emma]

Sorry about that, please accept my apologies. . . I'll try to explain better.

frm_Tbl_Master_Jobs is the main form.

On it is are two option buttons for contract types because there are two
separate ranges of numbers for the two different contract types (Int and
Biopsy). The main form also has a Job_ID field and a cbo_Account_No field.

If the Int option is selected, the frm_tbl_Contracts_Int form should open
up. This form is based on a query that connects the tables tbl_contracts and
tbl_Master_Jobs throught the fields tbl_master_Jobs!Job_ID and
tbl_Contracts_Int!fk_Job_ID.

Same goes for the Biopsy option.

When either of these forms opens up, the Job_ID should populate the
frm_tbl_Contracts_Int!txt_Job_ID field and the txt_Account_No field. My
issue is that I can't figure out how to get the txt_job_ID and the
txt_account_no fields to populate with the same values that are in the
current record of the main form. I don't think I am linking them correctly.

I know that the tbl_Contracts_Int!fk_Job_ID does not automatically populate
just because I created a relationship on that field with the
tbl_Master_Jobs!Job_ID field. So How do I get the Job_ID to populate the
fk_Job_ID.

Please let me know if my explanation is still lacking.

 

[Douglas J. Steele]

Your statement

DoCmd.OpenForm "Frm_Tbl_Contracts_Int", acNormal, , "Me!Job_ID = '" &
Forms!Frm_Tbl_Contracts_Int!txt_Job_ID & "'", acFormAdd, acWindowNormal

is trying to use whatever values in Forms!Frm_Tbl_Contracts_Int!txt_Job_ID
as the criteria to open Frm_Tbl_Contracts_Int. It should probably be:

DoCmd.OpenForm "Frm_Tbl_Contracts_Int", acNormal, , "Me!Job_ID = '" &
Forms!Frm_Tbl_Master_Jobs!txt_Job_ID & "'", acFormAdd, acWindowNormal

Relationships do nothing to populate fields in related tables. You must
still explicitly provide the value(s) required to update the tables.