Finding Subform Records

[Guest]

Hello!

I've searched high and low on this forum and others, but I can't seem to
find an answer to this dilemma.

I am using Access 2003. My database has several tables, but the 2 in
question are Companies and Contacts. Companies has a one-to-many
relationship with Contacts. I have a Contact subform on the Companies main
form, which I use to bring up a Company and see all of the contacts at that
company. On the main form, I have a "Find Record" command button that opens
up "Find & Replace" to locate a company. I would like to have something
similar on the subform to be able to find contacts when I don't know the
company they work for. However, when I add this command button I can only
search within the company that I am on in the main form. I would like to be
able to search the entire contacts table.

I've seen very similar questions to this posted in the past, but the answer
almost always refers to Allen Browne's article
(http://members.iinet.net.au/~allenbrowne/ser-28.html) - this doesn't really
answer my dilemma here though. The main thing that I need to accomplish is
to be able to look up a contact and see what company they work for. I do not
need a filter.

Any ideas?

Thanks in advance for any suggestions..

Nicolle

 

[John Vinson]

The main thing that I need to accomplish is
to be able to look up a contact and see what company they work for.

Create a Query joining the company table to the contact table, and
search in that query.

The Find and Replace dialog on the form won't cut it in this case -
the data simply is not there to find!

Sample air code, untested: I'll assume that you have a textbox on the
subform named txtLastName and that you want to search the entire
contacts table for that name, and that you have the above query saved
as qryBoth. Adjust the criteria as needed...


Private Sub cmdFindCompany_Click()
Dim vCompanyID As Variant
Dim rs As DAO.Recordset ' set the Microsoft DAO 3.6 object library
vCompanyID = DLookUp("[CompanyID]", "[qryBoth]", "[LastName] = """ _
& Me!txtLastName & """")
If IsNull(vCompanyID) Then
MsgBox "Name not found", vbOKOnly
Else
Set rs = Parent.RecordsetClone
rs.FindFirst "[CompanyID] = " & vCompanyID
If rs.NoMatch Then
MsgBox "Company not found", vbOKOnly
Else
Parent.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

John W. Vinson[MVP]

 

[Guest]

Hi John,

Thanks for your advice. I'm not sure I follow what the code is suppsoed to
do.

I created a command button using this code and placed it on the subform
(fsubContacts) but it seemed to only look up the contact name of the current
record. I would like the user to be able to perform a search where they can
type in the contact name and be directed to the record the contact belongs to.

I apologize if I'm missing something here - I'm very new to VBA. Any
further help would be greatly appreciated!

Thanks,

Nicolle

The main thing that I need to accomplish is
to be able to look up a contact and see what company they work for.

Create a Query joining the company table to the contact table, and
search in that query.

The Find and Replace dialog on the form won't cut it in this case -
the data simply is not there to find!

Sample air code, untested: I'll assume that you have a textbox on the
subform named txtLastName and that you want to search the entire
contacts table for that name, and that you have the above query saved
as qryBoth. Adjust the criteria as needed...


Private Sub cmdFindCompany_Click()
Dim vCompanyID As Variant
Dim rs As DAO.Recordset ' set the Microsoft DAO 3.6 object library
vCompanyID = DLookUp("[CompanyID]", "[qryBoth]", "[LastName] = """ _
& Me!txtLastName & """")
If IsNull(vCompanyID) Then
MsgBox "Name not found", vbOKOnly
Else
Set rs = Parent.RecordsetClone
rs.FindFirst "[CompanyID] = " & vCompanyID
If rs.NoMatch Then
MsgBox "Company not found", vbOKOnly
Else
Parent.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End If
End Sub

John W. Vinson[MVP]