Solved Help! Simple Dlookup in unbound form field not working (1 Viewer)

[slharman1]

Ok Guys, I guess I still don't get it. What the hell is wrong with this code!
Quote number is a field in the tblQuotes
I've tried it with brackets around fields as well, I just am not letting the proper syntax into my brain I guess.
Microsoft website is exactly like my code.

    Dim quoteInput As Variant, quoteno As Variant
    quoteInput = InputBox("Enter quote number for new order:", "New Order From Quote")
    quoteno = DLookup("QuoteNumber", "tblQuotes", "QuoteNumber =" & quoteInput)
    If quoteno <> quoteInput Then
        MsgBox quoteInput & " is not a valid quote number"
        Exit Function
    End If

This is straight from the MS VBA support website - What the hell is different in my code above!
Frustrated!!!

 

[cheekybuddha]

>> Quote number is a field in the tblQuotes <<

What datatype is field QuoteNumber?

 

[slharman1]

>> Quote number is a field in the tblQuotes <<

What datatype is field QuoteNumber?

It’s a number field not a text field

 

[cheekybuddha]

You haven't said what's not working!

It look like you're just trying to check the existence of the QuoteNumber in the table.

If the QuoteNumber does not exist because you will be checking whether Null <> quoteInput - this will yield Null and your MsgBox will never fire.

Try this way:

    Dim quoteInput As Variant, quoteno As Integer
    quoteInput = InputBox("Enter quote number for new order:", "New Order From Quote")
    quoteno = DCount("*", "tblQuotes", "QuoteNumber = " & IIf(Len(quoteInput), quoteInput, 0))
    If quoteno = 0 Then
        MsgBox quoteInput & " is not a valid quote number"
        Exit Function
    End If

**** NB edited to add missing closing bracket in DCount() statement ****

 

[slharman1]

You haven't said what's not working!

It look like you're just trying to check the existence of the QuoteNumber in the table.

If the QuoteNumber does not exist because you will be checking whether Null <> quoteInput - this will yield Null and your MsgBox will never fire.

Try this way:

    Dim quoteInput As Variant, quoteno As Integer
    quoteInput = InputBox("Enter quote number for new order:", "New Order From Quote")
    quoteno = DCount("*", "tblQuotes", "QuoteNumber = " & IIf(Len(quoteInput), quoteInput, 0))
    If quoteno = 0 Then
        MsgBox quoteInput & " is not a valid quote number"
        Exit Function
    End If

**** NB edited to add missing closing bracket in DCount() statement ****

Found my problem, it wasn't the Dlookup, it was the order of my statements in the procedure.
I am actually checking to see if a quote number exists, then if it does, copy the quote data to a new order, otherwise i will open the order form in what I call an "on the fly order" - one that does not have a quote associated with it.
So in that case I don't think Dcount is the right method.
Thank you

 

[cheekybuddha]

So in that case I don't think Dcount is the right method.

Did you try?

DCount() is a good way of checking whether a value exists in a table - it returns 1 (or more) if the value is present, 0 if not.

You do not have to handle nulls returned by other domain aggregate functions like DLookup().

 

[slharman1]

Did you try?

DCount() is a good way of checking whether a value exists in a table - it returns 1 (or more) if the value is present, 0 if not.

You do not have to handle nulls returned by other domain aggregate functions like DLookup().

So are you saying dcount will check the existence of the quote number entered into the input box and return it if it’s there? Or does it just return 1 for true and 0 for false?
Thank you

 

[cheekybuddha]

The statement I posted will count how many occurrences of the quoteInput are in the field QuoteNumber in the table tblQuotes.

If it is not present (ie the Quote number does not exist) the DCount will return 0. If it is present it will return 1 (or however many records have that QuoteNumber)

So, if it returns 0 then you know the quote number does not exist. Then you can fire your MsgBox

But the function always returns a number, never Null, so you can rely on your If statement.