INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

insert into not working

insert into not working

(OP)
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

RE: insert into not working

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

RE: insert into not working

(OP)
ok I'll give it a try. Thanks

RE: insert into not working

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

Duane
Hook'D on Access
MS Access MVP

RE: insert into not working

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

And if your code would work:

CODE

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.

RE: insert into not working

(OP)
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

RE: insert into not working

(OP)
oh yea forgot to add in .movenext -- that was crucial :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close