Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Copy database structure (A2k)

    Well, it's that time of year again and I'm back! Our annual conference is coming up in November and I need to copy my database structure without the data, in order to make the necessary changes to reflect this year's particulars. I would like to somehow be able to do a look up on the participants from last year from the new database as many people are return attendees. How might I accomplish this?

    Many thanks,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy database structure (A2k)

    You could make a copy of the entire database. The copy becomes your working database for 2004. Within it, make a copy of the participants table, to use as a lookup table (for example as row source for a combo box.) Then, delete all records in the data tables, but not in the various lookup tables.

  3. #3
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy database structure (A2k)

    I'll do that. Thanks Hans, I promise it won't be as bad as last year!

  4. #4
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy database structure (A2k)

    Do I need to remove the relationships before I start deleting records? The first recordset deleted fine, but the second set I tried to delete gave me an error message stating that the records could not be deleted due to another table including related records. I'm a little afraid to delete the relationships because there are so many.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy database structure (A2k)

    No, don't delete the relationships. Start by deleting records from the "detail" tables, do the "main" tables at the end. For example, you probably can't delete a person as long as there are invoices or courses for that person, so you must delete the invoices and courses before deleting the persons. This may go several levels deep.

  6. #6
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy database structure (A2k)

    Ah, that makes sense. I'll do that. Thanks

  7. #7
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy database structure (A2k)

    Ok, that part is done and worked just great. I've been looking at the help topics about using my participants list from 03 as a row source for my new participants list but can't find the answer I'm looking for. What I'd like to have happen is to look up by last name on a new record, choose a name from the list if it's the same participant as last year, and then have the entire form auto populate based on last year's information. I could then type over any information that has changed (phone number, address, etc.) and update the source table. Can that be done?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy database structure (A2k)

    I cannot provide the exact details, but you can do something like this:

    1. Create a query based on the participants list for 2003. The first field will be the unique ID (probably the AutoNumber field) of the table, the second the last name field, or preferably a calculated field that provides some more information, for example

    FullName: [LastName] & ", " & [FirstName] & " " & [MiddleInitial]

    (with the correct field names from your table substituted). Order the query by the name field.

    2. On your participants form, put a combo box cboSelectParticipant, with the following properties:

    (In the Format tab)
    Column Count: 2
    Column Widths: 0"; 1"
    (In the Data tab)
    Control Source: (leave blank, this will be an unbound combo box)
    Row Source Type: Table/Query
    Row Source: the name of the query you created
    Bound Column: 1
    Limit to List: Yes

    3. Next to the combo box, put a command button cmdSelect with caption "Select Participant". Create an On Click event procedure for this command button. You must substitute the correct table names, field names etc.

    Private Sub cmdSelect_Click()
    ' Need some variables
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    ' Set error handling
    On Error GoTo ErrHandler

    ' Get out if no participant selected
    If Me.cboSelectParticipant.ListIndex = -1 Then Exit Sub

    ' Give user a chance to cop out
    If MsgBox("Do you want to copy the information for this participant from last year?", _
    vbQuestion + vbYesNo) = vbNo Then Exit Sub

    Set dbs = CurrentDb
    ' Use your table name etc. here
    Set rst = dbs.OpenRecordset("SELECT * FROM [tblParticipants2003] WHERE [ParticipantID] = " & _
    Me.cboSelectParticipant, dbOpenSnapshot)

    ' Copy fields - use your field names here
    Me.LastName = rst!LastName
    Me.FirstName = rst!FirstName
    ' Etc. - add fields as needed

    ExitHandler:
    ' Clean up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    ' Inform user, then clean up
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    Note: this code uses DAO. Make sure there is a reference to the Microsoft DAO 3.6 Object Library in Tools | References... in the Visual Basic Editor.

  9. #9
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy database structure (A2k)

    Hans, you're awesome. Thanks for spending so much time working this out. Tomorrow I will try this and let you know of any trouble.

  10. #10
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy database structure (A2k)

    Hi,

    I've followed your instructions step by step and have had success with the query, and the combo box. I have repeated the code you gave me for the cmdbutton, all looks good until I try to execute it-I get a compile error that says "Label not defined", and the next to the last line of code is highlighted, which says, "Resume ExitHandler". I went back through the code line by line to double check and it's all as is written, just with my specific field and table names substituted. I have a feeling that somehthing is not referenced correctly. Any Help?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy database structure (A2k)

    The end of the code I posted looks like this:<pre>ExitHandler:
    ' Clean up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    ErrHandler:
    ' Inform user, then clean up
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub</pre>

    The next to last line refers to the label <code>ExitHandler:</code> at the start of the fragment I copied here. Apparently, this label is missing. Note: the colon after ExitHandler (in the first line of this fragment) is required, without it it is not recognized as a label.

  12. #12
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy database structure (A2k)

    Ah, I get it. I was misinterpreting the : as your comments.

    Thanks Hans!

  13. #13
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy database structure (A2k)

    Hi,

    This is populating just like I wanted it to. Now I need the data to populate a new Participants04 table. I added one record and then tried to add another, and it just overwrote the first one. Not sure how to point it in the right direction. Any help would be great, thanks!

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Copy database structure (A2k)

    You will have to move to a new record, then select a name and click the button.

  15. #15
    3 Star Lounger
    Join Date
    May 2003
    Location
    Sacramento, California, USA
    Posts
    310
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Copy database structure (A2k)

    Well, that was simple. Thanks Hans!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •