Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Aug 2009
    Posts
    61

    Unanswered: Update Table based on Query

    Hi There,

    I have developed a database to control off-site warehouse storage. The database is made up of 3 main tables. The skid contents, the shipments to the warehouse, and the shipmnets returned from the warehouse. The database is used by several operators and has a form where they request the material back from the warehouse. After the material has been requested back (usually by somebody working in the inventory department), it is then processed by somebody in the material department. At the moment they are copying the items requested form into the create return form. It works okay, but unfortunately we run into problems like people entering records while the return is being created, which leads to materials being missed. Also, copying and pasting comes with many issues, so I would like to avoid it if possible.

    What I am trying to do is this. I have a query that looks into the BoxContents table and picks out all records that have been requested back, but have not been returned. The materials planner opens a form ran from that query in order to see what needs to be returned. I then have a form set up to write a return number to the PorterReturns table, with a subform to enter the materials that require returning into. The subform writes to a returns detail table. At the moment, the operator must look into the form to show what requires returning and then copy the data into the returns subform. What I would like to do is open the returns detail subform and it be populated with the material that requires returning and have the unique return ID number assigned to each line. This way the operator could just click a button to print the returns report that is sent to warehousing company.

    I am just trying to avoid the copy/paste because it can lead to some scary mistakes. I hope this makes sense...it is always hard to try and write these things out!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry but your explanation is a little bit obscure, at least to me, and some points need to be precisely explained in order to figure out what the problem is and how to solve it.

    1. You're talking about 3 main tables and enumerate them as being "the skid contents", "the shipments to the warehouse" and 'the shipments returned from the warehouse". Please be more precise and supply the name of each table as well as useful information about its structure and schema (name of the Identity column, names of other significant columns, etc.) and also explanations on how these tables are in relation with each others.

    2. At the moment you describe some processes and problems linked to them that I try to figure out whith not much success, e.g. I cannot figure out what you mean when you write:
    we run into problems like people entering records while the return is being created
    This could possibly change with the information requested in 1., though.

    3. In the second paragraph you wrote:
    I have a query that looks into the BoxContents table and picks out all records that have been requested back, but have not been returned.
    What is the BoxContents table? Is it one of the three tables you mentioned in the first paragraph or an additional one? In the same sentence, you describe a query in very general functional terms. From a database-oriented point of view, what does this query and how (SQL)?

    4. When you write:
    The subform writes to a returns detail table.
    Do you mean that the subform is bound to a table (yet another one?), or that some code (VBA, SQL or both) is used there?

    I'm sure there are many people here that are ready to help you (I'm among them) but cannot because it's hard to figure out how your system is organized and works from a database point of view. Your description is too general, not technical enough, and with the information you supplied there are tens if not hundreds of possible database implementations, hence there are a number of possible solutions in the same order of magnitude.

    You could have a look at: http://www.dbforums.com/microsoft-ac...ml#post4534485 for general guidelines.
    Have a nice day!

  3. #3
    Join Date
    Aug 2009
    Posts
    61
    Hi Sinndho,

    Sorry for the last confusing post...it had been quite a long day I guess!

    I will try and explain this a little more clearly.

    Table Structures:

    PorterBoxNumbers: (Used to assign unique numbers to skids of material being stored).

    SkidID (Auto Number)
    SkidNumber (Sequential Number assigned by operator).
    ReadytoShip
    Shipped

    BoxContents: (List of the individual pieces of material stored on each skid)

    SkidID
    BoxID (Auto Number)
    BoxItemNumber (Sequential Number assigned by operator).
    ProductID
    Batch
    Qty
    Notes
    RequiresReturn
    Returned
    Shipped
    ReturnAll
    User
    DateScanned

    ShipmentNumbers: (Used to create unique shipment ID number fo material being sent to the warehouse)

    ShipmentNumber (Auto Number)
    DateShipped
    PstgNo

    ShipmentDetails: (list of skids being sent on the shipment).

    SkidID
    BoxID
    ShipmentNumber
    DateShipped

    ReturnNumbers: (Used to create unique return ID number of material being returned from the warehouse)

    ReturnNumber
    DateReturned
    PostingNo

    PorterReturns: (list of material being returned. Can be idividual BoxID's or full skidID).

    SkidID
    BoxID
    ReturnNumber
    BoxItemNumber
    QtyReturned
    Notes
    Batch
    PostingNo

    PartNumbers: (General Table of Materials and Descriptions).

    ProductID
    TTCPN
    PartDescr

    The Process.

    Skid Creation
    1. Create Skid – Form used to create unique skid number in PorterBoxNumbers table.
    2. Enter Skid Contents – Form and subform combo used to enter information into BoxContents table.
    3. Marked Ready to Ship – after all material data has been entered into the BoxContents table, the skid is marked ready to ship.

    Shipments to Warehouse
    1. Create Shipment - Form used to create unique shipment number in ShipmentsNumbers table.
    2. Enter Shipment Contents – The SkidIDs being sent to the warehouse. The ones marked “ready to ship”. Form and subform combo used to enter information into ShipmentDetails table.
    3. SkidID marked as shipped in PorterBoxNumbers table.

    Returns from the Warehouse
    1. Operator on the floor marks the BoxID(s) that is required back.
    2. Materials Dept opens a form that shows all the BoxIDs that are required to be returned.
    3. Return Created - Form used to create unique return number in ReturnNumbers table
    4. Enter Return Details – All of the material that requires return is entered here. This a form with a data source of ReturnNumbers running a subform with the data source of PorterReturns table. Here the material is entered so that it populates the PorterReturns table with the unique return number assigned to each line. To populate this form, the operator basically copies and pastes the information from the form in step 3 above (Return Created).
    5. Marked as Returned – the final step is for the BoxID in the BoxContents table to be marked as returned. The essentially closes the BoxID and lets us know that the material is back.

    The Problem I’m facing

    I need to fix the Returns from the warehouse process, specifically section point 4 (Enter Return Details). Currently the operator is copying and pasting from another form, which ran from a query. The query is looking at Boxcontents table and filtering on the records that have the RequiresReturn selected, but do not have the Returned field selected.

    The process is run off two forms. The first, called RequiresReturn, is the form ran from the query that shows all of the lines that need to be returned. The second, called ProcessReturn, is a form with a data source of ReturnNumbers and a subform with a data source of PorterReturns. The operator copies the records from the RequiresReturn table into the Process return form. The operator then goes back to the first form and marks each line as returned. This is where the problem starts. This causes a problem because the people working in the inventory department are constantly requesting material back and quite often that is done while the return is being created. This causes problems because when the operator tabs between the two forms, access refreshes the data and adds any new BoxIDs that have been requested and often this material is marked as returned as well because it is hard to pull out the few records that may have been added in.

    Obviously having this material marked as returned without it actually being requested causes many problems, such as bad inventory numbers in the database and delays because it takes a day or two to realize this has happened.

    What I would like to do

    I would like the person creating the return to open a form that filters on the records that require return (this is the easy part as I already have that process working). I would then like to have a command button (or something similar) that the operator can click and then return be created. So basically this button would need to first create the individual record in the ReturnNumbers table, second create the records in the PorterReturns table, and third mark each line in the BoxContents table as returned. I just have no idea how I would get access to first create the new record in the ReturnNumbers table and then create all of the new records in the PorterReturns table with reference to the unique return number in the ReturnNumbers table and then mark each record from the BocContents table as returned.

    Having this process would fix the problem of new records being added to the requires return list before the ones being returned are marked as returned.

    I hope this is a better description of what I am looking for.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Thank you for your explanations, its clearer now.

    I still have several questions, though:

    1. From the structure of the tables, I infer that one row must be created into the [ReturnNumbers] table for each row into the [RequiredReturn] query and that later on [PorterReturns].[SkidID] = [RequiresReturn].[SkidID], [PorterReturns].[BoxID] = [RequiresReturn].[BoxID] and [PorterReturns].[ReturnNumber] = [ReturnNumbers].[ReturnNumber]. Is this correct?

    Note: To me the query RequiredReturn is:
    Code:
    SELECT BoxContents.*
    FROM BoxContents
    WHERE (BoxContents.RequiresReturn=True) AND (BoxContents.Returned=False);
    2. What data go into the columns [DateReturned] and [PostingNo] of the table [ReturnNumbers]. Are those columns filled during the creation process you describe? Manual entries, computed values or...?

    3. I also suppose that [PorterReturns].[QtyReturned] = [RequiresReturn].[Qty] and that [PorterReturns].[Batch] = [RequiresReturn].[Batch]. Is this also correct?
    Have a nice day!

  5. #5
    Join Date
    Aug 2009
    Posts
    61
    Quote Originally Posted by Sinndho View Post
    Thank you for your explanations, its clearer now.

    I still have several questions, though:

    1. From the structure of the tables, I infer that one row must be created into the [ReturnNumbers] table for each row into the [RequiredReturn] query and that later on [PorterReturns].[SkidID] = [RequiresReturn].[SkidID], [PorterReturns].[BoxID] = [RequiresReturn].[BoxID] and [PorterReturns].[ReturnNumber] = [ReturnNumbers].[ReturnNumber]. Is this correct?

    Note: To me the query RequiredReturn is:
    Code:
    SELECT BoxContents.*
    FROM BoxContents
    WHERE (BoxContents.RequiresReturn=True) AND (BoxContents.Returned=False);
    2. What data go into the columns [DateReturned] and [PostingNo] of the table [ReturnNumbers]. Are those columns filled during the creation process you describe? Manual entries, computed values or...?

    3. I also suppose that [PorterReturns].[QtyReturned] = [RequiresReturn].[Qty] and that [PorterReturns].[Batch] = [RequiresReturn].[Batch]. Is this also correct?
    Hi Sinndho,

    1. Um there is one row in [ReturnNumbers] table, but several rows in [PorterReturns]. The PorterReturns table is a detail table. The rest is correct.

    2. DateReturned is manually enetered and is the date the return was created. PostingNo is the SAP transaction code of the movement of material, also manually entered after the material movement is doen in SAP.

    3. Yes that is correct.

    Thanks for your help!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's one possible solution:
    Code:
    Function ProcessNewReturns()
    ' Function ProcessNewReturns(ByVal PostingNo As Variant, ByVal DateReturned As Variant)
    
        Dim lngNewReturnNumber As Long
        
        lngNewReturnNumber = NewReturnNumber    '(PostingNo,DateReturned)
        NewPorterReturns lngNewReturnNumber
        UpdateBoxContents
        
    End Function
    If [PostingNo] and [DateReturned] are known when the new row is inserted into the table [ReturnNumbers] you can adapt the function to supply them (commented parts).

    It uses the following functions:
    Code:
    Function NewPorterReturns(ByVal ReturnNumber As Long)
    
        Dim strSQL As String
        
        strSQL = "INSERT INTO PorterReturns ( BoxID, SkidID, BoxItemNumber, Batch, QtyReturned, ReturnNumber ) " & _
                 "SELECT BoxID, SkidID, BoxItemNumber, Batch, Qty " & ReturnNumber & _
                 "FROM FROM BoxContents " & _
                 "WHERE (BoxContents.RequiresReturn=True) AND (BoxContents.Returned=False);"
        CurrentDb.Execute strSQL, dbFailOnError
    
    End Function
    
    Function NewReturnNumber(Optional ByVal PostingNo As Variant = Null, Optional ByVal DateReturned As Variant = Null) As Long
    
        Const c_strSQL As String = "INSERT INTO ReturnNumbers ( DateReturned, PostingNo ) VALUES ( "
    
        Dim strDateReturned As String
        Dim strPostingNO As String
    
        If IsNull(DateReturned) Then strDateReturned = "Null" Else strDateReturned = Format(DateReturned, "#mm/dd/yyyy#")
        strPostingNO = Nz(PostingNo, "Null")
        CurrentDb.Execute c_strSQL & strDateReturned & ", " & strPostingNO & " );", dbFailOnError
        NewReturnNumber = DMax("Returnnumber", "ReturnNumbers")
        
    End Function
    
    Function UpdateBoxContents()
    
        Dim strSQL As String
        
        strSQL = "Update BoxContents " & _
                 "Set BoxContents.Returned = True " & _
                 "WHERE (BoxContents.RequiresReturn=True) AND (BoxContents.Returned=False);"
        CurrentDb.Execute strSQL, dbFailOnError
    
    End Function
    It's also possible to achieve the same result using Recordsets but it is far slower. As an example I tested both the NewReturnNumber() function here above and it's DAO equivalent:
    Code:
    Public Function DAO_NewReturnNumber(Optional ByVal PostingNo As Variant = Null, Optional ByVal DateReturned As Variant = Null) As Long
    
        Dim rst As DAO.Recordset
        Set rst = CurrentDb.OpenRecordset("ReturnNumbers", dbOpenDynaset)
        With rst
            .AddNew
            If Not IsNull(DateReturned) Then !DateReturned = Format(DateReturned, "#mm/dd/yyyy#")
            If Not IsNull(PostingNo) Then !PostingNo = PostingNo
            .Update
            .Bookmark = .LastModified
            DAO_NewReturnNumber = !ReturnNumber
            .Close
        End With
        Set rst = Nothing
        
    End Function
    on an average system (Intel Core 2 Duo E6850 3Ghz with 3GB RAM running Windows XP SP3) the mean time for executing the SQL version is 1.2 millisecond while the DAO version needs 58.3 ms. (computed from a sample of 1000 x 1000 calls).

    However the reliability of the SQL method cannot be 100% garanteed: there is a slight risk that 2 clients would perform concurrent accesses to the table [ReturnNumbers] in less than 1.2 ms. That would mean that the returned value of the function ProcessNewReturns() could be incorrect. However, if the value of [PostingNo] and [DateReturned] are known when the new row is inserted, it's possible to eliminate the problem by modifying the call to the DLookUp() function, like this:
    Code:
    NewReturnNumber = DMax("Returnnumber", "ReturnNumbers", "PostingNo=" & PostingNo & " AND DateReturned=" & DateReturned)
    Last edited by Sinndho; 05-04-11 at 04:23.
    Have a nice day!

  7. #7
    Join Date
    Aug 2009
    Posts
    61
    Hi Sinndho,

    Thanks for your continued help.

    I am quite novice at teh VBA at the moment and although I can follow what the code is doing, I do not knwo how I should get the code to work. I have a a couple of questions.

    1. Should I be saving each one of the functions separatly or as one large function?

    2. How would I get the functions to work? Should I create a command button on my form and paste the code into the on click property? Or should the button call the function(s)? How would I do that?

    Thank you again for your time!

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Create a new module.

    2. Copy the 4 functions (ProcessNewReturns, NewPorterReturns, NewReturnNumber, UpdateBoxContents) into it.

    3. Create a command button on the form (let's suppose its name is: Cmd_ProcessNewReturns).

    4. Select the newly created button, open the Property windox, select the Other tab and on the Name line type: Cmd_ProcessNewReturns.

    5. Now select the Event tab and create an event handler for the Click event.

    6. In the VBA windows, you now have:
    Code:
    Private Sub Cmd_ProcessNewReturns_Click()
    
    End Sub
    7. Add this into the Cmd_ProcessNewReturns_Click() procedure: ProcessNewReturns

    8. The VBA code should now look like this:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Sub Cmd_ProcessNewReturns_Click()
    
        ProcessNewReturns
        
    End Sub
    9. Compile and save. That's it.

    10. Do not forget that if the [PostingNo] and [DateReturned] values are known when you call the function (i.e. when you click on the command button) you should modify the ProcessNewReturns() function, like this:
    Code:
    Function ProcessNewReturns(ByVal PostingNo As Variant, ByVal DateReturned As Variant)
    
        Dim lngNewReturnNumber As Long
        
        lngNewReturnNumber = NewReturnNumber(PostingNo, DateReturned)
        NewPorterReturns lngNewReturnNumber
        UpdateBoxContents
        
    End Function
    11. In that case, let's suppose that there are two text boxes on the form: Txt_PostingNo and Txt_DateReturned that contain the related data. The Cmd_ProcessNewReturns_Click() procedure should become:
    Code:
    Private Sub Cmd_ProcessNewReturns_Click()
    
        ProcessNewReturns Me.Txt_PostingNo.Value, Me.Txt_DateReturned.Value
        
    End Sub
    Have a nice day!

  9. #9
    Join Date
    Aug 2009
    Posts
    61
    Hi Sinndho,

    I have followed your instructions, but the code is running into problems. The NewPorterReturns function is not working. When I click my button I get the following error:

    Run Time Error '3075'

    Syntax error (missing operator) in query expression 'Qty 189From'.

    When I click debug, it highlights the CurrentDb.Execute strSQL, dbFailOnError of the function.

    I have checked to make sure all of my field names are correct and correspond to the code.

    If understand this function correctly, it is taking the BoxID, SkidID, BoxItemNumber, Batch, Qty , and ReturnNumber from the [boxContents] table and inserting that into the Porter Returns table?

    If this is true, then that may be the problem because the ReturnNumber resides in the [ReturnNumbers] table.

    I have another question. How does this create a new ReturnNumber in the [ReturnNumbers] table?

    Presently I have a form ran from the [ReturnNumbers] table with a subform ran from the [PorterReturns] table. The form/subform are linked through ReturnNumber. Having it set up like this means that as I insert new records into the subform, it populates the [PorterReturns] table with the ReturnNumber. If I do not have the subform setup, how will that number get assigned to each record in the [PorterReturns] table?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sorry there are some typos in the line creating the strSQL string. Here's a corrected version:
    Code:
    Function NewPorterReturns(ByVal ReturnNumber As Long)
    
        Dim strSQL As String
        
        strSQL = "INSERT INTO PorterReturns ( BoxID, SkidID, BoxItemNumber, Batch, QtyReturned, ReturnNumber ) " & _
                 "SELECT BoxID, SkidID, BoxItemNumber, Batch, Qty, " & ReturnNumber & _
                 " FROM BoxContents " & _
                 "WHERE (BoxContents.RequiresReturn=True) AND (BoxContents.Returned=False);"
        CurrentDb.Execute strSQL, dbFailOnError
    
    End Function
    If it still does not work, could you please run the code, when the error occurs select Debug and with the VBA Editor open with the faulty line highlighted in yellow, open the Immediate window (Ctrl+G), type:
    Code:
    ? strSQL
    + Enter, then copy the line that is displayed and post it?
    Have a nice day!

  11. #11
    Join Date
    Aug 2009
    Posts
    61
    Sinndho this works like a charm. Thank you so much for you help!!!!

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

  13. #13
    Join Date
    Aug 2009
    Posts
    61
    Hi Sinndho,

    Sorry to bug you again!

    For some reason my button has stopped working. When I click it I get the following message:

    The expression On Click you entered as the event property setting produced the following error: Object or class does not support the set of events.

    I have not changed the code or the form. I have been adjusting my query in order to get it all working properly. My query is workign well now, but the button has decided to stop working.

  14. #14
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Open the VBA Editor and try recompiling the application. If it does not work, try recreating the event handler from scratch (steps 3 to 9 in a former post).
    Have a nice day!

  15. #15
    Join Date
    Aug 2009
    Posts
    61
    Hi Sinndho,

    i tried both of those and neither worked. What could be causing this?

Posting Permissions

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