Getting tables to work

[seppi]

Hello everyone,

I am really stuck on something that seems very simple to me but i cant get it to work.

What i have got is one master form and sevral forms sub forms that all hold information.

What i want to do is have an auto numbered ID in my master document and then all of the sub forms automatically have the same number. How do i build up this link??

THIS MIGHT HELP TO MAKE THINGS CLEARER....
I.e so if i opne a form for the master documents table (ID number 5) then when i click to open a sub form it opens in case ID 5

Can anyone please please help???

thanks guys

 

[Tay]

You need to establish the links between the master form and sub forms. So when you put the subform control in your main form, you will get an option with the wizard to link master and child fields. So you would choose the ID number for each table.
In the Relationships window, make sure that you have got the tables linked, and that you have enforced referential integrity and checked the cascading update and delete boxes.
If you aren't talking about a subform control on your main form, but you want to open another form from the main form via a button, then when you add the button using the wizard, choose the option where it says 'open the form and find specific data to display', and then you will be prompted to choose the ID number you want.

 

[seppi]

thanks for that

Yeah i think i am working along those line at the moment..... but i havent got around the problem yet...

so if i have id no 5 open in my master document form and then i want to open (id no 5) in another form how do i set the tables upto so that the autonumber in the master document some how automatically is copied into the sub form when i want to add data for this case.

i.e the situation is at the moment that when i open the sub form it shows the last case that anything was added to this section so it could be 2 or 3 or 4

 

[Tay]

For example.....

Project table (Main table)

ProjectID = autonumber (Primary Key)
ProjectName = text

Project Details table (Sub table)

ProjectID = long integer
ProjectType = text

In the Relationships window (go to Tools on the Main Menu, the Relationships) Show the tables you want, and join them by ProjectID.

With this set up, now go to your forms. Build your main form. If you want the subform to appear in it, put a subform control in, and do as I suggested earlier.
If you want to open another form (which is joined by the same ID number like ProjectID) then add a button to your main form, and follow the wizards' instructions how I explained them.

The code behind the button to open the subform would look like this

Private Sub CommandOpenSubForm1_Click()
On Error GoTo Err_CommandOpenSubForm1_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "SubForm1"

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

Exit_CommandOpenSubForm1_Click:
Exit Sub

Err_CommandOpenSubForm1_Click:
MsgBox Err.Description
Resume Exit_CommandOpenSubForm1_Click

End Sub

This will make your 'subform' open at the correct ID number, so if the main form says ID 5, the subform will then open at ID 5.

Post back if you need more information.