Using access query in visual basic?

[electro]

I have an access Query(named newSerial) :
SELECT TOP 1 (Productbase.Serialnumber+1) AS Expr1 FROM ProductBase
ORDER BY (Productbase.Serialnumber+1) DESC;

I want to access this new value. Its not a key since an autonumber may be any number. When I create a new product. How do I get this value inside a form?
It works fine when clicking on it. But if I use an unbound box I get 1 as result and not like 76067 which it should be. If I click the query in access it works fine and show 76067.
I tried:
outbox=Expr1
' gives null
'or
outbox=[newserial].[Expr1]
' which gives "Access cant find the field "|" refered to you in your expression"

What should I write? Whats the correct expression?

 

[ejstefl]

You can try using DLookup.

outbox = DLookUp("Expr1", "newSerial")

It looks like you just have one value in the query, but there is an optional third part of DLookUp which would let you set criteria.

 

[electro]

You can try using DLookup.

outbox = DLookUp("Expr1", "newSerial")

It looks like you just have one value in the query, but there is an optional third part of DLookUp which would let you set criteria.

Thanks for the answer but is this the only way to do it or is there other ways?

 

[ejstefl]

What don't you like about this way? While there are always several ways to do things in Access, this is certainley the easiest, and requires no VBA (which other ways would).

 

[electro]

What don't you like about this way? While there are always several ways to do things in Access, this is certainley the easiest, and requires no VBA (which other ways would).

Well I just was wondering
If I did the VBA way.. How would it look?

 

[electro]

DMax() is the function you are looking for, not DLookup().

Your method won't work because you can't just reference a query from code. You need to open a recordset and position yourself to the record you want. Functions can be used pretty much anywhere and you won't need to write more than a line or two of code to use them.

Stangest thing is that DLookup worked fine... I will try Dmax also

 

[ejstefl]

The difference between DLookup and DMax is that DLookup will return the first value it finds that meets the criteria, while DMax will return the maximum. The reason I had you use Dlookup was because your query was only returning one result. Dmax should also work. Alternativley, you could get rid of your query and use DMax directly on your table:

=DMax("Serialnumber", "ProductBase") + 1