Display highest number?

[Marie]

Hi,

I have a 97 database with a form (frmInput) linked to a
table (tblData) that has several fields: ID, Type, Order,
and Code.
ID = Type+Order, where Type is a choice of three (A,B,C)
and Order is an integer. When I open frmInput, I would
like to see (in a subform?) the highest Order number for
each of the three Types (to help avoid inputting duplicate
information). So, ideally, when I open the frmInput, the
subform(s) would read A87, B66, C103, for example.
('Count' queries won't work as some records have been
deleted retrospectively.) Does this make sense?

Is there an easy way to do this? Any help would be much
appreciated.

many thanks and kind regards,

Marie

 

[Ronald W. Roberts]

Hi,

I have a 97 database with a form (frmInput) linked to a
table (tblData) that has several fields: ID, Type, Order,
and Code.
ID = Type+Order, where Type is a choice of three (A,B,C)
and Order is an integer. When I open frmInput, I would
like to see (in a subform?) the highest Order number for
each of the three Types (to help avoid inputting duplicate
information). So, ideally, when I open the frmInput, the
subform(s) would read A87, B66, C103, for example.
('Count' queries won't work as some records have been
deleted retrospectively.) Does this make sense?

Is there an easy way to do this? Any help would be much
appreciated.

many thanks and kind regards,

Marie

Try this. put this in the form Open event.

Dim iOrderA As Long
Dim txtOrderA As String


if IsNull(DMin("[Order]", "tblData", "[Type] = 'A'")) then
iOrderA = 0
Else
iOrderA = DMin("[Order]", "tblData", "[Type] = 'A'")
Endif
txtOrderA="A" & iOrderA

Ron