Get the last record from a filtered table and display in a text box on form.

[Les]

I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the last year
data was entered for the current staff member. At the moment most staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for linking
the data.

I have run a Last record query which will display this but I guess I need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les

 

[Dan Artuso]

Hi,
The domain lookup function you're looking for is DMax.

 

[Les]

Dan thanks for below but what I really need is the code for the following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les

Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP

I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the last year
data was entered for the current staff member. At the moment most staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for linking
the data.

I have run a Last record query which will display this but I guess I need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les

 

[Dan Artuso]

Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" & Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP

Dan thanks for below but what I really need is the code for the following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les

Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP

I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the last year
data was entered for the current staff member. At the moment most staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for linking
the data.

I have run a Last record query which will display this but I guess I need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les

 

[Les]

Thanks Dan that's working fine now.

Les

Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" & Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP

Dan thanks for below but what I really need is the code for the following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les

Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the

last
year

data was entered for the current staff member. At the moment most staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for linking
the data.

I have run a Last record query which will display this but I guess I need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les

 

[Les]

Sorry Dan spoke to soon.

The year is working fine but if I use the code for the month then if the
year is 2005 and the month is 6 it shows the month as 12 as it's the
highest in the previous year. Any thoughts on this please.

Les

Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" & Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP

Dan thanks for below but what I really need is the code for the following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les

Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the

last
year

data was entered for the current staff member. At the moment most staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for linking
the data.

I have run a Last record query which will display this but I guess I need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les

 

[Dan Artuso]

Hi,
What are you using as criteria?
If you want the max month for for a particular year, you have to use
the year in your criteria. I have no idea what you're tables look like, so that's
all I can suggest for now.


--
HTH
Dan Artuso, Access MVP

Sorry Dan spoke to soon.

The year is working fine but if I use the code for the month then if the
year is 2005 and the month is 6 it shows the month as 12 as it's the
highest in the previous year. Any thoughts on this please.

Les

Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" & Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP

Dan thanks for below but what I really need is the code for the following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les


Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the last
year
data was entered for the current staff member. At the moment most staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for
linking
the data.

I have run a Last record query which will display this but I guess I
need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les

 

[Greg Kraushaar]

You need to filter ofr the YEar when you get the month
endYear = DMax("[Yr]","tbl","key =" & ID
EndMonth = DMax("Mth","tbl",Key="& ID&" and Yr= "&EndYear

Sorry Dan spoke to soon.

The year is working fine but if I use the code for the month then if the
year is 2005 and the month is 6 it shows the month as 12 as it's the
highest in the previous year. Any thoughts on this please.

Les

Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" & Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP

Dan thanks for below but what I really need is the code for the following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les


Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the last
year
data was entered for the current staff member. At the moment most staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for
linking
the data.

I have run a Last record query which will display this but I guess I
need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les

 

[Les]

Greg thanks I have tried the line below but I get a "Type Missmatch" error.
All the fields are Text fields so am have i miss interpreted your code?

endMonth = DMax("[numMonth_Number]", "tblAssociate_Diary", "key =" &
Forms!frmAssociate_Details!id & " and numYear = " & endYear)

You need to filter ofr the YEar when you get the month
endYear = DMax("[Yr]","tbl","key =" & ID
EndMonth = DMax("Mth","tbl",Key="& ID&" and Yr= "&EndYear

Sorry Dan spoke to soon.

The year is working fine but if I use the code for the month then if the
year is 2005 and the month is 6 it shows the month as 12 as it's the
highest in the previous year. Any thoughts on this please.

Les

Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" & Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP


Dan thanks for below but what I really need is the code for the following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les


Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear"

in
an

unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see

the
last

year
data was entered for the current staff member. At the moment

most
staff

have records from 2003 & 2004. "frmProject" has a field "[id]" for
linking
the data.

I have run a Last record query which will display this but I guess I
need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les

 

[Dan Artuso]

If the criteria is text, then you have to delimit with single quotes.
Usually a num or int prefix indicates the variable is a number while a str
prefix indicates strings. These things do matter, hence the convention.
Are you saying your key field is text as well?

endMonth = DMax("[numMonth_Number]", "tblAssociate_Diary", "key =' " & _
Forms!frmAssociate_Details!id & " ' and numYear = ' " & endYear & " ' ")

the spaces between quotes are for clarity.



--
HTH
Dan Artuso, Access MVP

Greg thanks I have tried the line below but I get a "Type Missmatch" error.
All the fields are Text fields so am have i miss interpreted your code?

endMonth = DMax("[numMonth_Number]", "tblAssociate_Diary", "key =" &
Forms!frmAssociate_Details!id & " and numYear = " & endYear)

You need to filter ofr the YEar when you get the month
endYear = DMax("[Yr]","tbl","key =" & ID
EndMonth = DMax("Mth","tbl",Key="& ID&" and Yr= "&EndYear

Sorry Dan spoke to soon.

The year is working fine but if I use the code for the month then if the
year is 2005 and the month is 6 it shows the month as 12 as it's the
highest in the previous year. Any thoughts on this please.

Les


Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" &
Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP


Dan thanks for below but what I really need is the code for the
following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to
populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les


Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in
an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text
box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the
last
year
data was entered for the current staff member. At the moment most
staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for
linking
the data.

I have run a Last record query which will display this but I guess I
need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les

 

[Les]

Dan Appologies for the confusion. What happened I changed the format in the
field and forgot to rename the prefix. The key and ID are numbers so I have
changed the formula and it works perfectly now.

Many thanks for your continued assistance with this.

Les

If the criteria is text, then you have to delimit with single quotes.
Usually a num or int prefix indicates the variable is a number while a str
prefix indicates strings. These things do matter, hence the convention.
Are you saying your key field is text as well?

endMonth = DMax("[numMonth_Number]", "tblAssociate_Diary", "key =' " & _
Forms!frmAssociate_Details!id & " ' and numYear = ' " & endYear & " ' ")

the spaces between quotes are for clarity.



--
HTH
Dan Artuso, Access MVP

Greg thanks I have tried the line below but I get a "Type Missmatch" error.
All the fields are Text fields so am have i miss interpreted your code?

endMonth = DMax("[numMonth_Number]", "tblAssociate_Diary", "key =" &
Forms!frmAssociate_Details!id & " and numYear = " & endYear)

You need to filter ofr the YEar when you get the month
endYear = DMax("[Yr]","tbl","key =" & ID
EndMonth = DMax("Mth","tbl",Key="& ID&" and Yr= "&EndYear

Sorry Dan spoke to soon.

The year is working fine but if I use the code for the month then if the
year is 2005 and the month is 6 it shows the month as 12 as it's the
highest in the previous year. Any thoughts on this please.

Les


Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" &
Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP


Dan thanks for below but what I really need is the code for the
following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to
populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les


Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all

the
staff

records.

What I need to do is display the last year entered in

"numYear"
in

an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text
box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can

see
the

last
year
data was entered for the current staff member. At the moment most
staff
have records from 2003 & 2004. "frmProject" has a field

"[id]"
for

linking
the data.

I have run a Last record query which will display this but I guess I
need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les