You are here:

Using MS Access/query by vba from existing query

Advertisement


Question
QUESTION: Hi expert,

I have one query in access as below.

SELECT dcount("id","dn_details","id<=" & id) AS cnt
FROM dn_details
WHERE DN_DETAILS.D_C_NO=Forms.Debit_Note.D_c_No And stax='0';




------trying to retrieve data by vba from above query but system throws error to few parameters run time error 3061

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim str As String

Set db = CurrentDb
'Dim str, strSQL As String

strSQL = "select cnt from seq_rpt_stax_rmks"
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

please help.

vimal thakar

ANSWER: First, why would you use DCount like that? Why not just have a query:

SELECT * FROM dn_Details
WHERE DN_DETAILS.D_C_NO=Forms.Debit_Note.D_c_No And stax='0';

Then in VBA use:

Dim lngCount As Long:

lnCount = DCount("[ID]","seq_rpt_stax_rmks")

That should get you what you want:

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA



---------- FOLLOW-UP ----------

QUESTION: Dear Expert,

I don't want counts I want values as below

1
2
3

there after I will loop through all values and create string as below.

1,2,3 and will be updated in textbox of form.

Regards,

Vimal Thakar

ANSWER: I'm really not clear what you want. If you want to concatenate values from multiple records into one comma separated string, you can do that using this function:

http://www.utteraccess.com/forum/Combine-Multiple-Records-t1187785.html&hl=conca

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Dear Expert,

my problem is as below.

I have query in front end access.

I want to retrieve value from that query by vba query STRSQL="Select *"

at that time system throws error "error 3061"

Answer
You really are giving me very little to go on here. You can't retrieve a value using a SQL statement. Since a SQL statement can return a number of fields and rows, you can't assign the results to a value.

What you can do is open a recordset using a SQL statement, then assign the contents of a field to a variable.

If you would explain the exact, real world situation you are dealing with using a specific example of real data, I might be able to help further. But with the scanty info you are giving me I can only guess at what you want.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.