If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Complicated search set up?
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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200803/1 |
#2
|
|||
|
|||
Complicated search set up?
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 Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "HLCruz via AccessMonster.com" u27207@uwe wrote in message news:817f61ce144d8@uwe... 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 |
#3
|
|||
|
|||
Complicated search set up?
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! Allen Browne wrote: 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 -- Message posted via http://www.accessmonster.com |
Thread Tools | |
Display Modes | |
|
|