lesson required in the use of qry as recordset

[penguino29]

Hi Gary,

That's exactly the problem! If a query that requires parameters is used as a recordset, one will need to use querydef and feed the parameter into it beforehand.

In post #27 of this article is an example of how it is done

http://www.access-programmers.co.uk/forums/showthread.php?t=154306&page=2

Just shout again if you are stuck or need some sample codes.

 

[GaryPanic]

you mean this ?if so I need to digest

Option Compare Database
Dim db As DAO.Database 'variable to point to a database
Dim rsquery As DAO.Recordset

Private Sub Search_Button_Click()
Set db = CurrentDb
Dim vabQuery As String
Dim qdfpara1 As QueryDef

vabQuery = "qryfaultfinder"

Set qdfpara1 = db.QueryDefs(vabQuery)
qdfpara1![Forms!frmsearch!product_type] = Me.Product_Type
qdfpara1![Forms!frmsearch!range] = Me.Range
qdfpara1![Forms!frmsearch!type] = Me.Type
qdfpara1![Forms!frmsearch!typedetails] = Me.TypeDetails
qdfpara1![Forms!frmsearch!manufacturers] = Me.Manufacturers


Set rsquery = qdfpara1.OpenRecordset()



With rsquery
If rsquery.RecordCount > 0 Then
.MoveFirst
Do While Not .EOF

 

[penguino29]

OK, I've done a bit more research into parametric queries

http://forums.devarticles.com/microsoft-access-development-49/pass-parameters-from-vba-to-query-62367.html

And here's my outline to show you how its done... your mileage may vary

Private Sub Search_Button_Click()

Dim dbs As DAO.Database
Dim rsQuotes As DAO.Recordset
Dim CoverMain As DAO.Recordset
Dim qdfpara1 As QueryDef

Set dbs = CurrentDb()

'--- set up a query definition for MultisysQry
Set qdfpara1 = dbs.QueryDefs("MultisysQry")

'--- and feed it with a parameter assuming there is only 1 parameter :)
qdfpara1.Parameters(0) = Forms![quotefrm]![Quoteno]

'--- OK, he is fed! Now convert it and assign it as the source recordset rsQuotes  
Set rsQuotes  = qdfpara1.OpenRecordset()

'--- assigning CoverMain as the destination recordset
Set CoverMain = dbs.OpenRecordset("coverMain") 

If rsQuotes.RecordCount > 0 Then
rsQuotes.MoveFirst

'--- we loop the source recordset rsQuotes
Do While Not rsQuotes.EOF

	'--- adding to the destination recordset
	With CoverMain
	.AddNew
	
	'--- 	e.g.: !DestinationField = rsQuotes("SourceDataField")
	'---    or   !DestinationField = (calculated expression)
	!QuoteNo = Me.QuoteNo
	!CSIexpeneses = Me.CSIexpeneses * Me.Transactionaction
	....etc
	....etc
	.Update

rsQuotes.Movenext
Loop
....etc
....etc

 

[GaryPanic]

yes - i think that will do it - its late and I need to totally digest -
will revert when I get it wrong

 

[GaryPanic]

Update -

sorry I have not reply to this one - but have been busy on other stuff

will let everyone kno how i get on
thanks in advance

g

 

[GaryPanic]

ok heres where we are at

Dim dbs As DAO.Database
Dim rsQuotes As DAO.Recordset
Dim MulitquoteCovertbl As DAO.Recordset
Dim qdfpara1 As QueryDef

Set dbs = CurrentDb()

'--- set up a query definition for MultisysQry (this is the qry )
Set qdfpara1 = dbs.QueryDefs("MultisysQry")

'--- and feed it with a parameter assuming there is only 1 parameter
qdfpara1.Parameters(0) = Forms![quotefrm]![QuoteNo]

'--- OK, he is fed! Now convert it and assign it as the source recordset rsQuotes
Set rsQuotes = qdfpara1.OpenRecordset()

'--- assigning Multiquotecovertbl as the destination recordset
Set MulitquoteCovertbl = dbs.OpenRecordset("MulitquoteCovertbl")

If rsQuotes.RecordCount > 0 Then
rsQuotes.MoveFirst

'--- we loop the source recordset rsQuotes
Do While Not rsQuotes.EOF

'--- adding to the destination recordset
With MulitquoteCovertbl
.AddNew

'--- e.g.: !DestinationField = rsQuotes("SourceDataField")
'--- or !DestinationField = (calculated expression)
!QuoteNo = Me.QuoteNo
!CSIexpeneses = Me.CSIexpeneses * Me.Transactionaction I think this will fall over here isn't me. relating to the current form ?
So !CSIexpeneses
should be !CSIexpeneses =rsquote("CSIexpeneses")*me.transaction
the first bit is my new field in my new record = the field already entered in my qry -times transaction (either a 0,1 or -1) does this sound right ???
' ....etc
' ....etc
.Update

rsQuotes.MoveNext
Loop
'....etc
'....etc

 

[GaryPanic]

just read my post looks messy.


CSIexpeneses = Me.CSIexpeneses * Me.Transactionaction I think this will fall over here isn't me. relating to the current form ?
So !CSIexpeneses
should be !CSIexpeneses =rsquote("CSIexpeneses")*me.transactionaction

the first bit is my new field in my new record = the field already entered in my qry *times transaction (either a 0,1 or -1) does this sound right ???


what I am doing is value A in my Qry will be added to a histroy table but along the way pick up bits and simple cal

theres about 10 fields in the qry and table

 

[GaryPanic]

and it bitched out on loop
loop without do

?

 

[penguino29]

Hi Gary,

Nice to know that you are willing to give it a go.

Yes basically you are picking up the source data a row at a time, perform a calculation and add it to the destination.

The idea is to use a single loop, moving the source dataset one record at a time using rsQuotes.Movenext.

Sorry mate there was an End With statement missing...

'--- we loop the source recordset rsQuotes
Do While Not rsQuotes.EOF

	'--- adding to the destination recordset
	With CoverMain
		.AddNew
	
		'--- 	e.g.: !DestinationField = rsQuotes("SourceDataField")
		'---    or   !DestinationField = (calculated expression)
		!QuoteNo = Me.QuoteNo
		!CSIexpeneses = Me.CSIexpeneses * Me.Transactionaction
		....etc
		....etc
		.Update
 	[COLOR="Red"]End With[/COLOR]

rsQuotes.Movenext
Loop
....etc
....etc

Just a note on calculations - make sure that if you are referring to any form fields, they are unique and meaningful in a calculation.

In other words, for example: if you have a continuous form and referring to a field there [which would give you multiple values depending on the current record bookmark], then the following calculation

!CSIexpeneses  = Me!ContinuousFormField * rsQuote("somethingelse")

will certainly be meaningless (even though the calculation will produce a value) because the records are UNrelated (a field in a continuous form might or might not be related to certain point in the recordset), and there are multiple values of Me!ContinuousFormField so the result is likely to be garbage.

 

[GaryPanic]

Sorry in the delay in getting back to this - other projects ..


I will try

but doesn't Me! relate to the form I am in at the moment ?


So my record set will be a value say 10 and my form will have one of the following values (Transactionaction = 0,1, or-1) and the history will be
10 * 0 (noValue)
or 10* 1(Debit)
or 10*-1 (Credit)
then next record ....

 

[GaryPanic]

Yesss.....
tested on 1 field
on all three option - worked
I will need to tidy up what I have done and put it into my "Live " version

need to mess about with it a bit more

I might put an idiots guide together on this as its not easy as they say in the books

Big thanks Penguino

this will certainly make the d/b more code heavy - but more fluid

 

[penguino29]

Hi Gary,

I am glad to hear that your perseverance has given you a result.

Don't worry that the code is getting heavier, that's normal for a professional app.

Your Access VBA "Kung-fu" has reached a high plateau of enlightenment! Treat yourself a good pint of beer for making it so far, and kiss those messy queries good-bye.

 

[GaryPanic]

Its bitching

I do this at hoem and it works ok - I copied the code (cut paste) put it into my Live set and - bang - didn't like it

Runtime Error 3061 and error at

Set rsQuotes = qdfpara1.OpenRecordset()

I am going to copy the full data and take it home

and try it agian

any pointers would be most helpful

(At home its split but not running accross a server)

 

[GaryPanic]

my fault

two fields in the qry were not required and it cocked everything ..
works fine

G