One to Many query for a Form

[Jerry8989]

I have two tables (user and notes). It is a 1 to Many relationship.


I might have 0 notes for a user or I might have 5 separate rows of notes for 1 user.

I have a form that I want to display the user information and then show an indicator if this user has any notes so I plan on using a checkbox.

My problem is that I can't use distinct in my query or else my form becomes uneditable. Other then creating a seperate distinct list of id & notes table how can I get around this?

Thank you for any help pointing me in the right direction

[dcrake]

If you only want to show the user that the currently selected person has notes attached to them then you may be able to simply do a look up or a count. Count may be better.

OnCurrent Event

 
Dim xMany As Integer
 
xMany = Nz(DCount("*","TblNotes","UserID=" & Me.TxtUserId),0)
 
Me.CmdNotes.Caption = xMany & " Note(s) Found"

This is aircode and untested, likewise the control names are used for brevity.


David

[Jerry8989]

dcrake,
Thank you so much. What you posted reminded me that I don't have to tie everything using the data source query. I created a function within my module that I use as my data source for my checkbox field. I take the id from that row and pass it in and it returns me back a 0 or -1 and the checkbox appears correctly. Thank you again so much for sparking that idea into my head.

Thank You
Jerry