CODEWITHSUNDEEP
sqlms-accessvba
Kyle
Kyle

Reputation: 47

Run SQL from VBA button

What do I need to add/modify to this code to have txtDisease1 be populated with the results of strSQL? As of now the textbox populates with the SQL query. I'm sure it is a simple fix but for the life of me I can not find the answer. 

Private Sub btnDNA_Click()
Dim strSQL As String

strSQL = "SELECT LastName From Patient WHERE PatientID = 1"
Me.txtDisease1.Value = strSQL

End Sub

Upvotes: 1

Views: 450

Answers (3)

Matt Hall
Matt Hall

Reputation: 2412

I've not been able to test this, but I think this should work.

Private Sub btnDNA_Click()

    Dim strSQL As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    strSQL = "SELECT LastName From Patient WHERE PatientID = 1"

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)

    Me.txtDisease1 = rs!LastName

    rs.close
    Set rs = Nothing
    Set db = Nothing

End Sub

Upvotes: 2

neuralgroove
neuralgroove

Reputation: 580

For more complex lookups or to hit other data sources, you can use ADO (requires reference to Microsoft Active X Data Objects)

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "SELECT TOP 1 LastName From Patient WHERE PatientID=1", _
    CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF Then Me.txtDisease1.Value = rs(0)
rs.Close
Set rs = Nothing

Upvotes: 0

Emi
Emi

Reputation: 484

You can use DLookup function.

Me.txtDisease1.Value = DLookup("LastName", "Patient", "PatientID = 1")

Upvotes: 3

Related Questions