Updating Access from Excel UF

[CuriousGeorg]

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]

You should have a button that says retrieve, pull back all keys, let them select from the list, then retrieve that item.

[CuriousGeorg]

I need to have the UF that updates it. At no point can they ever go into the access database itself.

[Bob Phillips]

I was meaning from Excel. You have to present the options to them.

[CuriousGeorg]

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]

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]

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]

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