Results 1 to 6 of 6
  1. #1
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110

    Adding User Defined Number of Records

    Hello.

    Im putting together a database for which I need you help and advice.

    I receive a box of Bolts, each has a plastic jacket and each jacket has a sequential number from x value to y value. We receive them in batches batches of between 500 and 1000 at a time.



    I need to record each bolt number received and add that to a table, but dont want to add each bolt type, date received, supplier, manufacturer etc 500 to 1000 times, that will take forever. I have a form, I want to enter the generic data, but add a start range and add an end range, and then something to then add the number of records I need. so: Bolt Start Range, Bolt End Range, enter the generic data, click a button on my form and 500 sequential records are added to the table.

    This is so my staff can then log out individually numbered bolts and add them to the indivdual suppliers they send the individual bolts to.

    I can also stock check the remaining quantiy.

    I just need a way to do the aboe.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    VBA code could be something like:
    Code:
    Dim x As Integer, db As Database
    Set db = CurrentDb
    For x = Me.tbxStart To Me.tbxEnd
        db.Execute "INSERT INTO table(BoltNum, DateRcd, BoltType) VALUES(" & x & ",#" & Me.tbxDate & "#,'" & Me.tbxType & "')"
    Next
    Or
    Code:
    Dim rs As Recordset, x As Integer
    Set rs = CurrentDb.OpenRecordset ("SELECT * FROM table WHERE 1=0")
    For x = Me.tbxStart To Me.tbxEnd
        rs.AddNew
        rs!BoldNum = x
        rs!DateRcd = Me.tbxDate
        rs!BoltType = Me.tbxType
        rs.Update
    Next

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110
    Thank you for this information. It somewhat gives me ideas.

  4. #4
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110
    Hi June7

    I do undrstand some of what you have done, but am struggling to get it to work for me. I may need a guide for dummies to get it working for me. If I give you a few field examples, ie below, ,could you help me and I can expand on it to do the rest of the work for me?

    My Table is called TblSEALS and has these fields
    BoxID = Number
    BoltNumber = Number
    Supplier = Number (relationship to Lookup a company name)

    My presentl unbound form with add button is called FrmSEAL
    BoxID (ser defined)
    BoltStartRange (user defined)
    BoltEndRange (User Defind)
    Supplier (ComboBox)

    Your help is very much appreciated.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Substitute my examples with actual object names from your db.

    Should provide attempted code. What issue have you encountered - error message, wrong result, nothing happens?

    Where do you want date received, bolt type, manufacturer?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Steven19 is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jun 2020
    Location
    London
    Posts
    110

    Solved

    Thank you.

    I forgot to add the underscore for my table names. The code I used was the second option you provided.

    Many regards

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

Similar Threads

  1. Replies: 9
    Last Post: 09-29-2019, 01:11 PM
  2. Replies: 4
    Last Post: 10-16-2017, 09:09 AM
  3. Replies: 12
    Last Post: 12-09-2015, 03:42 PM
  4. mydb - User-defined type not defined
    By adams77 in forum Forms
    Replies: 4
    Last Post: 07-22-2015, 08:43 AM
  5. Replies: 3
    Last Post: 11-12-2013, 04:13 PM

Tags for this Thread

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