Displaying Acces rows from a UserForm

The following code will run an SQL query on the database and return the results to the Excel spreadsheet, simply replace the details of your DB and path in the cnn.Open line and your own SQL query in the SQL= bit

Private Sub CommandButton1_Click()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String
Me.CommandButton1.Caption = "Please Wait"
Me.Repaint
    Rows("2:5000").Select
    Selection.Delete Shift:=xlUp
    Range("A2").Select
strFilePath = Range("dbase_path") & Range("dbase_name")
Set cnn = New ADODB.Connection
Set rs = New ADODB.Recordset
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & strFilePath & ";"


SQL = "SELECT * FROM `qryFullRequestDetails` WHERE (Order_Number > 10) ORDER BY tblRequests.Order_Number ASC"

rs.CursorLocation = adUseClient
rs.Open SQL, cnn, adOpenStatic, adLockReadOnly
Application.ScreenUpdating = False
Sheet1.Range("A2").CopyFromRecordset rs 'Where to place the data
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Unload Me
End Sub

I then have a userform which opens on this data when you double click a row. The form has the following code in it:

Dim ws As Worksheet
Dim lRow As Long
Dim CurRecord As Long
Public i As Integer
Public origStatus As String
Public caseId As Integer


Private Sub UserForm_Initialize()
    Set ws = ThisWorkbook.Sheets("Sheet1")
        i = ActiveCell.Row
            refresh_form (i)
End Sub

Function refresh_form(i)
If (Len(Range("A" & i).Value) < 1) Then
i = i - 1
Else
'
End If
                caseId = Range("A" & i).Value
                txtRequestName.Text = Range("AB" & i).Value
                txtOrderNumber.Text = Range("C" & i).Value
                txtReqPhone.Text = Range("AE" & i).Value
                txtReqLocation.Text = Range("AC" & i).Value
                txtReqBusUnit.Text = Range("AF" & i).Value
                txtReqBusArea.Text = Range("AG" & i).Value
                txtReqDelivery.Text = Range("E" & i).Value
                txtReqDate.Text = Range("B" & i).Value
                txtCustName.Text = Range("F" & i).Value
                chkPreCred.Value = Range("K" & i).Value
                txtInfo.Value = Range("M" & i).Value
                txtAssignDate.Value = Range("O" & i).Value
                txtAssignedTo.Value = Range("P" & i).Value
                txtReturnDate.Value = Range("Q" & i).Value
                ComboStatus.Value = Range("U" & i).Value
                origStatus = Range("V" & i).Value
                RecNo.Value = i - 1
End Function

Function selected_row(rowNum)
Rows(i - 1).Font.Bold = False
Rows(i + 1).Font.Bold = False
Rows(i).Font.Bold = True
Rows(i - 1).Font.ColorIndex = 1
Rows(i + 1).Font.ColorIndex = 1
Rows(i).Font.ColorIndex = 3
Cells(i, ActiveCell.Column).Select
refresh_form (i)
End Function


Private Sub PrevRecord_Click()
i = i - 1
If (i > 1) Then
    selected_row (i)
End If
End Sub

Private Sub NextRecord_Click()
i = i + 1
If (i > 1) Then
    selected_row (i)
End If
End Sub

Basically, all you do is assign the relevant cell value to each field, the PrevRecord and NextRecord buttons then allow you to move records, simply by adding or subtracting 1 from the row number and refreshing the form.

Try replacing the wildcard * with %.