Display Message when not found

[Ozzboss]

Hi guys!
My question for this week is... lol

I have created a text box in my main screen/form to enter a quote number and then created a button to start a search for that quote number. This in turn opens a form to be able to edit that specific quote.

I have also added an error message if the button is clicked with no quote number entered in the text box.
All this works great BUT, I would like it to also display a message when the quote number for the search is not found. At the moment it simply opens the form to a new record. I want to stop the form from opening and display this error instead. Can anyone give me a guide on how to achieve this?

Private Sub EditQuoteBtn_Click()
If Not IsNull(Me.QuoteSearchTxt) Then
    DoCmd.OpenForm "EditQuote"
Else
    MsgBox MESSAGETEXT, vbExclamation, "Please Enter The Quote Number You Wish To Edit"
End If

End Sub

The image attached shows the query that is the source of the EditQuote form.

 

[Gasman]

Use DlookUp() to make sure it exists first.

 

[Ozzboss]

Hmmm.. I am obviously missing something important lol. But I'm not sure what.

I now have the following code

Private Sub EditQuoteBtn_Click()
If IsNull(DLookup("QuoteNumber", "JobDetails", "QuoteNumber=" & Me.QuoteSearchTxt)) Then
    MsgBox MESSAGETEXT, vbExclamation, "Quote Number Does Not Exist. Please try again"
Else
If Not IsNull(Me.QuoteSearchTxt) Then
    DoCmd.OpenForm "EditQuote"
Else
    MsgBox MESSAGETEXT, vbExclamation, "Please Enter The Quote Number You Wish To Edit"
End If
End If
End Sub

Now if I type in a quote number that exists, it opens the form to that record, If I enter a number that does not exist then it displays the message correctly. But now I am getting the following error if I leave the QuoteSearchTxt blank.

Run-time error '3075':
Syntax error (missing operator) in query expression 'QuoteNumber='.

 

[GPGeorge]

You probably will be better of not trying to do both tasks in a single line of code. In other words, validate the input as step one. If there is a valid value in the control THEN you can check for it's existence in the JobDetails table using DLookup as step two. The underlying principle is KISS. Maybe advanced developers are going to want to achieve an elegant solution. At this point, you need one that works.

That said, I wonder if you might want to consider another approach entirely. How many quotes are there? How many do you anticipate accumulating? Dozens? Hundreds? Thousands? It probably makes sense to supply a list of existing quotes in a combo box or list box for the user to pick, rather than forcing them to type exactly into a text box control. Unless you have a huge number of quotes, performance will not be a factor. If the user types in an existing quote, the combo or list box will attempt to match it using the default Auto Expand property. If not, then an error will be raised which you can trap and show the user your message as above.

 

[Gasman]

Hmmm.. I am obviously missing something important lol. But I'm not sure what.

I now have the following code

Private Sub EditQuoteBtn_Click()
If IsNull(DLookup("QuoteNumber", "JobDetails", "QuoteNumber=" & Me.QuoteSearchTxt)) Then
    MsgBox MESSAGETEXT, vbExclamation, "Quote Number Does Not Exist. Please try again"
Else
If Not IsNull(Me.QuoteSearchTxt) Then
    DoCmd.OpenForm "EditQuote"
Else
    MsgBox MESSAGETEXT, vbExclamation, "Please Enter The Quote Number You Wish To Edit"
End If
End If
End Sub

Now if I type in a quote number that exists, it opens the form to that record, If I enter a number that does not exist then it displays the message correctly. But now I am getting the following error if I leave the QuoteSearchTxt blank.

Run-time error '3075':
Syntax error (missing operator) in query expression 'QuoteNumber='.

Yes, that is because you are using the control without first checking it contains a value.
Would you boil a kettle first, before filling it with water?
So check it exists first (as you were doing previously, and only if there is something in the control, THEN use it in the DlookUp.

 

[Ozzboss]

You probably will be better of not trying to do both tasks in a single line of code. In other words, validate the input as step one. If there is a valid value in the control THEN you can check for it's existence in the JobDetails table using DLookup as step two. The underlying principle is KISS. Maybe advanced developers are going to want to achieve an elegant solution. At this point, you need one that works.

That said, I wonder if you might want to consider another approach entirely. How many quotes are there? How many do you anticipate accumulating? Dozens? Hundreds? Thousands? It probably makes sense to supply a list of existing quotes in a combo box or list box for the user to pick, rather than forcing them to type exactly into a text box control. Unless you have a huge number of quotes, performance will not be a factor. If the user types in an existing quote, the combo or list box will attempt to match it using the default Auto Expand property. If not, then an error will be raised which you can trap and show the user your message as above.

Yes, I considered a simple combo box but the list of quotes would go on for miles and it's just not a neat approach for this particular scenario.

 

[SHANEMAC51]

Hmmm.. I am obviously missing something important lol. But I'm not sure what.

At a minimum, I would filter by the fields that you know (including by a fragment of the value, in continuous form

1. by the client
2. companies
3. phone
4. time interval (for example, 2022 or the last 2-3 years or a long-ago interval before 2010)
5. type of work

....
And only then by the text of the quotation or supplement , and possibly by several fragments, to find not only aaa ccc, but also ccc aaa

Otherwise, the search can be very problematic when 10+ entries of similar quotes accumulate

 

[SHANEMAC51]

I considered a simple combo box but the list of quotes would go on for miles and it's just not a neat approach for this particular scenario.

the list is clearly not suitable for this search, it should also be a continuous form

 

[Ozzboss]

Yes, that is because you are using the control without first checking it contains a value.
Would you boil a kettle first, before filling it with water?
So check it exists first (as you were doing previously, and only if there is something in the control, THEN use it in the DlookUp.

Can you give me a hint?
I have literally pieced this together from half a dozen different sites lol

 

[Ozzboss]

At a minimum, I would filter by the fields that you know (including by a fragment of the value, in continuous form

1. by the client
2. companies
3. phone
4. time interval (for example, 2022 or the last 2-3 years or a long-ago interval before 2010)
5. type of work

....
And only then by the text of the quotation or supplement , and possibly by several fragments, to find not only aaa ccc, but also ccc aaa

Otherwise, the search can be very problematic when 10+ entries of similar quotes accumulate

The approach I have taken works perfectly for the application I am using. The only issue I am having is getting the correct messages to display when needed and I am sure this is only going to require a minor change from what I have cobbled together.

 

[Gasman]

Can you give me a hint?
I have literally pieced this together from half a dozen different sites lol

Private Sub EditQuoteBtn_Click()

If Not IsNull(Me.QuoteSearchTxt) Then
    If IsNull(DLookup("QuoteNumber", "JobDetails", "QuoteNumber=" & Me.QuoteSearchTxt)) Then
        MsgBox MESSAGETEXT, vbExclamation, "Quote Number Does Not Exist. Please try again"
    Else  
        DoCmd.OpenForm "EditQuote"
    End If
Else
    MsgBox MESSAGETEXT, vbExclamation, "Please Enter The Quote Number You Wish To Edit"
End If

End Sub

 

[Ozzboss]

Private Sub EditQuoteBtn_Click()

If Not IsNull(Me.QuoteSearchTxt) Then
    If IsNull(DLookup("QuoteNumber", "JobDetails", "QuoteNumber=" & Me.QuoteSearchTxt)) Then
        MsgBox MESSAGETEXT, vbExclamation, "Quote Number Does Not Exist. Please try again"
    Else 
        DoCmd.OpenForm "EditQuote"
    End If
Else
    MsgBox MESSAGETEXT, vbExclamation, "Please Enter The Quote Number You Wish To Edit"
End If

End Sub

THANK YOU!!
I tried to spin it around but got more errors. I didn't quite have it right
This works PERFECT!

It also helps (for me anyhow) to be able to see the correct code and learn from it

 

[Gasman]

Well it is not doing anything special.
It checks if you have a value, and if you have, only then checks whether it is valid, else you get an error message if not valid or not present.

 

[GPGeorge]

Yes, I considered a simple combo box but the list of quotes would go on for miles and it's just not a neat approach for this particular scenario.

That's why I posited the proposal in the context of the number of quotes you currently have, or expect to have. However, even if it's into the thousands, AutoExpand will minimize any need to scroll the entire list. Start typing with the first character of the quote number, letter or digit (we can't see how your quotes are structured, so I'm guessing probably numbers, but possibly letters are also part of them). The combo box leaps to the section of the list starting with that character. Type the second character and now the combo box matches on those two, and so on.

Your user can still type the entire quote into the combo box if they want, or they can type only enough to bring the one they want into view and then click it. List boxes only match on the first character, though, so that's not as good a choice for a longer list.

The advantage is that you don't force users to remember the entire quote number and type it exactly correctly.

That said, I see you have the answer you want. Good luck with the project.

 

[Gasman]

I hope there are no letters, as the DlookUp does not use quotes?

 

[GPGeorge]

the list is clearly not suitable for this search, it should also be a continuous form

As I explained before, I can't agree with the first point, given the totality of the work flow, and the second point is in need of explanation. "what" needs to be a continuous form and why?

 

[SHANEMAC51]

As I explained before, I can't agree with the first point, given the totality of the work flow, and the second point is in need of explanation. "what" needs to be a continuous form and why?

the quote is usually quite a long line , it may need to be moved to additional lines - this does not agree with the list

the second remark is that rarely anyone remembers the exact order of words in a quote, which will greatly reduce the chance of finding what is required if there are not 10-15 quotes, but 10000-15000

of course I read and write through google, maybe I don't understand something

 

[GPGeorge]

I think I get your point now, but perhaps a bit of clarification is in order.
What is being searched for is a quote number. It's the code that identifies which quote for a job needs to be retrieved. In this context, we're talking about jobs the OP does for clients, and as part of that process, the OP prepares quotes about the work, cost, etc.

It sounds more like you are thinking of a different kind of quote, perhaps. "...rarely anyone remembers the exact order of words in a quote..." I take that to imply you are thinking more of a quoted remark, the ones we use here and in other forums to repeat what was written in a previous post.

In every project and job system I ever saw, quote numbers, or project numbers, or bid numbers, etc. are strings of digits 4 to 10 digits long, although they may also include letters as prefixes or suffixes in some cases.

If we were talking about the kind of quotes I take it to mean when you say that a "quote is usually quite a long line", then I would agree that we'd be talking about a whole different approach anyway.

 

[SHANEMAC51]

If we were talking about the kind of quotes I take it to mean when you say that a "quote is usually quite a long line", then I would agree that we'd be talking about a whole different approach anyway.

it seems that terminology and Google are not compatible

• enough for me, a quote is a long text from some book, a fragment of information
• for you is a link, a code to some primary source

 

[GPGeorge]

it seems that terminology and Google are not compatible

• enough for me, a quote is a long text from some book, a fragment of information
• for you is a link, a code to some primary source

I don't trust Google on this or much else.

Look at the original post for context.

Ah, heck, I'm gonna "quote" the OP.

"I have created a text box in my main screen/form to enter a quote number"

And, if you open the attached field so it is readable, there is a field called QuoteNumber in a table called JobDetails....