Importing data from Access table into Excel Userform Textbox

[mr_nic]

Hi all,

I'm trying to extract data from an Access DB into an Excel user form (as the intended users will not all have MS Access).
The access table (TblData) has multiple rows and fields, and the SQL query I have created will search the 'CaseRef' field for the value the user puts in to the first text box, and then return the value from 2 specific fields into the two text boxes below.

The issue I am having is that the values that are being returned have the character "¶" added to the end.
Furthermore, if I try to copy the value, it pastes as "??" or two square boxes.

Would someone be able to tell me where I'm going wrong, or what I should change?

The code is below - many thanks for your help!

Private Sub CommandButton1_Click()
    Dim Cn As ADODB.Connection
    Set Cn = New ADODB.Connection
    Dim Rs As ADODB.Recordset
    Set Rs = New ADODB.Recordset
    Dim strDataSQL As String
    strDataSQL = "SELECT divsion FROM TblData WHERE CaseRef=" & Nums
    Dim Db As String
    Db = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\users\user\documents\kyc database.accdb;Persist Security Info=False;"
    Dim Nums As String
    Nums = Me.CaseNum.Value 'this is the reference the user inputs'
     
    If CaseNum = 0 Then
        MsgBox "Please enter a value"
        Exit Sub
        End If
    
    Cn.Open Db
    With Rs
        .ActiveConnection = Cn
        .Open "SELECT division FROM TblData WHERE CaseRef=" & Nums, _
        Cn, adOpenStatic
    TestForm.DivBox.Value = Rs.GetString
    End With
    Rs.Close
    
    With Rs
        .ActiveConnection = Cn
        .Open "SELECT casehandler FROM TblData WHERE CaseRef=" & Nums, _
        Cn, adOpenStatic
    TestForm.WorkerBox.Value = Rs.GetString
    Rs.Close
    Set Rs = Nothing
    Cn.Close
    Set Cn = Nothing
    End With
    
End Sub

 

[xenou]

TestForm.DivBox.Value = Rs.GetString

rs,GetString is unusual.

What you want is:

TestForm.WorkerBox.Value = Rs.Fields(0).Value

Or

TestForm.WorkerBox.Value = Rs.Fields("division").Value

You can do this in one query if they are the same CaseRef record:

    With Rs
        .ActiveConnection = Cn
        .Open "SELECT [COLOR="#FF0000"]division, casehandler[/COLOR] FROM TblData WHERE CaseRef=" & Nums, _
        Cn, adOpenStatic
    TestForm.DivBox.Value = Rs.Fields("division").Value
    TestForm.WorkerBox.Value = Rs.Fields("casehandler").Value
    End With
    Rs.Close

 

[mr_nic]

Hi Xenou,

thanks so much for your help - this has worked perfectly.
I've got multiple entries to display on the userform, so I've taken the combined approach suggested last.

I'm going to close this thread as you've solved my query, but I have another question with regards to using the 'INSERT INTO' sql function - if you spy it in the forum and are able to help again i'd be very grateful!!

Mr Nic