Linking forms

[oneeyedwilly]

How do i link 2 forms without closing down the enitial form first?

I want to populate 2 tables both linked with a PersonID.

I can get the second form to link to the first form; but the info on the first form is not entered into a table.... When i try to save the data in the second form it says a record must exist in the other table. confused?

Is there a way of forcing the information into the table 'OnClick' as i try to open the second form with a button?

 

[VicM]

oneeyedwilly(Peter? ):

I've just done this very thing. If you are not using your second form as a subform on your first form, the coding is a bit more involved. I'll try to explain.

In the click event of the control button on your 1st form add code similar to the following. (I'm using DAO coding. If you are using ADO, you'll need to modify accordingly.) -

This code assumes that the key field in both forms' recordsets is identical. First need to check if a record in the 2nd recordset associated with the 1st recordset already exists.

Dim rst As DAO.Recordset, booNoMatch As Boolean
Set rst = CurrentDb.OpenRecordset("name of recordset for form 2&quot

With rst

.FindFirst "keyfieldnamein2ndRS = " & Me.keyfieldnamein1stRS.Value
booNoMatch = .NoMatch
.Close
Set rst = Nothing

If booNoMatch Then
DoCmd.OpenForm "name of 2nd form", acNormal, , , acFormAdd, acDialog, CStr(Me.[keyfieldnamein1stRS]) 'This last variable is the OpenArgs argument passed to the 2nd form
Else
DoCmd.OpenForm "name of 2nd form", acNormal, , , acFormEdit, acDialog
End If
End With

Next you will need to add the following expression to the criteria line of your 2nd form's Record Source in the key field column (the field in common with the 1st recordset).

[forms]![name of 1st form]![name of key field on 1st form]

And finally, your 2nd form should have a non-visible textbox containing the key field, whose Default Value is:

=CLng([OpenArgs])

So in summary what happens when you click on the control button on the first form is: The 1st form first checks whether the recordset for the 2nd form contains an entry associated with the 1st form.

If there is an associated record, it will open the 2nd form, in dialog mode, containing only the associated record, thereby allowing modification of any data.

If there is no associated record in the 2nd form's recordset, it will open the 2nd form, in dialog mode, to a new record, already containing the key field link value.

Dialog mode forces the user to deal with the 2nd form, and then close it before any other action can be taken. It is a modal form.

I'm sure there may be other ways to accomplish this task, but after a couple of days of hair pulling I found that this works for me.

Hope this helps,

Vic

 

[oneeyedwilly]

cheers, i will give it a go......

oh, oneeyedwilly refers to the fact that i got a black eye playing football (soccer) last week; nothing more sinister honest