Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Will you Please Update (A97)

    I'm desperately trying to get this to work.....

    My application lets the user set up a work order and everything's working fine except for an intermittent problem that happens when they are requesting that a new machine or tool is added to a drop-down list of machines/tools. What's supposed to happen is, after the machine/tool is chosen, the work order number is assigned by taking the year, then tacking on the autonumber of the work order entry. The machine/tool is added to the appropriate table, then...

    Private Sub Tools_AfterUpdate()
    Dim myresults As String, myresults1 As String, msg As String, msg1 As String, msg2 As String
    .........here's where it's adding the new tool.......now it's trying to do the rest
    Select Case msg2
    Case vbYes
    DoCmd.SetWarnings False
    'this updates the tool list
    DoCmd.OpenQuery "q_newWorkTool"
    Tools.Requery
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.RunCommand acCmdSaveRecord
    .....here's where it's supposed to calculate the work order id, then display it on the screen. (workID)
    DoCmd.OpenQuery "Q_UpdWorkOrderID"
    Me.WorkID.Visible = False
    Me.WorkID.Requery
    Me.WorkID.Visible = True
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.SetWarnings True

    Case vbNo
    msg1 = "Please try again"
    msg2 = msgbox(msg1, vbDefaultButton1, "Status")
    Let Me.Machine = " "
    End Select

    ....if an existing machine/tool.......
    Else
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "Q_UpdWorkOrderID"
    Me.WorkID.Visible = False
    Me.WorkID.Requery
    Me.WorkID.Visible = True
    DoCmd.SetWarnings True
    End If

    End Sub

    What happens, sometimes (and there is no reason why it should), is that the work order number doesn't get updatedon a new machine/tool, and stays as 0. To try to counter this problem (we can't have 15 work orders with 0 on them), when the user exits the screen, the code checks to make sure that the work order number isn't 0:

    If Me.WorkID = 0 Then
    DoCmd.OpenQuery "Q_UpdWorkOrderIDtest"
    Me.WorkID.Requery
    Else
    End If

    Now, you'd think everything would work - well, it doesn't. Any suggestions? <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>
    Carpy Diem, it&#39;s .

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Will you Please Update (A97)

    It's difficult to see what happens, because there are parts you haven't posted.

    I assume that Tools is a combo box that has the LimitToList property set to No.

    What I usually do is set LimitToList to Yes and handle adding an item to the list in the NotInList event. That way, you don't have to worry about adding items in the AfterUpdate event. If you do a search in this Forum on NotInList, you'll find examples of how to handle it.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Will you Please Update (A97)

    Sorry about truncating the code - I was trying to save space. You were right - I had this tied to a combo box and the LimitToList property was no. I searched for NotInList and copied some code in. It appears to be working faster and better in my test database, so I will <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16> and hope it works for the end users.

    Thanks, Hans! <img src=/S/thankyou.gif border=0 alt=thankyou width=36 height=24>
    Carpy Diem, it&#39;s .

  4. #4
    3 Star Lounger
    Join Date
    Jul 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Will you Please Update (A97)

    Oops! <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    Also thanks to djoly whose code I copied <img src=/S/thankyou.gif border=0 alt=thankyou width=36 height=24>
    Carpy Diem, it&#39;s .

Posting Permissions

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