Is Unbound Search Form the best solution?

[DWolff]

I'm somewhat new to Access, and have no VB experience at all (except for modifying existing code where obvious).

I built a data entry form that works fine. Typically, a client will call in and the user has to find his record to add or modify existing data. Originally, I built the form based on a query with an input criteria for the last name and first name. The Last_Name critera is Like [What is Last Name?]&"*" so that partial names will work. The query also includes a similar criteria for first name.

Then, I needed to enable the search by Client_ID. I built a new query for this, because I didn't want the user to have to see the pop-up dialogs for Name. The problem with this approach is that I had to duplicate the existing data entry form based on the second query instead of the first. I don't want to have to maintain two identical forms.

I decided to try an unbound search form with these three fields, so that I could use a single query and form. It almost works. The query works fine when called directly, but when I use the search form to call the data entry form based upon the query, it goes awry.

My query has the following criteria:

Client_ID: [Forms]![search_Form]![S_ID]

Then, on a separate criteria line (meaning OR)

Last_Name: Like IIf(IsNull([forms]![Search_Form]![S_Last]),Null,[Forms]![Search_Form]![S_Last] & "*")

AND

First_Name: Like ([Forms]![Search_Form]![S_First] & "*")

My thinking was that if the last name from the Search form is left blank (Null), it will return nothing based on the NAME line. It works fine when I query directly. If I input name data, it only returns appropriate names records, and if I input a Client_ID, it only returns that Client_ID. However, if I use the Search_Form, only the NAME criteria works. If I input a Client_ID, it returns all names instead of none.

My search form consists only of these three fields and they are unbound. I tried the query with and without the field parameters specified (name fields=text, Client_ID field = long integer) and it made no difference.

Can anyone help?

[NeoPa]

I've gone through this and can't see anything amiss.
Perhaps if you post the SQL of the query you're using that might give us some more clues.
BTW to do that, design the query then switch views to SQL.
Copy the contents of that window and post in here between [ C O D E] & [ / C O D E] tags.

[Killer42]

Wouldn't it be better to use the same type of IIF( ) for the first name that you used for last name?

[DWolff]

Wouldn't it be better to use the same type of IIF( ) for the first name that you used for last name?

I see no benefit to that, since a the first name criteria is on the same line as the last name criteria, and a null last name is enough to return no values from the name criteria of the query.

[DWolff]

I've gone through this and can't see anything amiss.
Perhaps if you post the SQL of the query you're using that might give us some more clues.
BTW to do that, design the query then switch views to SQL.
Copy the contents of that window and post in here between [ C O D E] & [ / C O D E] tags.

Thanks for your help. Please keep in mind two things already mentioned:
1) I tried this with and without the PARAMETERS statement.
2) The query works FINE. It's only when I call it via the Search_Form. The button on the Search_Form opens my Lead_Form, whose data source is this query.

PARAMETERS [Forms]![Search_Form]![S_Last] Text ( 255 ), [Forms]![Search_Form]![S_First] Text ( 255 ), [Forms]![Search_Form]![S_ID] Long;
SELECT ASDS_Leads.ID, ASDS_Leads.Sell_Last, ASDS_Leads.Sell_First, ASDS_Leads.Sell_Street, ASDS_Leads.Sell_Str2, ASDS_Leads.Sell_City, ASDS_Leads.Sell_St, ASDS_Leads.Sell_Zip, ASDS_Leads.Sale_Date, ASDS_Leads.Sale_Price, ASDS_Leads.Loan_Amnt, ASDS_Leads.LTV, ASDS_Leads.Deed_Type, ASDS_Leads.Loan_Maturity, ASDS_Leads.Prop_Street, ASDS_Leads.Prop_Str2, ASDS_Leads.Prop_City, ASDS_Leads.Prop_St, ASDS_Leads.Prop_Zip, ASDS_Leads.Prop_Use1, ASDS_Leads.Prop_Use2, ASDS_Leads.Resdnt_Use, ASDS_Leads.Owner_Occ, ASDS_Leads.Notes, ASDS_Leads.Drop, ASDS_Leads.DropRsn, ASDS_Leads.LastMailDate, ASDS_Leads.CallbackDate, ASDS_Leads.CallPerson, ASDS_Leads.HPhone, ASDS_Leads.WPhone, ASDS_Leads.CPhone, ASDS_Leads.email, ASDS_Leads.BestTime, ASDS_Leads.PropType, ASDS_Leads.PropType2, ASDS_Leads.EstValue, ASDS_Leads.CreditScore, ASDS_Leads.LienPos, ASDS_Leads.PmtsCurrent, ASDS_Leads.NumPmtsMade, ASDS_Leads.curbal, ASDS_Leads.[Int Rate], ASDS_Leads.[P&I], ASDS_Leads.BalloonAmt, ASDS_Leads.DateBalloon, ASDS_Leads.DateNxtPmt, ASDS_Leads.DateFirstPmt, ASDS_Leads.Term, ASDS_Leads.src, ASDS_Leads.QuoteNote, ASDS_Leads.Int_Only, ASDS_Leads.AddrChgDate, ASDS_Leads.PhoneAppend, ASDS_Leads.PADNCFlag, ASDS_Leads.PADup, ASDS_Leads.PAConf, ASDS_Leads.CCStatus
FROM ASDS_Leads
WHERE (((ASDS_Leads.ID)=[Forms]![search_Form]![S_ID])) OR (((ASDS_Leads.Sell_Last) Like IIf(IsNull([forms]![Search_Form]![S_Last]),Null,[Forms]![Search_Form]![S_Last] & "*")) AND ((ASDS_Leads.Sell_First) Like ([Forms]![Search_Form]![S_First] & "*")))
ORDER BY ASDS_Leads.Sell_Last;

[nico5038]

Personally I prefer to use a mainform with a datasheet subform showing all "objects". On this datasheet subform the user can use the right-click poup menu to sort and filter the rows.
In your case e.g. a filter for the city could narrow down the number enough to show the rows with the client, but also a partial match with a LIKE *john* is possible.

Just check my "Right-click Instruction" at www.geocities.com/nico5038 I give all of my users. It saved me a lot of coding and the users feel "in control"...

Nic;o)

[NeoPa]

I see no benefit to that, since a the first name criteria is on the same line as the last name criteria, and a null last name is enough to return no values from the name criteria of the query.

That notwithstanding , it is still possible to have a Null in the field, going simply on what you've shared to date (you may know more - we don't).

[NeoPa]

Change your WHERE clause to :

WHERE ((Nz([Forms]![search_Form].[S_ID],0) In(0,[ID]))
  AND ([Sell_Last] Like Nz([forms]![Search_Form].[S_Last],'') & '*')
  AND ([Sell_First] Like Nz([Forms]![Search_Form].[S_First],'') & '*'))

The three filters MUST be ANDed, but to handle it correctly they must each be written as 'If a value is entered then match it, otherwise let ALL through'. I'm unable to test this myself so you'll need to do that and fix any typos if necessary (or repost with results whatever).
This may be the right way to do it, but bear in mind Nico's response, it wouldn't work for my users as they are not computer literate enough, but if yours are, it's certainly worth consideration.

[DWolff]

As NeoPa indicated further (above), most of my user community is not up the task of doing the filters. Then they might have to remember to change or delete the filter. That's asking too much!

Personally I prefer to use a mainform with a datasheet subform showing all "objects". On this datasheet subform the user can use the right-click poup menu to sort and filter the rows.
In your case e.g. a filter for the city could narrow down the number enough to show the rows with the client, but also a partial match with a LIKE *john* is possible.

Just check my "Right-click Instruction" at www.geocities.com/nico5038 I give all of my users. It saved me a lot of coding and the users feel "in control"...

Nic;o)

[NeoPa]

Please bear in mind that Nico has devoted some of his spare time to try to help and respond to your question.
Our experts (of which Nico is a most valued one), deserve your thanks whether their answer, on any particular occasion, is exactly what's required or not.

[DWolff]

Very cool. I was unaware of the NZ function. I understand its purpose, though your syntax on the ID field took a bit of contemplation before I understood what it was doing.

The syntax you provided works from both the query and the Search_Form (though I still don't understand why my syntax didn't work from the Form). HOWEVER, it takes an unacceptably long time (about a minute) to get a result if I input an ID (perhaps because it is looking for a match in every single record?). A Name input returns results very quickly.

Any ideas? Is there any way to fix the Search_Form so it works with my original query (which runs very quickly)?

Change your WHERE clause to :

WHERE ((Nz([Forms]![search_Form].[S_ID],0) In(0,[ID]))
  AND ([Sell_Last] Like Nz([forms]![Search_Form].[S_Last],'') & '*')
  AND ([Sell_First] Like Nz([Forms]![Search_Form].[S_First],'') & '*'))

[DWolff]

If I, in any way, did not seem appreciative, it was certainly not intentional. I SINCERELY appreciate anyone's help and thoughts!

Please bear in mind that Nico has devoted some of his spare time to try to help and respond to your question.
Our experts (of which Nico is a most valued one), deserve your thanks whether their answer, on any particular occasion, is exactly what's required or not.

[Killer42]

The syntax you provided works from both the query and the Search_Form (though I still don't understand why my syntax didn't work from the Form). HOWEVER, it takes an unacceptably long time (about a minute) to get a result if I input an ID (perhaps because it is looking for a match in every single record?). A Name input returns results very quickly.

I'd just like to hazard a guess here, that the In( ) function might be the cause of the slowdown. Perhaps Access (not too surprisingly, in my opinion) does it less efficiently than might be the case. You could test that by trying an OR in place of the In( ), I suppose.

[NeoPa]

Personally I've never found In() to cause performance problems.
What may do, is the fact that all three separate WHERE elements must be processed.
It may be possible to include only the relevant one(s) if adjusted via VBA.

BTW, sorry if I misinterpreted your response. We do have members on here with an extraordinary attitude to those that help. It certainly doesn't sound as if that describes you.

[NeoPa]

Very cool. I was unaware of the NZ function. I understand its purpose, though your syntax on the ID field took a bit of contemplation before I understood what it was doing.

Yes, I understand where you're coming from.
Sometimes the ability to test what you actually want is restricted by the structures available, so I've had to come up with unusual answers. I don't think I'm unique in this though, other threads include similarly different approaches.