Results 1 to 8 of 8
  1. #1
    AMJADJ is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jul 2018
    Posts
    34

    Create New Rows based on number entered

    Hi

    We have a database that tells our offsite warehouse what the contents are of each load. Among other info, is the order number and the number of pallets.
    When the load reaches the warehouse, they will then book the pallets in. Firstly confirming the full load has been received, then booking in individual pallets by entering the rack and location they have been moved to.



    What I need assistance of is to split out the number of pallets entered at loading bay so that the offsite warehouse can then book in individual pallets:

    So at loading bay they will for example say order number ABC 4 Pallets, Order Number DEF 2 Pallets and enter trailer number XYZ

    When the offsite warehouse receive this they will click on a button to receive this load (entering trailer number and date will bring up the full load list), which will then write to a table (Table_Example for now) and create 4 individual rows for order number ABC and 2 rows for order DEF.

    I am comfortable creating the buttons and updating the required info into Table_Example, but just need assistance on the code that looks at the total pallets for each order then splits this into individual rows.

    Thank you in advance for your time and effort.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    so what do you want?, like:
    1, ABC, xyz
    2, ABC, xyz
    3, ABC, xyz
    4,ABC, xyz
    1, DEF, xyz
    2, DEF, xyz

    Code:
    sub btnAdd_click()
    dim i as integer
    dim sSql as string
    
    for i = 1 to txtNumPalet
       sSql = "Insert into tExample ([itemNo], [order], [trailer]) values (" & i &  ",'" & txtOrderNo & "','" & txtTrailer & "')"
       docmd.runsSql sSql
    next i
    end sub
    text box for: Order, trailer, and # palletes

  3. #3
    AMJADJ is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jul 2018
    Posts
    34
    Hi "ranman"

    I have the code taken from above as

    Code:
    Private Sub Command272_Click()
    Dim i As Integer
    Dim sSql As String
    For i = 1 To Me.pallet
       sSql = "Insert into oswh_warehouse_storage ([itemNo], [order], [trailer]) values (" & i & ",'" & Me.order_no & "','" & Me.Text242 & "')"
       DoCmd.RunsSql sSql
    Next i
    End Sub
    but I am getting a compile error: Method or data member not found and it highlights .RunsSql

    Thank you for your time.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    DoCmd.RunsSql sSql
    It's .RunSql.
    Remember, Google is your friend. You could have found that in 2 minutes or less.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    AMJADJ is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Jul 2018
    Posts
    34
    Ha yes I guess so! but as the variable was sSql with the run sSql it made sense to a novice! and I had tweaked the query, was making sure it wasn't something I had done!
    The above code works, but on the form there are many order numbers, the code looks at the first order number and creates X number of rows depending on the pallets stated on the order.
    I would like the code to run through all the orders on the form and do the same i.e. look at number of pallets and create a new row.

    Thanking you for your time.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Is this a continuous form or datasheet form? Then I suppose you need to create a clone of the recordset and in an outer loop, do the provided code in an inner loop. The outer loop would be from 1 to the record count of the recordset clone and would advance by 1 when the inner loop completes. Maybe it would also need to make use of the order id/number, but not much about your situation to be able to say for sure.

  7. #7
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    Quote Originally Posted by Micron View Post
    DoCmd.RunsSql sSql
    It's .RunSql.
    Remember, Google is your friend. You could have found that in 2 minutes or less.
    runs SQL -- this procedure runs an SQL statement

    that is totally believeable

    If Google is so great at Access help, why are we here?

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If Google is so great at Access help, why are we here?
    To help those who don't or can't seem to help themselves, I guess. So much of what is asked here is straight forward stuff that we're only repeating. If the question is not about specific design, or broken code, or how to perform a complex task or calculation, etc., then we're probably just repeating what has been covered elsewhere. Not complaining, just saying waiting for hours for an answer to a simple thing doesn't make sense to me when it can be found in a million other places within 2 minutes. Even if I Googled the wrong phrase as in "ms access docmd.runssql", I still get results. The second one says

    expression. RunSQL( _SQLStatement_, _UseTransaction_ )

    expression A variable that represents a DoCmd object.

    Clearly then, it is RunSQL, not RunsSql.
    You don't think that's helpful? Maybe some are here to just spoon feed everyone on the simple stuff, but what's better, teaching others to help themselves or just solving everything for them? There is that old saying, Give a man a fish...

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

Similar Threads

  1. Replies: 1
    Last Post: 08-10-2016, 02:44 AM
  2. Replies: 7
    Last Post: 05-30-2016, 06:56 AM
  3. Replies: 2
    Last Post: 08-15-2012, 02:21 AM
  4. Replies: 4
    Last Post: 07-27-2011, 12:42 PM
  5. Insert x number of rows based on value
    By opopanax666 in forum Programming
    Replies: 4
    Last Post: 10-26-2010, 03:26 AM

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