Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Posts
    3

    Unanswered: Creating multiple new records automatically.

    I am trying to create a small database to print and record inventory tags. Each tag needs to have an Id number. When I print the tags, I want to add the ID number to a table so I can keep track of printed tags. What I would like to do is prompt the user for how many tags they want to print, then automatically add that many records to a table and print the new tags. The only data to print is the tag-id, so that can just be an auto-increment field in a table, but I don't know how to automatically add a variable number of new records.


    Any help would be appreciated!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here is a possiblity, among many others. Table1 is the name of the table into which you want to add records and SysCounter is the name of the AutoNumber column (in my case, it is also the primary key):
    Code:
    Function AddRows(RowCount As Integer)
    
        Dim strSQL As String
        Dim i As Integer
        
        strSQL = "INSERT INTO Table1 (SysCounter) SELECT MAX (SysCounter) + 1 FROM Table1"
        For i = 1 To RowCount
            CurrentDb.Execute strSQL
        Next i
        
    End Function
    Have a nice day!

  3. #3
    Join Date
    Jun 2006
    Posts
    3

    Run-time error 3162

    Thanks for the quick response. I put the code in place, but when it executes the line "CurrentDb.Execute strSQL" I get Run-time error 3162: "You tried to assign the Null value to a variable that is not a variant data type."

    If it helps, I am running Access 2007. I am calling the code from the "on-click" event of a form. I am using a text box on the form to get the number of records to add, then passing that value to the function argument RowCount. That value is coming through correctly.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is T-SQL so the code is not much use to you but:
    Adam Machanic : You REQUIRE a Numbers table!

    There is code on the net to populate a numbers table. I loves them.
    Something like:
    Code:
    INSERT INTO myTagTable (tagBatchID) 
    SELECT someUniqueValue AS tagBatchID
    FROM numbers
    WHERE numbers.number BETWEEN 1 AND Forms!myForm!textboxWithTheNumberOfTagsIn
    myTagTable will have an autonumber column you can query to get the tag numbers for your batch.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - someUniqueValue would be something you generate so you can get back identify the tag batch. As such, it would be a parameter and not a column in the numbers table (in case that was not obvious). In fact, you might have a tagBatch table to record information about the batch, for example the creation date.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For this to work there must be at least one row in the table (otherwise SELECT MAX (SysCounter) returns Null)
    If you have to use it often on empty tables you could try:
    Code:
    Function AddRows(RowCount As Integer)
    
        Dim strSQL As String
        Dim i As Integer
        
        strSQL = "INSERT INTO Table1 (SysCounter) SELECT IIf(IsNull(Max([SysCounter])),1,Max([syscounter])+1) FROM Table1"
        For i = 1 To RowCount
            CurrentDb.Execute strSQL
        Next i
        
    End Function
    Have a nice day!

  7. #7
    Join Date
    Jun 2006
    Posts
    3

    Found the problem

    Sinndho hit the nail on the head. I was running the program on an empty table. Once I made the changes, it worked!


    Thanks for all the help!

Posting Permissions

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