Searching Problem

[RuralGuy]

How big is the zip file? Have you done a Compact and Repair?

 

[RuralGuy]

You can send me email at abunch at wild blue dot net without any spaces.

 

[lauriphi]

got it to post

i abbreviated the file size and got it to post so as to avoid clogging your inbox.. let me know what you think, and i appreciate so much your help.

 

[RuralGuy]

Start by going to Tools>Options - General Tab

Turn off Name AutoCorrect! It is responsible for numerable corruptions. While there turn on Compact on Close. Close your db and open and close 1 more time. Check out the size of the db!!!

Now you need to bind the form to Query1 or RecordSetClone blows up. The way you have it there is no RecordSet to reference. I'm still working out a few problems but that will get you started.

 

[RuralGuy]

Here's my adjusted AfterUpdate code:

Private Sub QuickSearch_AfterUpdate()
    
DoCmd.Requery
Me.RecordsetClone.FindFirst "[Name] = '" & Me![QuickSearch] & "'"
If Not Me.RecordsetClone.NoMatch Then
   Me.Bookmark = Me.RecordsetClone.Bookmark
Else
   MsgBox "Could not locate [" & Me![QuickSearch] & "]"
End If
End Sub

It seems to function as one would expect. All of the fields in the table are text so I removes that Str() stuff. That pretty much cleared up all of the issues I could find.

hth

 

[lauriphi]

"Now you need to bind the form to Query1 or RecordSetClone blows up. The way you have it there is no RecordSet to reference. I'm still working out a few problems but that will get you started."

apologies, but can you elaborate a bit here?

 

[RuralGuy]

Open the Search form in design mode. Right click the bar at the top and go to properties. Select the Data tab and set the Record Source to "Query1". Now the form is "Bound" to Query1!

 

[lauriphi]

wow...that was a pretty dumb mistake on my part

all set now! truly appreciate all of your help!

 

[lauriphi]

crap, now im having troubles in that when you highlight the one individual row in the search results the data that shows up in the other portion of the form doesnt change when you click a different record in the search results... thinking it has something to do with the way that the query identifies the record.. will look in to it

 

[RuralGuy]

Well you are certainly welcome. Glad I could be of assistance.

 

[RuralGuy]

OK, I spoke too soon!

Here's your db back with my changes. It seems to work ok for me.

Post back if you have questions.

 

[lauriphi]

is there a way that you know of to make the association between what is typed in in the search box, the search results and then the information that is displayed in the text boxes below the search results more precise in their query? i think that what is happening is when you type in a search query the records are found jsut fine in the search results, but when you highlight a specific record it does not necessarily display that exact record in the text boxes but instead one that is very similar but not EXACTLY the same (slight change in purchase order number or differernt component supplied is displayed instead)... i am thinking it has to do with how the search terms, the way that the query takes those and then most definitely the way that the VBA code associates the information in the search string with the actual records in the database themself... HOWEVER, now it is jsut fixing that... grr stuck again

 

[lauriphi]

i think the difference is that the one that i sent to you has a significantly reduced number of records and therefore when i populated my version with the the thousands more i run in to problems --- the association between what is typed in to the search box and how that language is associated with the original database data is where the problem lies... i THINK.

 

[lauriphi]

ok i take that back slightly....

this perhaps better explains the problem:

when i type in a search command "x" it will return results matching "x." lets say there are 18 records returned in the window as search results. now, lets say i want to edit the information contained in the 14th record... i go to click on the 14th record and the information from the 1st record still shows up in the text boxes below the search results. i click any record in the 18 search results and it it still displays information from the 1st record, however, if i use the record selectors in the bottom of the form it will cycle through the correct search results just fine.. does that make sense?

 

[RuralGuy]

I think I understand your symptom but I can't duplicate it here. How about sending me a new db with just "Main Table" in it all zipped up. Send to "a bunch at wild blue dot com" without the spaces. You have turned off "Name Auto Correct" and checked "Compact on Close" haven't you?

 

[RuralGuy]

If you go into Query1 and change the 4 Conditions from:

Like "*" & [Forms]![Search]![Search2] & "*"

To:

Like [Forms]![Search]![Search2] & "*"

then the fields have to begin with the characters you are typing.

Right now they just have to occur somewhere in the field in the sequence you are typing in. THat might be what you mean when you say more precise.

What version of Access are you using and what OS?

 

[da poet]

Thanks much for the post for the after update. Myu problem is slightly different, i got a type mismatch error so i changed the field from number to text and that works except now i get a message when i click the record that the record is not found .

Please advise

 

[RuralGuy]

How about posting the QuickSearch_AfterUpdate code.

 

[lauriphi]

i tried that suggestion earlier that you had on changing the query conditions without any luck...

im gonna keep trying at it today and perhaps send you an email later today with some thoughts if i cant get this ironed out.

im on windows xp, running access 2000

 

[lauriphi]

rural guy, i am still having the same issue that i was having about a week ago with this search form and was wondering if you could help me out. i tried to send it off to you but the email would not go through as your server kept denying it.. curious to see if you had any thoughts. i appreciate any help very much!