How to retrieve data from access into a userform

Mlbblue

New Member
Joined
Oct 8, 2006
Messages
20
Hi,

I'm looking for a bit of help with this problem. I'd like to retrieve a single record from a .mdb file straight into a user form.

For example the .mdb file has one table with 13 fields

On my user form I have 13 fields which correspond to the fields from the table.

Using a unique identifier (field 1) I'd like to retrieve that record straight into the fields on the user form. The user then has the option of making changes to any of those fields and then updating the corresponding record in the .mdb file.

I've seen plenty of examples of retrieving records to the worksheet but so far my searches have not found what I'm looking for above.

Any help with this query is appreciated! :biggrin:

Cheers

Mark
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Mark

This certainly sounds possible, probably using ADO via VBA.

What examples have you seen so far?

I'll try and knock something up and post back later.

PS couldn't you just do this all in Access?
 
Upvote 0
The examples so far are using ADO and retrieving the data to the range in a worksheet. I'm looking to build it within excel because it gives me multi-user access to the data, especially for users without access on their machines.

Any help you can give would be great! :)

Cheers

Mark
 
Upvote 0
Mark

What I did was create a database called Test.mdb with 1 table, table1 which had 12 fields - ID and Field1-11.

Then in Excel I created a userform with a combobox and 11 textboxes - TextBox1-11.

It also had 2 command buttons labeled Update and Close.

I then used the following code.
Code:
Option Explicit
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

Private Sub ComboBox1_Change()
Dim strSQL As String
Dim I As Long

    Set rst = New ADODB.Recordset
    strSQL = "SELECT * FROM Table1 WHERE ID =" & ComboBox1.Value
    
    rst.Open strSQL, cn
    
    For I = 1 To 11
        Me.Controls("TextBox" & I) = rst.Fields("Field" & I)
    Next I
      
End Sub

Private Sub CommandButton1_Click()
Dim strSQL As String
Dim I As Long

    Set rst = New ADODB.Recordset
    rst.CursorType = adOpenKeyset
    rst.LockType = adLockOptimistic

    strSQL = "SELECT * FROM Table1 WHERE ID =" & ComboBox1.Value
    
    rst.Open strSQL, cn, adOpenDynamic
    rst.MoveFirst
    For I = 1 To 11
        rst.Fields("Field" & I).Value = Me.Controls("TextBox" & I)
        rst.Update
    Next I
End Sub

Private Sub CommandButton2_Click()
    Set rst = Nothing
    Set cn = Nothing
    Unload Me
End Sub

Private Sub UserForm_Initialize()

    Set rst = New ADODB.Recordset
    Set cn = New ADODB.Connection
    
    With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=C:\ADOTest.mdb;"
        .Open
    End With
        
    rst.Open "Select ID From Table1", cn
    
    ComboBox1.List = Application.WorksheetFunction.Transpose(rst.GetRows)
    
End Sub
Note this was only something I mocked up, I'm not too up on me ADO.:)

I'll see if I can post a link to the file.

I seem to remember Right Click had kindly provided the means to do that.

Need to go and search that out.:)
 
Upvote 0
Great, thanks for this. I've played around with the code and I think I get it to fit with what I want. It's getting late here so I'm calling it a night.

But, hopefully I can apply this to what I've been developing tomorrow at work. I'll let you know how I get on!

Many thanks for your assistance! :biggrin:

Mark
 
Upvote 0
I'm having problems and I'm totally stuck! :confused:

I've applied the code as you show above to the application I'm building. But, when I hit the drop down combobox and select an ID I get a runtime error that states:

Run-time error"-2147217904 (80040e10)":

No Value given for one or more required parameters


The code I'm using is below:

==============================
Option Explicit
Dim cn As ADODB.Connection
Dim rst As ADODB.Recordset

Private Sub close_frm_Click()
Set rst = Nothing
Set cn = Nothing
Unload Me
End Sub

Private Sub ComboBox1_Change()
Dim strSQL As String
Dim I As Long

Set rst = New ADODB.Recordset
strSQL = "SELECT * FROM master_table WHERE REF =" & ComboBox1.Value

rst.Open strSQL, cn **debugger highlights this line**

For I = 1 To 12
Me.Controls("TextBox" & I) = rst.Fields("Field" & I)
Next I

End Sub

Private Sub update_mdb_Click()
Dim strSQL As String
Dim I As Long

Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic

strSQL = "SELECT * FROM master_table WHERE REF =" & ComboBox1.Value

rst.Open strSQL, cn, adOpenDynamic
rst.MoveFirst
For I = 1 To 11
rst.Fields("Field" & I).Value = Me.Controls("TextBox" & I)
rst.Update
Next I
End Sub

Private Sub UserForm_Initialize()

Set rst = New ADODB.Recordset
Set cn = New ADODB.Connection

With cn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source=C:\Documents and Settings\Mark Bevington\Desktop\penappl_master_dbase2.mdb"
.Open
End With

rst.Open "Select REF From master_table", cn

ComboBox1.List = Application.WorksheetFunction.Transpose(rst.GetRows)

End Sub

=============

When I hover over the highlighted line of code when debugging the values look fine. What has really puzzled me is why it works fine on the example workbook above but when the same logic is applied to my application it wont work.

Btw, my application also writes to a recorset. However the connections are opened and closed all within the same bit of code.

I can access the ID fine when opening the user form (altho field named REF in my example) so I'm really stuck as to why it wont have it when trying to then select the ID (REF) which then drags in the data from the record.

Any help is really appreciated, I've been stuck on this all day!!! :(

Thanks

Mark
 
Upvote 0
Mark

What type of field is REF?

In the attachment ID was a numeric field.
 
Upvote 0
It's an alphanumeric field as the ref will contain numeric and alpha characters.

It does bring back the data in this field in the combo box when the user form is initialised, there's no problem with that part, it's when you select one you get a run time error as above.

Cheers

Mark
 
Upvote 0
Mark

You need to enclose the value from the combobox in single quotes.
Code:
strSQL = "SELECT * FROM master_table WHERE REF ='" & ComboBox1.Value & "'"
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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