Will you Please Update (A97)

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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 and hope it works for the end users.

Thanks, Hans!

Reply | Quote

Oops!

Also thanks to djoly whose code I copied

Reply | Quote