Defaults in Cascaded Combo Boxes

Hi, I have a form with 2 combo boxes.

In the first (UCTableYear), I select the Year & in the 2nd (UCTableVersion),
I select the version I want to use from that year from the table Unit Cost
I've managed to set the default for the year to the maximum year in the
table, but I then want the default for the Version to be the maximum version
corresponding to the same year.
This is the code I have:
Private Sub Form_Load()
Me!UCTableYear.Value = Nz(DMax("[Year]", "[unit cost]", ""), 0)
End Sub

Private Sub UCTableYear_AfterUpdate()
Form.UCTableVersion.Requery
Me!UCTableVersion.Value = Nz(DMax("[Version]", "[unit cost]", ""), 0)
End Sub

It's the bit 'Me!UCTableVersion.Value = Nz(DMax("[Version]", "[unit cost]",
""), 0)' which isn't working. What do I need to do to make it set itself to
the maximum available for the year appearing in the 1st combo box?

Thanks

On Mon, 26 Oct 2009 06:07:01 -0700, merry_fay
wrote:

Don't leave the third argument of DMax() empty, but set it to:
"[Year]=#" & Me!UCTableYear.Value & "#"
I am assuming Year is your field name. Not a good choice because it is
a reserved word. Date values need to be wrapped in #-signs.

-Tom.
Microsoft Access MVP


Hi, I have a form with 2 combo boxes.

In the first (UCTableYear), I select the Year & in the 2nd (UCTableVersion),
I select the version I want to use from that year from the table Unit Cost
I've managed to set the default for the year to the maximum year in the
table, but I then want the default for the Version to be the maximum version
corresponding to the same year.
This is the code I have:
Private Sub Form_Load()
Me!UCTableYear.Value = Nz(DMax("[Year]", "[unit cost]", ""), 0)
End Sub

Private Sub UCTableYear_AfterUpdate()
Form.UCTableVersion.Requery
Me!UCTableVersion.Value = Nz(DMax("[Version]", "[unit cost]", ""), 0)
End Sub

It's the bit 'Me!UCTableVersion.Value = Nz(DMax("[Version]", "[unit cost]",
""), 0)' which isn't working. What do I need to do to make it set itself to
the maximum available for the year appearing in the 1st combo box?

Thanks