Populating Form Controls with SQL query

[unaon]

I have been trying to populate a text box on a form based on the result of an SQL SELECT statement, but can't get it to work.

I want the statement to look at one field on my form, draw related data from a table and populate another field with the result.

Any suggestions?

Thanks,
Una

 

[rbowes]

I would do it this way:

Form: txtCriteria, txtResult

Create a routine called ResultFill:

Private Sub ResultFill()
Dim db as Dao.Database
dim rs as dao.recordset
dim strsql as string

strsql = Select MyField From tblMyTable Where Criteria = '" & me.txtCriteria & "'"

set db = currentdb
set rs = db.openrecordset(strsql, dbopensnapshot)

if rs.bof or rs.eof then
Msgbox "No records match this criteria", vbInformation
set rs = nothing
set db = nothing
end if

me.txtResult = rs!Myfield
set rs = nothing
set db = nothing

end sub

Call this procedure in the On Open event and/or the On Current event.

That should work.

Bob

 

[Guest_imported]

Table1 has fields 'MyName' and 'MyValue'
Form1 has controls 'txtName', 'txtValue' and
'cmdGetMyValue' command button

VBA behind Command Button:

Private Sub cmdGetMyValue_Click()
Dim sSQL As String
sSQL = "SELECT MyValue FROM Table1 WHERE MyName=" & _
Chr(34) & txtName.Value & Chr(34)
txtValue.Value = _
CurrentDb.OpenRecordset(sSQL).Fields("MyValue&quot.Value
End Sub