Hi

I have a table called clients these are clients of an insurance brokerage,
the brokerage has no control over the format of the reference applied to each
client. these take the form of the first two letters of surname followed by
first two letters of forename followed by 2 digits.

the first time any combination is used the digits would be 01 the next 02
and so on.

so john doe would be dojo01, joanne doe would be dojo02 etc.

these are stored in table clients with fields F_name, S_name, C_ref.

i want a form which when data is entered will check all the existing records
to see if combination exists and create c_ref automatically

thanks

I know you said you have no control over the format, but what happens when
someone gets married? Divorced? Adopted?

Very poor design to use initials as a key.

--
Rick B



"colm o'brien" <(E-Mail Removed)> wrote in message
news:9BDABC05-8C5D-45F6-9503-(E-Mail Removed)...
> Hi
>
> I have a table called clients these are clients of an insurance brokerage,
> the brokerage has no control over the format of the reference applied to
each
> client. these take the form of the first two letters of surname followed
by
> first two letters of forename followed by 2 digits.
>
> the first time any combination is used the digits would be 01 the next 02
> and so on.
>
> so john doe would be dojo01, joanne doe would be dojo02 etc.
>
> these are stored in table clients with fields F_name, S_name, C_ref.
>
> i want a form which when data is entered will check all the existing
records
> to see if combination exists and create c_ref automatically
>
> thanks
>
>

Since editing, or changes may occur to the name, then you would have to
"test/run" the code for both the firstname field, and the lastname field.

So, in the after update event of the F_Name, you could put:

Call MakeCref


And, in the after update event of the S_Name field you put:

Call MakeRef


Then, in the forms module code your code would be:

Public Sub MakeRef()

Dim strWhere As String
Dim lngNextNum As Long

Dim s_name2 As String
Dim F_name2 As String

If Len(Nz(Me!F_Name, "")) < 2 Then
' blank First name...don't do anything
Exit Sub
End If

If Len(Nz(Me!S_Name, "")) < 2 Then
' blank surname... don't do anything
Exit Sub
End If

s_name2 = Left(Me!S_Name, 2)
F_name2 = Left(Me!F_Name, 2)


strWhere = "(S_name like '" & s_name2 & "*')" & _
" and (F_name like '" & F_name2 & "*')"

If IsNull(Me!ID) = False Then
' this not a new reocrd, so we must exclude this reocrd
strWhere = strWhere & " and (id <> " & Me!ID & ")"
End If

lngNextNum = DCount("*", "tblCustomers", strWhere) + 1

Me.C_ref = s_name2 & F_name2 & Format(lngNextNum, "00")


End Sub

The above code is air code, and not tested..but it should be quite close to
what you need. Make sure there is a index on firstname, and surename fields
for performance...


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)
http://www.members.shaw.ca/AlbertKallal

This is a common problem and question. The first step is to do a Dlookup to
see what the current number is:

DLookup('Process ITMS
'Get a List of ITMS
strsql = "SELECT CISAttributeTable.ITM " _
& "FROM CISAttributeTable_ME " _
& "GROUP BY ITM;"
Set rstItms = qdf.OpenRecordset(strsql, dbOpenSnapshot, dbReadOnly)
If rstItms.RecordCount > 0 Then
rstItms.MoveLast
rstItms.MoveFirst
lngItmCount = rstItms.RecordCount
If Me.opgRecurring = 1 Then
Call Build_RR_Report
Else
Call Build_NR_Report
End If
End If
rstItms.Close

'Process Program Managers
'Get a List of Program Managers
strsql = "SELECT CISAttributeTable.[Program Manager] " _
& "FROM CISAttributeTable_ME " _
& "GROUP BY [Program Manager];"
Set rstItms = qdf.OpenRecordset(strsql, dbOpenSnapshot, dbReadOnly)
If rstItms.RecordCount > 0 Then
rstItms.MoveLast
rstItms.MoveFirst
lngItmCount = rstItms.RecordCount
If Me.opgRecurring = 1 Then
Call Build_RR_Report
Else
Call Build_NR_Report
End If
End If


"colm o'brien" wrote:

> Hi
>
> I have a table called clients these are clients of an insurance brokerage,
> the brokerage has no control over the format of the reference applied to each
> client. these take the form of the first two letters of surname followed by
> first two letters of forename followed by 2 digits.
>
> the first time any combination is used the digits would be 01 the next 02
> and so on.
>
> so john doe would be dojo01, joanne doe would be dojo02 etc.
>
> these are stored in table clients with fields F_name, S_name, C_ref.
>
> i want a form which when data is entered will check all the existing records
> to see if combination exists and create c_ref automatically
>
> thanks
>
>

Albert thanks for your input
when i save this code in the forms module and add the call makeref i get an
error message
can't find the macro makeref

why am i getting that
colm

"Albert D.Kallal" wrote:

> Since editing, or changes may occur to the name, then you would have to
> "test/run" the code for both the firstname field, and the lastname field.
>
> So, in the after update event of the F_Name, you could put:
>
> Call MakeCref
>
>
> And, in the after update event of the S_Name field you put:
>
> Call MakeRef
>
>
> Then, in the forms module code your code would be:
>
> Public Sub MakeRef()
>
> Dim strWhere As String
> Dim lngNextNum As Long
>
> Dim s_name2 As String
> Dim F_name2 As String
>
> If Len(Nz(Me!F_Name, "")) < 2 Then
> ' blank First name...don't do anything
> Exit Sub
> End If
>
> If Len(Nz(Me!S_Name, "")) < 2 Then
> ' blank surname... don't do anything
> Exit Sub
> End If
>
> s_name2 = Left(Me!S_Name, 2)
> F_name2 = Left(Me!F_Name, 2)
>
>
> strWhere = "(S_name like '" & s_name2 & "*')" & _
> " and (F_name like '" & F_name2 & "*')"
>
> If IsNull(Me!ID) = False Then
> ' this not a new reocrd, so we must exclude this reocrd
> strWhere = strWhere & " and (id <> " & Me!ID & ")"
> End If
>
> lngNextNum = DCount("*", "tblCustomers", strWhere) + 1
>
> Me.C_ref = s_name2 & F_name2 & Format(lngNextNum, "00")
>
>
> End Sub
>
> The above code is air code, and not tested..but it should be quite close to
> what you need. Make sure there is a index on firstname, and surename fields
> for performance...
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)
> http://www.members.shaw.ca/AlbertKallal
>
>
>

thanks for your input but this seems very complicated i am only a beginner

"Klatuu" wrote:

> This is a common problem and question. The first step is to do a Dlookup to
> see what the current number is:
>
> DLookup('Process ITMS
> 'Get a List of ITMS
> strsql = "SELECT CISAttributeTable.ITM " _
> & "FROM CISAttributeTable_ME " _
> & "GROUP BY ITM;"
> Set rstItms = qdf.OpenRecordset(strsql, dbOpenSnapshot, dbReadOnly)
> If rstItms.RecordCount > 0 Then
> rstItms.MoveLast
> rstItms.MoveFirst
> lngItmCount = rstItms.RecordCount
> If Me.opgRecurring = 1 Then
> Call Build_RR_Report
> Else
> Call Build_NR_Report
> End If
> End If
> rstItms.Close
>
> 'Process Program Managers
> 'Get a List of Program Managers
> strsql = "SELECT CISAttributeTable.[Program Manager] " _
> & "FROM CISAttributeTable_ME " _
> & "GROUP BY [Program Manager];"
> Set rstItms = qdf.OpenRecordset(strsql, dbOpenSnapshot, dbReadOnly)
> If rstItms.RecordCount > 0 Then
> rstItms.MoveLast
> rstItms.MoveFirst
> lngItmCount = rstItms.RecordCount
> If Me.opgRecurring = 1 Then
> Call Build_RR_Report
> Else
> Call Build_NR_Report
> End If
> End If
>
>
> "colm o'brien" wrote:
>
> > Hi
> >
> > I have a table called clients these are clients of an insurance brokerage,
> > the brokerage has no control over the format of the reference applied to each
> > client. these take the form of the first two letters of surname followed by
> > first two letters of forename followed by 2 digits.
> >
> > the first time any combination is used the digits would be 01 the next 02
> > and so on.
> >
> > so john doe would be dojo01, joanne doe would be dojo02 etc.
> >
> > these are stored in table clients with fields F_name, S_name, C_ref.
> >
> > i want a form which when data is entered will check all the existing records
> > to see if combination exists and create c_ref automatically
> >
> > thanks
> >
> >

"colm o'brien" <(E-Mail Removed)> wrote in message
news:8C2A8508-3CB0-400F-8843-(E-Mail Removed)...
> Albert thanks for your input
> when i save this code in the forms module and add the call makeref i get
> an
> error message
> can't find the macro makeref

You are not using macros, you are using code here. So, remove that you put
in for those two fields..and start over.

You select the after update event in the properties sheet. you then click on
the [...] button. You get 3 options,

Expression Builder
Macro Builder
Code Builder

You need to use the 3rd one. Then you select that, you are put into the
code editor, and that is where you put:

Call MakeRef


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)
http://www.members.shaw.ca/AlbertKallal

that has stopped the error but the c_ref field is not generating

where did i go wrong

Also it gives a runtime error 3078 cannot find table or query tblsellers
but i have a table called sellers


"Albert D.Kallal" wrote:

> "colm o'brien" <(E-Mail Removed)> wrote in message
> news:8C2A8508-3CB0-400F-8843-(E-Mail Removed)...
> > Albert thanks for your input
> > when i save this code in the forms module and add the call makeref i get
> > an
> > error message
> > can't find the macro makeref
>
> You are not using macros, you are using code here. So, remove that you put
> in for those two fields..and start over.
>
> You select the after update event in the properties sheet. you then click on
> the [...] button. You get 3 options,
>
> Expression Builder
> Macro Builder
> Code Builder
>
> You need to use the 3rd one. Then you select that, you are put into the
> code editor, and that is where you put:
>
> Call MakeRef
>
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)
> http://www.members.shaw.ca/AlbertKallal
>
>
>

"colm o'brien" <(E-Mail Removed)> wrote in message
news:224A8688-CA78-4848-AE2D-(E-Mail Removed)...
> that has stopped the error but the c_ref field is not generating

Good, we are making progress

>
> where did i go wrong
>
> Also it gives a runtime error 3078 cannot find table or query tblsellers
> but i have a table called sellers

It is kind of diffcilet for you to try and run code with no understaning of
this. But, lets try some more....


lngNextNum = DCount("*", "tblCustomers", strWhere) + 1

In the above, you have to change tblCustomers" to whatever your table name
is. Note that you must use the EXACT name of your table. So, if you have
something called sellers, then above would be:

lngNextNum = DCount("*", "sellers", strWhere) + 1

If you table name is zoo, then you use;

lngNextNum = DCount("*", "zoo", strWhere) + 1

If you table name is tblzoo, then you use;

lngNextNum = DCount("*", "tblzoo", strWhere) + 1

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)
http://www.members.shaw.ca/AlbertKallal

fantastic albert now i have a new query?

how do i take account of ' in a name as in o'brienwhere instead of 2nd
character being' i want to use b

colm

"Albert D.Kallal" wrote:

> "colm o'brien" <(E-Mail Removed)> wrote in message
> news:224A8688-CA78-4848-AE2D-(E-Mail Removed)...
> > that has stopped the error but the c_ref field is not generating
>
> Good, we are making progress
>
> >
> > where did i go wrong
> >
> > Also it gives a runtime error 3078 cannot find table or query tblsellers
> > but i have a table called sellers
>
> It is kind of diffcilet for you to try and run code with no understaning of
> this. But, lets try some more....
>
>
> lngNextNum = DCount("*", "tblCustomers", strWhere) + 1
>
> In the above, you have to change tblCustomers" to whatever your table name
> is. Note that you must use the EXACT name of your table. So, if you have
> something called sellers, then above would be:
>
> lngNextNum = DCount("*", "sellers", strWhere) + 1
>
> If you table name is zoo, then you use;
>
> lngNextNum = DCount("*", "zoo", strWhere) + 1
>
> If you table name is tblzoo, then you use;
>
> lngNextNum = DCount("*", "tblzoo", strWhere) + 1
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> (E-Mail Removed)
> http://www.members.shaw.ca/AlbertKallal
>
>
>