Access won't let me select a default record from the database

[SamSharma]

The big problem behind this is that I want to fill in one of the fields in a form with a default value. The current expression in data-->default value looks like this

[race number] is a FK to the main table's primary key.

=(1+Nz(DMax("ID","[race tasks]","[race number] =" & "[race number]")))

So for each [race number], every time I add a task I should get a new high number for the task ID

But this gives me duplicates. For the life of me I cannot figure out why it's giving me duplicates (only one user on this database at this time)

I add a record, start editing and Access pops up its automatic new record addition, with a duplicate value

It occurred to me that the way Access automatically pops up a new row without me asking might be causing some difficulty, so One thing I tried was In my form design I disable 'allow additions' to false and put an 'add a new record button' on the page.

In the action for the button I have to, guess what, allow additions.

please, please help.

Thanks for your time.

 

[Zion7]

Because, the records not saved yet.
As you can see, the default value is calculated,
even before a new record is made.
I would put your command, on the afterInsert event of the form,

Me.refresh
Me![RaceNumber]=(1+Nz(DMax("ID","[race tasks]","[race number] =" & "[race number]")))

 

[Zion7]

Actually, you may want to try the OnDirty Event

(I put the wrong control, last time)

Private sub Form_Dirty(Cancel As Integer)
Me!TaskId = (1+Nz(DMax("ID","[race tasks]", _
"[race number] =" & "[race number]")))
End sub

 

[PHV]

Anyway, I quite don't understand this criteria for the DMax function:
"[race number] =" & "[race number]"

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

 

[Zion7]

racenumber, is the fkId of the sub form.
Relationship key.
Automatically created, when subrecord created (as you know).
He wants to sequential create new numbers for TaskID, within the main record.
They will duplicate, outside of their parent record.

That's my understanding, anyhow?

excuse me for taking the initiative to respond PHV.
I was a little stumped at first myself, on why the stipulation. And am still hesitant to believe I'm 100% correct.

... I was curious to hear, your thoughts?

 

[PHV]

My thought:
=(1+Nz(DMax("ID","[race tasks]","[race number]=" & Forms![race form name]![race number]),0))

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

 

[PHV]

Furthermore I don't think this formula is well suited for a DefaultValue property.
I'd suggest the Current event procedure of the subform:
If Me.NewRecord Then
Me![task ID] = 1 + Nz(DMax("ID", "[race tasks]", "[race number]=" & Forms![race form name]![race number]), 0)
End If

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

 

[SamSharma]

Thanks for your answers so far, I'll try out some of them.

Nobody has a suggestion for the "nuissance new row" issue yet

As one of the answers above suggested, I tried putting the code not in the default property but in some event -

right now I disable 'allow additions' and have an 'add record' button, I press it to add a new record - in the click event handler I have to set 'allow additions' to true.

But as soon as I start editing this new record, Access adds another row - I want to prevent this new automatic row from coming up - it's confusing for the users, it also does the duplicate record silliness but can't find a way to stop it.

If I set 'allow additions' to false in any of the events, the record I'm trying to add with my manual add button disappears Nobody mentioned yet how to prevent Access from

 

[SamSharma]

OK, when I add the code to the On Dirty event handler I get a significant

when I add the code to the oncurrent event, what happens is I click on my "add new task" button and 2 rows pop up, one with the correct new task number (and a pen beside indicating that row is being edited)

and a second row (with the asterisk) with a zero in the task number field (since I removed the code from the "default" box.

Is there a way to programmatically write out to the database the record that's being worked on? If I do that in the OnCurrent event, for example, maybe I can make the nuissance "new row" go away, and my users are just editing the one new row that my "add task" button adds.

 

[SamSharma]

Please comment more if you can - here's my progress so far.

For the moment this seems to be working - (I'm testing a LOT more before submitting )

I put the code to fetch the new ID ID in the oncurrent event.

Towards the end of the oncurrent event I save the record.

Together these 2 things solve some of my problems

1. no duplicates in the ID
2. no nuissance new row - the only row I get is the one row that shows up when the user clicks on the 'add new task' button (adding a new record in other ways is disabled in the form properties.)