Complicated search set up?

[HLCruz via AccessMonster.com]

I have a database for a small non-profit with a member list. My NEW table
structure splits our member information into two tables: tblMailingList and
tblContacts. Essentially this allows for multiple family members within a
household.

tblMailingList has MailingID as the primary key and contains basic address
fields.
tlbContacts has ContactID as the primary key and stores their MailingID along
with personal and demographic fields. So, for example, 4 members of the same
household would appear in tblContacts with unique ContactIDs but all have the
same MailingID.

In the past I have used a "searching" text and combo box set up, which allows
a user to type a name or address into a text box and it searches for the
matching record in a combo box as you type. This was really easy for me to
set up because my names and addresses, etc. were all in the same table. The
query behind the searching had two fields, the ID field (primarykey) and a
concatenated field (LastName & ", " & FirstName). The bound field was that
concatenated field, "NameSearch".

Here is my code for the text box:
Private Sub txtLastNameSearch_Change()
Dim varRetVal As Variant

varRetVal = acbDoSearchDynaset(Me.txtLastNameSearch, _
Me.lstLastNameSearch, "NameSearch")

End Sub

Private Sub txtLastNameSearch_Exit(Cancel As Integer)
acbUpdateSearch Me.txtLastNameSearch, Me.lstLastNameSearch

End Sub

Here is the code for the combo box which finds the record:
Private Sub lstLastNameSearch_AfterUpdate()
acbUpdateSearch Me.txtLastNameSearch, Me.lstLastNameSearch
End Sub

Private Sub lstLastNameSearch_Enter()
'Find the record that matches the control

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NameSearch] = """ & Me![lstLastNameSearch] & """"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

My users really like the way this works but with my new structure I'd need to
modify this and I'm not sure where to start.

IMy new structure has a main contact management form based on my
tblMailingLists and uses subforms to manage the information in tblContacts.
I would like for them to be able to search (I currently put my search options
in the form's header) for the tblMailingList record that matches the
tlbContact record they want to see.

For example, I want the user to be able to type into my text field, Jane Doe,
and for it to search the combo box based on that name, but ultimately when
they enter after the name is found, I need the form to pull up the record
based on the MailingID. I'm not sure how to do that because I want my text
box to search my query based on one field, but my combo box code to find the
record based on another field ...

I hope this makes sense, please let me know if I can clarify anything.

Thanks,
Heather

 

[Allen Browne]

There are several ways to approach this.

One possiblity would be to use a search form with a UNION query that gets
the info from both tables. In the search results, you could then click a
button to open the appropriate form for editing (contacts or mailing list.)

Another would be to open 2 forms, so the user can search either one.

A better solution might be to merge the contacts and mailing list into one
table, which means you can create mailings that go to either individuals or
to households. It takes a little bit of lateral thinking to wrap your head
around it, but it's so flexible a way to store the data. You can still have
your 2 forms (contacts and mailing lists) for data entry since that's the
way the user thinks about the data, but it's just laughably easy to search
it since it's all in the one table.

For an example, see:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a database for a small non-profit with a member list. My NEW table
structure splits our member information into two tables: tblMailingList
and
tblContacts. Essentially this allows for multiple family members within a
household.

tblMailingList has MailingID as the primary key and contains basic address
fields.
tlbContacts has ContactID as the primary key and stores their MailingID
along
with personal and demographic fields. So, for example, 4 members of the
same
household would appear in tblContacts with unique ContactIDs but all have
the
same MailingID.

In the past I have used a "searching" text and combo box set up, which
allows
a user to type a name or address into a text box and it searches for the
matching record in a combo box as you type. This was really easy for me
to
set up because my names and addresses, etc. were all in the same table.
The
query behind the searching had two fields, the ID field (primarykey) and a
concatenated field (LastName & ", " & FirstName). The bound field was
that
concatenated field, "NameSearch".

Here is my code for the text box:
Private Sub txtLastNameSearch_Change()
Dim varRetVal As Variant

varRetVal = acbDoSearchDynaset(Me.txtLastNameSearch, _
Me.lstLastNameSearch, "NameSearch")

End Sub

Private Sub txtLastNameSearch_Exit(Cancel As Integer)
acbUpdateSearch Me.txtLastNameSearch, Me.lstLastNameSearch

End Sub

Here is the code for the combo box which finds the record:
Private Sub lstLastNameSearch_AfterUpdate()
acbUpdateSearch Me.txtLastNameSearch, Me.lstLastNameSearch
End Sub

Private Sub lstLastNameSearch_Enter()
'Find the record that matches the control

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[NameSearch] = """ & Me![lstLastNameSearch] & """"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

My users really like the way this works but with my new structure I'd need
to
modify this and I'm not sure where to start.

IMy new structure has a main contact management form based on my
tblMailingLists and uses subforms to manage the information in
tblContacts.
I would like for them to be able to search (I currently put my search
options
in the form's header) for the tblMailingList record that matches the
tlbContact record they want to see.

For example, I want the user to be able to type into my text field, Jane
Doe,
and for it to search the combo box based on that name, but ultimately when
they enter after the name is found, I need the form to pull up the record
based on the MailingID. I'm not sure how to do that because I want my
text
box to search my query based on one field, but my combo box code to find
the
record based on another field ...

I hope this makes sense, please let me know if I can clarify anything.

Thanks,
Heather

 

[HLCruz via AccessMonster.com]

Thank you Allen - I'll take a look at the information on your webpage about
merging those two tables. I apprecite that you share your experience with
the rest of us!

There are several ways to approach this.

One possiblity would be to use a search form with a UNION query that gets
the info from both tables. In the search results, you could then click a
button to open the appropriate form for editing (contacts or mailing list.)

Another would be to open 2 forms, so the user can search either one.

A better solution might be to merge the contacts and mailing list into one
table, which means you can create mailings that go to either individuals or
to households. It takes a little bit of lateral thinking to wrap your head
around it, but it's so flexible a way to store the data. You can still have
your 2 forms (contacts and mailing lists) for data entry since that's the
way the user thinks about the data, but it's just laughably easy to search
it since it's all in the one table.

For an example, see:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

I have a database for a small non-profit with a member list. My NEW table
structure splits our member information into two tables: tblMailingList

[quoted text clipped - 80 lines]

Thanks,
Heather