Error message box appearing when no error occurs

[VWSpeedyB]

Hello all! I'm hoping someone can find the flaw in my code. I have a form that shows 'products to be set-up in our ERP'. I've got a search button for the reference number, called BoMNo, so I can jump to the open record. The search requires input of the last four digits of the record number (prefix is alpha-numeric with leading zeros, so is the same for all records). If the digits match an existing record, that record populates the form. If it does not match, the error box is supposed to appear asking if the user wants to retry or cancel. On error, both the cancel and retry options work as scripted.

The issue is that the error box shows when the digits entered match an existing record. What needs to be changed in my code to eliminate this issue?

Code:

Private Sub GoToBoM_btn_Click()



Dim S As String, BoMNo As String


Userinput:
S = InputBox("Please enter BoM Number", "BoM Number", "BoMNo")
If S = "" Then Exit Sub
'user hit cancel

Me.Filter = "BoMNo Like ""*" & S & "*"""
Me.FilterOn = True
'user inputs last four digits of number - if record exists, that record is populated on the form


'if the record is not found, the below is supposed to occur
'the Else statement works as expected. However, the initial if function is appearing even when the digits entered
'match an existing record. I've tried multiple configurations, and cannot figure out how to prevent this occurence.
'Can anyone advise?


If BoMNo Like "" Then

If MsgBox("BoM Number not found. Please check the BoM Number and try again.", vbRetryCancel, "No Data") = vbRetry Then
GoTo Userinput
'reshow the input box

Else

DoCmd.Close acForm, "NPSU_Updt_frm", acSaveNo
'close the form
End If


End If


End Sub



Thanks in advance!!

Bryon
(VERY Green programmer)

[Gicu]

Please see if this works:

Private Sub GoToBoM_btn_Click()


Dim S As String, BoMNo As String




Userinput:
S = InputBox("Please enter BoM Number", "BoM Number", "BoMNo")
If S = "" Then Exit Sub
'user hit cancel


Me.Filter = "BoMNo Like ""*" & S & "*"""
Me.FilterOn = True
'user inputs last four digits of number - if record exists, that record is populated on the form




'if the record is not found, the below is supposed to occur
'the Else statement works as expected. However, the initial if function is appearing even when the digits entered
'match an existing record. I've tried multiple configurations, and cannot figure out how to prevent this occurence.
'Can anyone advise?




'If BoMNo Like "" Then
If Me.Recordset.RecordsetCount =0 'you applied a filter, if no record found the form will be empty
    If MsgBox("BoM Number not found. Please check the BoM Number and try again.", vbRetryCancel, "No Data") = vbRetry Then
        GoTo Userinput 'reshow the input box
    Else
        DoCmd.Close acForm, "NPSU_Updt_frm", acSaveNo 'close the form
    End If
End If




End Sub

Cheers,

[Micron]

Please use code tags (# button on posting toolbar) to maintain indentation and code readability. Hard to decipher that, especially with all the notes in it.
If ... Then ... End If isn't a function - it's referred to as an If block. Without knowing what BoMno is it's hard to say but your description of the behaviour suggests that it contains an empty string even if the record is found.
I think a better approach might be to use the NoMatch function on a recordset.
EDIT- forgot to say that stepping through your code is troubleshooting 101. Since the IF depends on the value of a variable here, you would step through and validate the values of your variables.
https://learn.microsoft.com/en-us/of...h-property-dao

[VWSpeedyB]

Sorry for the difficulties, Micron. When I copied and pasted my code, it was indented as it shows on my screen. I'll try to double check if I post in the future. Thanks for the tip about the recordset and noMatch.

[Gicu]

Have you tried my updated code in post #2?

[VWSpeedyB]

Hi Vlad -

I've been in and out of meetings.

I actually have a question - do I remove my " If BoM Like "" Then " and replace with the Recordset line, or use them in conjunction with each other?

[Gicu]

I commented out the first line as it is not the proper syntax, the field will never be an empty string if the filter returns no records.
Cheers,

[VWSpeedyB]

Ok, I'll give it a shot and let you know how it goes. Thanks again!

[VWSpeedyB]

Hi Vlad - Unfortunately, I still have not had any success with this task. I truly appreciate your time and effort.

[Gicu]

Can you please show what you've tried? Do you get run time errors or still get the message even if the filter is successful? Can you upload a small db sample with just the required object to illustrate the issue?

Cheers,