Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2014
    Location
    United Kingdom
    Posts
    6

    Unanswered: Help! Generating multiple records

    Hi Guys!!

    I am new to MS Access and looking to some help with the above topic.

    I have a table that I can enter data into via a form that I have created, however, I need to enter multiple records at once...

    The fields in the table that I need to populate are:

    SerialNumber (needs to be different with every record)
    Order Number (e.g. 3375)
    Order Date (e.g. 12/06/2014)
    Customer (e.g. Microsoft)
    Part Number (e.g. L1965A8206)
    Hose Kit (e.g. Yes)
    Comments (e.g. Customer requests special connector)

    The above (apart from serial number) contain the same data with every record entered between the stated range (inclusive of the serial numbers entered).

    I.E. in one text box (TextFrom)I would put a serial number of AD-ORACLE-2230, and in the other text box (TextTo) I would put another serial number AD-ORACLE-2240 (if the customer bought 10 machines), I would like to then press a button (GenerateRecords) to generate the records 2230 to 2240 and enter the data from the headings(text boxes) above to the relevant fields in the table.

    I have some VBA already taken from another thread on the forum and is as follows:

    Private Sub GenerateRecords_Click()
    Dim mn As Long
    Dim Rec As Long

    Rec = (Me.TextTo - TextFrom) + 1

    For mn = 1 To CInt(Rec)
    DoCmd.GoToRecord , , acNewRec
    Me.SerialNumber = mn + (TextFrom - 1)
    Next mn
    End Sub

    I have tried this to generate new records/serial numbers but comes back with a compile error "Method or Data member not found" with (TextFrom - 1) highlighted.

    Please Help!! like I said , I'm very new to MS Access and learning via Google and Youtube, but I can't seem to find any answers for this issue. I have tried to be as clear as possible, I hope this makes sense to someone...

    Any help is greatly appreciated!!

    Thanks

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    Instead of doing this via record addition.
    Make the loop run a query X times.
    Then do the loop but execute a query to handle the records

    Code:
    docmd.setwarnings off
    For mn = 1 To CInt(Rec)
          Me.SerialNumber = Me.SerialNumber +1
          docmd.openquery "qaAddNextRec"    'query posts using the new serial#
    Next 
    End Sub

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Actually,

    DoCmd.SetWarnings

    requires an argument of True or False, so to turn warnings off would be

    DoCmd.SetWarnings False

    and immediately after doing what needs to be done, you must always, always, always turn them back on!

    DoCmd.SetWarnings True

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    With an unbound form, you could use:
    Code:
    Private Sub Command_AddOrders_Click()
    
        Const c_SQL As String = "INSERT INTO Tbl_Orders (SerialNumber, [Order Number], [Order Date], Customer, [Part Number], [Hose Kit], Comments) " & _
                                               "VALUES ( '@S', @O, #@D#, '@C', '@P', @H, '@M');"
    
        Dim strSQL As String
        Dim strBaseSerNum As String
        Dim strSerialNumber As String
        Dim lngFirst As Long
        Dim lngLast As Long
        Dim i As Long
        
        strBaseSerNum = Left(Me.Text_From.Value, InStrRev(Me.Text_From.Value, "-"))
        lngFirst = Val(Mid(Me.Text_From.Value, InStrRev(Me.Text_From.Value, "-") + 1))
        lngLast = Val(Mid(Me.Text_To.Value, InStrRev(Me.Text_To.Value, "-") + 1))
        strSQL = Replace(c_SQL, "@O", Me.Text_OrderNumber.Value)
        strSQL = Replace(strSQL, "@D", Format(Me.Text_OrderDate.Value, "yyyy-mm-dd"))
        strSQL = Replace(strSQL, "@C", Me.Text_Customer.Value)
        strSQL = Replace(strSQL, "@P", Me.Text_PartNumber.Value)
        strSQL = Replace(strSQL, "@H", Me.Text_HoseKit.Value)
        strSQL = Replace(strSQL, "@M", Me.Text_Comments.Value)
        For i = lngFirst To lngLast
            strSerialNumber = strBaseSerNum & i
            CurrentDb.Execute Replace(strSQL, "@S", strSerialNumber), dbFailOnError
        Next i
        
    End Sub
    With a bound form, you could use:
    Code:
    Private Sub Command_AddOrders_Click()
    
        Const c_SQL As String = "INSERT INTO Tbl_Orders (SerialNumber, [Order Number], [Order Date], Customer, [Part Number], [Hose Kit], Comments) " & _
                                                "VALUES ( '@S', @O, #@D#, '@C', '@P', @H, '@M');"
    
        Dim strSQL As String
        Dim strBaseSerNum As String
        Dim strSerialNumber As String
        Dim lngFirst As Long
        Dim lngLast As Long
        Dim i As Long
        
        Me.Dirty = False
        strBaseSerNum = Left(Me.Text_SerialNumber.Value, InStrRev(Me.Text_SerialNumber.Value, "-"))
        lngFirst = Val(Mid(Me.Text_SerialNumber.Value, InStrRev(Me.Text_SerialNumber.Value, "-") + 1)) + 1
        lngLast = lngFirst + Me.Text_Quantity.Value - 1
        strSQL = Replace(c_SQL, "@O", Me.Text_OrderNumber.Value)
        strSQL = Replace(strSQL, "@D", Format(Me.Text_OrderDate.Value, "yyyy-mm-dd"))
        strSQL = Replace(strSQL, "@C", Me.Text_Customer.Value)
        strSQL = Replace(strSQL, "@P", Me.Text_PartNumber.Value)
        strSQL = Replace(strSQL, "@H", Me.Text_HoseKit.Value)
        strSQL = Replace(strSQL, "@M", Me.Text_Comments.Value)
        For i = lngFirst To lngLast
            strSerialNumber = strBaseSerNum & i
            CurrentDb.Execute Replace(strSQL, "@S", strSerialNumber), dbFailOnError
        Next i
        Me.Requery
        
    End Sub
    Notes:

    1. Replace the name of the objects with those in use in your project.

    2. Using the whole serial number for both the first and the last in the serie is not a good idea because you force the user to type almost the same data twice (boredom, risk of mistyping, etc). In the bound version I used a textbox control named Quantity that specifies the number of orders to add (refer to the code to see how it is processed).

    3. You should refrain from using spaces in the names of the objects in an Access database. Sooner or later there will be troubles because of that. Use either an underscore to replace the space: Order_Number or use the so-called CamelCase convention: OrderNumber. On the same path also refrain from using non-alphanumeric characters (except the underline, see above) and reserved words (see: Access 2007 reserved words and symbols - Access).

    4. Be coherent when choosing the names. In your post, we have a column named SerialNumber while another column is named Order Number. You'll spend a lot of time trying to remember whether there is a space (or better an underline, see above) or not in the name of such or such object.
    Have a nice day!

  5. #5
    Join Date
    Aug 2014
    Location
    United Kingdom
    Posts
    6
    Thanks all for you're help with this
    Sinndho, thanks for the advise, I have taken this on board and with these things in mind I am going to look at reconstructing my db with coherent names.
    This will end up playing a big part for my company and wouldn't want any issues further down the line.
    I will implement these codes once I have done so and will come back should I have any issues.

    Thanks again to all

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Symon247 View Post
    I have implemented one of the codes you kindly wrote for me and I am getting a run time error of 3061 with a status of "Too few parameters. Expected 1.

    I have looked into this and found that it could be down to a field not being named correctly or something simple like that...
    But this leaves me at a dead end which is due to my limited knowledge of MS Access and not being able to see any mistakes...
    Its hard to provide a solution without seeing the code in use AND the table definition (name, name and data type of the columns). Please post both.
    Have a nice day!

  8. #8
    Join Date
    Aug 2014
    Location
    United Kingdom
    Posts
    6
    Here is my unfinished db, the form ADAccess (Which is the name of a product) will eventually be used as a template for other forms for other products, such as ADOracle and ADNano like you will see on the UnitNavigation form. The form FrmLogin will eventually be used to password protect the tab control.
    The form ReturnsRegister is a subform which will stay the same on every template.


    As you know, the problem I am having is generating multiple records from inputting one set of information fields.
    This is under the tab Order Processing, I have created the layout that I would like to see and hopefully, what I want to achieve is quite obvious.
    I'm pretty sure that the db I have created could have been done in a better way, but as far as I can see, it will work well enough for our current needs.
    Thank you for looking into this
    Attached Files Attached Files

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by Symon247 View Post
    Here is my unfinished db
    Where is it? Didn't you forget to attach the db to your post?
    Have a nice day!

  10. #10
    Join Date
    Aug 2014
    Location
    United Kingdom
    Posts
    6
    Try again
    To add, I have not put a box for the serial number (Text_To), which I took out but just realised the user will need to see the start and end serial number. I.E. From AD-ACCESS-2231 To AD-ACCESS-2240 for 10 units.

    Thanks Again, I hope this makes sense
    Attached Files Attached Files
    Last edited by Symon247; 08-18-14 at 09:24.

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
  •