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

Is Unbound Search Form the best solution?

P: 16
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?
Nov 26 '06 #1
Share this Question
Share on Google+
31 Replies


NeoPa
Expert Mod 15k+
P: 31,419
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.
Nov 27 '06 #2

Expert 5K+
P: 8,434
Wouldn't it be better to use the same type of IIF( ) for the first name that you used for last name?
Nov 27 '06 #3

P: 16
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.
Nov 27 '06 #4

P: 16
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.

Expand|Select|Wrap|Line Numbers
  1. PARAMETERS [Forms]![Search_Form]![S_Last] Text ( 255 ), [Forms]![Search_Form]![S_First] Text ( 255 ), [Forms]![Search_Form]![S_ID] Long;
  2. 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
  3. FROM ASDS_Leads
  4. 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] & "*")))
  5. ORDER BY ASDS_Leads.Sell_Last;
  6.  
Nov 27 '06 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Nov 27 '06 #6

NeoPa
Expert Mod 15k+
P: 31,419
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).
Nov 27 '06 #7

NeoPa
Expert Mod 15k+
P: 31,419
Change your WHERE clause to :
Expand|Select|Wrap|Line Numbers
  1. WHERE ((Nz([Forms]![search_Form].[S_ID],0) In(0,[ID]))
  2.   AND ([Sell_Last] Like Nz([forms]![Search_Form].[S_Last],'') & '*')
  3.   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.
Nov 27 '06 #8

P: 16
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)
Nov 27 '06 #9

NeoPa
Expert Mod 15k+
P: 31,419
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.
Nov 27 '06 #10

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

Expand|Select|Wrap|Line Numbers
  1. WHERE ((Nz([Forms]![search_Form].[S_ID],0) In(0,[ID]))
  2.   AND ([Sell_Last] Like Nz([forms]![Search_Form].[S_Last],'') & '*')
  3.   AND ([Sell_First] Like Nz([Forms]![Search_Form].[S_First],'') & '*'))
Nov 27 '06 #11

P: 16
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.
Nov 27 '06 #12

Expert 5K+
P: 8,434
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.
Nov 27 '06 #13

NeoPa
Expert Mod 15k+
P: 31,419
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.
Nov 27 '06 #14

NeoPa
Expert Mod 15k+
P: 31,419
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.
Nov 27 '06 #15

Expert 5K+
P: 8,434
...other threads include similarly different approaches.
Bwahahaha... :D

Don't get me wrong, I agree completely. But that is just such a cool quote...
Nov 27 '06 #16

Expert 5K+
P: 8,434
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.
Good point. That's what I do in building the searches for my fairly large database (55 million records and counting). My input form has a whole bunch of datetime pickers and so on. by default I set the values to match the whole range in the data. Then in the code behind the "go" button, I build WHERE clauses only for those which are actually changed by the user to restrict the range.

It makes a huge difference. The scans used to take ages because fields which were not specified were matching everything, then being filtered further by other fields.
Nov 27 '06 #17

NeoPa
Expert Mod 15k+
P: 31,419
Bwahahaha... :D

Don't get me wrong, I agree completely. But that is just such a cool quote...
It's an example of an oxymoron. Is that why?
Nov 27 '06 #18

Expert 5K+
P: 8,434
It's an example of an oxymoron. Is that why?
Maybe - all I know is, it gave me quite a laugh.
Nov 27 '06 #19

P: 16
Good point. That's what I do in building the searches for my fairly large database (55 million records and counting). My input form has a whole bunch of datetime pickers and so on. by default I set the values to match the whole range in the data. Then in the code behind the "go" button, I build WHERE clauses only for those which are actually changed by the user to restrict the range.

It makes a huge difference. The scans used to take ages because fields which were not specified were matching everything, then being filtered further by other fields.
This makes a LOT of sense to me! Unfortunately, I don't get to work on my access front end as much as I'd like, so I have to try these things when I get a chance. I'm going to try the NZ function where the null returns: like "*". I'll also try the OR function. I also thought about changing the IN fuction with [Forms]![Search_Form]![S_ID] instead of all [ID].

If anyone cares, I'll report back what I learn (which may not be for a few days). However, I would still LOVE to know why my original syntax works fine if I run the query, but fails when I run it from the SEARCH_FORM.

Thanks to All!
Nov 29 '06 #20

Expert 5K+
P: 8,434
... If anyone cares, I'll report back what I learn (which may not be for a few days).
We always like to hear how things turned out.

However, I would still LOVE to know why my original syntax works fine if I run the query, but fails when I run it from the SEARCH_FORM.
That probably makes at least 3-4 of us.

Maybe you can attach a copy of the form or something, some time. There might be some subtle little thing which means it's not the same syntax each way, or something. For example (this is completely made up) what if clicking causes the form to get ready for a new record, and clears out some fields which you thought were populated.

Or something...
Nov 29 '06 #21

P: 16
We always like to hear how things turned out.

That probably makes at least 3-4 of us.

Maybe you can attach a copy of the form or something, some time. There might be some subtle little thing which means it's not the same syntax each way, or something. For example (this is completely made up) what if clicking causes the form to get ready for a new record, and clears out some fields which you thought were populated.

Or something...
Were it only that simple. The form is very basic with three Text boxes for input of last name (format text), first name (format text) and ID (format long integer). When the user clicks the SEARCH button, it simply opens up another form that gets its data from the query. If the user input data to the name fields, the search form works, if they input data to the ID field, it returns roughly 139,000 of about 165,000 records. It should only return a single record.
Nov 29 '06 #22

Expert 5K+
P: 8,434
Were it only that simple. The form is very basic with three Text boxes for input of last name (format text), first name (format text) and ID (format long integer). When the user clicks the SEARCH button, it simply opens up another form that gets its data from the query. If the user input data to the name fields, the search form works, if they input data to the ID field, it returns roughly 139,000 of about 165,000 records. It should only return a single record.
Perhaps there's a way to hit it from another angle - can you identify anything those 139,000 records have in common?
Or to put it another way, what is different about the other 26,000? Presumably it has to be related to those three fields.
Nov 29 '06 #23

Expert 5K+
P: 8,434
I'm just trying to pick apart your query and simplify it in order to understand it. Can you just check whether this looks right? It's from before you started playing with the NZ function, so may need updating. Plus I've removed the table name from all the fields, things like that, and made a real mess of the formatting. I don't expect this to be any use to you, it's just an "educational aid" for my own use.
Expand|Select|Wrap|Line Numbers
  1. SELECT 
  2.  
  3.   ID, Sell_Last, Sell_First, Sell_Street, Sell_Str2,
  4.   Sell_City, Sell_St, Sell_Zip, Sale_Date, Sale_Price,
  5.   Loan_Amnt, LTV, Deed_Type, Loan_Maturity,
  6.   Prop_Street, Prop_Str2, Prop_City, Prop_St, Prop_Zip,
  7.   Prop_Use1, Prop_Use2, Resdnt_Use, Owner_Occ, Notes,
  8.   Drop, DropRsn, LastMailDate, CallbackDate, CallPerson,
  9.   HPhone, WPhone, CPhone, email, BestTime, PropType,
  10.   PropType2, EstValue, CreditScore, LienPos, PmtsCurrent,
  11.   NumPmtsMade, curbal, [Int Rate], [P&I], BalloonAmt,
  12.   DateBalloon, DateNxtPmt, DateFirstPmt, Term, src,
  13.   QuoteNote, Int_Only, AddrChgDate, PhoneAppend,
  14.   PADNCFlag, PADup, PAConf, CCStatus
  15.  
  16. FROM ASDS_Leads
  17. WHERE 
  18.       ID=[Forms]![search_Form]![S_ID]
  19.       OR
  20.       (
  21.          Sell_Last Like
  22.             IIf(
  23.                   IsNull([forms]![Search_Form]![S_Last]),
  24.                   Null,
  25.                   [Forms]![Search_Form]![S_Last] & "*"
  26.                 )
  27.          AND
  28.          Sell_First Like ([Forms]![Search_Form]![S_First] & "*")
  29.       )
  30. ORDER BY Sell_Last;
Nov 29 '06 #24

P: 16
I'm just trying to pick apart your query and simplify it in order to understand it. Can you just check whether this looks right? It's from before you started playing with the NZ function, so may need updating. Plus I've removed the table name from all the fields, things like that, and made a real mess of the formatting. I don't expect this to be any use to you, it's just an "educational aid" for my own use.
Well, everyone, I tried everything. The "OR" recommendation worked exactly like the "IN" solution. It worked, but was extremely slow in returning the result if an ID was input. My idea of using the form's [S_ID] input in the "IN" didn't work. I also tried playing around with converting the ID to a text field in a few different places (str([ID]) to see if it handled the ID any better as a string rather than integer. No change. Finally, I copied Killer42's "where" section back into my query and that restored it perfectly to my original problem. It works GREAT if I just run the query and manually input, but if I input the ID into the Search_Form, it returns those 139,000 records. I haven't yet analyzed what is excluding the rest of the records, but that sounds like an avenue worth pursuing. This is ridiculous. It should just WORK!

Thanks to everyone for the ideas. This would probably be SO much easier if I could write the VB code. But I REFUSE to give up hope.
Dec 1 '06 #25

Expert 5K+
P: 8,434
...Finally, I copied Killer42's "where" section back into my query and that restored it perfectly to my original problem. It works GREAT if I just run the query and manually input, but if I input the ID into the Search_Form, it returns those 139,000 records. ...
Well, I hope I'm not just reiterating earlier posts, but it seems to me that we have quite definitely pinned down the problem to an errant comparison (last name and first name). The next step, in my opinion, would be to pull out first one name and then the other from the WHERE clause, and see whether it is the last name, the first name or the combination which produces the problem. For that matter, what if you remove the ID part and just leave the names in there. Does it still do the same if you don't enter a name? I'd hope so, as that would confirm the "name hypothesis". It's useful to be able to strip things down to just the part which actually has a problem.

I realise that it should work the same from the form as when done "manually", but first lets pin down exactly what is producing the bug. There's a fair chance that will give us the how/why. My guess is it's something related to form -vs- database fields, and Nulls. Nulls do have a nasty tendency to propagate where you don't want them.

To be honest, though it can be frustrating, I rather enjoy a good debugging challenge. Zeroing in on the bug like this keeps the brain in condition. :)
Dec 1 '06 #26

P: 16
The next step, in my opinion, would be to pull out first one name and then the other from the WHERE clause, and see whether it is the last name, the first name or the combination which produces the problem. For that matter, what if you remove the ID part and just leave the names in there. Does it still do the same if you don't enter a name? I'd hope so, as that would confirm the "name hypothesis". It's useful to be able to strip things down to just the part which actually has a problem.

I realise that it should work ...
. . . and I now realiz]e that you are helping me from another country. Neat!

Interesting thoughts. What I discovered is that the only way I can make the ID part of the query work from the form is by deleting BOTH the first name and last name criteria. Either one prevents the ID from working.

That gave me another interesting thought. I tried adding a wildcard to the lastname and first name when an ID was selected as follow:

Expand|Select|Wrap|Line Numbers
  1. WHERE (((ASDS_Leads.ID)=[Forms]![search_Form]![S_ID]) 
  2. AND ((ASDS_Leads.Sell_Last) Like "*")
  3. AND ((ASDS_Leads.Sell_First) Like "*")) OR
  4.  . . . and the names part here
  5.  
It made no difference.
Dec 1 '06 #27

P: 16
NeoPa and Killer42! I GOT IT! I finally made it work, using a combination of ideas you gave me.

I was never fond of the NZ syntax, because I thought my original code was simpler and just couldn't understand why it wouldn't work. I went back to the message where NeoPa said I should do it with an AND rather than an OR, and applied similar logic to his NZ code as follows:
Expand|Select|Wrap|Line Numbers
  1. WHERE (((ASDS_Leads.ID) Like IIf(IsNull([forms]![Search_Form]![S_ID]),"*",[Forms]![Search_Form]![S_ID]))
  2. AND ((ASDS_Leads.Sell_Last) Like IIf(IsNull([forms]![Search_Form]![S_Last]),"*",[Forms]![Search_Form]![S_Last] & "*"))
  3. AND ((ASDS_Leads.Sell_First) Like IIf(IsNull([forms]![Search_Form]![S_First]),"*",[Forms]![Search_Form]![S_First] & "*")))
  4.  
As you can see, I simply said if a search field is null, then return everything for that field. The AND function limits what actually comes back.

It now works perfectly.

Thanks to both of you!
Dec 2 '06 #28

NeoPa
Expert Mod 15k+
P: 31,419
Well, fancy that! I do know what I'm talking about :).
Seriously, I'm glad you'e got a working solution.
Dec 3 '06 #29

Expert 5K+
P: 8,434
Well, fancy that! I do know what I'm talking about :).
Wow! Who would have thought? ;)

Seriously, I'm glad you'e got a working solution.
Yep. It's gratifying when bugs like this are finally tracked to their lair and slain.

As for the different country thing, yes, I'm in Australia. If it was the "s" that tipped you off, then you might be surprised by some of the other members, too. :)

Just remember, in debugging, the critical point (usually) is to narrow down precisely what (and where) the bug is, before trying to solve it.
Dec 3 '06 #30

P: 16
Wow! Who would have thought? ;)

Yep. It's gratifying when bugs like this are finally tracked to their lair and slain.

As for the different country thing, yes, I'm in Australia. If it was the "s" that tipped you off, then you might be surprised by some of the other members, too. :)

Just remember, in debugging, the critical point (usually) is to narrow down precisely what (and where) the bug is, before trying to solve it.
Well, here we are 2 months later. I now have about 10 people using the form, and through use we've discovere my solution doesn't work if the FNAME field is null. I tried a whole bunch of stuff to no avail. I give up! I'm going back to separate queries by Name and by ID. Extra programming, but at least it works!
Feb 2 '07 #31

Expert 5K+
P: 8,434
Well, here we are 2 months later. I now have about 10 people using the form, and through use we've discovere my solution doesn't work if the FNAME field is null. I tried a whole bunch of stuff to no avail. I give up! I'm going back to separate queries by Name and by ID. Extra programming, but at least it works!
I'm really sorry to hear there's still a problem, but it's good that you have a workaround.

Even if it doesn't turn out to be any practical use to you, it would still be nice to work out exactly what's wrong.

However, as far as I can tell this is the first time in this thread that FNAME has been mentioned. Is it a new name for ASDS_Leads.Sell_First?
Feb 2 '07 #32

Post your reply

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