Userform to query access database and return specific result & then update database

[DodoRevival]

Hi Guys

I have been trying to work this out all day with no success and wondered if anyone here could help me out. I'm not usre if this is the right place to post or if it should be in the access section as its a bit of both.

I have a database called records with a table called Remedial_Action

There are 5 fields called ref, further investigation, investigation notes, date required & complete

I have a userform with 2 text boxes and 2 command buttons (retrieve & Submit)

what i want to do is the user inputs a reference number into text box 1 and click on the retrieve command button. this querys the database for the matching ref and returns the notes in textbox 2. i want the user to be able to update the notes and then click the submit button which will then update the database with the revised notes and mark the complete field as yes. I'm sure this is possible but im just not getting it.

thanks in advance

 

[Kyle123]

It should be a yes/no field or some kind of boolean. It shouldn't be text.

Change it to yes/no then the sql will work

 

[DodoRevival]

All sorted

thanks again Kyle

One final thing i would like to do with this is when i am doing the retrieval as in the first code i posted is there a way to get an exception message when the reference entered is not in the database?

Thanks

 

[Kyle123]

Doesn't it raise an exception anyway as something would have gone wrong?

 

[DodoRevival]

no it doesnt do anything if the ref number is wrong

 

[Kyle123]

Wouldn't the ref number always be right if they are editing a returned record?

 

[DodoRevival]

Basically the people updating would be recieving an email to advise that remedial action has been taken and that email will have the reference number on it. They just type it in and call up the notes to update and mark as complete. I'm just wondering if we can get a warning message if they type in an incorrect ref number as invariably if you give people the chance to type things in you will get some who get it wrong.

Thanks

 

[Kyle123]

Ok, have a look here:
http://support.microsoft.com/kb/195048

 

[pbornemeier]

Your search returns Ref as well as the notes. Compare the returned Ref to tbSearch.Value. If they are not the same then process the error.

 

[DodoRevival]

Thanks guys you have been a great help :D