Filter in a form with more tables

[MarkusJohn]

I have a form.. in the header there are unbound search fields. The
entered values are concatenated to a string. When you press the filter
button in the form all the found entries appear.

The searchable fields are not only based on one table.
In my case I have the tables order and customer. primary keys for order
is order no and for customer custid. When I created the form I used the
wizzard and selected all required fields. But when I look for a
customers name the realtion order customer is not linked over name but
id. So I get for the name only one result or a mistake.
What I want is all Peter´s for example.
So my question how can I execute in a query in my vba code? DBLookup
works but results only one person..

Here´s a snippet of my code:
If Not IsNull(Me.TxTName) Then
id = 0
id = DLookup("[KundenID]", "Customer", "[Name] = '" &
Me.TxTName & "'")
strWhere = strWhere & "([CustomerID] = " & id & ") AND "
End If

 

[Douglas J. Steele]

First of all, you need to handle the possibility that the name doesn't exist
in the table (i.e.: that your DLookup statement returns Null, rather than a
valid Id).

If Not IsNull(Me.TxTName) Then
id = 0
id = Nz(DLookup("[KundenID]", "Customer", _
"[Name] = '" & Me.TxTName & "'"), 0)
If id <> 0 Then
strWhere = strWhere & "([CustomerID] = " & _
id & ") AND "
End If
End If

Now, I don't know what else you're putting into strWhere, but eventually
you'll hopefully have built your complete Where clause. Strip the final AND
off it:

If Len(strWhere) > 0 Then
strWhere = Left$(strWhere, Len(strWhere) - 5)
End If

then use strWhere as a filter on your form:

Me.Filter = strWhere
Me.FilterOn = True

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a form.. in the header there are unbound search fields. The
entered values are concatenated to a string. When you press the filter
button in the form all the found entries appear.

The searchable fields are not only based on one table.
In my case I have the tables order and customer. primary keys for order
is order no and for customer custid. When I created the form I used the
wizzard and selected all required fields. But when I look for a
customers name the realtion order customer is not linked over name but
id. So I get for the name only one result or a mistake.
What I want is all Peter´s for example.
So my question how can I execute in a query in my vba code? DBLookup
works but results only one person..

Here´s a snippet of my code:
If Not IsNull(Me.TxTName) Then
id = 0
id = DLookup("[KundenID]", "Customer", "[Name] = '" &
Me.TxTName & "'")
strWhere = strWhere & "([CustomerID] = " & id & ") AND "
End If

 

[Marcin]

Uzytkownik "MarkusJohn" <[email protected]> napisal w wiadomosci
I have a form.. in the header there are unbound search fields. The
entered values are concatenated to a string. When you press the filter
button in the form all the found entries appear.

The searchable fields are not only based on one table.
In my case I have the tables order and customer. primary keys for order
is order no and for customer custid. When I created the form I used the
wizzard and selected all required fields. But when I look for a
customers name the realtion order customer is not linked over name but
id. So I get for the name only one result or a mistake.
What I want is all Peter´s for example.
So my question how can I execute in a query in my vba code? DBLookup
works but results only one person..

Here´s a snippet of my code:
If Not IsNull(Me.TxTName) Then
id = 0
id = DLookup("[KundenID]", "Customer", "[Name] = '" &
Me.TxTName & "'")
strWhere = strWhere & "([CustomerID] = " & id & ") AND "
End If
===========================================================================
FULL LEGAL SOFTWARE !!!
Games, video, program, image, chat, questbook, catalog site, arts, news,
and...
This site it is full register and legal software !!!
Please download and you must register software !!!

PLEASE REGISTER SOFTWARE:
http://www.webteam.gsi.pl/rejestracja.htm
DOWNLOAD LEGAL SOFTWARE:
http://www.webteam.gsi.pl

Full question and post: http://www.webteam.gsi.pl

Contact and service and advanced technology:
http://www.webteam.gsi.pl/kontakt.htm
FAQ: http://www.webteam.gsi.pl/naj_czesciej_zadawane_pytania.htm

Please add me URL for you all site and search engines and best friends !!!

Me site:
SERWIS WEBNETI: http://www.webneti.gsi.pl
PORTAL WEBTEAM:http://www.webteam.gsi.pl
LANGUAGE: http://www.webneti.cjb.net

==========================================================================