lesson required in the use of qry as recordset

[GaryPanic]

OK guys
in my head (which is quite often empty) I want to do x with a qry
basically its an append qry - but its a little messy and I think that I can do better with the use of recordset

So I want to :-
select data (qry done nice an easy) and then add it to another table (history)
and change some of the values along the way

so qry have
QNo (common accross everything)
Eventname
Dates
Value1
Value2
Value3
these 3 values I will tinker with along the way by times either by 1 or -1 or 0

so How do I call the qry in vb then add it to another table

Query1 (name of query)
History (name of target table)
I have looked around and I am missing somthing

 

[jdraw]

Can you give a specific example with real data?

This

so qry have
QNo (common accross everything)
Eventname
Dates
Value1
Value2
Value3
these 3 values I will tinker with along the way by times either by 1 or -1 or 0

can be interpreted various ways

 

[GaryPanic]

this is what I am trying to do but covermain is a table

Dim dbs As DAO.Database
Dim CoverMain As DAO.Recordset,
Set dbs = CurrentDb
Set CoverMain = dbs.OpenRecordset("coverMain")


With CoverMain
.AddNew
!QuoteNo = Me.QuoteNo
!CSIexpeneses = Me.CSIexpeneses * Me.Transactionaction
etc..
me. is the form I am in and transactionaction is 1,-1,0

 

[GaryPanic]

will do ..

 

[GaryPanic]

will do ..

Qno ...12345
EventName - Birthday Party
Dates 01/01/11
Value1 65,000
Value2 20,000
Value3 1,000,000

what are the value
V1 = cost of event
V2 Equipment = marquee or whatever
V3 = PL limit
other values
but you coudl have 2 or more evnets in the qry
so Birthday
Bar Mitzah (?)
Wedding
each with different Values but still part of the qry

 

[GaryPanic]

So B'day at 65,000 then oops something happens and I need to cancel it a return premium will be -1 and this woudl be for the whole qry - orr I need to add an endorsemnt - but th is does not effect the limts or the ocst so a 0
so everthing that is additional will be 1
anything that is a return will be -1
and things that do not need to be accountded for will be 0
(I have to aggregate as well)

 

[penguino29]

Hi GaryPanic,

You might just be able to do all this with an append query.

First you create a select query with the Quote table and the fields that you wish to append to the CoverMain table. Then you change the query type from select query to an append query, and you will be asked which table you wish to append the data to:





Once you've confirmed the above, you can then click on the Append To row and match the fields from the Quote table that you want to append to the CoverMain table.


You can also apply criteria to choose which records from the Quote table you want to append. (e.g. you wish to append QuoteNo:11 only)

The clever bit comes in where you can make up your own values, from a calculation based on form field and/or fields from table.

Save this query and when you run it from a macro/vba via a button, it should append the data.

Good luck.

 

[GaryPanic]

Penguino - thanks for this -
yes this is how I currently have it done - but i want to use the other method
as it is "cleaner" - more complicated than i thought
in essense all i need is a qry as a record set - and that seems to be the issue (i have select qry setup)

 

[penguino29]

You are right, its cleaner using codes - I apologise that I have misunderstood and not read your post properly.

To use code, you will need to open 2 recordsets, one for the existing select query (source data) and one for the CoverMain table (destination)

Simply refer the select query as if it was a table.

Dim dbs As DAO.Database
Dim CoverMain As DAO.Recordset, rsQuotes As DAO.Recordset
Set dbs = CurrentDb
Set CoverMain = dbs.OpenRecordset("coverMain")
Set rsQuotes = dbs.OpenRecordset("quotes_select_query")


With CoverMain
.AddNew
!QuoteNo = rsQuotes.QuoteNo

etc..

Is that what you were looking for?

 

[GaryPanic]

yes thats excatly what i am trying to do and I thought i had tried this - but i will retry again

its as if the select qry isn't runnign - but i will have a bash and revert

(Sorry for the delay in responding - had to do some work )

 

[GaryPanic]

GBrr . &*#£$!!!!
ok may be its me . but this isn't playing
MultisysQry is my qry and MulitquoteCovertb is the target tbl i am trying to get to the qry is a select qry .. heres the code to date

any pointers please
Private Sub mulitadding()



Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("MultisysQry")
Dim MultisysQry As Recordset, MulitquoteCovertbl As Recordset
Set MultisysQry = dbs.OpenRecordset("MultisysQry")
Set MulitquoteCovertbl = dbs.OpenRecordset("MulitquoteCovertbl")

With MulitquoteCovertbl
.AddNew
!QuoteNo = MultisysQry.QuoteNo
!Texttxt = quotemultitbl.testtxt

.Update

End With

 

[gemma-the-husky]

gary

seriously - if you can do this with an updateable query, then definitely go that way.

i end up having to execute sql statements (currentdb.execute "insertstatement") as a last resort occasionally, but only if the recordset is not updateable. you have to manage the recordset loop, assemble the insert statements. best avoided, if possible

 

[GaryPanic]

bother .. it would of been tider to do it the way I wanted -but always listen to the voice of reason - I'll re-write the append qry to do what I want
cheers anyway

( the books state that this is easy - lying little b#stards)

 

[penguino29]

Hi GaryPanic,

Don't give up. You are almost there.

What error did you see and why didn't it work?

BTW. when you are referring to a field in a recordset/table, try using this notation

recorderset("fieldname")

e.g.

With MulitquoteCovertbl
.AddNew
!QuoteNo = MultisysQry("QuoteNo")


Apologies for giving you the incorrect notation in my earlier post!

 

[gemma-the-husky]

well, it's doable. it's this sort of thing

set rst = db.openrecordset("somequery")
while not rst.eof
 
'ok, using the current record, produce an insert statement that will work
 
sqlstrg = "insert into sometable "
sqlstrg = sqlstrg & " ( fld1, fld2, fld3) "
sqlstrg = sqlstrg & " select fld1value, fld2value .... "  etc
 
on error goto errorhandler
currentdb.execute sqlstrg
 
wend

so, at this point, you now have to get the values that correspond to each field in order, and format and wrap them in appropriate chars "" or ## as appropriate.

in the case of error, you also need a way of managing the error, and either continuing the loop, or aborting - whatever option you take, you then need also to decide what to do about records already written.

it's just a bind to do for a large number of fields and easier if you can just do it with a query.

 

[Galaxiom]

This line would definitely be a problem:

!QuoteNo = MultisysQry.QuoteNo

Recordsets must use the bang operator for fields:

!QuoteNo = MultisysQry[B]![/B]QuoteNo

It is one of the few places where Access really cares which operator is used.

Penguin's nototation is another aternative.

!QuoteNo = MultisysQry("QuoteNo")

which is really just a shorthand for a reference via the fields collection of the recordset:

!QuoteNo = MultisysQry.Fields("QuoteNo")

 

[GaryPanic]

Sorry everyone - i went off in a huff -lots of swearing thinking to myself that i am a bigger idiot than I look, and haven't look at the site for a couple of days -

I will revisit and revert back
big thank in advance

G

 

[GaryPanic]

heres where we are at

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("MultisysQry") @ bitching here
Dim MultisysQry As Recordset, MulitquoteCovertbl As Recordset
Set MultisysQry = dbs.OpenRecordset("MultisysQry")
Set MulitquoteCovertbl = dbs.OpenRecordset("MulitquoteCovertbl")

With MulitquoteCovertbl
.AddNew
!QuoteNo = Me!QuoteNo
!Texttxt = quotemultitbl!testtxt
.Update

End With

runtime error 3061 too few paratimer expected 1

 

[penguino29]

Hi GaryPanic,

Just checking - does the query "MultisysQry" run ok in a query window? Is it a straight forward select query or does it need any parameter input from a form/user?

 

[GaryPanic]

its a select qry with
[forms]![quotefrm]![Quoteno]

g
qry runs ok
I put a button on my form and it runs from this ok