Msg box for record not found

[KhanEl]

Hello All,

I am relatively new to Access and definitely new to vb, and I think I've bitten off more than I can chew.

I have created a pop-up search form with the search criteria being one unbound field. When I populate the field and click search I want it to search the records and if there is a matching record, I want it to open the data entry form with the complete record showing and available to view/edit/delete. If there is no matching record, I want a message box to appear stating that a record was not found and when you click 'ok' it opens a blank data entry form to complete a new record. I also want it to automatically close the search pop-up form.

The following works great... except that the MsgBox pops up even if the record was found. Any help would be very much appreciated. Thank you!

Private Sub Search_Click()
On Error GoTo Err_Search_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MgrData"

'get current MgrID
tmp = Me.MgrID

Me.MgrID.Enabled = False

'check if the MgrID was found
If (tmp = Me.MgrID) And (Me.MgrID <> stDocName) Then
MsgBox "A record does not currently exist for this Manager ID. You may enter a new record, or click CANCEL to return to the main menu"
stLinkCriteria = "[MgrID]=" & "'" & Me![MgrID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
stLinkCriteria = "[MgrID]=" & "'" & Me![MgrID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

DoCmd.Close acForm, Me.Name

Exit_Search2_Click:
Exit Sub

Err_Search2_Click:
MsgBox Err.Description
Resume Exit_Search2_Click

End Sub

 

[pr2-eugin]

Hello KhanEl, Welcome to AWF.

Your code is a bit off. Your first part of If checks if tmp equals Me.MgrID, which is what you assign two lines before checking the condition. Your second part of the If conditions checks if the ID is not equal to a String MgrData. That will never be Equal, thus the Condition is executed, and the message box appears, and then opens the Form with the Record.

What exactly you wish to check for using the If Statement?

 

[KhanEl]

Thank you for the quick reply!

Basically I want to search my records to see if a record already exists in my master table based on the MgrID (unique identifier).

If the record already exists, open form 'MgrData' and display the record.

If the record does not already exist, open a msgbox stating no record exists, and also open the blank form so a new record can input.

I cobbled together the code via self research, so 'a bit off' is not surprising, and probably putting it kindly

Thank you,
EK

 

[pr2-eugin]

I am not sure what your field/table names are.. but the following code should give you a start..

Private Sub Search_Click()
On Error GoTo Err_Search_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "MgrData"
    
    Me.MgrID.Enabled = False

    [COLOR=Green]'check if the MgrID was found[/COLOR]
    If IsNull(DLookUp("[COLOR=Red][B]IDfieldName[/B][/COLOR]", "[COLOR=Red][B]tableName[/B][/COLOR]", "[COLOR=Red][B]IDfieldName =[/B][/COLOR] '" & Me.MgrID & "'")) Then
        MsgBox "A record does not currently exist for this Manager ID. You may enter a new record, or click CANCEL to return to the main menu"
        DoCmd.OpenForm stDocName, [COLOR=Blue][B]DatMode:=acFormAdd[/B][/COLOR]
    Else
        stLinkCriteria = "[MgrID]= '" & Me![MgrID] & "'"
        DoCmd.OpenForm stDocName, WhereCondition:=stLinkCriteria
    End If

    DoCmd.Close acForm, Me.Name
    
Exit_Search2_Click:
    Exit Sub

Err_Search2_Click:
    MsgBox Err.Description
    Resume Exit_Search2_Click
End Sub

 

[KhanEl]

Hi Paul,

The field/table names are 'MgrID'/'Managers'. When I plugged in the names and tested it, I receive the following error:

Syntax error (missing operator) in query expression '*MgrID*'

EK

 

[pr2-eugin]

The field name MgrID suggests that it is a Number type, is that correct? Show the code you have right now.

 

[KhanEl]

The MgrID is an alpha-numeric code. Here's what I have...

Private Sub Search_Click()
On Error GoTo Err_Search_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = " MgrData"

Me.MgrID.Enabled = False
'check if the MgrID was found
If IsNull(DLookup("*MgrID*", "*Managers*", "*MgrID =* '" & Me.MgrID & "'")) Then
MsgBox "A record does not currently exist for this Manager ID. You may enter a new record, or click CANCEL to return to the main menu"
stLinkCriteria = "[MgrID]=" & "'" & Me![MgrID] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Else
stLinkCriteria = "[MgrID]= '" & Me![MgrID] & "'"
DoCmd.OpenForm stDocName, WhereCondition:=stLinkCriteria
End If
DoCmd.Close acForm, Me.Name

Exit_Search_Click:
Exit Sub
Err_Search_Click:
MsgBox Err.Description
Resume Exit_Search_Click
End Sub

 

[pr2-eugin]

Why do you have asterisks in the field and table names?

Also, is there a reason why you have not used DataMode as acFormAdd as shown in Post #4, but used strLinkCriteria?

If there is No record that exists for the Manager then you cannot go to that record on the Form. That's the reason I added the code to Add a New record using the Form...

Private Sub Search_Click()
On Error GoTo Err_Search_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "MgrData"

    Me.MgrID.Enabled = False
   [COLOR=Green] 'check if the MgrID was found[/COLOR]
    If IsNull(DLookup("MgrID", "Managers", "MgrID = '" & Me.MgrID & "'")) Then
        MsgBox "A record does not currently exist for this Manager ID. You may enter a new record, or click CANCEL to return to the main menu"
        DoCmd.OpenForm stDocName[COLOR=Red][B], DatMode:=acFormAdd[/B][/COLOR]
    Else
        stLinkCriteria = "[MgrID]= '" & Me![MgrID] & "'"
        DoCmd.OpenForm stDocName, WhereCondition:=stLinkCriteria
    End If
    
    DoCmd.Close acForm, Me.Name

Exit_Search_Click:
    Exit Sub
Err_Search_Click:
    MsgBox Err.Description
    Resume Exit_Search_Click
End Sub

PS: Please use Code Tags when posting VBA Code

 

[KhanEl]

Okay... a little embarrassed. Didn't even see the asterisks and no clue how they got to be there. Keep the coffee coming.

Made the corrections and it works perfectly! Thank you so much for your help.

 

[pr2-eugin]

Glad to help. Good Luck