Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 25th, 2005, 01:14 PM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default need to copy and add new records in table

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




  #2 (permalink)  
Old April 25th, 2005, 05:03 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Port Orchard, WA, USA.
Posts: 1,621
Thanks: 1
Thanked 3 Times in 3 Posts
Default

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.
  #3 (permalink)  
Old April 30th, 2005, 09:49 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #4 (permalink)  
Old April 30th, 2005, 10:42 AM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default

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

  #5 (permalink)  
Old April 30th, 2005, 11:15 AM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default

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....



  #6 (permalink)  
Old April 30th, 2005, 01:10 PM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default

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



  #7 (permalink)  
Old April 30th, 2005, 02:07 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #8 (permalink)  
Old April 30th, 2005, 02:41 PM
Authorized User
 
Join Date: Feb 2004
Location: , , Canada.
Posts: 55
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to chacquard
Default

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...

  #9 (permalink)  
Old April 30th, 2005, 03:39 PM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #10 (permalink)  
Old May 1st, 2005, 09:56 AM
Friend of Wrox
 
Join Date: Nov 2004
Location: Seattle, WA, .
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post
Default

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.)




Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy whole structure of table in #temp table maulik77 SQL Server 2000 2 December 21st, 2006 02:42 AM
ADO record copy and add to table Freddyfred Access 2 February 16th, 2005 10:36 PM
copy and append records from table-A to table B bhunter Access 6 March 9th, 2004 02:02 PM
Add Records to Table with 1 to Many Relationship twsinc Access VBA 8 October 5th, 2003 05:35 AM
Update or add multiple table records rosenzl VB.NET 3 June 12th, 2003 01:59 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.