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

 

[pbornemeier]

If you use google to search this site as follows:

form update database site:mrexcel.com

you will get several approaches to your problem. I have some examples on another machine. I'll post them later.

 

[DodoRevival]

Hi Phil

Thanks for your reply. I have googled the site and came up with lots of hits but just cant find any that can solve all my problems. At this stage im probably looking for a bit of bespoke code as i cant really see anything similar to what i want to do, well nothing that i can understand anyway, of course that could be due to it being very early in the morning and pre coffee so my understanding is quite low at the moment.

Thanks

 

[DodoRevival]

ok post coffee this is what i have it means that i can pull back the notes to tbnotes textbox using the ref in tbsearch textbox. Now the hard bit i need to be able to overwrite the old notes with the new ones and mark the record as complete. Any help would be greatly appreciated

Thanks

Sub CommandButton2_Click()
Dim cn As ADODB.Connection, rs As ADODB.Recordset
 
    ' connect to the Access database
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=filepath.mdb;"
 
Search = tbSearch.Value
' open a recordset
    Set rs = New ADODB.Recordset
    searchstring = "SELECT Ref, Investigation_Notes FROM Remedial_Action WHERE [Ref] = '" & Search & "'"
    rs.Open searchstring, cn, adOpenStatic
    tbNotes.Text = rs.Fields("Investigation_Notes")
    Set rs = Nothing
End Sub

 

[Kyle123]

maybe something like:

Sub CommandButton2_Click()
Dim cn As ADODB.Connection
Dim notes As String, search As String, sql As String


    ' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=filepath.mdb;"
 
search = tbSearch.Value

notes = Replace(tbnotes.Text, "'", "''")

sql = "UPDATE Remedial_Action SET Investigation_Notes = '" & notes & "' WHERE Ref = '" & search & "';"
cn.Execute sql
cn.Close

End Sub

 

[DodoRevival]

Hi Kyle

Im getting
run-time error '-2147217904 (80040e10)':
No Vaue given for one or more required parameters and its highlighting this line of code

cn.Execute sql

Any ideas as i think i understand what you were doing but im still very much the novice

Thanks

 

[DodoRevival]

ignore me i was being an idiot the code works fine



Thank you for your help

 

[DodoRevival]

The code above is working great and again thanks very much for the help but i still need to be able to mark the action as complete in the complete field and cannot figure out how to do it. I'm assuming that i just need to update the sql but im not sure how

Thanks again

 

[Kyle123]

I think access sql uses true/false, I'm assuming that's what completed is set up as?

Try


sql = "UPDATE Remedial_Action SET Investigation_Notes = '" & notes & "', complete=TRUE WHERE Ref = '" & search & "';"

 

[DodoRevival]

that is giving me a result of -1 in the table

Completed field is set to text. i tried changing it to yes/no but i get check boxes when i run the table then??