PDA

View Full Version : Updating Access from Excel UF



CuriousGeorg
02-18-2014, 05:14 AM
Hi there,

the quest is simple!! ha, I have an excel user form that when filled in sends the record to an access database. This is the bit I understand.

What I need however, from that Excel UF, is a button that you can update a record in access.

I'm not sure how I'd code this. I'm sure it can have a message box asking for the reference that then finds it and returns it to the UF for updating...

any help is great.

Cheers.

Bob Phillips
02-18-2014, 05:34 AM
You should have a button that says retrieve, pull back all keys, let them select from the list, then retrieve that item.

CuriousGeorg
02-18-2014, 05:50 AM
I need to have the UF that updates it. At no point can they ever go into the access database itself.

Bob Phillips
02-18-2014, 06:53 AM
I was meaning from Excel. You have to present the options to them.

CuriousGeorg
02-18-2014, 06:56 AM
i think my problem is retrieving the info from acess to be able to update. Im trying all sorts but cant do it. I can ask them to fill a textbox in that has a unique number then using that string they search for the record.

i dont know if im explaining it right.

Bob Phillips
02-18-2014, 07:02 AM
How are you querying Access, ADO, MS Query? If I were doing it, I would create a stored query in Access to get all of the keys, and maybe another column to help identify, and then use ADO to issue a stored procedure query using ADO's Command method. That would pull back a recordset which I would use to load a Listbox. The user selection would then be used to call another stored query that pulls back that specific data.

CuriousGeorg
02-18-2014, 07:15 AM
I'm doing it using ADO,

I was hoping that, if say primary key was "reference" and they fill this in on the user form. there was a way they type the number they wish to edit in the "reference" field then the update button would locate that record and pull it back to the UF. They then edit as necessary and .update ?

CuriousGeorg
02-18-2014, 07:44 AM
for example:

Private Sub lblupdate_Click()Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset


Search = txtClaim.Value
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & "Data Source=U:\test DB\cho.accdb;"
searchstring = "SELECT Reference_Number FROM chotable1 WHERE [Reference Number] = '" & Search & "'"
rs.Open searchstring, cn, adOpenStatic
cboongoing.Text = rs.Fields("ongoing")
txtrecs.Text = rs.Fields("Recommendations")
Set rs = Nothing


End Sub


What is wrong with that code? For some reason i cant get the search string to work. for it to pull it back. error is showing on


rs.Open searchstring, cn, adOpenStatic