Relating forms (newbie)

[BirdByte]

Using Access 2000, how does a record chosen on one form get transferred to another form? A user choses a record in form 1, and presses a button which opens form 2. I want to work with that record in form 2 but with data from a 2nd, related table. The tables are all related with one to many relationships.

Is there a sql join statement that needs to go behind an event procedure or something?

Thanks for any ideas.

[MMcCarthy]

Using Access 2000, how does a record chosen on one form get transferred to another form? A user choses a record in form 1, and presses a button which opens form 2. I want to work with that record in form 2 but with data from a 2nd, related table. The tables are all related with one to many relationships.

Is there a sql join statement that needs to go behind an event procedure or something?

Thanks for any ideas.

Set the recordsouce of the second form to return ALL relevant records

Then when opening the form using the on click event change the code to

 
Private Sub CommandXX_OnClick()
Dim stDocname As String
Dim stLinkCriteria As String
 
  stDocName = "Your second form"
  stLinkCriteria = "[ID Field on Form2]=" & Me.[ID Field on Form1]
 
  DoCmd.OpenForm stDocName, , , stLinkCriteria
 
End Sub
 
 

[BirdByte]

Thanks very much. In debug mode I saw that the ID field did get passed, but I kept getting an 'enter parameter' box, and couldn't figure out where or what the expression was that it was looking for (I'm assuming I had a faulty query behind the record source, but I'm really not sure).

I got around that by using this behind the OnClick event: DoCmd.OpenForm "myForm", acNormal, , "UserID = '" & UserID & "'", , , UserID

10 additional text boxes get populated behind the myForm OnCurrent event: txtVisit2.Value = DateAdd("m", 6, Me.OrigDate)

How can I update the 'Visit' table with the new values in the 10 txtVisit boxes. I tried adding the table to the control source and binding the text boxes, but that didn't work, neither did binding behind a different event (LostFocus specifically).

Thanks for any guidance and your patience with a newbie.

[MMcCarthy]

10 additional text boxes get populated behind the myForm OnCurrent event: txtVisit2.Value = DateAdd("m", 6, Me.OrigDate)

How can I update the 'Visit' table with the new values in the 10 txtVisit boxes. I tried adding the table to the control source and binding the text boxes, but that didn't work, neither did binding behind a different event (LostFocus specifically).

 
Dim strSQL As String
 
strSQL = "UPDATE Visit SET Visit.FieldName=#" & txtVisit2 & "# WHERE Visit.OtherFieldName=<Something>;"
DoCmd.RunSQL strSQL