MS Access VBA call sub works on the first pass but not on the second

Question

I have hit a wall and I am completely at a loss.

So I have a Form in MS Access. In it I have a single text box and a single button. When I type in a number in the text box and then click the button it opens a public sub which then runs a few queries, updates the database, displays a text box and then clears out the text box. This all works perfectly.

My issue is trying to do the exact same thing with hitting enter in the text box. The strangest thing is that the code works fine right after I open up the form, but all subsequent attempts give the following error until I close the form and reopen it:

Data type mismatch in criteria expression.

For the life of me I cannot figure out why it does what I want the first time, then falls apart on me.

Here is the complete VBA code for reference:

 Option Compare Database Public Sub Cut_Update() On Error GoTo Cut_Update_Err DoCmd.OpenQuery "UPDATE_WIP_Cut", acViewNormal, acEdit DoCmd.OpenQuery "UPDATE_LastRun", acViewNormal, acEdit MsgBox "Database Updated" [Forms]![Portal_02_Cut]![WO_Num].Value = "" Cut_Update_Exit: Exit Sub Cut_Update_Err: MsgBox Error$ Resume Cut_Update_Exit End Sub '------------------------------------------------------------ ' Return in Textbox ' '------------------------------------------------------------ Private Sub WO_Num_KeyUp(KeyCode As Integer, Shift As Integer) If KeyCode = 13 Then Call Cut_Update Me.Refresh End If End Sub '------------------------------------------------------------ ' Command2_Click ' '------------------------------------------------------------ Private Sub Command2_Click() Call Cut_Update End Sub

Did you debug with a break point and run each statement step by step? That will narrow down the issue to a particular line of code. — theSushil
– theSushil, Commented Jan 1, 2020 at 23:23
Does this answer your question? Press enter in textbox and execute button function in VBA — Rene
– Rene, Commented Jan 2, 2020 at 1:07
Rene, that was exactly what I had been looking for and I for the life of me could not figure that setting out. Thank you so much. — foxbomb
– foxbomb, Commented Jan 2, 2020 at 18:50

Gustav · Accepted Answer · 2020-01-02 07:36:09Z

The only thing you do in between the calls in the form is:

[Forms]![Portal_02_Cut]![WO_Num].Value = ""

So, try with either:

[Forms]![Portal_02_Cut]![WO_Num].Text = ""

or:

[Forms]![Portal_02_Cut]![WO_Num].Value = Null

zip · Accepted Answer · 2020-01-02 04:10:12Z

My only guess is that the update is trying to update with a non numeric value. Let me know

 Option Compare Database Public Sub Cut_Update() On Error GoTo Cut_Update_Err '' Make sure it is numeric If IsNumeric([WO_Num].Value) Then DoCmd.OpenQuery "UPDATE_WIP_Cut", acViewNormal, acEdit DoCmd.OpenQuery "UPDATE_LastRun", acViewNormal, acEdit MsgBox "Database Updated" [Forms]![Portal_02_Cut]![WO_Num].Value = "" End If Cut_Update_Exit: Exit Sub Cut_Update_Err: MsgBox Error$ Resume Cut_Update_Exit End Sub '------------------------------------------------------------ ' Return in Textbox ' '------------------------------------------------------------ Private Sub WO_Num_KeyUp(KeyCode As Integer, Shift As Integer) If KeyCode = 13 Then Call Cut_Update Me.Refresh End If End Sub '------------------------------------------------------------ ' Command2_Click ' '------------------------------------------------------------ Private Sub Command2_Click() Call Cut_Update End Sub

Ok well that got me closer. So when I added your little check I am able to get it to run the first time as before. After it clears out when I type in the number and then hit enter it just highlights the text. If I hit enter again it successfully runs the code. Not sure why it has to have me hit return twice.... It is late, but I might see if I can brute force through with a loop tomorrow.
Compare[Forms]![Portal_02_Cut]![WO_Num].Valuewith[Forms]![Portal_02_Cut]![WO_Num].Text. Value property may not be set in KeyUp-Event, but if you use the button the control loses focus annd then. Value is set to.Text.

Collectives™ on Stack Overflow

MS Access VBA call sub works on the first pass but not on the second

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related