MS Access - querying a database

Hi,
Im using Access2000 and am tring to run a veriety of database inserts and retievals through my own vb code. I am using the default version of visual basic that comes with access when required to add my code to form buttong and would like to know how i can make my own button that can query a record in the database called Candidate and return a value from the candidateId & candidateName column, and then store these value in variables. I would also like to have a button that can insert a value of bob into CandidateName (still using the candidate table!)
I have found some code the last fuction, using the doCmd.RunSql(AQuery) - but this is not very suitable as everytime i run it, it promts me with - do you want to add a new row etc.
Another thing is that I do not want to install any extra add ins as this access file should be able to run on several computers without confusing the users.

I have done this before making a seperate application exe file in visual basic 6, but as I said, im using the vbcode within access2000 form builder.

Thanks for your help!

Private sub CmdFind_Click()

Dim db as database
Dim rs as recordset

dim strName as string
dim strID as string

set db = currentdb

set rs = db.openrecordset("Candidate", dbopenSnapshot)

Do while not rs.eof

strID = rs!CandidateID
strName = rs!CandidateName

msgbox strID & " : " & strname

rs.movenext

Loop

End Sub

SHould give you something to work with for finding.
-------------------------------------

Private sub cmdInsert_Click

Dim db as database
Dim rs as recordset

set db = currentdb

set rs = db.openrecordset("Candidate", dbopenDynaset)

rs.addnew
rs!CandidateName = "Bob"
rs.update

End sub

Very basic really, but should start you off. This is DAO btw.

Cheers Granty,
thanks for your help, but i tried something similar to this before, but i recieve an error of:

Compile error:
User defined type not defined

and the error is refering to the first line: Dim db As database
For some reason I can not add Database as a data type - I can do recordset, but not database :(
- 'database' does not even occur in that drop down list thing when you type dim db as ......
I assume the data type 'database' may be some kind of extra 'add in' that im trying to avoid. (allthough i may need to end up using it if i cant find another allternative)

mmmm, any other suggestions?

there is a command in access you can use to turn user prompts off (temporarily). Check out the DoCmd object in the help files, it is probably a method in there.

I would recommend just writing the code in VB tho. Access is a horrible development environment...

Thanks for your feedback optikal!
- Ill see what i can dig up on gettting rid of those prompts!

I agree - access is a pretty horrible environment! - The only reason im using it because I thought id save the company im temping for a little cash. - Alllthough with the amount of time ive been spending trying to find access alternatives to what i normally do in vb..... - on the plus side tho, i have got a little better at designing forms in access & coding with fewer options available to me!
mmm, back to turning those promts off then :D

Coding in access doesn't really save money...because you have to keep in mind the support and maintenance costs...and maintaining code in access takes way longer and more effort than maintaining code in VB. So the TCO (total cost of ownership) is almost always more for access solutions.

- just put in a request for the company to get a copy of visual basic! - hopefully that should help me out.
- the only reason why I suggested doing it in access was that I was brought in as a temp to work on a simple database with a simple front end, now they have put me on other database projects, so things have changed.

The only thing I found to turn off those prompts was in the access menu >tools>options... Edit/find - and unselecting the disable action queries.
I guesse that will have to do for now!
- any one know of a way to get information from the database now in a similar kind of way to the doCmd.RunSql????