Subform Search Function

[DavidG]

Hi All,

I know this question has been asked before but i havent been able to find an
appropriate solution as yet. Ill start by defining my setup.

I currently have two tables where my data is stored, "Customers" and
"Contacts" with "Customer ID" being the primary key for "Customers". The
Customers table stores contact details for company i.e. delivery adress,
website, and the Contacts table contains data for employees of that company.
They are linked via the "Customer ID" key.

My form is set up with "Customers" details being displayed in the header of
my main form and the "Contacts" details as a subform in the Details section
of my main form.

I would like to be able to search for a "Name" from my subform which would
subsequently bring up the details for the "Customer" and the rest of the
"Contacts" relating to that Customer ID. Here is the code i am using at the
moment. It is a modified version of a previous solution that was trying to
accomplish a similar task to mine:

Private Sub Search_AfterUpdate()
Dim strWhere As String
Dim varResult As Variant
Dim rs As DAO.Recordset

If Me.Dirty Then Me.Dirty = False
If Not IsNull(Me.Search) Then
strWhere = "customer_ID = """ & Me.Search & """"
Debug.Print strWhere
varResult = DLookup("Customer_ID", "Contacts1", strWhere)
If IsNull(varResult) Then
MsgBox "Not found"
Else
With Me.RecordsetClone
strWhere = "Name = """ & varResult & """"
Debug.Print strWhere
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found. Filtered?"
Else
Me.Bookmark = rs.Bookmark
End If
End With
End If
End If
End Sub

I keep getting "Run-time error '2001': You canceled the previous operation.

It points me to this line when i de-bug:
varResult = DLookup("Customer_ID", "Contacts1", strWhere)

Is this something to do with the Where function?

I hope someone will be able to help me as i am really stuck with this.

Thanks in Advance,

Dave.

 

[Tom van Stiphout]

On Thu, 10 Dec 2009 19:16:01 -0800, DavidG

Let's talk functionality first. You write:
"I would like to be able to search for a "Name" from my subform"
That seems to indicate you would like to search for a ContactName. For
example ContactLastName='Jones'. But that could be true for several
companies. Perhaps you're speaking of Filtering instead: filter all
companies down to the ones with a contact with lastname=Jones.

Please use precise language describing your problem, and I'm sure
we'll be able to assist.

-Tom.
Microsoft Access MVP

 

[DavidG]

Hi Tom thanks for the reply.

Yes you are correct, i might not have been clear enough in my initial post,
"Name" is the name of the field i am trying to search under within my subform
and relates to a contacts full name.

The Customers Table contains the "Customer_ID" primary key and i then have a
one to many relationship linking my primary key to the Contacts Table data.

I have investigated the use of filters but from what i have read and tried
out, this will only provide me with the contact details for that person and
will "hide" the details for other workers at that company. I am more looking
to implement something that works in the same way as the "find next" function
where the user searches a name and it takes them to the record containing the
match. I realise that this can cause multiple results but in some cases this
is actually a desirable outcome. Whilst the "find next" function of course
works for the main form, it will only search within the current record for my
subform rather than the entire contacts list.

Please let me know if there is still anything i havent explained properly.

Thanks for you time,

Dave.