Access - Linking Forms from different tables

[SantasLittleHelper]

I currently have 4 different tables that all have relationship links to their ID number. I am trying to set up a button for the user to click on to open another form that opens to the record with the same ID number.

E.g. When the button is clicked:
Open 'Form1' where 'ID' in 'Form1' is the same 'ID' in 'Form2'

I have a button on 'Form1' with a macro to open 'Form2' with the condition '="[ID]=" & [ID]' but this is currently opening 'Form2' to a New record (With a new ID)

Any help would be appreciated.

 

[Joe4]

Have you considered using Subforms?
https://support.office.com/en-us/ar...any-form-ddf3822f-8aba-49cb-831a-1e74d6f5f06b

 

[SantasLittleHelper]

Ideally I prefer not to use Subforms as I have 4 different forms/tables that are linked together with IDs. Can you think of any other way around this issue?

 

[Joe4]

I have a button on 'Form1' with a macro to open 'Form2' with the condition '="[ID]=" & [ID]' but this is currently opening 'Form2' to a New record (With a new ID)

Please post the code that you do have that is not working quite right.

 

[SantasLittleHelper]

It was a macro but I've converted it to the following: - It is Opening Page 2 where ID is the same as Page 1

Private Sub cmd_Page2_Click()On Error GoTo cmd_Page2_Click_Err


    DoCmd.OpenForm "frm_Page2", acNormal, "", "[ID]=" & ID, acEdit, acNormal
    DoCmd.Close acForm, "frm_Page1"




cmd_Page2_Click_Exit:
    Exit Sub


cmd_Page2_Click_Err:
    MsgBox Error$
    Resume cmd_Page2_Click_Exit


End Sub

 

[Joe4]

So, is "ID" the exact name of the field on the Form?
If so, try:

    DoCmd.OpenForm "frm_Page2", acNormal, "", "[ID]=" & [COLOR=#ff0000]Me.[/COLOR]ID, acEdit, acNormal

 

[SantasLittleHelper]

Yes that is the exact name.

I've made those changes to the code but it still opens the form at a new ID

 

[Joe4]

It worked fine for me.
Are you sure that you have an existing record in the other Table with the exact same ID?
If you don't, it will open a new record, like you are experiencing.

 

[SantasLittleHelper]

Ah yes sorry. Basically when form 1 is completed, a record (with an ID) is created. The other tables that link to the other forms are all blank so I was trying to open the other forms with a record that has that ID.

E.g
Open Form 1 to a new ID
Then Open Form 2 - creating a record with the same ID number.

Is this possible?

 

[Joe4]

Ah, that is a big difference and important distinction. You are not trying to open an existing record in the other table, but add a new record that that table.
Note that in order for that to work, you need to make sure that:
- The ID field in the other table is NOT an Autonumber field
- The record does not already exist in the other table

If those things are true, this code should do what you want:

'   Capture current ID
    Dim curID As Long
    curID = Me.ID
'   Open new form
    DoCmd.OpenForm "Form2", acNormal, "", , acFormAdd, acNormal
'   Assign ID to ID in new record
    [Forms]![Form2]![ID].Value = curID
'   Close original form
    DoCmd.Close acForm, "Form1"