How to write code to FIND a record in one column of a table..

[murphysdad]

Yep,
I am trying to have a form (frmFind) with a text box(txtSearch)and command button(cmdFind) look up a record by using the controlNumber[this is the name of the column in the table(tblmainFTag)] that is supposed to be entered into the text box on the form.

I would like them to enter the controlNumber and hit the cmd button...POOF then it brings up that record in form view. I would like the record to come up in the form they use to add new items(frmMainFTag).

So I have the find form(frmFind)
table where to look it up(tblMainFTag)
form to where results should go(frmMainFTag)

I will take any help!! Rather new at code.

Thank you!

 

[Paddyo]

Dim Rst as new adodb.recordset
Dim StrSql as String
rst.locktype = adoptimistic
rst.supports adupdate '(if you want to update the record)
rst.CursorType = adOpenStatic

StrSql = "Select * from tblMainFTag where controlNumber = '" & txtsearch & "'"
rst.Open StrSql
If not rst.eof then
Forms!formname.controlname = rst!Fieldname
and so on until all of you fields have been filled

else
msgbox "No Record found"
rst.close
exit sub
end if
rst.close
set rst = nothing

This presumes that you have unboud controls on your form
This will only display the record on you form.
You can then change the details and save the record by using the same method as above only this time you will have rst!fieldname = txtfieldname

This may be a bit complicated but it should work

Hope it helps
Paul

 

[murphysdad]

Paul,

Thanks for the help! I am getting an error message that is saying the arguement requires an object name. I have no idea what this means...You?

Jeremy

 

[murphysdad]

Paul,

My bad. It should have read"user defined type not defined"
then it highlights: dim rst as new adodb.recordset

Jeremy

 

[Paddyo]

Sorry
After the Dim StrSql as String there should be another line

rstacc.ActiveConnection = CurrentProject.Connection


Not having this would cause errors

Best of luck
Paul

 

[Paddyo]

One other thing

This works with Access2000 - not sure about access97

Paul

 

[murphysdad]

I am getting the same error. Should I put a something in place of CurrentProject? It seems that the code stops before it gets to this new command anyway.
Sorry, I am a Rookie at this stuff.

J

 

[murphysdad]

i am 97...crap.

 

[Paddyo]

There are ways of doing what you want with 97 using DAO

I have never used them - maybe someone else can help here.

Sorry about that

Paul