Run SQL from VBA button

Question

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

Your strSql is literally only the text "SELECT LastName From Patient WHERE PatientID = 1" and not the executed result of the query it describes. Using the DLOOKUP function as @Emi has suggested is the simpler approach for what you're trying to do here. — Matt Hall
– Matt Hall, Commented Nov 25, 2015 at 14:06
I know DLookup but I need to use SQL because the queries will get more complex. Is there a way to do this? — Kyle
– Kyle, Commented Nov 25, 2015 at 18:05

Emi · Accepted Answer · 2015-11-25 02:34:38Z

3

You can use DLookup function.

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

answered Nov 25, 2015 at 2:34

Emi

4844 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kyle Over a year ago

I know DLookup but I need to use SQL because the queries will get more complex. Is there a way to do this?

Matt Hall · Accepted Answer · 2015-11-25 19:05:14Z

2

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

answered Nov 25, 2015 at 19:05

Matt Hall

2,4227 gold badges41 silver badges64 bronze badges

Comments

neuralgroove · Accepted Answer · 2015-11-25 17:00:54Z

0

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

answered Nov 25, 2015 at 17:00

neuralgroove

5804 silver badges13 bronze badges

Collectives™ on Stack Overflow

Run SQL from VBA button

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related