conditional autonumber

[Guest]

I posted a question on the same subject several days b4. It was answere
by Geral Stanley. But I could not work it out with his answer. I tried som
codes. Can somebody take a look and let me know where I did wrong

[Question
When I entering a new record, I want Access to assign a new value to
the primary key automatically in the following format
AByyyy000

Where "A" stands for Agent, "B" stands for Dept, "yyyy" stands for current year, an
"0000" stands for the auto number, starting from 1

In addition, When A or B or year changes, I wan
Access to start the "0000" part from 1 again.

[My trials
I create a table with the following fields: RefNumber(text), DeptID(text), AgentID(text
and RefYear(number)

Then I make a query to count the RefNumber (NoRef) and get the max RefNumber (MaxRef)
"SELECT nz(Count([RefNumber])) AS NORef, Max(Right([RefNumber],4)) AS MaxRef,
TestRefLog.AgentID, TestRefLog.DeptID, TestRefLog.RefYear FROM TestRefLog GROUP
BY TestRefLog.AgentID, TestRefLog.DeptID, TestRefLog.RefYear;

I tried the following code on a fome try to assign a RefNumber automatically after I selec
an agent from the [SelectAgent] combo box

Private Sub SelectAgent_AfterUpdate(

Dim Counter As Lon
Dim SetYear As Lon
Dim SetAgent As Strin
Dim SetDept As Strin

SetYear = Year(Date
SetAgent = Forms![RefLogNew]![SelectAgent
SetDept = Forms![RefLogNew]![SelectDept

If DLookup("NoRef", "NoRefQuery", "AgentID=" & "'" & SetAgent & "'" AND "DeptID=" & "'" & SetDept & "'" AND "RefYear=" & SetYear) < 1 The
Counter =
Els
Counter = Nz(DLookup("MaxRef", "NoRefQuery", "AgentID=" & "'" & SetAgent & "'" AND "DeptID=" & "'" & SetDept & "'" AND "RefYear=" & SetYear)) +
End If

Me.Counter00 = SetAgent & SetDept & SetYear & Format(Counter, "0000"

End Su

[Problem
When the two DLookup has only one criteria, either AgentID only or DeptID only or RefYear only,
the code works. But with all the three criterias, I got a "run time error 13: Type mismatch"

Can somebody tell me what is wrong with me code? Thanks a lot.

 

[dan artuso]

Hi,
Try something like this:

Counter = Nz(DLookup("MaxRef", "NoRefQuery", "AgentID='" & SetAgent & _
"' AND DeptID='" & SetDept & "' AND RefYear=" & SetYear)) + 1

you were not placing your ANDs inside the quotes.


--
HTH
Dan Artuso, MVP

I posted a question on the same subject several days b4. It was answered
by Geral Stanley. But I could not work it out with his answer. I tried some
codes. Can somebody take a look and let me know where I did wrong?

[Question]
When I entering a new record, I want Access to assign a new value to
the primary key automatically in the following format:
AByyyy0000

Where "A" stands for Agent, "B" stands for Dept, "yyyy" stands for current year, and
"0000" stands for the auto number, starting from 1.

In addition, When A or B or year changes, I want
Access to start the "0000" part from 1 again.

[My trials]
I create a table with the following fields: RefNumber(text), DeptID(text), AgentID(text)
and RefYear(number).

Then I make a query to count the RefNumber (NoRef) and get the max RefNumber (MaxRef):
"SELECT nz(Count([RefNumber])) AS NORef, Max(Right([RefNumber],4)) AS MaxRef,
TestRefLog.AgentID, TestRefLog.DeptID, TestRefLog.RefYear FROM TestRefLog GROUP
BY TestRefLog.AgentID, TestRefLog.DeptID, TestRefLog.RefYear;"

I tried the following code on a fome try to assign a RefNumber automatically after I select
an agent from the [SelectAgent] combo box:

Private Sub SelectAgent_AfterUpdate()

Dim Counter As Long
Dim SetYear As Long
Dim SetAgent As String
Dim SetDept As String

SetYear = Year(Date)
SetAgent = Forms![RefLogNew]![SelectAgent]
SetDept = Forms![RefLogNew]![SelectDept]

If DLookup("NoRef", "NoRefQuery", "AgentID=" & "'" & SetAgent & "'" AND "DeptID=" & "'" & SetDept &

"'" AND "RefYear=" & SetYear) < 1 Then

Counter = 0
Else
Counter = Nz(DLookup("MaxRef", "NoRefQuery", "AgentID=" & "'" & SetAgent & "'" AND "DeptID=" & "'"

& SetDept & "'" AND "RefYear=" & SetYear)) + 1

End If

Me.Counter00 = SetAgent & SetDept & SetYear & Format(Counter, "0000")

End Sub

[Problem]
When the two DLookup has only one criteria, either AgentID only or DeptID only or RefYear only,
the code works. But with all the three criterias, I got a "run time error 13: Type mismatch".

Can somebody tell me what is wrong with me code? Thanks a lot.

 

[Guest]

Thanks Dan. It works. Sometimes I am just so confused wit
the creteria. I just could not figure out why it should be quote
like that. You are great. Thanks again

----- dan artuso wrote: ----

Hi
Try something like this

Counter = Nz(DLookup("MaxRef", "NoRefQuery", "AgentID='" & SetAgent & "' AND DeptID='" & SetDept & "' AND RefYear=" & SetYear)) + 1
(I just edit the above line. It works that way)

you were not placing your ANDs inside the quotes


-
HT
Dan Artuso, MV

I posted a question on the same subject several days b4. It was answere
by Geral Stanley. But I could not work it out with his answer. I tried som
codes. Can somebody take a look and let me know where I did wrong

[Question

When I entering a new record, I want Access to assign a new value t
the primary key automatically in the following format
AByyyy000

Where "A" stands for Agent, "B" stands for Dept, "yyyy" stands for current year, an

"0000" stands for the auto number, starting from 1

In addition, When A or B or year changes, I wan

Access to start the "0000" part from 1 again

[My trials

I create a table with the following fields: RefNumber(text), DeptID(text), AgentID(text
and RefYear(number)

Then I make a query to count the RefNumber (NoRef) and get the max RefNumber (MaxRef)

"SELECT nz(Count([RefNumber])) AS NORef, Max(Right([RefNumber],4)) AS MaxRef
TestRefLog.AgentID, TestRefLog.DeptID, TestRefLog.RefYear FROM TestRefLog GROU
BY TestRefLog.AgentID, TestRefLog.DeptID, TestRefLog.RefYear;

I tried the following code on a fome try to assign a RefNumber automatically after I selec

an agent from the [SelectAgent] combo box

Private Sub SelectAgent_AfterUpdate(
Dim Counter As Lon

Dim SetYear As Lon
Dim SetAgent As Strin
Dim SetDept As Strin

SetYear = Year(Date

SetAgent = Forms![RefLogNew]![SelectAgent
SetDept = Forms![RefLogNew]![SelectDept

If DLookup("NoRef", "NoRefQuery", "AgentID=" & "'" & SetAgent & "'" AND "DeptID=" & "'" & SetDept

"'" AND "RefYear=" & SetYear) < 1 The

Counter =
Els
Counter = Nz(DLookup("MaxRef", "NoRefQuery", "AgentID=" & "'" & SetAgent & "'" AND "DeptID=" & "'

& SetDept & "'" AND "RefYear=" & SetYear)) +

End I

Me.Counter00 = SetAgent & SetDept & SetYear & Format(Counter, "0000"
End Su
[Problem

When the two DLookup has only one criteria, either AgentID only or DeptID only or RefYear only
the code works. But with all the three criterias, I got a "run time error 13: Type mismatch"

Can somebody tell me what is wrong with me code? Thanks a lot