Many-to-one, is it possible?

  • Thread starter Thread starter Jeff
  • Start date Start date
J

Jeff

I have a form displaying the details of a student.
In the form I have a subform (as a table) displaying all the students by
name and code.

I want to be able to click on one of the names in the table list and the
information in the main form will change accordingly.

I can make one-to-many, but I want many-to-one!

Any ideas welcome

Jeff
 
Yes. This is probably easier than you think.
On your subform find the unique identifying field (like student ID). Now use
the on current event of the subform to store the unique number in a public
variable. Instead of using a table as the subform design use a form with the
detail section containing all the fields of the table you want to view. also
create a small button the size of the field height at the beginning of the
form. On the click event of the button populate the parent form by
filtering, or setting the record source to the record you want to view. I
would think that using a second pop-up form for this would be smarter, but
that is just my thinking. (why change the original form/subform child/master
properties, and anything else that this might effect)
 
My choice would be to redesign your form. Use a listbox with the student
names and select from that listbox to fill in the fields on the rest of the
form....
The code for this is relatively simple, if you decide to go that way, let us
know.

Damon
 
Jeff,
this may work....
use the subform form_oncurrent() event to run a find on
the main form recordset. use the following as an example...

****** code starts*****

private sub Form_OnCurrent()
dim strCriteria as string
dim frm as Access.Form
dim rst as dao.recordset

' set criteria to find record using primary key
' StudentID is field name
' txtStudentID is textbox name in subform
strCriteria="[StudentID]=" & txtStudentID

set frm=Forms("mainFormName")
set rst=frm.recordsetclone
rst.findfirst strCriteria
if not rst.nomatch then
frm.bookmark = rst.bookmark
end if

end sub

****** code ends *******

this is air code

Luck
Jonathan
 
Thanks to all of you for the advice.

I'm going to check out the ideas and let you know.

jeff
 
Back
Top