How to put a Id number into another table

[Guest]

I have 2 forms

1. This table has a field "IdNumber" and on the form it has a text box for
data entry.
2. This table has a field "idNum" but it is not on the form it is going to
be used to hold the "IdNumber from the 1st form. Also there are other
questions that are on the 2nd form which should go right next to the idNum.
I need to know how to get the "IdNumber" from the first table into the second
with questions corresponding to the idNum.

One IdNumber will only have one set of questions for the 2nd table.

 

[John Vinson]

I have 2 forms

1. This table has a field "IdNumber" and on the form it has a text box for
data entry.
2. This table has a field "idNum" but it is not on the form it is going to
be used to hold the "IdNumber from the 1st form. Also there are other
questions that are on the 2nd form which should go right next to the idNum.
I need to know how to get the "IdNumber" from the first table into the second
with questions corresponding to the idNum.

One IdNumber will only have one set of questions for the 2nd table.

Make the second Form a Subform on the first one; use IdNumber as the
Master Link Field and idNum as the Child Link Field.

John W. Vinson[MVP]

 

[Guest]

Could you go into a little more detail on this part "IdNumber as the Master
Link Field and idNum as the Child Link Field".

Also, I have like 10 forms and tables that I have to do this to. Should I
make all the subforms on the main form say 10 or so.

 

[John Vinson]

Could you go into a little more detail on this part "IdNumber as the Master
Link Field and idNum as the Child Link Field".

When you create a Subform (using the subform tool on the Tools bar)
Access will default to linking the subform to the mainform based on
the relationships that you have defined on the Relationships window.
If you want to create the link manually, you can view the Properties
of the Subform control; on the Data tab two of the properties are the
"Master Link Field" and the "Child Link Field". These should be set to
the fieldnames of the linking fields in the "one" side table and the
"many" side table respectively.

Also, I have like 10 forms and tables that I have to do this to. Should I
make all the subforms on the main form say 10 or so.

That would be a rather heavy Form, but I'd say so. You may want to put
a Tab Control on the form and put one (or more) Subform on each page
of the tab control, just so your form isn't excessively busy.

John W. Vinson[MVP]

 

[Guest]

Ok, I put a subform on the main form. Then I set the Child to idNum and the
Master to idNumber. Then when I went to the second form and answered some
question and hit save it didn't bring over the idNumber from the main form.
I close out the main form when the 2nd one is brought up do I have to leave
up the main form?

 

[Rick Brandt]

Ok, I put a subform on the main form. Then I set the Child to idNum
and the Master to idNumber. Then when I went to the second form and
answered some question and hit save it didn't bring over the idNumber
from the main form. I close out the main form when the 2nd one is
brought up do I have to leave up the main form?

To automatically propogate a linking field you have to make the entry IN the
subform. If you are opening a second independent form then what did you
create a subform for?

 

[Guest]

Ok, I understand now. But I am thinking that there is another probably
easier way. What about a module. I am not familiar with these at all.
Can you take this number from the first form and save it into a variable
then move it into a field in different forms?

 

[Rick Brandt]

Ok, I understand now. But I am thinking that there is another
probably easier way. What about a module. I am not familiar with
these at all. Can you take this number from the first form and save
it into a variable then move it into a field in different forms?

I would pass the desired value in the OpenArgs argument when opening the
second form. The second form's Open event can then pull the value from
there and use it. If the second form will only be creating one record then
you can just set the value of that field directly. If it might need to
create numerous records then you would want to use the OpenArgs value to set
the DefaultValue property of the desired control and all records you create
(in that instance) will automatically get the proper value in them.

 

[Guest]

I think I understand what you are trying to suggest I just don't know how to
do it. Could you give me a little help. Here is an example of my forms and
fields

Main form which will have the idNumber
Form - About_You
Field - idNumber

Second form where I want the idNumber from the main form to go
Form - Employment
Field - idNum

 

[Rick Brandt]

I think I understand what you are trying to suggest I just don't know
how to do it. Could you give me a little help. Here is an example
of my forms and fields

Main form which will have the idNumber
Form - About_You
Field - idNumber

Second form where I want the idNumber from the main form to go
Form - Employment
Field - idNum

Code in main form to open seconf form...

DoCmd.OpenForm "Employment",,,,,,idNumber


Code in second form's open event...

If Me.OpenArgs <> "" Then Me.idNum.DefaultValue = Me.OpenArgs

 

[Guest]

I am getting this error when I go to save the first form:
An expression you entered is the wrong data type for one of the arguments
I made sure IdNumber and idNum were both numbers. I also tried it with
changing them to text and still got the same problem. I have thetext box on
the 2nd form also.

And the error happens on the acNewRec

Here is the code from the Main form:
DoCmd.GoToRecord , , acNewRec
' DoCmd.Close
stDocName = "Day-to-Day"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , IdNumber

Here is the code for the On Open:
If Me.OpenArgs <> "" Then Me.idNum.DefaultValue = Me.OpenArgs

 

[Rick Brandt]

I am getting this error when I go to save the first form:
An expression you entered is the wrong data type for one of the
arguments
I made sure IdNumber and idNum were both numbers. I also tried it
with changing them to text and still got the same problem. I have
thetext box on the 2nd form also.

And the error happens on the acNewRec

Here is the code from the Main form:
DoCmd.GoToRecord , , acNewRec
' DoCmd.Close
stDocName = "Day-to-Day"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , IdNumber

Here is the code for the On Open:
If Me.OpenArgs <> "" Then Me.idNum.DefaultValue = Me.OpenArgs

If you move to a new record on the first form then won't IdNumber be Null?

 

[Guest]

Yes, that is right. I put this in for the save instead:
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
And it is saying that SaveRecord isn't available

 

[Guest]

I got it. Thanks for being so patient and help me.

 

[Guest]

Another question. I tried this with another form also and it didn't work. I
had it open up after the 2nd one is there something else that i have to do?

 

[Rick Brandt]

Another question. I tried this with another form also and it didn't
work. I had it open up after the 2nd one is there something else
that i have to do?

What is the code for opening the new form and where are you running it from?

 

[Guest]

Here is what I have on the current form:
Private Sub Form_Open(Cancel As Integer)
If Me.OpenArgs <> "" Then Me.idNum.DefaultValue = Me.OpenArgs
DoCmd.Maximize
End Sub


This is in the save code: The IdNumber is supposed to be pulled from the
first form. Which I have left open.
So I save the first form with the IdNumber(works fine)
Then it opens a new form and uses the IdNumber from the first form and puts
it into the idNum field(works fine) and I close this form.
Then I open another form and try to bring the IdNumbe in using the code
above and below and it does not work.

DoCmd.Save

DoCmd.Close
stDocName = "FinancialExpenses"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , IdNumber