View Single Post
Old October 7th, 2003, 07:24 AM
stoneman stoneman is offline
Authorized User
Join Date: Jun 2003
Location: , , .
Posts: 47
Thanks: 0
Thanked 0 Times in 0 Posts
Default using fields in recordset to populate textbox

I have a db on a company in which I want to make available
Company name, address, phone numbers tax rate.
 I build a table named tblStandard with one row only to that will hold all these information. I have these fields; Name, Address, Phone and TaxRate as just 3 examples.

I would like to write just 1 procedure using SQL statement and create 1 recordset and be able to access any field in this recordset and display them in textboxes on other forms. (I do not want to used DLookup functions).

I have created a module and write a Public Function to create the recordset.

Public Function StandardValues(ctl As Control, str As String)

    'ctl is the type of control to populate
    ' str is the name of the control

    Dim rst As ADODB.Recordset
    Dim cnn As ADODB.Connection

    Set rst = New ADODB.Recordset
    Set cnn = CurrentProject.Connection
    rst.Open "SELECT tblStandard.* FROM tblStandard", cnn, adOpenKeyset, , adCmdText ‘ selecting all fields

    If ctl.Name = str Then
        ctl = rst!TaxRate ' populate textbox name Tax on form
    End If

End Function

I would call the above function when I click an unbound checkbox and populate Tax textbox

Private Sub CheckBox_Click(acCheckBox, Tax)

    Call StandardValues
End Sub

I cannot get it to work and cannof find out what I am doing wrong. I am using Access 20000