Subform linked to master by SSN

[Ryan]

I have a subform linked to master via SSN so it populates data into my
Subform's tbl (tblCT). However, it only populates the SSN and whatever
training fields are on the form. How could I make it also show peoples names.

Reason being...

I want to use the tblCT to easily enter multiple "checkbox info" and need to
be able to see names. I dont want to have to go to each indiv record in the
master then go down to the CT info and check boxes (would take forever).

Is there a better way?

 

[strive4peace]

Hi Ryan,

make SSN a combobox
assuming SAN and Lastname, firstname are store in the same table:

Name --> SSN
RowSource -->
SELECT SSN, [Lastname] & ", " & [Firstname] as Fullname
FROM [Tablename]

ColumnCount --> 2

the combobox does not have to be visible -- and probably should not be
since it is just being used as a LinkChildfield

then, make a textbox control:

=SSN.column(1)

column 1 is really column 2 since indexing starts at zero

.... but, if SSN is the link child field, don't you already have the name
displayed on the main form?


Warm Regards,
Crystal
remote programming and training

Video Tutorials on YouTube!
http://www.youtube.com/user/LearnAccessByCrystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Jeff Boyce]

Ryan

It sounds like you might be trying to work directly in the tables. Access
tables store data, Access forms display data. Use the forms, Luke!

If your main table already stores peoples' names, don't duplicate your data
by re-storing the names in a "child" table.

A standard main form/subform construction let's you retrieve a main form
record (e.g., a person, with name), then add/edit subform records (i.e., a
"child" table records) without ever having to store the name a second time.

Or have I misunderstood your description?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Ken Sheridan]

If the subform is linked to the parent form on SSN then surely you going to
get just one person's data showing in the subform? I don't really see why
you need a subform at all for what you are trying to do here. Just base a
continuous view form on a query which joins the tblCT to your table of
'people', which I'll call tblPeople for this example, so the query would be
along these lines:

SELECT tblPeople.Firstname & " " & tblPeople.LastName
AS FullName, tblCT.*
FROM tblPeople INNER JOIN tblCT
ON tblPeople.SSN = tblCT.SSN
ORDER BY tblPeople.LastName, tblPeople.Firstname;

You can then bind a text box to the computed FullName column along with
controls bound to the columns from tblCT and the form will list the rows by
name alphabetically.

If you wish you can add an unbound combo box to the form's header set up as
follows to enable you to navigate to a person by selecting a name from the
combo box's list:

RowSource: SELECT SSN, FirstName & " " & LastName FROM tblEmployees
ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

Then in its AfterUpdate event procedure put:

Dim rst As Object
Dim ctrl As Control

Set rst = Me.Recordset.Clone
Set ctrl = Me.ActiveControl

With rst
.FindFirst "SSN = " & ctrl
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

The above assumes that the SSN column is a number data type. If its text
data type amend the code to:

.FindFirst "SSN = """ & ctrl & """"

Ken Sheridan
Stafford, England