Dynamic Default Values 1

[pianoben]

On a form (bound to the table 'Passenger')in Data Entry mode, I would like to have a text box bound to the primary key, automatically generate a default value. The primary key is an ID number, which is simply one greater than the previous entry.

I've attempted several things.

I've placed this query

Count(SELECT * FROM Passenger)

into the 'Default Value' field of the box's Properties, but in Form View the string '#Name?' is all that appears. I've futzed around with some VBA code (of which I know next to nothing) and various permutations of the query.

Can someone help me get this Default Value thing going? Thanks!

 

[pianoben]

I also tried using a macro that executes when the form opens that attempts to set the value of the field to "(SELECT Count(*) FROM Passenger) + 1". When the form opens, a message informs me that the macro has failed.

I'm suspecting that maybe forms and SQL Aggregate functions don't mix, but I don't know. Any thoughts? Thanks!

 

[Remou]

How about DMax?
=Dmax("IDFieldName","Passengers")+1

 

[pianoben]

Never mind, I was able to generate the proper values using a VBA module attached to a button's OnClick event. Code below:

[tt]'Container variables
Dim SQL, temp, p1, p2, p3, p4, p5, p6, p7, p8, c, q As String

Dim rec As Recordset

Set rec = CurrentDb.OpenRecordset("Passenger", dbOpenDynaset)

Set rec = CurrentDb.OpenRecordset("Select Max(PassID) + 1 AS newID FROM Passenger")

p1 = CStr(rec!newID)[/tt]

 

[Remou]

And what about DMax, which will do the same thing in one line?

 

[pianoben]

Maybe I'm a dunce with Access, but Dmax didn't work for me, either. I still get the same erroneous output. Thanks for your fast response, though!

 

[TheAceMan1]

How are ya pianoben . . .

Really! . . .

Perhaps if you post the DMax syntax you tried?

See Ya! . . . . . .

 

[pianoben]

I placed

[tt]DMax([PassID],[Passenger])[/tt]

into the Default Value field of the textbox, which when run displayed the "#Name?" value.

 

[Remou]

You seem to have missed the quotes. Try:
DMax([Red]"[/Red][PassID][Red]"[/Red],[Red]"[/Red][Passenger][Red]"[/Red]) + 1

 

[pianoben]

Remou, you're right; I did forget the quotes. It now works just as you said it would. Thanks a ton!

 

[PHV]

And to handle the first addition in an empty table:
Nz(DMax("[PassID]","[Passenger]"),0) + 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Pampers]

so I don't have to put in the first value in by hand!

Pampers
Just let it go...