Is DLookup the tool for ythis job?

[SimonPGreen]

Hi all,

I would like to prepopulate a record based on another record in the table and need to define a variable that is the TOP value + 1 of the existing values for the field in the table. When a user then adds a new record the field is therefore prepopulated with an incrementing number. This number can then also be edited if the number autogenerated should not be sequential as thia happens on occasion.

I basically need to assign the value of the following query to a variable and add 1:

select top intValuationNumber from applications where fkPackageRef = me!fkPackageRef.

Can Dlookup do this or is it possible to execute the query directly in VBA and assign the value to a variable?

Appreciate any help.

Regards,

Simon

 

[AlexCuse]

I have a similar need (although this one is a little different, first position is based on what source a person comes from, remaining eight are sequential) and what I do is set up a query that will take the max value of the right 8 characters in the sequence number. I then DLookup from there and add (whatever integer) to the result to get my new sequence number. I am not sure if DLookup can take TOP 1 directly, but this has worked very well for me.

I hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ZmrAbdulla]

DMax will do.
if it is stored as string then use Val() to get the value.
see thread181-1342156 for an example.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[SimonPGreen]

Thanks for the info.

DMax worked a treat.

Regards,

Simon