retrieving namesTable data to Form

[zSplash]

Klatuu, thanks for all your help. Your discernment is much appreciated!
I've moved this discussion to a new thread, because only the first post had
to do with the subject. (old subject = search results - several fields for
LastName)

So, anyway, I've tried what you suggest, but I'm not there yet. FYI, I
moved all the names information from the original table to a Names table,
with 6 columns:
1. (new) pk
2. OLDpk (which linked to the old form's names?? not unique numbers)
3. nameType
4. First
5. Last
6. DOI

(I'm not really sure how to relate the oldTable to the new table, since the
pk's are different...)

Anyway, I have tried what you taught: The first combo box shows the
possible name types, but the second combo box contains absolutely no data,
ever. (There are 1300+ records in the db.) I think I am erring in entering
the SQL statements. Do I enter those SELECT statements in the Data Tab,
Filter By property? Everything you say sounds perfectly logical, but isn't
working for me.

TIA, AaA...

 

[Klatuu]

Not having the original thread to refer back to my answers, I may not get the
names and such correct, but what you need (I think) is to make the row source
for the second combo filtered by the name type selected in the first combo.
It would be something like:
SELECT pk, FIRST & LAST AS FullName FROM NamesTable WHERE nameType =
Me.cboNameType;

Then in the AfterUpdate event of the names type combo, requery the names
combo:

Me.cboNames.Requery


--
Dave Hargis, Microsoft Access MVP

Klatuu, thanks for all your help. Your discernment is much appreciated!
I've moved this discussion to a new thread, because only the first post had
to do with the subject. (old subject = search results - several fields for
LastName)

So, anyway, I've tried what you suggest, but I'm not there yet. FYI, I
moved all the names information from the original table to a Names table,
with 6 columns:
1. (new) pk
2. OLDpk (which linked to the old form's names?? not unique numbers)
3. nameType
4. First
5. Last
6. DOI

(I'm not really sure how to relate the oldTable to the new table, since the
pk's are different...)

Anyway, I have tried what you taught: The first combo box shows the
possible name types, but the second combo box contains absolutely no data,
ever. (There are 1300+ records in the db.) I think I am erring in entering
the SQL statements. Do I enter those SELECT statements in the Data Tab,
Filter By property? Everything you say sounds perfectly logical, but isn't
working for me.

TIA, AaA...

I would use cascading combo boxes to select a record. That is, the second
combo would be filtered based on the value in the first combo.

Your first combo (cboNameType) should be based on the NameTypes Table:
SELECT NameType FROM NameTypesTable

In the After Update event of the first combo, you requery the second combo
so it only shows names with the selected name type:

Me.cboPerson.Requery

Your second combo (cboPerson) needs to be a two column combo. That is so
you can use the pk of the table to navigate to the record you want:
SELECT pk, First & Last AS FullName FROM NamesTable Where NameType =
Me.cboNameType

Set the combo's column count property to 2
Set the bound column property to 1
Set the Column Widths property to 0";3.0"
This will hide the pk and the user will only see the name.

In the After Update event of the second combo, you will use something like
the following to make the selected person's record the form's current
record:

With Me.RecordsetClone
.FindFirst "[pk] = " & Me.cboPerson
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

 

[zSplash]

Thanks for all your help, Dave.

st.

Not having the original thread to refer back to my answers, I may not get
the
names and such correct, but what you need (I think) is to make the row
source
for the second combo filtered by the name type selected in the first
combo.
It would be something like:
SELECT pk, FIRST & LAST AS FullName FROM NamesTable WHERE nameType =
Me.cboNameType;

Then in the AfterUpdate event of the names type combo, requery the names
combo:

Me.cboNames.Requery


--
Dave Hargis, Microsoft Access MVP

Klatuu, thanks for all your help. Your discernment is much appreciated!
I've moved this discussion to a new thread, because only the first post
had
to do with the subject. (old subject = search results - several fields
for
LastName)

So, anyway, I've tried what you suggest, but I'm not there yet. FYI, I
moved all the names information from the original table to a Names table,
with 6 columns:
1. (new) pk
2. OLDpk (which linked to the old form's names?? not unique numbers)
3. nameType
4. First
5. Last
6. DOI

(I'm not really sure how to relate the oldTable to the new table, since
the
pk's are different...)

Anyway, I have tried what you taught: The first combo box shows the
possible name types, but the second combo box contains absolutely no
data,
ever. (There are 1300+ records in the db.) I think I am erring in
entering
the SQL statements. Do I enter those SELECT statements in the Data Tab,
Filter By property? Everything you say sounds perfectly logical, but
isn't
working for me.

TIA, AaA...

I would use cascading combo boxes to select a record. That is, the
second
combo would be filtered based on the value in the first combo.

Your first combo (cboNameType) should be based on the NameTypes Table:
SELECT NameType FROM NameTypesTable

In the After Update event of the first combo, you requery the second
combo
so it only shows names with the selected name type:

Me.cboPerson.Requery

Your second combo (cboPerson) needs to be a two column combo. That is
so
you can use the pk of the table to navigate to the record you want:
SELECT pk, First & Last AS FullName FROM NamesTable Where NameType =
Me.cboNameType

Set the combo's column count property to 2
Set the bound column property to 1
Set the Column Widths property to 0";3.0"
This will hide the pk and the user will only see the name.

In the After Update event of the second combo, you will use something
like
the following to make the selected person's record the form's current
record:

With Me.RecordsetClone
.FindFirst "[pk] = " & Me.cboPerson
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With