SQL statment

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I have a form based on a query.

While the form loading I want to check if there`re any results.
If no results, msgbox say "No Data found"

I think that sql statment will do the job, but i find hard to wrap it up
into a sub.
"SELECT DISTINCTROW Chartqry.Price, Count(*) AS [Count Of Chartqry] FROM
Chartqry GROUP BY Chartqry.Price;"


Thanks,

Tom
 
Use a recordset:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

If rst.EOF And rst.BOF Then
MsgBox "There are no records to edit. This form will not open."
Cancel = True
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Form_Open

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Use a recordset:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

If rst.EOF And rst.BOF Then
MsgBox "There are no records to edit. This form will not open."
Cancel = True
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Form_Open

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks Arvin,
It`s look elegant, but when I tried I got error message:
7951: you entered an expression that has invalid reference to the
RecordsetClone property.
What is that mean? how can I fix this?

Thanks again,
Tom

Arvin Meyer said:
Use a recordset:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

If rst.EOF And rst.BOF Then
MsgBox "There are no records to edit. This form will not open."
Cancel = True
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Form_Open

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


Tom said:
I have a form based on a query.

While the form loading I want to check if there`re any results.
If no results, msgbox say "No Data found"

I think that sql statment will do the job, but i find hard to wrap it up
into a sub.
"SELECT DISTINCTROW Chartqry.Price, Count(*) AS [Count Of Chartqry] FROM
Chartqry GROUP BY Chartqry.Price;"


Thanks,

Tom
Click to expand...
Click to expand...
 
Thanks Arvin,
It`s look elegant, but when I tried I got error message:
7951: you entered an expression that has invalid reference to the
RecordsetClone property.
What is that mean? how can I fix this?

Thanks again,
Tom

Arvin Meyer said:
Use a recordset:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

If rst.EOF And rst.BOF Then
MsgBox "There are no records to edit. This form will not open."
Cancel = True
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Form_Open

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


Tom said:
I have a form based on a query.

While the form loading I want to check if there`re any results.
If no results, msgbox say "No Data found"

I think that sql statment will do the job, but i find hard to wrap it up
into a sub.
"SELECT DISTINCTROW Chartqry.Price, Count(*) AS [Count Of Chartqry] FROM
Chartqry GROUP BY Chartqry.Price;"


Thanks,

Tom
Click to expand...
Click to expand...
 
Make sure that you have a reference set to DAO (3.6 for Access 2000 and
greater). Do that in any code window:

Tools ... References
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Tom said:
Thanks Arvin,
It`s look elegant, but when I tried I got error message:
7951: you entered an expression that has invalid reference to the
RecordsetClone property.
What is that mean? how can I fix this?

Thanks again,
Tom

Arvin Meyer said:
Use a recordset:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

If rst.EOF And rst.BOF Then
MsgBox "There are no records to edit. This form will not open."
Cancel = True
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Form_Open

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


Tom said:
I have a form based on a query.

While the form loading I want to check if there`re any results.
If no results, msgbox say "No Data found"

I think that sql statment will do the job, but i find hard to wrap it up
into a sub.
"SELECT DISTINCTROW Chartqry.Price, Count(*) AS [Count Of Chartqry] FROM
Chartqry GROUP BY Chartqry.Price;"


Thanks,

Tom
Click to expand...
Click to expand...
Click to expand...
 
Make sure that you have a reference set to DAO (3.6 for Access 2000 and
greater). Do that in any code window:

Tools ... References
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Tom said:
Thanks Arvin,
It`s look elegant, but when I tried I got error message:
7951: you entered an expression that has invalid reference to the
RecordsetClone property.
What is that mean? how can I fix this?

Thanks again,
Tom

Arvin Meyer said:
Use a recordset:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

If rst.EOF And rst.BOF Then
MsgBox "There are no records to edit. This form will not open."
Cancel = True
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Form_Open

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


Tom said:
I have a form based on a query.

While the form loading I want to check if there`re any results.
If no results, msgbox say "No Data found"

I think that sql statment will do the job, but i find hard to wrap it up
into a sub.
"SELECT DISTINCTROW Chartqry.Price, Count(*) AS [Count Of Chartqry] FROM
Chartqry GROUP BY Chartqry.Price;"


Thanks,

Tom
Click to expand...
Click to expand...
Click to expand...
 
Thank you Arvin for your time.
eventually, I used Dcount.

Thanks,
Tom
Arvin Meyer said:
Make sure that you have a reference set to DAO (3.6 for Access 2000 and
greater). Do that in any code window:

Tools ... References
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Tom said:
Thanks Arvin,
It`s look elegant, but when I tried I got error message:
7951: you entered an expression that has invalid reference to the
RecordsetClone property.
What is that mean? how can I fix this?

Thanks again,
Tom

Arvin Meyer said:
Use a recordset:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

If rst.EOF And rst.BOF Then
MsgBox "There are no records to edit. This form will not open."
Cancel = True
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Form_Open

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


I have a form based on a query.

While the form loading I want to check if there`re any results.
If no results, msgbox say "No Data found"

I think that sql statment will do the job, but i find hard to wrap it up
into a sub.
"SELECT DISTINCTROW Chartqry.Price, Count(*) AS [Count Of Chartqry] FROM
Chartqry GROUP BY Chartqry.Price;"


Thanks,

Tom
Click to expand...
Click to expand...
Click to expand...
 
Thank you Arvin for your time.
eventually, I used Dcount.

Thanks,
Tom
Arvin Meyer said:
Make sure that you have a reference set to DAO (3.6 for Access 2000 and
greater). Do that in any code window:

Tools ... References
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Tom said:
Thanks Arvin,
It`s look elegant, but when I tried I got error message:
7951: you entered an expression that has invalid reference to the
RecordsetClone property.
What is that mean? how can I fix this?

Thanks again,
Tom

Arvin Meyer said:
Use a recordset:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

If rst.EOF And rst.BOF Then
MsgBox "There are no records to edit. This form will not open."
Cancel = True
End If

Exit_Form_Open:
Exit Sub

Err_Form_Open:
MsgBox Err.Number & ": " & Err.Description
Resume Exit_Form_Open

End Sub
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access


I have a form based on a query.

While the form loading I want to check if there`re any results.
If no results, msgbox say "No Data found"

I think that sql statment will do the job, but i find hard to wrap it up
into a sub.
"SELECT DISTINCTROW Chartqry.Price, Count(*) AS [Count Of Chartqry] FROM
Chartqry GROUP BY Chartqry.Price;"


Thanks,

Tom
Click to expand...
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How Rename a Table in SQL Statment that Contain Data? 3
Execute sql 5
recordssetClone.recordcount 4
using VBScript to open a QueryDef 0
If statments 1
Help 4
If statment Help 1
SQL Statment to create a Report 3

Back
Top