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

Relating forms (newbie)

P: 2
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.
Oct 27 '06 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub CommandXX_OnClick()
  3. Dim stDocname As String
  4. Dim stLinkCriteria As String
  5.  
  6.   stDocName = "Your second form"
  7.   stLinkCriteria = "[ID Field on Form2]=" & Me.[ID Field on Form1]
  8.  
  9.   DoCmd.OpenForm stDocName, , , stLinkCriteria
  10.  
  11. End Sub
  12.  
  13.  
Oct 28 '06 #2

P: 2
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.
Oct 30 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534

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).
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim strSQL As String
  3.  
  4. strSQL = "UPDATE Visit SET Visit.FieldName=#" & txtVisit2 & "# WHERE Visit.OtherFieldName=<Something>;"
  5. DoCmd.RunSQL strSQL
  6.  
  7.  
Oct 30 '06 #4

Post your reply

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