Result of query into label.caption ?

[SpookiePower]

I use SQL to find the max value in a column, and now need to show the result in a label - or what else might be good to choose.



I use to know a bit VBA but have forgot much of it now, but I have tried something like this, without luck -

label.caption = "Query"

But I'm not sure how to tell, that it is an SQL and not just some text put into the caption.

- Kim -

[ItsMe]

I use SQL to find the max value in a column.......

How are you using SQL? A label is not going to run an SQL statement so you need to first run or executer your query. When you run a query it will create a recordset. You then need to iterate through the recordset to retrieve the record and or field you are after.

[June7]

Is this for a report? Options are to include the query in the report's RecordSource or use DMax in a textbox expression.

[SpookiePower]

Thanks both of you, it works

This is my SQL -

SELECT MAX(Ordrenummer)+1 AS MaxOrdreNr FROM Lager;

and this is my VB code -

Private Sub lOrdreNr_Click()
lOrdreNr.Caption = DLookup("[MaxOrdreNr]", "qMaxOrdreNr")
End Sub

But there is something I would like to change. Right now the result is placed in the caption of a label, and only shows up when I click on the label. Would it be possible to show the result in the label. when I open up the form ?

- Kim -

[June7]

Why are you using a label control? What is purpose of this code? It looks like this is incrementing a custom unique identifier, in which case why is it not being saved into a field?

[SpookiePower]

I'm trying to build up a little webshop, and would like to use access to keep track of all my items, orders and sales.

I don't know much about access so far, but I use those things from access that I can get to work - there might be much better ways to do this - but I'm in a learning process.

My SQL shows the next available ordernumber, that I much attach to the next order made from my shop. I guess that this can be made automatic by access, but I'm learning it one step at a time

So far it works for me, but if there is a better way to do this, I'm willing to learn

- Kim -

[June7]

Autonumber field can generate unique identifier. Can't count on it being sequential (no gaps) nor always positive (although I've never seen negative).

Setting label caption does not save the value into record.

[ItsMe]

There are ways to import an existing number value into an Autonumber field. For instance, if your last order number was 1000, you can import that value into a new table's autonumber field. It is not the easiest thing to accomplish, but you only need to do it once.

Like June has mentioned, Autonumber fields do not function perfectly. If you can not have a missing order number you can not depend on Autonumber fields to generate your order number.