Error trapping search form

[Neekos]

i have the following code that performs a search:

Private Sub Command47_Click()
On Error GoTo Err_Command47_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frmMainGroup"
    If IsNull(Me![GroupNumber]) Then
        MsgBox "Please enter a Group Number.", vbExclamation
    Else
        stLinkCriteria = "[GroupNumber]=" & "'" & Me![GroupNumber] & "'"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
Exit_Command47_Click:
    Exit Sub
 
Err_Command47_Click:
    MsgBox Err.Description
    Resume Exit_Command47_Click
 
End Sub

I'm trying to figure out a way to error trap it so that if someone puts in a number that doesn't exist, they get a message telling them that it doesnt exist. Right now if they enter a wrong number, it just opens up a blank form.

[puppydogbuddy]

Use a DCount to your number table:

If DCount("[GroupNumber]", "YourTable", "[GroupNumber] = " & Me![GroupNumber]) = 0 Then 
 
MsgBox "Number does not exist"
 
End If

[Neekos]

Thanks! I see how that would work, but i get a data type mismatch error. Maybe i'm placing it in the wrong spot?

Here's my code:

Private Sub Command47_Click()
On Error GoTo Err_Command47_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frmMainGroup"
    If IsNull(Me![GroupNumber]) Then
        MsgBox "Please enter a Group Number.", vbExclamation
    Else
    If DCount("[GroupNumber]", "tblGroupTool", "[GroupNumber] = " & Me![GroupNumber]) = 0 Then
        MsgBox "Number does not exist"
    Else
        stLinkCriteria = "[GroupNumber]=" & "'" & Me![GroupNumber] & "'"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
    End If
 
Exit_Command47_Click:
    Exit Sub
 
Err_Command47_Click:
    MsgBox Err.Description
    Resume Exit_Command47_Click
 
End Sub

[puppydogbuddy]

Thanks! I see how that would work, but i get a data type mismatch error. Maybe i'm placing it in the wrong spot?

Here's my code:

Private Sub Command47_Click()
On Error GoTo Err_Command47_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frmMainGroup"
    If IsNull(Me![GroupNumber]) Then
        MsgBox "Please enter a Group Number.", vbExclamation
    Else
    If DCount("[GroupNumber]", "tblGroupTool", "[GroupNumber] = " & Me![GroupNumber]) = 0 Then
        MsgBox "Number does not exist"
    Else
        stLinkCriteria = "[GroupNumber]=" & "'" & Me![GroupNumber] & "'"
        DoCmd.OpenForm stDocName, , , stLinkCriteria
    End If
    End If
 
Exit_Command47_Click:
    Exit Sub
 
Err_Command47_Click:
    MsgBox Err.Description
    Resume Exit_Command47_Click
 
End Sub

Hi Neekos,

The problem appears to be a syntax problem. I gave you the synatax for a numeric data type, and apparently you are looking up numbers as a text data type.

So try changing this:
If DCount("[GroupNumber]", "tblGroupTool", "[GroupNumber] = " & Me![GroupNumber]) = 0 Then

To this:
If DCount("[GroupNumber]", "tblGroupTool", "[GroupNumber] = '" & Me![GroupNumber] & "'") = 0 Then
__________________________________________________ _____

I noticed that even my text type syntax is different that what you used
as strCriteria ......does your syntax work? where did you get this syntax from? I have not seen the syntax like you have it in strCriteria. Let me know.

Thanks,

pDog

[Neekos]

Thank you! That was the fix!

The reason i have to have to GroupNumber as a text field is because many group numbers are alphanumeric.

And my syntax for the stlinkCriteria does work - i believe i got that from this board somewhere.

I still dont really understand the syntax of it though - i'm not sure when and where to use double quotes or single quotes and the reasoning for it. It drives me nuts! I have a VBA for Access book that i use for reference, but that doesnt really explain it either.

[puppydogbuddy]

Thank you! That was the fix!

The reason i have to have to GroupNumber as a text field is because many group numbers are alphanumeric.

And my syntax for the stlinkCriteria does work - i believe i got that from this board somewhere.

I still dont really understand the syntax of it though - i'm not sure when and where to use double quotes or single quotes and the reasoning for it. It drives me nuts! I have a VBA for Access book that i use for reference, but that doesnt really explain it either.

Neekos,
Thanks for the feedback. I am glad I was able to help resolve your problem. In regards to the quote syntax, I haven’t found any single reference source either. MS makes learning the rules for quote syntax like potty training…you get rewarded if you get it right…and get nothing if you get it wrong!

All I can tell you is that there is a distinction between a literal string (quotes at the begin and end of the string) and a string that contains a reference to an object or variable that supplies a dynamic value to the string that is not known until runtime. Then there is a further distinction made if the value supplied is numeric or text as you have seen in your case with the groupnumber.

[Neekos]

I do understand it when it's a literal string, but like you said - when its refering to an object within a string - is where i dont get it.

Thanks for your assistance with this. I really appreciate it! This search form is making go nuts. I have it so that they can search by either Group Name, Group Number, or Cruise Line AND sail date. The only problem with Cruise Line and Sail date is: What if there are multiple Groups on the same cruise line and on the same date? I'm not sure how to handle this....right now its just opening the form with the first qualifying group showing.

[puppydogbuddy]

I do understand it when it's a literal string, but like you said - when its refering to an object within a string - is where i dont get it.

Thanks for your assistance with this. I really appreciate it! This search form is making go nuts. I have it so that they can search by either Group Name, Group Number, or Cruise Line AND sail date. The only problem with Cruise Line and Sail date is: What if there are multiple Groups on the same cruise line and on the same date? I'm not sure how to handle this....right now its just opening the form with the first qualifying group showing.

Neekos,
Ok, understand that in the case where an object is supplying a value at runtime, that object/variable has to be separated out of the string in order for Access to know that there is a value to be returned to the string. Once the value is returned, Access needs to know how to treat it....Is it a numeric value or part of a text string. If the object or variable that is supplying the return value is of the data type that returns a text value to the string, there are an extra set of quotes to designate the return value as text.

example>>>>>>"[GroupNumber] = '" & Me![GroupNumber] & "'"

and if the return is numeric data type, no extra quotes are needed:
for example if GroupNumber were numeric:
>>>>>>"[GroupNumber] = " & Me![GroupNumber]
__________________________________________________ ______________
In reference to your other question, there are several ways to go about it. Off the top of my head (I haven't had the time to test this) , i think this would be my preference:

1. go to your table and create a composite index using the index button in table design view. the index would be on Cruise Line/sail date/groupnumber. This index would be used internally by Access to speed up the search.
2. on your main form, I assume you already capture the Cruise Line/sail date/group number in respective text boxes, so I would place a textbox (e.g. txtCruiseLineIdx) on the form, set its visible property to no, and define its control source as the concatenated value of the 3 controls as shown below. By making the textbox hidden, the search index will be hidden from users.

txtCruiseLineIdx = Me!txtCruiseLineName & Me!txtSailDate & Me!txtGroupNumber

Then do your search Like before

stLinkCriteria = "[txtCruiseLineIdx] = '" & Me!txtCruiseLineIdx & "'"
DoCmd.OpenForm stDocName,,,stLinkCriteria


If for some reason that does not work, try the search this way.
' I believe focus can be set on an invisible textbox as long as control is enabled and not locked.:

Me.txtCruiseLineIdx.SetFocus
DoCmd.FindRecord Me!txtCruiseLineIdx
Me!GroupNumber.SetFocus


Let me know how it goes.