Search button stopped working, BAD OMEN? 1

[Maqu1na]

Hi folks, we have a problem here, maybe you can help us.

We have a main table, with a lot of columns. Two are of special relevance. One is the Id,primary key, which is an auto incremental integer. The other is client number, not a key, but many times is the only info that we got to retrieve data from the table, so we do the searching using that field. We have a button on the form -created with the wizard- that worked perfectly, until two days ago.

From one day to another it does not retrieve the data to the form anymore. It works exactly the same way as the search button for the id, the only difference is that this works great! -at least, at the moment-

So, we are using Access '97. I heard there are a lot of trouble with indexation.

Could be the problem, that the column client number wasn't indexed? I said was, because when the button on the form stopped working, i checked the table design, and changed indexation from no one, to one that allows duplicated entries.

Could be this a Bad Omen, having problems with the other search button on the next days?

Thanks for your time!

 

[MajP]

can you post the button code?

 

[Maqu1na]

Sure MajP, thanks for your post.
HC is a textbox on the form, BuscaHc is the search button.

This is the code:

Private Sub BuscaHC_Click()
On Error GoTo Err_BuscaHC_Click
Me.HC.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
Exit_BuscaHC_Click:
Exit Sub

Err_BuscaHC_Click:
MsgBox Err.Description
Resume Exit_BuscaHC_Click

End Sub

 

[MajP]

When you say it stopped working does the Find window open, but it does not find a record? Or does the find window not open.

although this is unlikely to be your problem replace
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
with
DoCmd.RunCommand acCmdFind

Now if the find window does not open. Check the properties of the button. Find the Click event. Ensure it says [EventProcedure].

If the find window does open look at the fields
Lookin
Match
Search
Search as Formatted
Match Case.

Are they what you want?

If you want a more advanced search we can help. Normally you would have a combobox that lists all of your client numbers. Select the desire client number and go to that record.

 

[Maqu1na]

I excuse for the delay of my answer, we're closed on weekends and yesterday was a holiday.

The find windows opens ok, but it hangs after you search for a value.

"although this is unlikely to be your problem replace
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70
with
DoCmd.RunCommand acCmdFind" I tried, but it didn't worked either.

The options on the find window are what we want, and that's the strange thing, they never changed! From one day to another it didn't work anymore. Fun thing is we have a copy of the database -pretty old- and it still works on that version.

We have no clue about this one. Combobox... maybe could be good, but we have hundreds of clients here...

Can we start from there? Thanks for all MajP, we really appreciate it.

 

[MajP]

1) There may be some corruption of the DB. I would create a new blank database. Then import all the forms, queries, modules, and reports into your new db. Compact and repair the new database. Compile the code in the new database.

a Combobox can handle thousands of clients with no problem. Use a query to load the combobox from your existing names. When you type into a combobox it automatically scrolls to the name/part of name that matches, or you can scroll down. I am using one now with 15,000 entries and can find any record in a second or less without problem.

 

[Maqu1na]

Well, we are arriving at something interesting here.

I thought the same as you, it's very likely to be a 'corruption' problem. But there is an issue here: the main table of the database, is used to create invoices. Invoices are numerated with an auto-incremental... and when you cut&paste all the info on a blank copy of the table, the id will not correspond to the original.

Another problem: i do not have experience on Access. When you say 'compile the code in the new database', do you refer to the modules? Because all the code in the application is written on the forms. And maybe that's wrong.

We are studying the option of the combobox, i think it's a great idea. Thanks for your tips MajP.

 

[MajP]

To import into a new database there is a feature to do this. Do not cut and paste. That would probably be real bad especially with autonumbers.

in 2003 or earlier I believe it is
File
Get External Data

In 2007 there is an import from the External Data ribbon.

In the vba window there is a menu choice
Debug
Compile

 

[MajP]

Also the combo box search should be a Wizard option when you add the combobox to the form. Look at putting it in the header, and make sure to leave it unbound. In 2007 it is a wizard option.

 

[Maqu1na]

Well, first of all, i apologise for the delay of my post, we had a lot of trouble at office with internet -among other things-

We have found the problem: we had an event after the textbox lost the focus, implementing an algorithm used to determine check digit:

http://en.wikipedia.org/wiki/Check_digit

It was added after the search button was implemented, and after changing this, the search started to work perfectly again.

Thank you very much MajP for your valuable help.