VBA access run sql check row count

Question

I'm trying to display a field on a form depending on the results of an sql query. Since I don't know vba in access I'm struggling and don't know where I am going wrong. Help will be greatly appreciated.

Dim RecordSt As Recordset
Dim dBase As Database
Dim stringSQL As String

Set dBase = CurrentDb()

stringSQL = "SELECT * FROM Table1 WHERE ID = 2"

DoCmd.RunSQL (stringSQL)

If RecordSt.Fields.Count > 0 Then
Me.Other.Visible = True
Else
Me.Other.Visible = False
End If

What is RecordSt ? Do you have an Option Explicit at the beginning of your module ? Couldn't you just use a DCount() here, eliminating the requirement for VBA code ? — Patrick Honorez
– Patrick Honorez, Commented Jul 31, 2014 at 14:13
5 questions, only 1 with an accepted answer. No good replies for the 4 others ? — Patrick Honorez
– Patrick Honorez, Commented Jul 31, 2014 at 14:15

Patrick Honorez · Accepted Answer · 2014-07-31 15:08:48Z

3

If DCount("*", "table1", "id = 2") > 0 Then
   Me.Other.Visible = True
Else
   Me.Other.Visible = False
End if

or even quicker:

Me.Other.Visible = (DCount("*", "table1", "id = 2") > 0)

answered Jul 31, 2014 at 15:08

Patrick Honorez

25.4k12 gold badges97 silver badges163 bronze badges

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

Comments

kiks73 · Accepted Answer · 2014-07-31 14:27:35Z

0

There are many problems in your code.

First of all Docmd.RumSQL(stringSQL) do not return nothing, and it is not related to your recordset RecordSt.

Also RecordSt.Fields.Count will count the FIELDS of your table and not the number of RECORDS selected.

This is a solution using ADODB. You probably have to add the reference to ADO (you can choose another version if you don't have 6.1) in your Tools->Reference menu from your VBA editor:

enter image description here

Dim rst As new ADODB.Recordset
Dim stringSQL As String

stringSQL = "SELECT * FROM Table1 WHERE ID = 2"

rst.Open SQL, CurrentProject.AccessConnection
If rst.RecordCount > 0 Then
    Me.Other.Visible = True
Else
    Me.Other.Visible = False
End If

answered Jul 31, 2014 at 14:27

kiks73

3,8383 gold badges30 silver badges56 bronze badges

1 Comment

Patrick Honorez Over a year ago

Can also be done using DAO, avoiding to add an external reference. The less they are, the better I feel :-)

Collectives™ on Stack Overflow

VBA access run sql check row count

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related