Help needed with form (little or no experience)

[colm o'brien]

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

 

[Rick B]

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.

 

[Albert D.Kallal]

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...

 

[Klatuu]

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]

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

 

[colm o'brien]

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

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

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 D.Kallal]

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

 

[colm o'brien]

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 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 address removed)
http://www.members.shaw.ca/AlbertKallal

 

[Albert D.Kallal]

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

 

[colm o'brien]

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