query result on form

[skyrat]

query : SELECT LAST(ID) FROM tblBookings AS LastID
Works and shows the correct number.
But how can I get that number in a TextBox (I hope that's the name as I have a Dutch version of Access) on the form that shows on starting an Access application. I tried all kinds of tricks,but nothing seem to work !
It should be very simple, but as usual it isn't for me in Access !

[Micron]

Think of a table as a bucket of marbles - it has no distinct order that you can recognize unless you apply one to its datasheet view such as ordering ascending/descending on a date/time field, or autonumber field for example. LAST is the last function you'd want to use on an unordered set because more than likely, it doesn't mean what you think. Last will give you whatever is the last record, and that can change over time.

For a single value like this, better to do a DLookup using criteria as it is the simplest method. Or use Max or DMax.
Forgot to add something with respect to your original question. Depends on whether or not the textbox is bound. If not, you could use the DLookup or an expression using Max or DMax as the textbox control source. If it is bound, then the form must have a record source, in which case make the desired field part of the recordsource and bind the textbox to it.

[skyrat]

Thanks sofar. I'm getting nearer now using DLookUp("Max(ID)","tblBookings") in the OnOpen event of the Form. It now gives the right number in the Textbox, but only after I have clicked away a whole lot of error messages about illegal or missing things like Utility.mda etc.

I dont know where you are, but here in Holland it's almost 3 am, so it's time for this 82 years old guy to go to bed now !

[Micron]

You can click on a member name and see what's on their profile page.
You have a table field called "Max(ID)"? Bad idea - Max is a function thus is a reserved word. Don't use them
http://allenbrowne.com/AppIssueBadWord.html

If you get a value from that expression and it looks right, it's a fluke. Even if there was no issue with your table or field names, the expression has no criteria so you will get whatever is in the first record - and First is no better than Last.

If you're getting error messages about things like Utility.mda, you must have converted this db from version 2003? Open vb editor (VBE), click Tools>References and uncheck any that show up as missing, or if you think you need them, find their new paths (assuming you upgraded Office). Sounds like you have more than one issue going on.

[skyrat]

As the description of DLookUp says that the first part is an expression, I thought I could use Max(ID), where ID is an autoincrement field. And , apart from the error messages : it works, I get the right number in the textbox !!
Now if I put ID in the first part of DLookUp , what shoud I put in the criteria part? The Max function is not allowed there !!
The Access Database was updated 2003 --> 2007 --> 2010.
So the simple question remains : how do I get the highest ID of a table in a textbox on opening the first form of a database ?

[davegri]

if you simply want the max id, you don't need criteria.
me.myTextbox=DMax("ID","tblBookings")

[Micron]

Don't know where you saw that - maybe post a link to it? AFAIK, DLookup syntax is DLookup("FieldName", "DomainName","Criteria")
You seem to be defining Max(ID) as a field name, which is why I asked if that was your field name. I suggested using DLookup OR Max OR DMax, not a combination of them. If all you want is the maximum of a numeric value from a table field, then use DMax. If it's a text field, then neither Max or DMax is suitable IMHO.
Max would be more suitable in a query where it operates on a group.

So the simple question remains : how do I get the highest ID of a table in a textbox

In form design view, on property sheet data tab, in the control's source property try =DMax("ID","tblBookings")
ID makes for a poor field name. When (not 'if') you are faced with several of them in a sql statement because there are multiple tables involved they become ambiguous names in a sense. That is, it makes your code and sql harder to follow - even if you wrote it. Better to add a descriptor so that you have BookingID for example.

[Micron]

I wish this site updated faster.
Or I guess I shouldn't take so long to compose an answer...

[davegri]

I wish this site updated faster.
Or I guess I shouldn't take so long to compose an answer...

I hear you. All of us that lean to the didactical approach have had this happen to them.

[Micron]

How much does the OP grow if you just spoon feed them one morsel at a time? The teacher part of me is my downfall, I guess.

[skyrat]

Found the DLookUp description in "DLookup, functie - Toegang - Office Support"


Of course ID is not the fieldname in the real database, but I only used it as a simplified example.
I tried your suggestion =DMax("ID", "tblBookings") but it doesn't work for me. I'll get back later to tell you what happened.

@Davegri :
that very simple solution works fine, the only trouble now is that I have to click away 6 error messages about missing or illegal objects like I said before. As that happens everytime I start the application it's not realy workable ! And they only appear when I start the application, NOT when I change from desgn view to the real form !

[Micron]

my suggestion in post 7:=DMax("ID", "tblBookings")
davegris said in post 6: =DMax("ID","tblBookings")
Other than the fact I had an extra space, which I suspect Access would have removed, I'm amazed it didn't work.

As for the link (I had to translate so there may be room for interpretation error) it says

If expr contains a function, this can be a built-in function or a user-defined function, but not another statistical domain or SQL function.

I would classify DMax as a domain function. Anyway, as I said before, you have other issues. I think after a dozen posts it's time for you to upload a zipped copy of your db - unless you think language will be a barrier. I don't understand Dutch but would be willing to try.

[skyrat]

YEEEEHAAAA !
I found the VB Editor and uncheck all "Missing" items, and now the whole thing works perfectly !

Thanks Micron and Davegri for being such great helps , I certainly learned a lot about Access. As people say : you're never too old to learn and that certainly goes for me at 82 !
I could not send the real database because it's not mine and contains a lot of commercial information.

[Micron]

Glad we could help. FWIW, if you need to post a db with sensitive information in the future...

https://www.accessforums.net/showthread.php?t=77482

[skyrat]

Thanks again, I will keep that in mind !