SQL Statement as Row Source...?

[zym1ne]

Have to say first of all folks that only been on here for a day or 2 and the info that can be found is invaluble to an inexperienced newbie to Access such as myself !!!

The particular problem I'm trying to over-come at the moment is that of an SQL Statement used to populate a list box within a form, I am trying to make it possible so that a that a user can open a second form from with fields auto-filled etc...

Now for the crux...

On the first form (frmDealers1) the user enters either a UniqueID (DID#) or Dealer Name (Client) into an unbound txt Box that applies a filter to my list box or the user can simply scroll thourgh the List Box, problem here is twofold really

I can type in a Dealers Name and the Filter works fine, but there is 3800 Dealer Names within the total list...and some of the names are not unique!

Secondly I am unable to use the DID# to locate the Dealers, i.e i cannot use the DID# to look up records?

Below is a copy of the SQL Statement I have used:

SELECT Dealers.Client, Dealers.dealerID FROM Dealers WHERE (((Dealers.dealerID) Like Forms!frmDealers!txtSearchBox & "*")) Or (((Dealers.Client) Like Forms!frmDealers!txtSearchBox & "*"));

The List Box only displays Dealer Names and not DID# aswell, however if I change the positions of the fields in the SQL Statement Builder (1st is Dealers 2nd is DID#...if these are swapped) then only the DID# is displayed and the txt Box will only except a search by DID#.............

Have tried replacing the "OR" with "AND" in the syntax but with no success!!

Maybe had better include the code for the opening of my second form aswell to elaborate more...

Private Sub lstDealers_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDealers1", acNormal, , "client = '" & Me.lstDealers & "'"

This probably is a confusing way of trying to explain things on my side, but if anyone is able to understand this, then any help offered is MOST gratefully received !!!

 

[Pat Hartman]

When you reference a combo - Forms!frmDealers!txtSearchBox - you are referencing the BOUND column which is usually column 1. The bound column is a property that you can set in the property sheet for the control. That is why you experienced different results when you swapped the field order. If you want users to be able to choose either a name or a number from a combo, you need to use two separate combos. Base the first one on a query that selects the two fields and sorts by name and the second one on a different query that sorts by id.

Add a hidden text box to the form. In the AfterUpdate event of each of the combos copy the ID field from the rowsource to the hidden text box. Then change the query to reference the hidden text box.

SELECT Dealers.Client, Dealers.dealerID FROM Dealers WHERE Dealers.dealerID = Forms!frmDealers!HiddenDealerID;

In the AfterUpdate event of the name combo, use:
Me.HiddedDealerID = Me.txtSearchBox.Column(1)

In the AfterUpdate event of the id combo, use:
Me.HiddedDealerID = Me.txtSearchBox

In the first case you are referencing the SECOND field in the rowsource and in the second case you are referencing the bound field.

FYI, although the Like operator is invaluable when you have a partial field value, it is unnecessary when you have a complete field value. The like operator should only be used if you have no other option as queries that use it cannot be optimized. Jet must do what are called "full table scans" which read and search EVERY record in the table. No indexes can be used whereas, the equal operator can be optimized and Jet will use indexes to spead up record location when they are available.

 

[zym1ne]

Thanks for the advice Pat...

Still a bit confusing though to newbie like myself...

I take it that the combo u refer to is my text Box (txtSearchBox) and have suggested creating a second txt Box that has it Visible arg set to "NO"?

I'm un-sure as to how to go forward still though, so let me explain further....

Text Box (txtSearchBox) is an unbound txt box, at it's On Change Event it has the following EVent Procedure:

Me.lstDealers.Requery
Me.Refresh
Me.txtSearchBox.SelStart = Me.txtSearchBox.SelLength

a user at present can only type in a Dealer Name to conduct search.


List Box (lstDealers) is an unbound List Box, it's row source is taken from the SQL Query that I believe I have messed up...

SELECT Dealers.Client, Dealers.dealerID FROM Dealers WHERE (((Dealers.dealerID) Like Forms!frmDealers!txtSearchBox & "*")) Or (((Dealers.Client) Like Forms!frmDealers!txtSearchBox & "*"));


at present as I type in my text into txtSearchBox it filters through to the desired Client (Dealer) or I can simply scroll through the List Box, once I have the Required Record filtered (by typing or scrolling) I dbl/click on the entry in the list box:

Private Sub lstDealers_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmDealers1", acNormal, , "client = '" & Me.lstDealers & "'"

End Sub


this opens my 2nd form with the Client Info auto-filled and ready for me to work with....

I think I might be understanding Pat wrong here somewhere (sorry to be dense) I have tried adding a hidden Lixt Box who's row source and bound column was dealerID but aint having much success and not to sure about what u meant about adding a second text box?

any help in clarifying the help Pat offered would be great if possible

many thanks

Zym

 

[Pat Hartman]

You can use list boxes rather than combos. The method in this case is the same although I prefer combos.

You said you wanted to look up by id or by name. That requires two controls. I suggested the hidden unbound text box because that simplifies your query so that it only needs to refer to a single ID field. Both combos (or listboxes) will contain the id field. Since that is the primary key, that is what your query should use as its criteria. When a row is chosen from EITHER combo, the hidden text box is populated with the id field.

 

[zym1ne]

U R A STAR

Pat ur a star mate...



Got it all working now on that score, ticks over like a dream can't say thanks enough...

Still alot of bugs in my DB though, having a hellish time trying to find answers to some threads of mine in ''Forms'' for the best and most effective way to filter info through from two tables...

Maybe you can help sum time mate ????

Thanks for the oinvaluable pointers on this though

zym

 

[Rich]

Mate's actually a Sheila

 

[zym1ne]

no offence intended

Me and my big mouth

Sorry Pat never wanted to cause offence...

And here's me needing all the help I can get aswell...how does it go...open mouth and insert foot here!!!!!

Helllllllllpppppppppp...!!!!

 

[Pat Hartman]

Not a problem

 

[zym1ne]

another obstacle...

Hi once again all...

Dizzy with happiness at the earlier assistance from the forums here I was getting on with life, when I discovered a problem with my DB.

I've returned to this string with it being directly related and hope someone might be able to point out my mistake here

I have a search form that works a treat and via the unbound txt field I can type in a ID# or Client name, an unbound List Box will filter through and display the correct record (from tblDealers)...

If I dbl click on the selected record it opens a form containing the relevant entries for this Client, with me so far...?

Now, I have a form that allows addtions to the tblDealers via straight input in to this form (frmDealerAdd) and this works fine, add a dealer and all the info is displaying in the Dealers tbl...
However if I try and search for any new entries I've inputed into my tbl via the frmDealerAdd it returns a run time error 3075

syntax error (missing operator) in query expression 'client = 'Whatever new name I entered''

the code used for the event is:

DoCmd.OpenForm "frmDealers1", acNormal, , "client = '" & Me.lstDealers & "'"

so it appears that any dealers I add cannot be accessed by my search form at present...

Any help pointing out what I need to do woudl be great folks as this is a real headache.

Many thanks and smiles

Zym

 

[zym1ne]

Am I stupid or wot...? DUH!

Well discovered the answer to my problem...

Forgot that VB so sensitive...the examples I was searching on where all containing comma's in their name field (Auto's, Dave's) and as a result was causing error in the lookup field or something, remove the comma's and problem goes away...yahooo