[RESOLVED] how put autonumber in tect box

[feeder23]

Hi,

I want to place a number constantly in a textbox when I open my form.

This number is the "maximum + 1" from an autonumberfield in one of my tables. Which code do I have to use for this problem?

thx
greetz

[dannymking]

You could try..

=DMAX("Autonumber","TableName")+1

[feeder23]

Do I have to place it in textbox when I open the form in Design View?

When I do this and replace autonumber and tablename, it gives an error.

greetz

[dannymking]

No place it as the controlsource in the data tab of a text box's properties in design view..

Control Source: =DMax("Autonumber", "TableName") +1

I could show how via code as well, but it seems a bit overkill for this

[feeder23]

There's still another problem.

I just used = MAX("tablefield") and it works but not with the right field.
When I scroll in Control Source, I see the 5 fields I used for this form, but the max I want is from another table and that autonumber doesn't stand there.

greetz

[dannymking]

the method I have given you is Domain Aggregates, it will reference the table to which the autonumber is present and return the maximum number and add one to it..

What is the autonumber's field name? and what is the table's name??

[feeder23]

Well, I use the table "TblFouten" in the form.
The autonumber I want to get, is in the field "IDOplossing" in the table "TblOplossingen".

greetz

[dannymking]

then using this code should do it..

If it doesn't then we'll have to set it by code..

=DMax("IDOplossing","TblFouten")+1

[feeder23]

Indeed, I use this but it gives errors.
Probably because the form has no link to the TblOplossingen.

greetz

[dannymking]

What is the error your getting, as this should be fine, it seems as though something else is causing the problem..

[feeder23]

When I enter it says:

The expression you entered contains invalid syntax.
You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding it in quotation marks.

But when I open Dmax in the control source it gives 3 fields instead of 2.
And I am sure my table and field is correct and I placed the quotation marks.

Also when I change to the main page of access and then back to the form, the control source i typed, has dissapeared.

greetz

[dannymking]

The third field is just a criteria if you are using one.. you will not be in this example..

Upload an example of the form and attached sample of the table/SQL to which the form relates and also some dummy data in TblOplossingen.

If you place what I have given you in the control source (DATA TAB) of the text box exactly as it stands you should be getting some results..

Try it out.. Press ALT+F11 to open the VBE then Press ATL+G to open the immediate window type or paste the following followed by enter..

?DMax("IDOplossing","TblFouten")+1

What is shown underneath?

[feeder23]

It says:

Compile error:
Expected: line number or label or statement or end of statement

but I now see a fault in =DMax("IDOplossing","TblFouten")+1

IDOplossing stands in TblOplossingen, but when I type
=DMax("IDOplossing","TblOplossingen")+1
it gives the same error, also in the control source.

[dannymking]

The autonumber I want to get, is in the field "IDOplossing" in the table "TblOplossingen".

You should be referencing the autonumber in the table to which it resides.. you stated this.. therefore this =DMax("IDOplossing","TblFouten")+1 will not work unless the ID is in there..

[feeder23]

sure sure, i did that

but i just tried this

i made a access file

with 2 tables, table1 and table2

in Table1: two fields 1 and 3
in Table2: two fields 2 and 4

1 and 2: autonumbering
3 and 4: text

now I made a form of Table1, I saw all the records in 1 and 3 (logically)
now I add a text box and typed in the source control.

=DMax("2","Table2")+1

but it gaves the same error as in my accessfile

[dannymking]

This is access we are working with here isn't it??

[feeder23]

yesyes

[dannymking]

Here's an example..

It's access XP so if you are using 97 then let me know and I'll convert it for you..

[feeder23]

now I see

=DMax("IDOplossing";"TblFouten")+1

we were using all the time , instead of ; between "IDOplossing" and "TblFouten".

now it works

thx
greetz

[dannymking]

I believe that is a regional setting thing as it uses comma here in the UK

[feeder23]

np