its a toughie 2

[brightstar]

ok.
i have a tabular form displaying the records of all the contacts in the database. the user can filter these records down to end up with just the few contacts he wants to hit with a marketing shot.
the name of the marketing shot is available to the form.

i want the user to push a button that will send the now filtered contact names to the marketing shot history table, insert their names, the current date and the name of the marketing shot into the table.

so i have a contacts form displaying this kinda data:

ContID ContFname ContLname ContIndustry ContSize
1 Bob Kelly Cosmetics PLC
2 Tim Strand Cosmetics PLC
3 Sally Smith Fabrics PLC
(Marketing shot name = run1)
and after pushing the button, on the form header i guess, the ContactID should get appended to the marketing shot history table that looks like this

ShotID ShotName Receiver ShotDate
1 Run1 1 06/03/04
2 Run1 2 06/03/04
3 Run1 3 06/03/04

this making any sense?
i gess what im going to have to do is get hold of the recordset of the current form and then somehow append that to the shot table

any ideas?

http://www.agonyonline.co.uk

free, anonymous advice provided by the whole world

 

[RoyVidar]

To get hold of the current forms recordset;

[tt]dim rs as dao.recordset
set rs = me.recordsetclone[/tt]

To append your data to the other table, several appraoches, open a recordset, use sql. You don't say anything about it, but are you trying to use some kind of "autonumbering" on the run field?

Anyway, open a recordset with dao:

[tt]dim rs2 as dao.recordset
set rs2 = currentdb.openrecordset("NewTable")[/tt]

Use a loop, where you loop thru the forms recordset and append

[tt]with rs2
rs.movefirst
do while not rs.eof
.AddNew
!shotid = rs!contid
!shotname = <whatever>
...
.update
rs.movenext
loop
rs2.close
end with
set rs=nothing
set rs2=nothing[/tt]

- typed not tested, some errorhandling will be needed...

Roy-Vidar

 

[gol4]

use the forms recordsetclone

Dim rst As Recordset
Dim rst2 As Recordset
Set rst2 = CurrentDb.OpenRecordset("tablename")
Set rst = Me.RecordsetClone
Do Until rst.EOF
rst2 AddNew
rst2.Fields(0) = rst.Fields(0)
rst2.Fields(1) = rst.Fields(1)
1 for each field...
rst2.Update
Loop

 

[gol4]

Looks as if Roy posted while I was typing
Good info Roy sorry

 

[RoyVidar]

Hey - nothing to be sorry about, there are also some slight differences in our approaches that might benefit whoever reads this (he he - and I've cheated, my Mom forced me to take typewriting lessons ages ago, hated it then, but now it does come in handy;-))

Roy-Vidar

 

[brightstar]

they both sound great,
i'll work on it now.
looks like this as good a time as any to sit down and learn DAO and recordest stuff.
or should i start learning ADO instead? are they the same? or is DAO more useful for this kinda stuff?

thanks guys

http://www.agonyonline.co.uk

free, anonymous advice provided by the whole world

 

[RoyVidar]

It seems examples on this site are more DAO than ADO.

I don't want to give a firm opinion, but suggest have a look at both.

Here are some threads on DAO/ADO:
thread1121-782286, thread702-782983

Here's also a thread mainly on ADO, that might be interesting:
thread705-788005

Roy-Vidar

 

[brightstar]

very helpful, thanks.

http://www.agonyonline.co.uk

free, anonymous advice provided by the whole world