By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,180 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

Linking records in two tables using a command button

P: 56

I am creating a database in which users will enter details for one client (record) in several tables via several associated forms. I would like to have a button on each form that takes the user directly to the next form. If it is a new record I want the form to load with a blank form but pulling across the ClientID (unique identifier) but if a record already exists then link by the ClientID. So if going from frmInitial to frmFamily some code that says if new record then ClientID in frmFamily will equal ClientID in frmInitial but if revisiting the record (i.e. data already input but being edited or viewed) the correct record as per ClientID appears.

I already have the following in the Where Condition section of a macro [Forms]![frmInitial]![PatientID]=[tblFamily]![PatientID] and an OnLoad expression to carry over ClientID if a new record but this doesn't seem to work for the command button - any ideas??

Help much appreciated
Jul 30 '09 #1
Share this Question
Share on Google+
2 Replies

Expert 2.5K+
P: 3,532
In The calling form

Expand|Select|Wrap|Line Numbers
  1. Private Sub Go2FormB_Click()
  2. If Not IsNull(Me.OrderID) Then
  3.   DoCmd.OpenForm "TableB", , , , , , Me.OrderID
  4.  Else
  5.   MsgBox "A Visit ID Must Be Entered First!"
  6.  End If
  7. End Sub
In the called form

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  3. If Not IsNull(Me.OpenArgs) Then
  5.  Set rst = Me.RecordsetClone
  7.  rst.FindFirst "[OrderID] = '" & Me.OpenArgs & "'"
  9.   If Not rst.NoMatch Then
  10.       Me.Bookmark = rst.Bookmark
  11.    Else
  12.     DoCmd.GoToRecord , , acNewRec
  13.     Me.OrderID = Me.OpenArgs
  14.    End If
  16. rst.Close
  17. Set rst = Nothing
  18. End If
  20. End Sub
The above code assumes that OrderID is Text. If it is Numeric instead, replace

rst.FindFirst "[OrderID] = '" & Me.OpenArgs & "'"


rst.FindFirst "[OrderID] = " & Me.OpenArgs

Linq ;0)>
Jul 30 '09 #2

P: 56
Great, that works - for the second part I already had an OnLoad event to pull in a few other fields as follows:

If Me.NewRecord Then
Me.ClientID = [Forms]![frmClientDetails].[ClientID]
Me.CHI = [Forms]![frmClientDetails].[CHI]
Me.ClientForename = [Forms]![frmClientDetails].[ClientForename]
Me.ClientSurname = [Forms]![frmClientDetails].[ClientSurname]
End If

How can I incorporate that into the Load event you describe?
Jul 30 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.