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

DodoRevival

New Member
Joined
Apr 19, 2012
Messages
30
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.
 
Upvote 0
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
 
Upvote 0
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

Code:
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
 
Upvote 0
maybe something like:
Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 & "';"
 
Upvote 0
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??
 
Upvote 0

Forum statistics

Threads
1,211,457
Messages
6,101,964
Members
447,766
Latest member
cool_aikon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top