Popup form's foreign key value is Null, even though it displays properly

[Joseph Ellis]

Hello all,

I have a church directory database that consists of two tables:
tblHouseholds and tblIndividuals. Each household can have many
individuals. The tables are in a one-to-many relationship, linked by
the primary key "hhID' in tblHouseholds. So, every household has an
hhID, and each individual in that household has the same hhID (as well
as their own "IndivID").

I have a main form (frmHouseholds) whose record source is
tblHouseholds which contains a subform (frmIndividuals) whose record
source is tblIndividuals. I've made (with lots of help from this
group) a rudimentary search engine which uses OpenForm to pop up a
Search Results form (frmSearchResults):

DoCmd.OpenForm "frmSearchResults", , , strWhere,
acFormReadOnly, acDialog

This is a continuous form which lists all the records that match the
search criteria, and has as its record source tblIndividuals.

The problem is that even though this form properly displays hhID for
each individual, when I try to use hhID to do a FindFirst on the main
form, I get an error due to the fact that hhID is null.

Dim rst As DAO.Recordset
With Forms!frmHouseholds
Set rst = .RecordsetClone
rst.FindFirst "[hhID] = " & Forms!frmSearchResults.hhID

If Not rst.NoMatch Then
.Bookmark = rst.Bookmark
End If

Set rst = Nothing
End With

Just for the heck of it, I even made a little button on
frmSearchResults that pops up a message box listing the first name,
last name, IndivID, and hhID of whichever record I click on. The
value for hhID is always blank, even though each record's hhID is
clearly displayed in the form.

What am I missing here?

Thanks,
Joseph

 

[Allen Browne]

Hi Joseph.

In tblIndividuals, hhID is a foreign key.
Open the table, and look at the data.
Is hhID null?
Unless you set the Required property of your foreign key, it can be Null,
even if you have referential integrity enforced.

If that is not the issue, open the search form and look for someone. Then
open the Immediate window (Ctrl+G), and ask for the value of hhID:
? Forms!frmSearchResults.hhID
Is it null? If so, open the search form in design view, and find the hhID
text box. Either it is bound to something other than the field, or there is
another object with that name, or it is called something else, or ...

 

[Joseph Ellis]

Hi Joseph.

In tblIndividuals, hhID is a foreign key.
Open the table, and look at the data.
Is hhID null?
Unless you set the Required property of your foreign key, it can be Null,
even if you have referential integrity enforced.

If that is not the issue, open the search form and look for someone. Then
open the Immediate window (Ctrl+G), and ask for the value of hhID:
? Forms!frmSearchResults.hhID
Is it null? If so, open the search form in design view, and find the hhID
text box. Either it is bound to something other than the field, or there is
another object with that name, or it is called something else, or ...

Thanks, Allen

You were right. There was another text box on the form named hhID,
due to the fact that in designing the form, I copied and pasted a
bunch of text boxes instead of creating them from scratch. The
duplicate hhID text box in this case was bound to a Prefix (Mr., Dr.,
etc.) field, and is Null for about 99% of my records.

I've gone through the form and appropriately renamed all the controls.
Lesson learned.

Thanks again,
Joseph