Solved

Update query-recordsaffected

Posted on 1998-10-07
34
378 Views
Last Modified: 2008-03-06
I want to get the number of records updated by a updatequery
NB!The updatequery has a criteria =[Forms]![Myform]![myfield]

So I tought I could use the recordsaffected after a execute on a querydef object but....

Here is my code
Sub ThisIsNotWorking()
Dim dbs As Database, qdf As QueryDef
Dim strSQL As String
'..cutting...cutting
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("MyUpdateQuery")
qdf.Execute '--->error
MsgBox qdf.RecordsAffected
qdf.Close
Set dbs = Nothing
Exit Sub
end sub
What is happen is that I get error in the execute line saying "to few parameters expecting 1
If I anlyze the qdf.perameters the value are empty, but instead the qdf.name property are set to [Forms]![Myform]![myfield]
So the thin I want to know is:
Is this a bug in access?? Does anybody have a workaround??
perove

PS If I use a criteria ex 2 in my query it works OK

PPS Yes I do have the form open and YES I can run the query with no problem from the databasewindow
0
Comment
Question by:perove
  • 15
  • 12
  • 6
  • +1
34 Comments
 
LVL 3

Expert Comment

by:dapperry
Comment Utility
I'm not sure why that happens, but here's a work around.  Instead of putting a form control value as the criteria in the query, instead put a real parameter in the criteria expression.  Make sure you list the paramter name, and data type under Query|Parameters.(in the query designer).  Then in your code do something like this:


   Dim dbs As Database, qdf As QueryDef
   Set dbs = CurrentDb
   Set qdf = dbs.QueryDefs("MyUpdateQuery")
   qdf.Parameters("Parameter1")=myfield ' Key line
   qdf.Execute '--->error
   MsgBox qdf.RecordsAffected
   qdf.Close
   Set dbs = Nothing
   Exit Sub

You can then call the query from any form in your app.

:) D Perry
0
 
LVL 9

Author Comment

by:perove
Comment Utility
dappery,
I'm aware of this option, but is not what I been looking for. The code is part of a function that on some of my queries (it is about 150 of them) should if a certian description are put in give a  message on how many records that are beiin' affected.
(there are also different number of parameter on the queries so doing it your way will give me a LOT of extra work, and sometimes the parameters are just single fixed value.

Thnks a lot for the input, (this has to be some kind of bug) but 'll have to reject it for now, but please keep on looking.

perove



0
 
LVL 9

Author Comment

by:perove
Comment Utility
inc points
0
 
LVL 7

Expert Comment

by:spiridonov
Comment Utility
If you wish ,you can send me an extract of your mdb with the form and query and I'l have a look. 39414530@mailexcite.com
0
 
LVL 5

Expert Comment

by:kulikuli
Comment Utility
Did you try to get the recordsaffected property from the database? Like msgbox dbs.recordsaffected
You also could try runsql method directly on the database and then get the recordsaffected property from the database object.
like dbs.runsql dbs.querydefs("myquery").sql
msgbox dbs.recordsaffected

0
 
LVL 9

Author Comment

by:perove
Comment Utility
spiridinov.
You can easy set ut the testing yourself.
create a updatequery in Northwind or whereever and add a refrence to a form in the criteria.
Then test with the code in my example. (I belive it is easyer then to zip up & down the whole mdb for us)

kulikuli:
there is a runsql method on the dbs object??
(dim dbs as database)
Well,I can't find it. If you mean docmd.runsql this will not give me the recorsaffected

Keep on the quest, I need this quickly
perove


0
 
LVL 3

Expert Comment

by:dapperry
Comment Utility
I don't see how it is a whole lot more work to have parameters in a saved query, instead of referenced form controls, but ... I'll look into it on your way anyhow.

:) D Perry
0
 
LVL 9

Author Comment

by:perove
Comment Utility
dappery,
If I started from scrach you would be right, but the thing is that the queries are alredy written... It was a request from the customer that he would like to se (and log) how may records that are affected. So this is a further develop of a existing program.

I have discoverd that the parameter(=[Forms]![Myform]![myfield] ) are stored in the .name of the querydef.

so if you look at my other question http://www.experts-exchange.com/topics/comp/databases/access/Q.10087139

if I get an answer to this I will be able to do a querydef.parameter=Function(querydef.name)
 
perove
0
 
LVL 5

Expert Comment

by:kulikuli
Comment Utility
I'm sorry there is no runsql on the database object. I ment to mention the execute method on the database, I suppose.
The problem is I don't have MS Access here right now. There is a method on the database that runs sql-statements anyway. That is what you need for a workaround that is as good as what you are trying to do right now. Check the helpfile for methods on the database object.
0
 
LVL 9

Author Comment

by:perove
Comment Utility
kulikuli,
using this method I get the same error as described when I execute the querydef itself. I have to reject it. I have solve the problem (or at least get a workaround) , but will leave the Q open to see if other comes up with other proposal
Here is the code I've been testing on

Private Sub Command180_Click()
Dim dbs As Database, qdf As QueryDef, rs As Recordset
Dim strSQL As String
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("Godkjenning oppdater alle for valgt uke")
dbs.Execute qdf.Name
MsgBox dbs.RecordsAffected
qdf.Close
Set dbs = Nothing
End Sub

If this was not what you had in mind, let me know

perove
0
 
LVL 5

Expert Comment

by:kulikuli
Comment Utility
That is just exactly what I ment! In this way you perform the action on the database object and retreive the recordsaffected property from it. The only thing I forgot was that the SQL statement has to be a concrete querydef :((
0
 
LVL 5

Expert Comment

by:kulikuli
Comment Utility
I am not happy with your rejection of my answer. It is just I didn't have MS Access to verify :(
0
 
LVL 9

Author Comment

by:perove
Comment Utility
Kulikuli,
You misunderstand me. Your solution gives me the same error as I had in my original query.
Pleas read my original question, the execute gives me an error if I run it on the querydef OR if I do like you suggest do it on a databse (test for yourself and see)
The workaround is another way (i set the qudf.parameters(0)=qdf.name(0), but it has nothing to do with your proposal running .execute under the dbf object.

Hope you are more happy with the rejection now ..or??

perove
0
 
LVL 5

Expert Comment

by:kulikuli
Comment Utility
ok, I'm sorry. I just checked MS Access to see how I have done it before:
db.execute strSQL
msgbox db.recordsaffected

This doesn't work?
Your problem is that you get a message about expected parameters?
If you post the exact code as a comment I promise I will get it solved. Post it within 1 hour, please. Then I can almost garantee you you'll get my answer few minutes later.

Your question about that 'bug': If you create a temporary querydef, then the SQL-statement becomes the querydefs' name.

0
 
LVL 9

Author Comment

by:perove
Comment Utility
Kulikuli.
here are the code:

Private Sub Command180_Click()
Dim dbs As Database, qdf As QueryDef, rs As Recordset
Dim strSql As String
Set dbs = CurrentDb
Set qdf = dbs.QueryDefs("Query1")
dbs.Execute qdf.SQL
MsgBox dbs.RecordsAffected
qdf.Close
Set dbs = Nothing
End Sub

NB Query1 is a query that have a reference to a field on a form in the criteria, THIS IS THE PROBLEM!
If I have criteria ex =40 then it works OK the problem is when the criteria is [Forms]![Myform]![myfield] in the query.

OK
perove
0
 
LVL 3

Expert Comment

by:dapperry
Comment Utility
Discovered your solution.  Just set mystery parameter before execution



     Private Sub Command180_Click()
     Dim dbs As Database, qdf As QueryDef, rs As Recordset
     Dim strSQL As String
     Set dbs = CurrentDb
     Set qdf = dbs.QueryDefs("Godkjenning oppdater alle for valgt uke")
     qdf.Parameters(0)=me!ControlName ' Set mystery parameter
     dbs.Execute qdf.Name
     MsgBox dbs.RecordsAffected
     qdf.Close
     Set dbs = Nothing
     End Sub

Hope this does what you want now.  You don't have to rewrite the queries, and the error doesn't show up.

:) D Perry
0
 
LVL 3

Expert Comment

by:dapperry
Comment Utility
Actually you might want to cut these lines:

dbs.Execute qdf.Name
MsgBox dbs.RecordsAffected

and do
qdf.execute
msgbox qdf.recordaffected

:) D Perry
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 9

Author Comment

by:perove
Comment Utility
dparry, this was what I was doing first(see original question!) I change to dbs.execute ..
because it was kulikulis suggestion but it is still not working as I want.
THE PROBELM IS THE REFERENCE TO A CONTROL IN THE QUERY!!!
Please test & see for yourself.
perove
0
 
LVL 5

Expert Comment

by:kulikuli
Comment Utility
Is that it? That shouldn't be a problem I think. You just get the criteria from the form, put it in a string or whatever datatype your parameter is, like:
strMyFormInput=forms!MyForm!MyControl
qd.parameters("MyFormInput")=strMyFormInput
qd.execute

What I suspect is that you are not using parameters, but you are using criteria. There is an important difference between these clauses: criteria are tested for each record, then the rest of the SQL-statement is run. Parameters are filled in right at the beginning and datatype dependend. The use of parameters is more efficient and faster. In order to use parameters you need to declare a parameter clause at the beginning of the sql-statement the query is based on. Dappery's answer won't work.
0
 
LVL 5

Expert Comment

by:kulikuli
Comment Utility
Please let me know if you accept the above solution (in my comment)
0
 
LVL 9

Author Comment

by:perove
Comment Utility
daperry, this is the same answer as you have provided before? And I have already told you why I rejected it.
but i can tell again:
I will not rewrite the code to 150 different queries.
(se my answer to you before ...)
Actually I have myself discoverd a workaround using this code:

If Not qdf.Parameters.Count = 0 Then
qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
End If
qdf.Execute
this works and It is enough with 1 line of code to set the parameter, not write 150 different solutions.(one for each query)

But i still want to see if there is a method of solving this, so that is why the Q is still here.
perove



0
 
LVL 9

Author Comment

by:perove
Comment Utility
Kulikuli & Dappery
First we are a bit cross-posted here, so we all (at least I) are a bit confused.


Let me try to make things clearer.
1.I have a program with 150 (or more) update queries that are run after each other
2.Each of the queries have a refernce to different fields on different forms in the criteria field.
3.Now the customer wants to LOG the number of rcordsaffected in each single one of the queries.
4.I dont want to write code for every single one of these 150 queries.
ex:
strMyFormInput=forms!MyForm!MyControl
qd.parameters("MyFormInput")=strMyFormInput
because the forms!MyForm!MyControl is alerady in the query, but is in in the prarmeter.NAME of the querydef NOT the .VALUE
(clearer)
so I can do a :
qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
to make it correct.
5.I still would like other suggestions

perove


 



0
 
LVL 9

Author Comment

by:perove
Comment Utility
Kulikuli & Dappery
First we are a bit cross-posted here, so we all (at least I) are a bit confused.


Let me try to make things clearer.
1.I have a program with 150 (or more) update queries that are run after each other
2.Each of the queries have a refernce to different fields on different forms in the criteria field.
3.Now the customer wants to LOG the number of rcordsaffected in each single one of the queries.
4.I dont want to write code for every single one of these 150 queries.
ex:
strMyFormInput=forms!MyForm!MyControl
qd.parameters("MyFormInput")=strMyFormInput
because the forms!MyForm!MyControl is alerady in the query, but is in in the prarmeter.NAME of the querydef NOT the .VALUE
(clearer)
so I can do a :
qdf.Parameters(0).Value = Eval(qdf.Parameters(0).Name)
to make it correct.
5.I still would like other suggestions

perove


 



0
 
LVL 5

Expert Comment

by:kulikuli
Comment Utility
You should write a procedure that gets the value from the right control, instead of directly trying to make a reference to a control.

I did this with over more than 20 comboboxes and textboxes on a form. The comboboxes represented the criteriafield and the textboxes the values for the parameters.
It is a very systematic way of assigning values to parameters. If you don't like it then I'm afraid I cannot solve your problem, because direct references only are valid within criteria.

Using WHERE clauses in this case would result in building large SQL-statements from scratch. WHERE-clauses are not ment to be variable and will become very slow and complex when getting too large.

In the end using parameters is the best way in such a flexibility as you need.

0
 
LVL 5

Expert Comment

by:kulikuli
Comment Utility
>>.I dont want to write code for every single one of these 150 queries.
You don't have to. Just give the control you need the value from a proper name and write a procedure that retreives the values from it when these are present.
0
 
LVL 9

Author Comment

by:perove
Comment Utility
I'm aware of this. If I understands you correctly you assign values of control on forms to varibels that you can get form a function (or write a function that returns this value from a control and then assifn it to parameters)

This is also what I normally do, I puts the value of the field into global variables, the use a function in the query to "filter" out the records I want. It is a wery quick and claen way to to things.
But this project was not mine from the start, It was written by a x-collegue of mine, and he loved to make direct references in all of his queries. So now I'm stucked with three of his projects that all contains this SH**

So I'll reject this, cause it did'nt give me any new info and use the way I've discoverd myself.

Thanks for the effort, and I'll guess our roads will cross again here at EE.
perove
0
 
LVL 3

Expert Comment

by:dapperry
Comment Utility
I understand that the name of the control is in the qdf.parameters(0).name property.  From what I am getting from you, you want a general function that: 1) is given the argument of the query to be run 2) Runs the query in question, by evaluating the parameters collection (.name and .type properties), set the value of the parameters based on the actual value on the form, of the form control contained in the param.name property 3) returns the recordsaffected property back to the calling statement.

Is this what you want?  If so, I've got a function for you.

:) D Perry
0
 
LVL 5

Expert Comment

by:kulikuli
Comment Utility
No, Dappery.
He wants to get a value from a Form by using criteria (WHERE clause), not parameters. And he wants the criteria to be variable (generic in some kind of way) without changing the actual references to the form that the criteria contains. This is of course in no way possible.
I think we are both wasting time trying to answer a question that its best answer is 'already known' by the author and therefore 'he does not accept' it. Fun!
0
 
LVL 3

Expert Comment

by:dapperry
Comment Utility
From my tests, it seems as though the criteria is somehow being transformed into a parameter by Access.  So by setting the values of the transformed (and new) parameters to equal the control value found in the parameter.name property, you would be accomplishing what he wants (ie He doesn't want to rewrite the 150 queries).  However, depending on what you (and he) mean by:
     And he wants the criteria to be variable (generic in some kind
     of way) without changing the actual references to the form that the criteria
     contains. This is of course in no way possible.

This could indeed be problematic.  Ball to Perove...

:) D Perry
0
 
LVL 9

Author Comment

by:perove
Comment Utility
<<     And he wants the criteria to be variable (generic in some kind  of way) without changing the actual references to the form that the criteria  contains. This is of course in no way possible. >>
Of course this is NOT what I want.

In my queries there are already a referece to a form. Located in the criteria field. If I run the query in the database window it works fine, but running it via a querydef object access is looking for a parameter that has no value just a name that are the name of the contol contaning that value.

When I discover this I put that information ont on EE as a comment, and also suggesting a workaround for me that worked, but still I wanted other suggestion 'cause this solution I have will not work if there comes a query (in the future) that coinatins a "real" parameter.

Then kukikuli suggest that I run the query use the database object instead of the querydef, but the same error occures. So I rejected his answer, and honest, I still think I was correct doing that.

What you suggest is that I set the parameter.value=parameter.name but this is what I have done already (please see my previous commntes) . And I discoverd this myself so I will reject your answer to.
I don't mean to give to create any hard feelings here so kulikuli and daperry, please reas trough the whole question and give a comment, if you really feel that your answer deserves it I will give you point .
perove

0
 
LVL 9

Author Comment

by:perove
Comment Utility
See prevoius comment
0
 
LVL 5

Accepted Solution

by:
kulikuli earned 200 total points
Comment Utility
To assign values to a parameter you MUST declare a parameter clause in the query's sql-statement. All parameters depend on this.
I think I got a workaround however:
From the parameters collection you can retreive the criteria (your fake parameter's property).
Take the sql-statement from the query.
Now Prefix that sql-statement with a parameters clause that contains as much parameters as your query's parameters.count property.Give these parameters names like Parm1, Parm2...
Then cut the WHERE statement from the sql-statement and replace your fake parameter's value with the name of a real parameter from the PARAMETER clause.
Since your fake parameters (criteria) are always references to forms you could easily find them using the Instr() function. This is because no other elements that make up your sql-statement contains a statement that contains 'forms!frmMyForm'.

Pretty cool answer, isn't it :)

Best regards,

Kulikuli
0
 
LVL 5

Expert Comment

by:kulikuli
Comment Utility
A little correction on my answer:
>>From the parameters collection you can retreive the criteria (your fake parameter's property).
Should be, of course:From the parameters collection you can retreive the criteria (your fake parameter's name property).
0
 
LVL 9

Author Comment

by:perove
Comment Utility
Yes and if I also check for every parameter.count where the value are empty, but do contain a name I can get the thin I want.
It's been a long road but this will work

perove
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now