search form using combo boxes and relates tbls

[Simon Jans]

Hello
I'm having serious troubles creating a seach form showing data from multiple tbls, I'll try to explain my problem using a simple example
by the way I'm using Acces 2003

tblEquipment 
equipment ID
description
brand ID
price 
...
 
tblBrand 
brand ID 
brand

these 2 tbls are linked using the brand ID (one to many)

now I trying to construct a continuos form with in the form header, a unbound combo box showing all possible brand names, an unboud text box for Description and a cmdFilter button

and in de detail section a dataview showing the filtered records.

Private Sub cmdFilter_Click()
    Dim strWhere As String                 
    Dim lngLen As Long                   
 
    If Not IsNull(Me.txtFilterDescription) Then
        strWhere = strWhere & "([Description] = """ & Me.txtFilterDescription & """) AND "
    End If
 
    If Not IsNull(Me.cboFilterBrand) Then
        strWhere = strWhere & "([Brand] = " & Me.cboFilterBrand & ") AND "
    End If  
 
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    
        strWhere = Left$(strWhere, lngLen)
 
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub

the filtering on the textbox works fine but the combo box is giving problems , I can work with the ID but not wirth the actual brands , know it has something to do with using a query that includes the lookup table as well but don't know ho to do this
please help

thanks in advance
Regards
Simon

[NeoPa]

Let me know if there is anything that (Example Filtering on a Form.) doesn't explain for you.

[Simon Jans]

Hi NeoPa thanks for replying so quikly

using the formfiltering dbase as example I would like to see the account type (customer or supplier) in the filtered account type combo box instead of the index number (0,1 or 2)

by the way in the example you use a value list but I need to use a linked table as rowsource but I suppose that doesn't make a difference

regards
Simon

[NeoPa]

I will just quote from something Rabbit's working on atm as a new Tutorial.

ASSUMPTIONS
We will use a simple scenario for this tutorial. You have an unbound form with two combo boxes. One named [Company] and the other named [Employee Name]. [Company] will get its values from table TblCompany while [Employee Name] will get its values from TblEmployees. The tables have the following layout:
Code:
TblCompany
[ID] – AutoNumber, PK
[CpyName] – Text, Name of the Company

TblEmployees
[EmpName] – Text, Name of Employee
[ID] – FK, Used to link the employee to the company from which they work.
[EID] – Autonumber, PK

As a default, [Company] will have the following properties:
Row Source – TblCompany
Column Count – 2 (We use 2 columns because we want to include both ID and CpyName.)
Column Widths – 0”;1” (We set the first column to 0” because the user does not need to see the ID.)
Bound Column – 1 (We bind it to the first column so that when we refer to [Company], it will return the ID rather than CpyName.)

And [Employee Name] will have no options because we want it to be empty until a company has been chosen.

Your needs will determine how you will set up your combo/list boxes and tables.

This should answer your question :)

[Simon Jans]

OK it's working now

thanks a lot

Simon

[NeoPa]

Very pleased to hear that Simon.
It's good that Rabbit's first Tutorial has reaped dividends even before it's official release :)