DeanO
01-27-2008, 12:57 PM
I have myself a database and was given the VBA code to auto increment a number and reset it with the new year. This code works great in the database it is in. When I try to copy and paste to another like database it locks up the command button and all I get is error messages for every command button on the form. I think I have done everything. I made the the query def the same as in the original database. I have not been able to hook up with the person that gave the code original and also I would like to solve this problem with out bother him.
Here is the code.
' PURPOSE: use a query to establish the IncidentYearIndex if needed
If Me.IncidentYearIndex = 0 Then
Dim rs As DAO.Recordset ' Variant
Dim parm As DAO.Parameter
Static qdef As DAO.QueryDef ' only look up the qdef once per session (WHICH IS SLOW THE FIRST TIME)
If qdef Is Nothing Then
Set qdef = Access.CurrentDb.QueryDefs("IncidentYearIndexNewValue")
End If
For Each parm In qdef.Parameters
parm.Value = Eval(parm.Name)
Next
Set rs = qdef.OpenRecordset
If rs.BOF Then
Me.IncidentYearIndex = 1001
Else
Me.IncidentYearIndex = rs.GetRows(1)(0, 0)
End If
End If
Here is the code.
' PURPOSE: use a query to establish the IncidentYearIndex if needed
If Me.IncidentYearIndex = 0 Then
Dim rs As DAO.Recordset ' Variant
Dim parm As DAO.Parameter
Static qdef As DAO.QueryDef ' only look up the qdef once per session (WHICH IS SLOW THE FIRST TIME)
If qdef Is Nothing Then
Set qdef = Access.CurrentDb.QueryDefs("IncidentYearIndexNewValue")
End If
For Each parm In qdef.Parameters
parm.Value = Eval(parm.Name)
Next
Set rs = qdef.OpenRecordset
If rs.BOF Then
Me.IncidentYearIndex = 1001
Else
Me.IncidentYearIndex = rs.GetRows(1)(0, 0)
End If
End If