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

Filter Data based value in combo boxes

100+
P: 106
Hi,

I have an accident claims form that I need to requery on one of three different fields.

Primary field is AccidentID
ClientName (Can appear on more than one AccidentID)
SolicitorsRef (unique to each accident claim but field can be null)
AccidentRefFinal (reference number for each claim)

from the main form, my comboboxes are:
cmbClientName and cmbAccRefFinal - These requery to find the requested data.

cmbSolRef, brings the required data plus all the nulls.
How can I avoid this to show only the record matching cmbSolRef.

Expand|Select|Wrap|Line Numbers
  1. SELECT tblPerson.Completed, Clients.ClientName, tblPerson.SolicitorsRef, tblPerson.AccidentID, tblPerson.AccidentRefFinal
  2. FROM Clients INNER JOIN tblPerson ON Clients.ContactID = tblPerson.ContactID
  3. WHERE (((Clients.ClientName) Like "*" & [Forms]![MMQ_Master_Client]![cmbClientName] & "*") AND ((tblPerson.SolicitorsRef) Like "*" & [Forms]![MMQ_Master_Client]![cmbSolRef] & "*" Or (tblPerson.SolicitorsRef) Is Null) AND ((tblPerson.AccidentID) Like "*" & [Forms]![MMQ_Master_Client]![cmbAccRefFinal] & "*"))
  4. ORDER BY tblPerson.Completed DESC;
  5.  
Your help is appreciated.
Jun 13 '10 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 1,221
Not sure, but maybe you need to change
AND ((tblPerson.SolicitorsRef) Like "*"
to
AND ((nz(tblPerson.SolicitorsRef,' ')) Like "*"

Jim
Jun 13 '10 #2

100+
P: 106
No.. same result

I have now modified to a different method:

I have a 'cmdShowAll' button to show all records by setting all comboboxes to NULL and requery.

cmbAccRefFinal has RowSource:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblPerson.AccidentID, tblPerson.AccidentRefFinal, Clients.ClientName
  2. FROM tblPerson INNER JOIN Clients ON tblPerson.ContactID = Clients.ContactID
  3. ORDER BY tblPerson.AccidentID DESC;
  4.  
cmbClientName has RowSource:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblPerson.AccidentID, Clients.ClientName, tblPerson.AccidentRefFinal
  2. FROM Clients INNER JOIN tblPerson ON Clients.ContactID = tblPerson.ContactID
  3. ORDER BY Clients.ClientName;
  4.  
cmbSolRef has RowSource:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblPerson.AccidentID, tblPerson.SolicitorsRef
  2. FROM tblPerson INNER JOIN Clients ON tblPerson.ContactID = Clients.ContactID
  3. WHERE (((tblPerson.SolicitorsRef) Is Not Null))
  4. ORDER BY tblPerson.SolicitorsRef;
  5.  
in the AfterUpdate code of all comboboxes, I copy the first coloumn to a temporary text box, txtACCID

the underlying query is now filtered in this manner:
Expand|Select|Wrap|Line Numbers
  1. SELECT tblPerson.AccidentID
  2. FROM Clients INNER JOIN tblPerson ON Clients.ContactID = tblPerson.ContactID
  3. WHERE (((tblPerson.AccidentID) Like nz([Forms]![MMQ_Master_Client]![txtAccID],' ') & "*"));
  4.  
cmbAccRefFinal and cmbClientName bring up the selected record.
but odd thing happen when I select cmbSolRef. e.g.:
when AccidentID=459, filtered records include AccidentID's beginning with 45

I am well confused.
Expert help is greatly appreciated
Jun 14 '10 #3

100+
P: 106
Resolved.

I created three textboxes and filled them with data from the comboboxes.
These three text boxes are then used as the search criteria.

Thanks
Jun 18 '10 #4

Post your reply

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