Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30

    Unanswered: Help me get straight?

    Hi, will anyone of you DB geniuses’ be willing to help me put some key statements in my code to help me clean it up? The code is working ...fairly but have some house cleaning to do.

    problem:

    When I add records to the form, previous values are shown. Then when I run the "Me.cboMaterial_ID.Requery" statement I get the run time error 2118......must save field...whats new?

    ignorantly, I thought the execute statements also saves the field...guess not

    How do I save fields and clean up the combo box for the next entry?

    Code:
    Private Sub cboMaterial_ID_BeforeUpdate(Cancel As Integer)
    Dim QTY_Requested As Integer, QTY_Remaining As Integer, QTY_Available As Integer, QTY_Issue As Integer
    Dim secured As String, strSQL As String, serialnum As String
    Dim MsgResponse
    Dim AnswerResponse
    cancelresponse = "Data entry Cancel"
    Const setzero = 0
    Const setone = 1
    ' This Sub routine checks the quantity value of a material to be sure materials are avaialbe to
    ' assign to employees or equipment trucks.
    ' If material are available,  the user is prompted to eneter the desired quantity to assign and
    ' the quantity is updated
    'Find out if the item is secured  and get serial number if it does not exist
    
    'Cancel = True
    'Me.SomeControl.SetFocus
    
    On Error GoTo Handler:
    If Me.cboMaterial_ID.Column(4, cboMaterial_ID.ItemsSelected) Then ' Check if the item is secured
       If Len(Me.cboMaterial_ID.Column(8, cboMaterial_ID.ItemsSelected) & vbNullString) = 0 Then ' Check if a serial number exists
            serialnum = InputBox(" What is the units serial number")
            strSQL = "UPDATE Materials SET Serial_Number = '" & serialnum & "' WHERE Material_ID = " & Me.cboMaterial_ID
            CurrentDb.Execute strSQL, dbFailOnError
            strSQL = "UPDATE Materials SET Issued_Quantity = " & setone & " WHERE Material_ID = " & Me.cboMaterial_ID ' Update Issued quantity
            CurrentDb.Execute strSQL, dbFailOnError
            strSQL = "UPDATE Materials SET Remaining_Quantity = " & setzero & " WHERE Material_ID = " & Me.cboMaterial_ID ' Update Remaining quantity
            CurrentDb.Execute strSQL, dbFailOnError
            Debug.Print strSQL
            Me.cboMaterial_ID.Requery ' Update the Combo
        End If
    Else
    'QTY_Available = CLng(Me.[cboMaterial_ID].Column(3)) - CLng(Me.[cboMaterial_ID].Column(4))
    'QTY_Available = Nz(Me![cboMaterial_ID].Column(3), 0) - Nz(Me![cboMaterial_ID].Column(4), 0)
    QTY_Available = Me.cboMaterial_ID.Column(3) - [cboMaterial_ID].Column(7) ' Get the quantity available by subtracting Stock_Quantity column(3) from Issued_Quantity column(7).
    Line1:
    QTY_Requested = InputBox("Enter the Quantity, " & QTY_Available & " Units are available?")  'Ask user how much of this material is needed. Handle the error on cancel
        If QTY_Requested > QTY_Available Then
            MsgResponse = MsgBox("Only have  " & QTY_Available & " Available, do you want continue?", vbRetryCancel)
                If MsgResponse = vbRetry Then
                    GoTo Line1
                End If
                Exit Sub
         End If
         
    QTY_Issue = Me.[cboMaterial_ID].Column(7) + QTY_Requested ' Increase Quantity issued (column 7) by Quantity Requested
    QTY_Remaining = Me.[cboMaterial_ID].Column(5) - QTY_Requested 'Quantity remaining = Current quantity - quantity issed
    Me.Quantity_issued = QTY_Requested
    strSQL = "UPDATE Materials SET Issued_Quantity = " & QTY_Issue & " WHERE Material_ID = " & Me.cboMaterial_ID ' Update Issued quantity
    CurrentDb.Execute strSQL, dbFailOnError
    strSQL = "UPDATE Materials SET Remaining_Quantity = " & QTY_Remaining & " WHERE Material_ID = " & Me.cboMaterial_ID ' Update Remaining quantity
    CurrentDb.Execute strSQL, dbFailOnError
    Debug.Print strSQL
    Me.cboMaterial_ID.Requery
    Line2:
    End If
    Exit Sub
    Handler:
    
    MsgBox cancelresponse
    
    End Sub

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Can't really follow what you're doing, here, but the problem is that you're trying to Requery the cboMaterial_ID Control in its own BeforeUpdate() event, and you cannot do that! The BeforeUpdate event fires immediately prior to the entered data being committed. This has to occur before you Requery the Control.

    The Requery will work from the AfterUpdate event of the Combobox; move it there and see if it works as you need it to.

    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

  3. #3
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    Thank you, makes sense. I moved the sub to BeforeUpdate so I could adequately handle a cancel. So, I must insert a AfterUpdate event and have one line to Requery the Combo Box.

    My programming is sad that you can't follow what I am doing. I haven't programmed in quite a while so it is a work in progress (collage of idea) that now works. It will get better a I learn more short cuts.

    Any other suggestions you can offer is greatly appreciated...ie, should I be executing a save command at the beginning or end of the this sub each time it runs?


    Update:
    Tried moving the (Me.cboMaterial_ID.Requery) to AfterUpdate but still have old data in the form. Any ideas why?


    Thanks again
    Last edited by back2basics; 03-12-13 at 17:15.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by back2basics View Post

    ...Tried moving the (Me.cboMaterial_ID.Requery) to AfterUpdate but still have old data in the form...
    To have new data reflected in the Form it (the Form) must be Requeried.

    Suggesting Requerying a Form usually brings cries about Access returning to the first Record in the RecordSet. To Requery a Record Source and then return to the Current Record, where [UniqueID] is a Field unique to only one Record:

    Where [UniqueID] is Text
    Code:
     Private Sub SaveRequeryReturn_Click()
    
    Dim UF_Rec As String
       
       UF_Rec = Me!UniqueID
       Me.Requery
       Me.Recordset.FindFirst "[UniqueID] = '" & UF_Rec & "'"
    
    End Sub

    Where [UniqueID]is Numeric

    Code:
     Private Sub SaveRequeryReturn_Click()
    
    Dim UF_Rec As Integer
       
       UF_Rec = Me!UniqueID
       Me.Requery
       Me.Recordset.FindFirst "[UniqueID] = " & UF_Rec
    
    End Sub


    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

  5. #5
    Join Date
    Jan 2013
    Location
    Chesapeake, VA
    Posts
    30
    Quote Originally Posted by Missinglinq View Post
    To have new data reflected in the Form it (the Form) must be Requeried.

    Thanks Missinglinq, I appreciate your code, but I think we are misunderstanding each other. This form is opened in the "Add records" mode using the Switch Board. The first field of the form calls the VB Code (above) via BeforeUpdate to perform some checks on the record before it is added. When the form is open, before the code is called, I need the form and the Combo Box to be clear of all fields and ready to go. Also, same scenario after each record is appended. Therefore; I do not want any data in the fields and simply want all previous entered data to be saved properly and the fields cleared. Requerying, while in the form, as you already know, is clearing the fields prematurely.

    So, I am stuck on how to handle this?


    Suggesting Requerying a Form usually brings cries about Access returning to the first Record in the RecordSet. To Requery a Record Source and then return to the Current Record, where [UniqueID] is a Field unique to only one Record:

    Where [UniqueID] is Text
    Code:
     Private Sub SaveRequeryReturn_Click()
    
    Dim UF_Rec As String
       
       UF_Rec = Me!UniqueID
       Me.Requery
       Me.Recordset.FindFirst "[UniqueID] = '" & UF_Rec & "'"
    
    End Sub

    Where [UniqueID]is Numeric

    Code:
     Private Sub SaveRequeryReturn_Click()
    
    Dim UF_Rec As Integer
       
       UF_Rec = Me!UniqueID
       Me.Requery
       Me.Recordset.FindFirst "[UniqueID] = " & UF_Rec
    
    End Sub


    Linq ;0)>
    _________________________________________________
    Last edited by back2basics; 03-14-13 at 12:11.

Posting Permissions

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