To link a record in a form with another form

[Kitana]

Hello guys

Hope you can help me with this.
Here's the situation....

I have a form with all the student's details. I've added a command button in the form to open another form to let user to add details of spouse/children if the student is married.

From Student form -> click 'Add Spouse' -> will open Spouse form (this works ok)

I tried added records of spouse to a specific student, but when I try to see the record of spouse of the student, it doesn't come out.
I've check the tables of spouse,children and student and it seems that none of it are related as it is supposed to (they're supposed to be related using Passport_No of student).

I've tried adding a Passport_No field in the Spouse form, and added a code in the field "=[Student]![Passport_No]" and hide it from user.
When I try to run it thru the Student form, it has this '#Name?' in the Passport_No field in that is supposed to be hidden in Spouse form.

How do I set for the Passport_No in Spouse form to take the value of Passport_No in Student form?

Thanks in advance

 

[ColinEssex]

Hi

I think that rather than having another form, why not have different "pages" (tabs) on the existing form for spouse details and the like?

Col

 

[Kitana]

Wow... that was fast Col.

Hmmm...I've never use Tab before in a form. And I'm not sure if I put a subform in a tab, can i then relate the spouse record with the student? is it the same as using a subform in a form?

At first, i wanted to put the spouse subform in the student's form as well. but it looks too crowded.

 

[ColinEssex]

Its not very often I'm called "fast"

Anyway, lets have a quick review.....

You've got a table of student data, name address etc - all you need do, because the spouse and children data are part of the students demographic data - is to add the spouse etc fields to the student table.

Then you can use the same table for all the data and therefore use the same form. You can create a tabbed form from the toolbox in design view.

The idea of tabbed pages is so you don't have the form too crowded and you split the dataFields up into little categories. I personally think of spouse, children, next-of-kin type fields as "sub" demographic data - nice to have but not as important as address, DOB etc.

You don't really need a seperate table and subform for it. Although you can use subforms on "pages" of the form for other data if you wish. (like courses detail or something)

Col

 

[Hayley Baxter]

If you wish to stick with your original plan. The code would look something like:

Private Sub CmdAddSpouse_Click()
Me.Form.Refresh
On Error GoTo Err_CmdAddSpouse_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "YourFormName"

stLinkCriteria = "[IDNumber]=" & Me![IDNumber]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_CmdAddSpouse_Click:
Exit Sub

Err_CmdAddSpouse_Click:
MsgBox Err.Description
Resume Exit_CmdAddSpouse_Click

End Sub

Now you've got two options either sticking with your first option or going with Col's method above. If you're stuck you are welcome to email me and I'll send you a sample.

HTH
Hay

 

[Kitana]

Hayley

I can see in your code there's a link field to link both forms. Does this code automatically sets the Passport_No of Spouse to have the same value of Passport_No of Student as well. Argh, VB.....

The user will click the 'add spouse' button when he adds a new students record.

Col
I've separated spouse's info. and children's info. on a separate table with students. I do believe you're right. I don't need to create a new table for the spouse's info. because one student has only one spouse (supposedly ). But I have to create another table for children because of 1-to-Many relationship. I can't combine the table for spouse and student into one now, because if I do that, it means I have to redo a whole lot of forms

I'll try using Hay's codes first
...and if I have extra time and energy, maybe I'll do a new form that has all these neat tabs like what Col suggest.

 

[ColinEssex]

The choice is yours

I'd still go for the same table option though. You can have children as

No. of Children = 4

ChildNames = Fred,Joe,Charlie,Sid

Hayley - I nominated you for one of the Access awards.....

Col

 

[Rich]

Quote:
Me.Form.Refresh
the problem there Haley is that the form may return to the first record, much better to save the record and do away with the refresh

 

[Hayley Baxter]

Cheers Rich, both yourself and Pat did get onto me for that before - Just didn't get round to changing it

 

[Kitana]

Hey Pat

That's a neat way to do referencing on records. Maybe I can try that in my next database project.
Thanks.

BTW, I've used tabs on my form. Looks nice and more organized.