Wrong data type error message in form

[wizardchef]

I have a subform in an Access window where I enter a name into a form field (drop-down box) and that name gets matched to names drawn from the database table. If there is no match and I delete the incorrect name and then hit the Cancel button, which is a simple:

Private Sub Cancel_Click()
DoCmd.Close acForm, "Search for Client", acSaveYes
End Sub

I get a popup error message that tells me that there is an error with the entered data type, although all text has been deleted. What can I do to clear or override or correct this message?

 

[Remou]

Perhaps:

Private Sub Cancel_Click()
    Me.Undo
    DoCmd.Close acForm, "Search for Client"[s], acSaveYes[/s]
End Sub

You might like to consider DlookUp for the name and only open the form if the name is available.

http://lessthandot.com

 

[wizardchef]

The Me.Undo didn't eliminate the error when the text is deleted and the 'Cancel' button is selected.

Here is the current code for looking up the name:

Private Sub ByName_AfterUpdate()
On Error GoTo By_Search_Type_DblClick_Err
DoCmd.SelectObject acForm, "Orders by Customer"
DoCmd.GoToControl "CustomerID"
DoCmd.FindRecord Forms![Search for Client]![ByName], acEntire, False, acDown, False, acCurrent
DoCmd.Close acForm, "Search for Client"

By_Search_Type_DblClick_Exit:
Exit Sub

By_Search_Type_DblClick_Err:
MsgBox Err.Description
Resume By_Search_Type_DblClick_Exit

End Sub

Any suggestions here that might prevent this problem?

 

[Remou]

Can you explain what you want to achieve in text? It seems that you want to see if "Orders by Customer" has the Customer ID entered in "Search for Client" form in a control called "ByName". Is that correct?

http://lessthandot.com

 

[wizardchef]

I can select to search by customer number or by customer last name (although I always use last name) using a radio button and I want to find the customer ID in the database corresponding to that number/last name. I then return to the main form with that information in the form.

 

[Remou]

Here is an outline of an idea that I think is in line with what you want to do. I would generally prefer to use a listbox for finding people on a form, it gets round the problem of people with the same name because you can include several fields to narrow down the identification; you can use a bookmark with the listbox to get the record on the main form.

This sample code should eliminate the need for clearing anything on the form and for the cancel button.

Private Sub txtSearchBox_AfterUpdate()
'You may wish to consider using a search button, rather than
'the AfterUpdate event
Dim rs As DAO.Recordset
Dim blnFound As Boolean
Dim strMsg As String

'Check that the main form is open
If Not CurrentProject.AllForms("Orders By Customer").IsLoaded Then
    MsgBox "This form is a search form for Orders By Customer."
Else
    'This is the recordset of the form and can be searched
    Set rs = Forms![Orders By Customer].RecordsetClone
    
    'fraSearchOptions is an option group with a default 
    'value of 1=Last Name and a second option, 2=ID.
    'Note that you must always watch out for single quotes
    'and replace them with two single quotes in the search
    'string. This does not affect the data.
    If Me.fraSearchOptions = 1 Then
        'Note that this deals only with finding the first instance
        'of a last name and there may be more than one person
        'with the same last name.
        rs.FindFirst "Last='" & Replace(Me.txtSearchBox, "'", "''") & "'"
    Else
        rs.FindFirst "ID=" & Me.txtSearchBox
    End If
    
    If Not rs.NoMatch Then
        Forms![Orders By Customer].Bookmark = rs.Bookmark
        blnFound = True
    End If
End If

If blnFound Then
    strMsg = Me.txtSearchBox & " was found."
Else
    strMsg = Me.txtSearchBox & " was not found."
End If

strMsg = strMsg & vbCrLf & vbCrLf _
      & "If you wish to search again, click OK." & vbCrLf _
      & "Click Cancel to close this form."

If MsgBox(strMsg, vbOKCancel) = vbCancel Then
    DoCmd.Close acForm, Me.Name
End If
            
End Sub

http://lessthandot.com

 

[wizardchef]

Remou, thanks for the code. I wrote the code in my form several years ago and have been away from VBA for some time now. I'll have to spend a few days reacquainting myself with the code in my form and try and integrate your code to create a better solution. Thanks again.