Programming help

  stylehurst 09:57 16 Aug 2006

Is there anybody out there who can help a novice pensioner programmer with a problem?
I am trying to find a record in an MSAccess database,
For example if I want to find the name "HOY" in the DEF_SUR field then code
Private Sub Command9_Click()
DataEnvironment1.rsCommand1.Find "DEF_SUR = 'HOY'"
End Sub
will find the required entry.

However if I want to make the search more general & enter the following code:
Private Sub Command9_Click()
Dim MyCriteria, Prompt
Prompt = "Please enter Defendants Surname."
MyCriteria = InputBox(Prompt)
DataEnvironment1.rsCommand1.Find "DEF_SUR = 'MyCriteria'"
End Sub
the search fails, I know that the variable MYCriteria is accepting the input box information.
If anybody can tell me where I am making a mistake it would be appreciated

  silverous 10:27 16 Aug 2006

I think you will find (if you'll pardon the pun) that it is searching for the word MyCriteria rather than the result of MyCriteria's value.

I've never used that particular construct but general programming experience in VBA and other languages tells me that you should:

Try appending MyCreteria into the find e.g.

DataEnvironment1.rsCommand1.Find "DEF_SUR = '" & MyCriteria & "'"

See how you go with that!

  stylehurst 11:03 16 Aug 2006

Hi Silverous

Tried you suggestion but it doesn't seem to work.

  silverous 11:58 16 Aug 2006

Did you copy and paste it?

The quoting is very important.

i.e. after = is a single quote followed by a double quote i.e.

' "

Then after Mycriteria & there is a double quote followed by single quote followed by double quote i.e.

" ' "

Don't space it out like that - I am just doing that to show you what is happening. I would copy and paste my line into your code to be safe.

When you say doesn't seem to work what happens?

  stylehurst 14:09 16 Aug 2006

Hi Silverous
Thanks, it worked perfectly.
You have made an old man, very happy.
That hint is now stored in my repository of useful program tips.

  silverous 15:09 16 Aug 2006


No probs at all, enjoy this kind of thing. Keep the requests coming if you need more help.

Can I ask, our of interest, what the database is for?

  stylehurst 14:33 17 Aug 2006

My other interest is genealogy, and I spend a lot of my retirement time, in conjunction with others, transcribing and indexing various records.In the pastI have managed to put together simple Visual Basic programs, which would accept just text input, which I could then convert into a database (usually visual dBase). However, the next project, involving transcription of quarter Sessions records, circa 1800 is a bit more involved and there was a need to import straight to a database. As many of my compatriots in these tasks, either do not have a database program or know how to use such a program, I usually finish up trying to write a front-end program that will allow them to write directly to the database via an input form. Sorry this is such a long winded reply to your question about what the database was for.

  silverous 14:42 17 Aug 2006

Stylehurst, thanks...very interesting, I like to know how people are using access, thanks for that.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Galaxy Note 8 vs iPhone X

This is what design agencies will look like in 2032

How to update iOS on iPhone or iPad

WhatsApp : comment lire vos messages sans que l’expéditeur le sache