need to copy and add new records in table

chacquard · April 25th, 2005, 01:14 PM

I have 2 tables with a one-to-many relation. I need to be able to select 1 record in the "one" table (and the according records in the "many" table), change the value of some of the fields (save value for all records selected) and add these new records in both tables... More specifically:

table 1: Event fields are id, name, date, time, etc..
table 2: Seats fields are id, idEvent, seat no, location, status (available or sold) etc..

So, I need to select and copy an event and all of its seats, change the date and time of the event, change the status to active for the seats to finally create a new event with this new information.

What would be the safest and easiest way to do this ? Would it be with queries and recordsets ? If so, i'm not too sure how to do it.

Thanks

BrianWren · April 25th, 2005, 05:03 PM

This must be done within VBA. It cannot be done with just a query.

Open a recordset of the one record on the one-side of the relationship.
Using that recordâs ID, open a recordset of all of the records on the many side.

Use DoCmd.RunSQL to create a new record in the table on the one side, with the new values.

Change the record in the recordset on the one-side to hold the new record instead of the original record, so that you can get that new recordâs ID.

I presume the new records on the many side are all going to be not-yet-sold.
Loop through the recordset on the many side (Do Until rs.EOF), using DoCmd.RunSQL to INSERT INTO the table on the many side a new record for each of the records in the recordset, setting the foreign key ID to the value you obtained for the record that was added to the one-side.

Or perhaps, with the new ID you could create a string (strSQL here)

Code:

   strSQL = "INSERT INTO tbl2 (idEvent, seat no, location, status) " & _
            "SELECT " & NewID & ", tbl2.[seat no], location, 'Not Sold' AS x " & _
            "FROM tbl2 " & _
            "WHERE idevent = " & OldID

If you ran that SQL, it would get each old record (along with some constant data), and make a new record for each old record.

rjweers · April 30th, 2005, 09:49 AM

Since you essentially want to "duplicate" the data for just one Event, you could do this with two queries. But you will have do two queries in order. I think that's why Brian recommended VBA.

For "table 1" (assuming that's the actual name of the table):

NOTE: I hope you haven't called the date and time fields in [table 1] "date" and "time". If so, please find different names. I'm going to call them EventDate and EventTime

INSERT INTO [table 1] (EventDate, EventTime, list the rest of the fields)
SELECT #[Enter New Event Date]#, #[Enter New Event Time]#, list the rest of the fields
FROM [table 1]
WHERE id = [Please enter the original event ID]

Note: the [Enter Event Date], [Enter Event Time] and [Please enter the original event ID] will provide a prompt when you run the query.

"table 2" is essentially the same, but you have to set the idEvent based on the one that was just inserted in "table 1":

INSERT INTO [table 2] (idEvent, status, list all the other fields)
SELECT [Please enter the Event ID that was created by the Insert to Table 1], "Available", list all the other fields
FROM [table 2]
WHERE idEvent = [Please enter the original event ID]

Like the "table 1" insert, the bracketed text ([]) will prompt for input.

Since you're looking for "safest and easiest", (which may also by why Brian suggested VBA) it would be a lot easier on the user if there was a form that allowed the user to select the event to copy and enter the new date and time. Those prompts from the queries cannot be edited. Type a bad value there and it will go into the database.

If you get round to using the form idea, the main thing you'll need know is the new event id of the event inserted in "table 1". The procedure to execute the copy of the event should be something like...

Code:

Dim db as Database
Dim rs as RecordSet
Dim NewID as Long
Set db = CurrentDB()
Set rs = db.OpenRecordSet("[table 1]",,dbAppendOnly)
With RS
    .AddNew
    NewID = !ID ' store the ID for use on "table 2"
    !Name = DLookup("Name","[Table 1]","id = " & me.cboSourceEventID)
    !EventDate = Me.txtDate ' user entered field
    !Time = Me.txtTime ' user entered field
    .... rest of fields ...
    .Update
    .Close
End With
Set rs = Nothing
Set db = Nothing

dim strSql as string
StrSql = "INSERT INTO [table 2] " _
       & "(idEvent, status, list_all_the_other_fields) " _
       & "SELECT " & NewID & ", ""Available"", list_all_the_other_fields " _
       & "FROM [table 2] " _
       & "WHERE idEvent = " & Me.cboSourceEventID
DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True

A couple of things to note:
+ The "DLookup" is merely one way to get the value of each field.
+ Me.cboSourceEventID is a combo box on the form that can select the prior event.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

chacquard · April 30th, 2005, 10:42 AM

I'm sorry, I'm kind of new at this but...

I've been running the insert into sql as you indicated, but when I check in the Event table, no record has been added and I get no error message.

I forgot to mention earlier that the ID is a autonumber field... Can it be a problem with sql statments ? Here is the sql statement I did:

strSQL = "INSERT INTO EVENTtbl ( CodeEvent, NameEvent, DateEvent ) " & _
"SELECT " & """ & stType & Intnum & """ & " AS CodeEvent, NameEvenement, [Event's new date] as DateEvent " & _
"FROM EVENTtbl " & _
"WHERE CodeEvent = " & """ & intCodeEvent & """ & ";"

The autonumber field is not part of this SQL statement (IDEvent). Even if I add it, the problem is the same: no record added and no errors. Am I forgetting to do something else ? Do I have to do the recordset before the runSQL command?

Thank you for helping me out :)

Chantal

chacquard · April 30th, 2005, 11:15 AM

I took out the extra quotation marks I had (I found with debug.print and ran in a query).

Now I do get a message that states I cannot add the record because of a key violation... so I guess it is really because of my autonumber ID (that is the primary key of the table). How can I work with that ?

Help....

chacquard · April 30th, 2005, 01:10 PM

It's me again... been working hard :D I fixed my violatio problem. I am now able to add new records. Now a new problem came along with this:

Like I said before, the ID is an autonumber. Now here is an example of what is happening:

EventID Name
5 xxxxx (record I copy to add in the same table)
6 yyyyy
8 zzzzz
9 aaaaa (last record of the table)

When I add the new record with INSERT INTO, instead of assigning 10 to the EventID, it will assign 7. How can I make it assign 10 ?

Thanks again

rjweers · April 30th, 2005, 02:07 PM

First of all, I doubt that it will assign ID 7. If you have Autonumber set to increment in the table definition, it should assign the next higher unused number. BTW, that number is going to be dependent on whether you've compacted and repaired the database.

Secondly, what difference does it make which EventID is assigned? If you want 7, then why did you assign autonumber to begin with?

Lastly, if you want to assign the EventID 7, you need to remove the auto number. Do be careful, if you remove the auto number and add a record, it's difficult to turn auto number back on.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

chacquard · April 30th, 2005, 02:41 PM

the Event ID is autonumber set to increment and I did not repair or compact the database. The "empty" Event ID are used but transfered in another database when then are "finished". so I do not want to reuse the same Event ID number. That is why, when I "insert into" the Event table, I want the Event ID to be the next higher unused number, not the next unused number.

I did repair and compact the database, but it still assigns the next unused number instead of the next higher unused number... I really need help here...

rjweers · April 30th, 2005, 03:39 PM

I would recommend you utilize something other the Event ID field to identify the correct event. The Event ID field should be used only as the foreign key to other tables. Otherwise, that field shouldn't mean anything.

Other things you can use to identify the event, "Name" & "EventDate" & "EventTime".

But if you insist, remove the auto number and assign your own ID using something like...

NewID = DMax("EventID","[Table 1]") + 1

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org

rjweers · May 1st, 2005, 09:56 AM

I was using the wrong number in my post... When I started talking about the ID you want, I should have been talking about 10 instead of 7. Sorry.

In my experience, Access assigns the next higher unused autonumber. If you do this before a compact and repair, it will assign the next number higher than the highest that was ever used regardless of whether that number is still in the table. If you delete the highest number ever used, then do a compact and repair, it will assign the next higher of whatever is the highest in the table. So as long as you don't move the highest off to the other database and then compact and repair, you should be fine. I believe this is the rule developed by the Access folks. Though we have had some discussion at PNWADG around problems people have encountered.

Sorry I didn't take the time to discuss this yesterday ... busy with other stuff. I'm curious about why you transfer data to another database anyway... Are you expecting the database to get larger than Access's 2 GB limit? Is there some legal requirement that you have to meet in regard to "finished" events?

The trouble with archiving, which is essentially what you're doing, is that you then have to deal with joining the data if anyone wants historical reports. And of course you really need to control the primary / foreign keys so you can archive without collisions. BTW, the DMAX will not give you the correct result if you move the highest number. To keep full control, you will have to store the last used number independent of your event table.

It appears you'll have some way to indicate that an event is "finished". If you simply add a check to all queries that are displaying event information, you can prevent others from seeing the "finished" events. Sure if someone opens a table directly they'll see the "finished" events. But if you provide tools so they don't have to open the tables to see what they want to see, why would they open the tables?

Provided you develop good code and good queries, I doubt you will see any performance problems leaving all of the data in one database.

My recommendation would be to stay away from this archiving thing unless there is a legal requirement. If your database is going to be bigger than 2GB without archiving, you should probaly start thinking about SQL Server right now. (Or as BrianWren would suggest, Oracle.)