CODEWITHSUNDEEP
sqlvba
Wilest
Wilest

Reputation: 1860

VBA access run sql check row count

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

Upvotes: 0

Views: 5500

Answers (2)

iDevlop
iDevlop

Reputation: 25252

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)

Upvotes: 3

kiks73
kiks73

Reputation: 3758

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

Upvotes: 0

Related Questions