error?

Do While Not rsSlot.BOF And Not rsSlot.EOF
Set rsSlot = adoconnection.Execute("SELECT Day, Start,Finish,Unit FROM tblImport")

adoconnection.Execute "INSERT INTO tblSlot ([day],[Start],[Finish], [Unit]) VALUES ('" & rsSlot.Fields("day") & "', '" & rsSlot.Fields("start") & "', '" & rsSlot.Fields("finish") & "', '" & rsSlot.Fields("unit") & "')"
Set rsGroup = adoconnection.Execute("Select slot_id, group from tblSlot, tblImport")
adoconnection.Execute "Insert into tblGroup ([slotid], [group]) Values ('" & rsGroup.Fields("slotid") & "', '" & rsGroup.Fields("group") & "')"
rsGroup.Close
rsSlot.MoveNext
Loop

rsSlot.Close

I am trying to insert a day,start, finish and unit into tblSlot and then take the slot_id which is a auto number from that table into tblGroup and a value from import table called group.

Could anyone tell me this why its erroing?

Set rsSlot = adoconnection.Execute("SELECT Day, start,Finish,Unit, group FROM tblImport")
Do
adoconnection.Execute "INSERT INTO tblSlot ([day],[Start],[Finish], [Unit]) VALUES ('" & _
rsSlot!day & "', '" & rsSlot!start & "', '" & rsSlot!finish & "', '" & rsSlot!unit & "')"
Set rsGroup = adoconnection.Execute("Select slot_id from tblSlot Where Unit = '" & rsSlot!unit & "'")
adoconnection.Execute "Insert into tblGroup ([slotid], [group]) Values ('" & _
rsGroup.Fields("slotid") & "', '" & rsSlot!group & "')"
rsGroup.Close
rsSlot.MoveNext
Loop until rsSlot.EOF

rsSlot.Close

I expect this is all wrong because 1, I had to guess that the group
field is actually in the tblImport table - your code didn't give much
in the way of clues, and 2, I figure there are actually multiple rows
in tblSlot that have the same value in the unit field, making the
select in the loop select multiple rows.
It is very hard to tell what your table layouts and relationships are
just from the code snippets you post.

True.

Maybe my understanding of a recordset is wrong then. I thought that by the select statement I have just done the first recordset would be from tblImport.

From that row, I could get the information I wanted to populate another table called Slot, and once that row was populated take Slot table id number and the group field from tblImport since I selected it in the first recordset and combine the two to populate another table...

This SQL, "Select slot_id, group from tblSlot, tblImport" will do
something that you never intended. It is what is known as a
Cross-Join, the single most confusing and useless ability in SQL.
It will return a recordset that contains the product of those two
tables (product as in muliplication.) For example, if there are 10
records in tblSlot and 10 records in tblImport, your recordset
would contain 100 records. If group is in tblImport, then my
change to your various SQLs will be better. The remaining
problem once again is the possibility of multiple rows in tblSlot
with the same unit.

I see your point.

So if I look at this logically :)

If I read four values from one table A which populates a different table B . In that table B is a auto key.

I then want to take the auto key from table B and write it to another table c but from the first table A I want to enter a value from the row I just read.

And do this in a loop until I have taken every row in Table A.

Is that possible?

heh... "Select slot_id, group from tblSlot, tblImport"

try that with a few thousand records in each table. :p

*confused* what do you mean????

It was a joke, linzy. He was referring to the fact that a few
thousand records in each table would end up creating a multi-
million row recordset.

I don't know what else I can give you to help. I gave you code
above that does what you want, as far as I understand your
tables (from the code you have posted,) and have twice stated
the one problem I see because of what I don't know about your
tables. What else can I do? I guess I could explain each line of
code.

' Create a recordset that contains all records in tblImport, and has the Day, start, finish, unit and group
Set rsSlot = adoconnection.Execute("SELECT Day, start,Finish,Unit, group FROM tblImport")

' Begin the loop (missing any error checking from the statement above)
Do

' Execute the SQL Insert that will create the new record in the tblSlot table,
' using four of the values from the current record in the rsSlot Recordset
adoconnection.Execute "INSERT INTO tblSlot ([day],[Start],[Finish], [Unit]) VALUES ('" & _
rsSlot!day & "', '" & rsSlot!start & "', '" & rsSlot!finish & "', '" & rsSlot!unit & "')"

' Create a new recordset that returns the autonumber slot_id from the record just inserted.
' Warning! This is a potential problem as I can't tell if there are multiple records in
' in this table with the same unit number. If there are this statement will probably not
' get the correct new slot_id in the first record.
Set rsGroup = adoconnection.Execute("Select slot_id from tblSlot Where Unit = '" & rsSlot!unit & "'")

' Create the new record in tblGroup with the new slot_id and group from the rsSlot recordset
adoconnection.Execute "Insert into tblGroup ([slotid], [group]) Values ('" & _
rsGroup.Fields!slot_id & "', '" & rsSlot!group & "')"

' Close the rsGroup recordset
rsGroup.Close

' Move to the next record in the rsSlot recordset
rsSlot.MoveNext

' If we have reached the end of the recordset, stop looping
Loop until rsSlot.EOF

' Close the rsSlot recordset
rsSlot.Close

If you still can't make sense of this, you might want to find a
local tutor who can explain things at whatever level of detail you
need to understand.

Thanks. I see with my select statement that it could never work as theres nothing unique from the table I am selecting. Since I am going to destroy the table each time I insert information first time around. i am going to add in a auto key. So when I do the first select statement I can get that key and thats How I no which row to reference the row that I want to insert the value into.

I am whats the word, a non natural programmer. I can do something several times, using certain functions but yet forget the concept. I find it annoying but in someways except that fact. ADO is something I never come to grips with but have to do at present!

Thanks for the help : )

Are you saying that tblSlot is just temporary? That you clear it
each time?

I have just reworked the concept based on what you explained.

And this is my new solution

This will get me the values i need to populate the other tables

Table Import
set rsSlot = adoconnection.Execute("select day,time, unit,group from tblImport")

Table Slot - this will insert day,time and unit into this table

adoconnection.execute("insert day,time, group")

This will get me the id that was just created by that insert into TblSlot

set rsSlotid = adoconnection.execute("select max(slotid) from tblSlot")

This will take the id and the group variable from rsSlot recordset and let me execute it in the tblGroup table

*syntax here could be slightly wrong but mostly right :)
adoconnection.execute("insert slot_id, group values rsSlotid!slotid, rsSlot!group into tblGroup")

:) Bit of paper and it begins to make sense!

That seems simpler. Glad you are writing your plans down before
you start trying to code. If start by putting each step you want
to do in comments before you code, then you can add the code
to do each step below the comment and you will be able to tell
how close you are coming to doing your plan.

Is this a standalone app? I mean... no one else (no other applications or other instances of your app) will be inserting into this database? Because if other people are inserting,

"select max(slotid) from tblSlot"

won't necessarily give you the id of the record you just inserted.

If SQL Server, you can do Select @@identity to get the id of the last record you inserted. If Access, you pretty much have to gen your own id.

If your app is standalone, and always will be, forget I said anything.

Its a standalone app. A user takes in a file and each time they reload this file all records are deleted from the database.

So while that code is going round the Do While loop only that user will be accessing that set of data...