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

 

[khexcel]

I tried the above code but each time it ran I keep getting this message:
Complie Error:
User-defined type not defined

and the following codes highlights:

Dim DB as database.

Is this because of Access 2000?

 

[Norie]

No it's kind of my fault. You'll need to set a Reference to Microsoft DAO 3.51 Object Library.

I think you could get away by removing the data types in the Dim statements.

 

[khexcel]

Did some research and this is what I understand:
When a recordset is asked for in a Microsoft Access database (.mdb),
a DAO recordset is returned.
In a Microsoft Access project (.adp), an ADO recordset is returned.

Here are the rules:-

ADO Recordset type:
Based on SQL data - Read/Write (1)
Based on Jet data - Read Only

DAO Recordset type:
Based on SQL data - N/A
Based on Jet data - Read/Write

Since I am using a .mdb database, my recordset type is DAO and based
on the rules, I cann't use SQL.

Here are my options:
DAO methods and properties:
FindFirst, FindNext, FindLast, FindPrevious, Move or other Move methods.

ADO methods and properties:
Find, Move or other Move methods

Am I correct? Any suggestions on how to use these methods?

 

[Norie]

Have you tried setting the reference?

Tools>Refereences...

Which version of Access are you using?

 

[khexcel]

I am using Access 2000 version 9.07616 SP-3.

I don't have tools--> Reference on my menu.

Thanks

 

[khexcel]

Correction, I do have tools--> reference in the visual basic menu

 

[Norie]

Yes sorry should have said. Any better?

 

[khexcel]

Thanks all for your help but my problem remains unresolve. I guess I need to study more VB and Access.

Thanks

 

[khexcel]

For those who are interested, I did the research are here is the result. In the visual basic menu, select Tools ---> Reference and check the DAO box.
The proper syntax for my version of Access is:

Private Sub enter_text_AfterUpdate()
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb
If Not IsNull(Me!enter_text) Then
strSQL = "SELECT * " & _
"FROM lookup_table " & _
"WHERE [Table_value_to_lookup] = " & Me!enter_text & ";"
Set rs = db.openrecordset(strSQL)
If rs.RecordCount = 0 Then
MsgBox "No such record exists ?", vbOKOnly, "ERROR"
With Me!enter_text
.Value = ""
.SetFocus
End With
DoCmd.CancelEvent
Else
Me!return_text = rs![Table_value_to_lookup]
End If
End If
End Sub