By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,414 Members | 1,050 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,414 IT Pros & Developers. It's quick & easy.

search form using combo boxes and relates tbls

P: 3
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

Expand|Select|Wrap|Line Numbers
  1. tblEquipment 
  2. equipment ID
  3. description
  4. brand ID
  5. price 
  6. ...
  7.  
  8. tblBrand 
  9. brand ID 
  10. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdFilter_Click()
  2.     Dim strWhere As String                 
  3.     Dim lngLen As Long                   
  4.  
  5.     If Not IsNull(Me.txtFilterDescription) Then
  6.         strWhere = strWhere & "([Description] = """ & Me.txtFilterDescription & """) AND "
  7.     End If
  8.  
  9.     If Not IsNull(Me.cboFilterBrand) Then
  10.         strWhere = strWhere & "([Brand] = " & Me.cboFilterBrand & ") AND "
  11.     End If  
  12.  
  13.     lngLen = Len(strWhere) - 5
  14.     If lngLen <= 0 Then     
  15.         MsgBox "No criteria", vbInformation, "Nothing to do."
  16.     Else                    
  17.         strWhere = Left$(strWhere, lngLen)
  18.  
  19.         Me.Filter = strWhere
  20.         Me.FilterOn = True
  21.     End If
  22. 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
Feb 16 '07 #1
Share this Question
Share on Google+
5 Replies


NeoPa
Expert Mod 15k+
P: 31,475
Let me know if there is anything that (Example Filtering on a Form.) doesn't explain for you.
Feb 16 '07 #2

P: 3
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
Feb 16 '07 #3

NeoPa
Expert Mod 15k+
P: 31,475
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 :)
Feb 17 '07 #4

P: 3
OK it's working now

thanks a lot

Simon
Feb 20 '07 #5

NeoPa
Expert Mod 15k+
P: 31,475
Very pleased to hear that Simon.
It's good that Rabbit's first Tutorial has reaped dividends even before it's official release :)
Feb 20 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.