How to pass a SQL to a text box in a form

[khexcel]

Hi,
I am fairly new in Access and programming. Can someone let me know if it is possible to pass the value from an SQL statement directly to a text box in a form. eg Txtbox1.value= "select value from table where id =4". The form is not tied to the table the SQL is selecting from. If possible, please let me know. If not possible, is DLOOKUP the best option to bring a value to the text box?

Since I am not good in programming, please explain in detail.

Thanks

 

[anvil19]

Hi khexcel

This can be done with the forms current event, like So:

Private Sub Form_Current()
    Dim vara

    If IsNull(Text2.Value) = True Then
        Exit Sub
    End If

    vara = Me.Text2

    Me.Text1 = "SELECT <FieldValueHere> FROM <TableNameHere> WHERE ID = '" & vara & "'"

End Sub

Now, every time you change any data in your form the value in Text1 will be evaluated and will reflect the value that relates to the value in Text2. So, if the value in Text2 is 5 then the Name (in our examples case) that refers to the record with 5 as a value of the ID field will be shown in the control. If the value in Text2 = 25 then....

The If IsNull(Text2.Value) = True bit just stops the error that would pop up if Text2 had no data in it. When you open a form that is not bound to a table then the controls will have Null values in them.

Of course you will substitute your control names and table and field names for Text1, Text2, FieldValueHere and TableNameHere for you own names. Just paste the code into the Form code window, change those names and try it out. back up your database before you try things in case something should go terribly wrong!!

Try that!

anvil19

 

[khexcel]

Hi,
Thanks for your response.
I tried your code exactly as you typed it and the value I am getting in Text2 is the text of the SQL statement: "SELECT field_name FROM Table1 WHERE ID = '" & vara & "'". Maybe since the SQL statement is surrounded by apostrophe, Access is reading the statement as a text instead of a SQL statement.
Am I doing something wrong? Or are there changes in the version of Access? I am using Access 2000.

 

[Norie]

You cannot assign a value to a textbox using that method.

Try setting the ControlSource of the textbox.

Why don't you tie in the table?

 

[anvil19]

Me again

I apologise khexcel, but Norie is quite right.

The ControlSource property will add the data into the control, like so:

Private Sub Form_Current() 
    Dim vara 

    If IsNull(Text2.Value) = True Then 
        Exit Sub 
    End If 

    vara = Me.Text2 

    Me.Text1.ControlSource = "SELECT <FieldValueHere> FROM <TableNameHere> WHERE ID = '" & vara & "'" 

End Sub

Sorry bud!!

anvil19

 

[Norie]

Control source can't be set to a SQL statement - you'll just get the #NAME error.

The best bet I think would be to use a label rather than a textbox and set the Caption property to whatever based on the sql statement or a DLOOKUP.

 

[khexcel]

Neither the code using control source nor the caption of label worked. I guess I am stuck with DLOOKUP. Is this really this best way? It is kinda slow.

 

[Norie]

You could use a Recordset

 

[khexcel]

I am not good in programming. Can you explain how to use recordset.
Thanks

 

[Norie]

Try something like this:

Private Sub Form_Current()
Dim rst As Recordset
Dim db As Database
Dim strSQL as String
Dim ID as Integer
Set db= CurrentDb

ID = TextID.value

strqSQL = "SELECT Value FROM Table1 WHERE ID=" & ID
' insert required SQL statement here

Set rst = db.OpenRecordset(strSQL)

rst.MoveFirst

ResultText.Value = rst.Fields("Value")

End Sub

Please this is only to try and demonstrate the concepts and will have to be adapted for individual circmustances e.g. names of controls