How to create Msgbox when no data is found 1

[Dophia]

Hi Everyone:

I have an unbound form where the user enters a number "cboPS" which corresponds to a record. Another form opens with that number's data. But if there are no numbers that match, how can I get a message box to appear and tell the user. Then have the form stay open for another number?

Private Sub OpenDetails_Click()
On Error GoTo Err_OpenDetails_Click

Me.Visible = False
Dim strWhere As String

strWhere = "1=1 "


If Not IsNull(Me.cboPS) Then
strWhere = strWhere & " And [PS_No]='" & _
Me.cboPS & "' "

End If


DoCmd.OpenForm "FrmCat", acNormal, , strWhere
' DoCmd.OpenForm "FrmFindAnimalInfo", acNormal, , strWhere

Exit_OpenDetails_Click:
Exit Sub

Err_OpenDetails_Click:
Msgbox "PS does not exist, try another"
Resume Exit_OpenDetails_Click
End If
End Sub


Sophia

 

[PHV]

What about the DLookUp function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dophia]

Can you guide me a bit more. What should be done?

 

[PHV]

A starting point:

If IsNull(DLookUp("PS_No", "[i]yourTableName[/i]", "PS_No='" & Me!cboPS & "'")) Then
  MsgBox ...
Else
  DoCmd.OpenForm ...
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Dophia]

Thanks for your help. But it doesn't fully work. If the unbound field is blank, it gives me the MsgBox, but if it is a number that does not exist, it opens a blank form.

Here is what I have now. Any suggestions?


Private Sub OpenDetails_Click()
On Error GoTo Err_OpenDetails_Click

Me.Visible = False
Dim strWhere As String

strWhere = "1=1 "

If Not IsNull(Me.cboPS) Then
strWhere = strWhere & " And [PS_No]='" & _
Me.cboPS & "' "
Else


If IsNull(DLookup("Pound_Sheet_No", "tblAnimals", "Pound_Sheet_No='" & Me!cboPS & "'")) Then
Msgbox "PS does not exist, try another"
Else
DoCmd.OpenForm "FrmCat", acNormal, , strWhere
End If




Exit_OpenDetails_Click:
Exit Sub

Err_OpenDetails_Click:

Resume Exit_OpenDetails_Click
End If
End Sub

 

[Dophia]

Can anyone help?

 

[dhookom]

I think you final End If is out of place. I think you want

Private Sub OpenDetails_Click()
    On Error GoTo Err_OpenDetails_Click
    
    'Me.Visible = False
    Dim strWhere As String
    
    strWhere = "1=1 "
    
    If Not IsNull(Me.cboPS) Then
        strWhere = strWhere & " And [PS_No]='" & _
            Me.cboPS & "' "
        If DCount("Pound_Sheet_No", "tblAnimals", "Pound_Sheet_No='" & Me!cboPS & "'") = 0 Then
            MsgBox "PS does not exist, try another"
         Else
            DoCmd.OpenForm "FrmCat", acNormal, , strWhere
        End If
     Else
        MsgBox "Select a value in cboPS"
    End If
    
Exit_OpenDetails_Click:
    Exit Sub
    
Err_OpenDetails_Click:
    
    Resume Exit_OpenDetails_Click
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Thanks for your help again Duane. When I enter a non existent record, it still opens a blank form instead of saying that the record does not exist.

Any other suggestions?

Sophia

 

[dhookom]

I'm curious why you first compare cboPS to [PS_No] and then to Pound_Sheet_No. Can we assume the field is text?

What is your exact code?

What do you find out when you set a breakpoint and step through your code?


Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Thanks again Duane: This is the third time I am writing this, so if there are multiple posts, sorry.

You are right. PS_No should be Pound_Sheet_No. I tried to step through the code, but it seems to do nothing. I'm not really experienced enough for that.

What I want to do is for the user to enter a Pound_Sheet_No, which looks up the data in the tblAnimals. If it is found, the form is opened. The problem is that I am trying to have a message that says, the Pound_Sheet_No does not exist if there isn't one.

Am I on the right track?

Sophia

 

[dhookom]

Again, "What is your exact code?"

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

I thought I responded to that question, but it must be in my missing posts. That is my exact code.

 

[dhookom]

I thought you had the field name wrong earlier based on your comments.
Try:

Private Sub OpenDetails_Click()
    On Error GoTo Err_OpenDetails_Click
    
    'Me.Visible = False
    Dim strWhere As String
    
    strWhere = "1=1 "
    
    If Not IsNull(Me.cboPS) Then
        strWhere = strWhere & " And [PS_No]='" & _
            Me.cboPS & "' "
        If DCount("Pound_Sheet_No", "tblAnimals", "Pound_Sheet_No='" & Me!cboPS & "'") > 0 Then
            DoCmd.OpenForm "FrmCat", acNormal, , strWhere
         Else
            MsgBox "PS does not exist, try another"
        End If
     Else
        MsgBox "Select a value in cboPS"
    End If
    
Exit_OpenDetails_Click:
    Exit Sub
    
Err_OpenDetails_Click:
    
    Resume Exit_OpenDetails_Click
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Thanks again for your time Duane. It still works the same. When a wrong number is entered, it opens a blank form instead of giving the error message.


I think the following excerpt may be the problem. All Pound_Sheet_No are greater than zero, but not all numbers are found. For example, there can be 1000, 1001, 1005, 1010. So, if 1002 is entered, I want a message to pop up to say that it doesn't exist.

If DCount("Pound_Sheet_No", "tblAnimals", "Pound_Sheet_No='" & Me!cboPS & "'") > 0 Then

Can you suggest anything else?

Sophia

 

[dhookom]

You seem to be suggesting the Pound_Sheet_No field is numeric but your code is treating it as text. Please answer my previous question "Can we assume the field is text?"

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Sorry. Yes, it is text. I sometimes change things to make it easier to explain. They are actually in the form of a number and letter....eg. 1000-x,1001-x,etc.

Sophia

 

[dhookom]

Try troubleshoot by opening the debug window (press Ctrl+G) and entering:

?DCount("Pound_Sheet_No", "tblAnimals", "Pound_Sheet_No='" & Forms!YourFormName!cboPS & "'")

Do this when you have both a known Pound_Sheet_No and when you don't.

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Duane: Do I put the "?" in front of "dcount". Also, what does the first Pound_Sheet_No refer to? The current form or the field in tblAnimals? Finally, "Forms!YourFormName!cboPS". Is that the current form or the form I want to open?

Thanks for your help again,
Sophia

 

[dhookom]

The DCount() is taken from your code and Me. is replaced with the form name of the form running the code. Yes, place a ? first. The first Pound_Sheet_No is a field in tblAnimals.

Duane
Hook'D on Access
MS Access MVP

 

[Dophia]

Duane: It works now. I had a procedure which was on the cboPS after update. I think it was interferring with the code that was "On Click". thank you for your patience Duane, you're always such a great help!

Sophia.