auto number won't increase

[Sue R via AccessMonster.com]

I have a command button in a form that auto fills in a reference number for
each new entry. It was working properly until recently but now keeps issuing
a duplicate error message - it doesn't increase the number by 1 anymore - and
since this is a database that I took over I'm not sure what may have changed.
An example of a number is 2239-06-112 ( the 06 being the year, the last 3
digits being the one that should increase.) The auto number is set to fill
in "on click" with the following code :

Private Sub cmdGrabID_Click()

Call modReferenceID.modSetReferenceID(Form_frmBirdSurveillance, "2239-" &
Format(Now, "yy") & "-0", "2239-" & Format(Now, "yy") & "-")

End Sub

Thanks for any help!
Sue

 

[John Vinson]

I have a command button in a form that auto fills in a reference number for
each new entry. It was working properly until recently but now keeps issuing
a duplicate error message - it doesn't increase the number by 1 anymore - and
since this is a database that I took over I'm not sure what may have changed.
An example of a number is 2239-06-112 ( the 06 being the year, the last 3
digits being the one that should increase.) The auto number is set to fill
in "on click" with the following code :

Private Sub cmdGrabID_Click()

Call modReferenceID.modSetReferenceID(Form_frmBirdSurveillance, "2239-" &
Format(Now, "yy") & "-0", "2239-" & Format(Now, "yy") & "-")

End Sub

Thanks for any help!
Sue

There is evidently an error in the VBA code in
modReferenceID.modSetReferenceID. Since we cannot see that code, it's
more than a little bit difficult to suggest what that error might be.

Please select the Modules tab, find the module named ModReferenceID,
and open it; copy and paste the code in modSetReferenceID to a message
here.

Also note that this field IS NOT A NUMBER. It's a text field, and (in
my opinion) a badly designed non-atomic composite text field at that.

John W. Vinson[MVP]

 

[Sue R via AccessMonster.com]

Sorry! I know it's poorly designed...I figured that out when I was trying to
find out what why it wasn't working - for now I have to get it to work, then
I can take some time and redesign it - thanks for your help. Sue

Here is the code for the module.

Option Compare Database
Option Explicit
Dim rsMain As ADODB.Recordset

Sub modSetReferenceID(frmMain As Form, strWNVIDValue1 As String,
strWNVIDValue2 As String)

rsMain.Requery
frmMain.HCHURefNum.SetFocus
If frmMain.HCHURefNum.Text = "" Then
If rsMain.RecordCount <= 8 Then
frmMain.HCHURefNum.Value = strWNVIDValue1 & rsMain.RecordCount +
1
Else
frmMain.HCHURefNum.Value = strWNVIDValue2 & rsMain.RecordCount +
1
End If
End If

End Sub

Sub modWNVDataInputLoad(frmMain As Form, strWNVTable As String)

Dim strYearAMOSQ As Integer
strYearAMOSQ = Format(Now, "yyyy")
Dim strYear As String
strYear = Format(Now, "yy")


Set rsMain = New ADODB.Recordset
rsMain.CursorLocation = adUseClient
'rsMain.Open "SELECT * FROM " & strWNVTable & " WHERE Format(CallDate,
'yy') = " & strYear, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
If (strWNVTable = "AMosqResults") Then
'MsgBox
rsMain.Open "SELECT * FROM " & strWNVTable & " WHERE [Date Collected]
Between #1/1/" & strYearAMOSQ & "# AND #12/31/" & CStr(strYearAMOSQ + 1) &
"#", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Else
rsMain.Open "SELECT * FROM " & strWNVTable & " WHERE HCHURefNum LIKE '%-"
& strYear & "-%'", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
End If


If (strWNVTable = "AMosqResults") Then
frmMain.RecordSource = "SELECT * FROM " & strWNVTable & " WHERE [Date
Collected] Between #1/1/" & strYearAMOSQ & "# AND #12/31/" & CStr
(strYearAMOSQ + 1) & "#"
frmMain.OrderByOn = True
frmMain.OrderBy = "Site Code DESC"
Else
frmMain.HCHURefNum.Locked = True
frmMain.RecordSource = "SELECT * FROM " & strWNVTable & " WHERE
HCHURefNum LIKE '*-" & strYear & "-*'"
frmMain.OrderByOn = True
frmMain.OrderBy = "HCHURefNum DESC"
End If

End Sub

I have a command button in a form that auto fills in a reference number for
each new entry. It was working properly until recently but now keeps issuing

[quoted text clipped - 13 lines]

Thanks for any help!
Sue

There is evidently an error in the VBA code in
modReferenceID.modSetReferenceID. Since we cannot see that code, it's
more than a little bit difficult to suggest what that error might be.

Please select the Modules tab, find the module named ModReferenceID,
and open it; copy and paste the code in modSetReferenceID to a message
here.

Also note that this field IS NOT A NUMBER. It's a text field, and (in
my opinion) a badly designed non-atomic composite text field at that.

John W. Vinson[MVP]