insert into not working 2

[Lhuffst]

I have a table that lists the forms people need to have. Since there will ultimately be a lot of people using this system, I want to add new forms that are required programmatically.
Currently my table (table A)
has fields: (I'm starting with these and will add more fields later)

id = number
formname = text
addit = yes/no
deleteit = yes/no

Each person (workgroupid) can have multiple forms but not everyone has the same forms.
what I need to do is get the distinct workgroup ids and then loop through to add the new form. I am defaulting the addit, deleteit to no's to start with.

I tried
do while not eof()
select distinct workgroupid from table a
insert into table a (workgroupid, addit, deleteit) values ([workgroupid], false, false)
loop

I know the syntax isn't correct and in fact the first thing it doesn't like is the insert statement.
Can anyone give me some guidance?

Thanks
lhuffst

 

[LaurieHamlin]

L,
1. You're pulling from table A and inserting into table A. You need to pull from 1 table and insert into another table.
2. You need to go to the next record within the loop. If you don't it's an endless loop.
I hope that helps.
Laurie

 

[Lhuffst]

ok I'll give it a try. Thanks

 

[dhookom]

Is that your actual code or pseudo code? Did you attempt to compile your code?

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

So your "id = number" is actually "workgroupid = number"

And if your code would work:

do while not eof()
 select distinct workgroupid from table a
 insert into table a (workgroupid, addit, deleteit) values ([workgroupid], false, false)
loop

You would have an endless loop.
Let's say you start with 10 records, and you point to 1st record. You copy that record and move to 2nd record. Now you have 11 records. Every time you insert a record, you have one more records in your table, so you are ALWAYS 10 records from the EOF and you will never reach EOF

Also, I would consider 2 tables:
TableA
workgroupid = number (PK)
FormId = number FK
add = yes/no
delete = yes/no

and TableB
FormId = number (PK)
FormName = text

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Lhuffst]

thanks much everyone.
Duane - yes it was pseudo code and with LaurieHamiln and Andy's comments, I was able to see what I was doing incorrect and resolved the issue.
I did have to use the 2 tables and then it was fine.
Thanks again

 

[Lhuffst]

oh yea forgot to add in .movenext -- that was crucial