auto fill last digits by increment not working

[Sue R via AccessMonster.com]

A database we use was working fine and now won't grab the next reference ID
in a sequence when we click the button on a new record, it keeps bringing up
the last one, not incrementing it and we get a duplicate value error message.
The database was designed by a previous employee of course and I know a bit
of Access but haven't used it in a while. This is the code I can find for
the Event Procedure (the only reference I can find to the number)

Private Sub cmdGrabID_Click()

Call modSetReferenceID(Form_frmHealthTrak, "2239-" & VBA.Format(Now, "yy")
& "-0", "2239-" & VBA.Format(Now, "yy") & "-")

End Sub

any help or point in the right direction would be appreciated.

Thanks
Sue

 

[Dorian]

You need to find the code for
modSetReferenceID
and then post that as well.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[Sue R via AccessMonster.com]

Thanks...I hope this is what you need...

Sub modSetReferenceID(frmMain As Form, strIDValue1 As String, strIDValue2 As
String)

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

End Sub

You need to find the code for
modSetReferenceID
and then post that as well.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

A database we use was working fine and now won't grab the next reference ID
in a sequence when we click the button on a new record, it keeps bringing up

[quoted text clipped - 14 lines]

Thanks
Sue

 

[Dorian]

Well from that routine it is only incrementing the number if Refnum is blank.
I'm assuming the test is for two double quotes rather than four single
quotes (I can't tell)
See the line where I placed the arrow.
So I assume RefNum is not blank. I have no idea what this means.
You could put a Msgbox in an else statement (between the last 2 End Ifs) to
confirm this e.g.
Else Msgbox "Refnum = " & frmMain.HCHURefNum.Text
If you know how to use the VB debugger you can set a breakpoint where this
routine is called and step through the code one line at a time to see exactly
what lines are being executed.
-- David
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

Thanks...I hope this is what you need...

Sub modSetReferenceID(frmMain As Form, strIDValue1 As String, strIDValue2 As
String)

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

End Sub

You need to find the code for
modSetReferenceID
and then post that as well.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

A database we use was working fine and now won't grab the next reference ID
in a sequence when we click the button on a new record, it keeps bringing up

[quoted text clipped - 14 lines]

Thanks
Sue