Condense Code? 1

[fredka]

I have a form that is used to run some reports and queries that update tables - I want the user to see the last month of data that has been entered into the table so they know if the last time it was updated (year to date data is appended to calcluate scores on a monthly basis)

I currently have this which does the same thing for two tables. The code is located in the after update event of the year field on my form - can I somehow condense this?

Thanks!!!!!

Dim strsql As String
Dim strsql1 As String
Dim rs As DAO.Recordset
Dim rs1 As DAO.Recordset

'updates the "MaxCPMonth" field with the max month field in tblSummaryCompletePaperwork
'using the year on the form

strsql = "SELECT Max(monthnumber) " & _
"FROM tblSummaryCompletePaperwork " & _
"Where Year = " & Me!year

Set rs = CurrentDb.OpenRecordset(strsql)

If Not rs.BOF And Not rs.EOF Then

Me.maxCPmonth = rs.Fields(0).Value
End If

'updates the "MaxMMMonth" field with the max month field in tblSummaryMemberMaintenance
'using the year on the form

strsql1 = "SELECT Max(monthnumber) " & _
"FROM tblSummaryMemberMaintenance " & _
"Where Year = " & Me!year

Set rs1 = CurrentDb.OpenRecordset(strsql1)

If Not rs.BOF And Not rs.EOF Then

Me.MaxMMMonth = rs.Fields(0).Value
End If

 

[PHV]

Your code may be condensed to only 2 lines:
Me!maxCPmonth = Nz(DMax("monthnumber", "tblSummaryCompletePaperwork", "[Year]=" & Me!year), 0)
Me!MaxMMMonth = Nz(DMax("monthnumber", "tblSummaryMemberMaintenance", "[Year]=" & Me!year), 0)

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

 

[fredka]

Wow - as always, thanks for your help PhV!!!!!