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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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?
 

Mlbblue

New Member
Joined
Oct 8, 2006
Messages
20
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
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.:)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Mark

Here's a link, kindly provided by Right Click.:)

ADOUFUpdateRetrieveEx.zip
 

Mlbblue

New Member
Joined
Oct 8, 2006
Messages
20
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
 

Mlbblue

New Member
Joined
Oct 8, 2006
Messages
20

ADVERTISEMENT

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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Mark

What type of field is REF?

In the attachment ID was a numeric field.
 

Mlbblue

New Member
Joined
Oct 8, 2006
Messages
20
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
Mark

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

Watch MrExcel Video

Forum statistics

Threads
1,109,358
Messages
5,528,219
Members
409,809
Latest member
VICKRAM

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top