Results 1 to 14 of 14
  1. #1
    MWilliams1188 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    8

    Multi-record thread

    This may be a simple (or even dumb) question:

    I have a multi-table database that tracks event tickets. When I place an order for say, 8 tickets. I use a forum to add the order and tickets to their respective tables.

    Within each order, the values for each ticket field are the same (section, row, price) EXCEPT for seat number. Rather than enter 8 records, I'd like to just be able to input the first and last seat number (1 and 8 for 1-8), and have Access create 8 records in the ticket table (seats 1, 2, 3, 4, etc.)

    What is the easiest way to do this? I don't mind programming some VB if I have to, but if there's an easier way, I'm open to that as well.



    Thanks,
    Mike

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd probably use a For/Next loop using the first and last values entered, and the AddNew method of a recordset within the loop to add a record to the table. The recordset would get the first 3 values from the form and the seat from the loop counter.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    MWilliams1188 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    8
    I figured I'd use a loop. I've done VB before but not within Access like this. I'm wondering how to even begin. Ideally, I'd like a datasheet-like form where I could paste values straight from excel, then have the VB logic take values from the fields and add them to different tables as needed (only after an ADD button is pressed, not real-time like Access defaults to).

    Could anyone just give a brief sample of what the VB code would look like?

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Well, here's the guts of a process that does it:

    Code:
    For lngPassNum = lngStartNum To lngEndNum
    
      strSQL = "INSERT INTO tblTrolleyDayPass (TicketNum,DateStock,Status) " & _
               "VALUES (" & lngPassNum & ", #" & Me.txtDate & "#, 'S')"
    
      CurrentDb.Execute strSQL
    Next lngPassNum
    Though these days I'd be more likely to use the AddNew method of a recordset to add records.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    MWilliams1188 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    8

    Screenshot

    I started writing some code just to start playing around with. I think I'm missing some really basic things. I've put code in there, but when I click the button it does nothing. I mean nothing. Like it's not even linked to the DB. On another form, I had a close form button that worked via macro, but when I converted it to VB, it doesn't work either.

    I've posted a screenshot of my code and my form layout. Could anyone tell me what I'm doing wrong. I know how to code but don't know how to link everything.

    Click image for larger version. 

Name:	TicketDatabase.jpg 
Views:	5 
Size:	105.7 KB 
ID:	9950

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    For starters, is the code properly associated with the button?

    http://www.baldyweb.com/FirstVBA.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    MWilliams1188 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    8
    Yes it is. The code was originally auto-created after I right-clicked the button and did 'Build Event'. My database connection code may be sketchy. I've only ever done raw SQL, but I'm trying to learn this Resultset way

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you post the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    MWilliams1188 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    8
    I wish....says its too big (2MB). Is there another way?

  10. #10
    MWilliams1188 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    8
    Nevermind, zipped it. Here it is.Tickets.zip

  11. #11
    MWilliams1188 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    8
    I want to take the New Order form and first create an Order, THEN take that order number and create all the ticket records.

  12. #12
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It worked for me. Is any code running? Starting in 2007, the database has to be in a trusted location, or you have to explicitly enable code (option right under the ribbon usually).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  13. #13
    MWilliams1188 is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Posts
    8
    There you go. threw it in a trusted location and now it's working.

    Going back to my original post... I'm going to add some code to put the tickets in there using a loop. One thing I need is the order number that I just created (it's an autonumber). How can I grab that so I can include it in the ticket entries?

    Thanks

  14. #14
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Is this cut from a production db what you're after?

    Code:
            With rsMaster
              .AddNew
              !FieldName = Value
              .Update
              'get autonumber of added record
              .Bookmark = .LastModified
              lngARKey = !KeyField
            End With
    
            'insert fare amount into detail
            With rsDetail
              .AddNew
              !ForeignKey = lngARKey
              !OtherFields = Values
              .Update
            End With
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Record Locking Multi User Environment
    By praetorianprefect in forum Database Design
    Replies: 5
    Last Post: 04-16-2012, 02:31 PM
  2. Multi-record form
    By dougie in forum Programming
    Replies: 2
    Last Post: 12-13-2011, 12:41 AM
  3. Totals in a multi-record sub form
    By bginhb in forum Forms
    Replies: 7
    Last Post: 09-03-2011, 07:33 PM
  4. Multi-record additions using a list box not working right
    By avarusbrightfyre in forum Programming
    Replies: 3
    Last Post: 10-27-2010, 01:50 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums