Adding a clear Function to data entry box

jakmc25

New Member
Joined
Jul 5, 2018
Messages
5
Hello I'm new to VBA coding and have searched everywhere and can't seem to find the answer. Have some code that a former co worker wrote that I am trying to edit. Basically as you type in the data into the box (cboFindBuyer) and click enter it fills in the data on the form in the first box. I have added a clear button to make all the text boxes blank but I want to be able to just type 000 and it clears all the boxes. If anyone could please help it would be great.

VBA Code:
Private Sub cboFindBuyer_AfterUpdate()
    Dim str As String
    Dim strNew As String
    Dim rst As DAO.Recordset
    Dim intBuyerID As Variant
    On Error GoTo Trap
    
    intBuyerID = Me.cboFindBuyer
    'If IsNull(Me.txt2) Then
    '    str = ""
    'Else
    '    str = Me.txt1
    'End If
    
    
    Set rst = CurrentDb.OpenRecordset("Buyers", dbOpenDynaset)
    With rst
        .MoveFirst
        .FindFirst "[Buyer ID] = " & intBuyerID
        If Not rst.NoMatch Then
            strNew = ![Buyer ID] & " " & ![Buyer]
            Me.Text20 = Nz(Me.Text19, "")
            Me.Text19 = Nz(Me.Text18, "")
            Me.Text18 = Nz(Me.Text17, "")
            Me.Text17 = Nz(Me.txt5, "")
            Me.txt5 = Nz(Me.txt4, "")
            Me.txt4 = Nz(Me.txt3, "")
            Me.txt3 = Nz(Me.txt2, "")
            Me.txt2 = Nz(Me.Text32, "")
            Me.Text32 = Nz(Me.txt1, "")
            Me.txt1 = strNew
            
            'Me.txt1 = ![Buyer ID] & "   " & (Chr(13) & Chr(10)) & ![Buyer] & (Chr(13) & Chr(10)) & "   " & ![Buyer Contact] & (Chr(13) & Chr(10)) & "   " & ![Buyer City] & ", " & ![Buyer State]
            'Me.txt1 =
        End If
    End With
    Set rst = Nothing
    Exit Sub
Trap:
    Set rst = Nothing
    If Err.Number = 3024 Then
        MsgBox "You must run ShoWorks and open your data file before you can use this feature.", vbExclamation
        DoCmd.Quit
    Else
        MsgBox Err.Number & "  " & Err.Description
    End If
End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
can you post the code for your clear button pls
 
Upvote 0

Forum statistics

Threads
1,214,983
Messages
6,122,592
Members
449,089
Latest member
Motoracer88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top