Results 1 to 4 of 4
  1. #1
    Imtiaz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    2

    VBA Code Help Needed

    I have a table in ms accesss database with name "tbl_books". Every I time I need to add 1000 records. I want to add theses records on one click.


    I have form with textboxs(booknr,startingnr,endingnr and button). Can you please help me how to do it? Thanks in advance. startingnr and endingnr difference is always 50.

    Book_Id Book nr Starting nr Ending nr
    1001 2001 2050
    1002 2051 2100
    1003 2101 2150
    1004 2151 2200
    1005 2201 2250
    1006 2251 2300
    1007 2301 2350
    1008 2351 2400
    1009 2401 2450
    1010 2451 2500
    1011 2501 2550
    1012 2551 2600

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    1) What are the actual field names in the table
    2) If ending number is always starting number + 49 (you said diff of 50 but your table shows 49) then I don't see a need to store the ending number in the table.
    3) what do these fields actually represent in the real world?

    Here's preliminary code to get you started:
    Code:
    Public Sub AddBooks()
    On Error GoTo ErrHandler_AddBooks
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim i As Integer
        Dim qry As String
        Dim book_num As Long
        Dim starting_num As Long
        
        'STEP 1) find the last book number and starting number in the table
        '------------------------------------------------------------------
        Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT TOP 1 Booknr, Startingnr FROM tbl_books ORDER BY Booknr DESC;") 'find the record with the highest book nr and return it aswell as it's starting nr
        If Not (rs.BOF And rs.EOF) Then
            'Query found some records
            book_num = rs!Booknr
            starting_num = rs!Startingnr
        Else
            'Query didn't find anything
            book_num = 1000
            starting_num = 1951
        End If
        rs.Close
        
        'STEP 2) loop 1000x and insert new records
        '------------------------------------------------------------------
        For i = 1 To 1000
            book_num = book_num + 1
            starting_num = starting_num + 50
            
            qry = "INSERT INTO tbl_books (Booknr, Startingnr, Endingnr) VALUES (" & book_num & ", " & starting_num & ", " & starting_num + 49 & ");"
            db.Execute qry, dbFailOnError
        Next i
    
    ExitHandler_AddBooks:
        Set rs = Nothing
        Set db = Nothing
        Exit Sub
    
    ErrHandler_AddBooks:
        MsgBox Err.Description, vbInformation, "AddBooks: Error #" & Err.Number
        Resume ExitHandler_AddBooks
    End Sub

  3. #3
    Imtiaz is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    2
    Thank you very much sir. It works for me.

  4. #4
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by kd2017 View Post
    2) If ending number is always starting number + 49 (you said diff of 50 but your table shows 49) then I don't see a need to store the ending number in the table.
    Starting number may not need to be stored either...
    Given the data you've shown it can be calculated: [starting number] = 50*[book number] - 48049

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

Similar Threads

  1. Replies: 10
    Last Post: 09-09-2016, 03:24 PM
  2. Urgent help needed with WHERE Command in Code
    By Steve1977 in forum Access
    Replies: 5
    Last Post: 03-17-2014, 09:54 AM
  3. Help Needed Fixing the Code
    By aamer in forum Access
    Replies: 8
    Last Post: 03-01-2014, 04:55 PM
  4. Date code needed
    By spider in forum Programming
    Replies: 1
    Last Post: 06-19-2013, 06:38 AM
  5. Code needed when Database is Opened
    By Access_Headaches in forum Access
    Replies: 7
    Last Post: 08-13-2010, 01:03 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