Problem finding records from a table 2

[0200050]

Hello!

I need help.

I have a form where are one command button and one textbox. User inserts information(i.e. numbers) to the textbox, and command button checks, does the number exist already in a table. If the number exists in the table, another form would open.

I don't have a much of knowledge about how to do that, i'm only guessing that docmd.findrecord could be involved to a probably solution.

If anyone has some suggestions about this, i would be very grateful if you would like to share your information.

Mike, finland

 

[KenReay]

Hi

Assuming the form is bound to the recordset (table) then you could use:

Me.RecordsetClone.FindFirst "MyKey = " & MyTextbox
If me.RecordsetClone.NoMatch Then
Else
docmd.openForm ...etc
End if
using of course your own data item names in place of the My.. examples above

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[0200050]

Hi KenReay, and thanks for your reply!

It is a nice post, but one error is occuring from the line
Me.RecordsetClone.FindFirst "MyKey = " & MyTextbox

Access says there is syntax error(missing operator) in statement.

I'm a real noob in Access programming things - in case someone hasn't noticed, and would like to also ask what means, or does, that MyKey -variable over there?

 

[Edski]

Hi 200050,
...and assuming your form is NOT bound to the underlying table, you could use this:

Dim dbs as Database, rcs as Recordset
Set dbs = CurrentDb
Set rcs = dbs.OpenRecordset("MyTable")
If rcs.EOF then
  MsgBox "Emtpy Table!"
Else
  rcs.FindFirst "MyKey = " & MyTextbox
  If rcs.NoMatch Then
    MsgBox "No Match"
  Else
    docmd.openForm ...etc
  End if
End If
rcs.close
dbs.close

 

[Edski]

You should read Ken's last sentence again.

Replace MyKey with the name of the field in your table that you want to search on. Note that if you wanted to search every field in the table then you would need to add a bit more code.
Cheers.

 

[0200050]

Hello Edski!

That looks like a real nice code, but i´m still getting an error.

The line:

rcs.FindFirst "MyKey = " & MyTextbox

generates following error:
"Method or data member not found"

After this Access points to MyTextbox(in my code to txtSearch).

Do you have any idea what could cause this problem?

Yeah, i replaced MyKey and MyTextBox with my own things, thank you for notification.

 

[Edski]

Hi again,
You have to make sure your Reference to the DAO 3.6 Object Library is as High Up as it can go.
Cheers.

 

[0200050]

Hi Edski,

that DAO re-priorization helped a little, i guess, at least the error message is now different:

Run-time error 3251:

Command is not able to execute with this type of object(i translated this from Finnish)

Is there yet something else that could be causing the failure of the code? Is there anything else to try out?

- Mike, finland

 

[0200050]

So this fore-mentioned Run-time error was caused by this same line:

rcs.FindFirst "MyKey = " & MyTextbox

Now Access points to this whole line when debugging.

What else there might be possible to do to fix this problem?

Mike, finland

 

[Edski]

OK, try this:

Set rcs = dbs.OpenRecordset("MyTable", dbOpenSnapshot)

instead.
Cheers.

 

[KenReay]

Hi

The fact that you are getting an error on this line when using both methods, makes me think that perhaps the 'key' youa re looking for is a string and not a number (I assumed it was a number because that is what you said in your first post), if it is a string you need:

Me.RecordsetClone.FindFirst "MyKey = '" & MyTextbox & "'"

or the equivalent variation for Edski's method

ie in short if it is a string you need to bound the value in quotes



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[0200050]

Hi again Edski and KenReay!

I changed the code like you suggested Edski.

Then i put extra quotes like you proposed KenReay.

And now the form opens nice and "easy"! I can't thank you both enough for putting such a large effort on my problem. You are just amazing guys.

Thank you so much!!!

One question for you i'd still have in my mind, how long you've been working with VBA?

Mike, finland

 

[KenReay]

Hi

VBA - 5 or 6 years

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK