Help needed with form (little or no experience)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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...
 
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
 
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
 
thanks for your input but this seems very complicated i am only a beginner

Klatuu said:
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 said:
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
Click to expand...
Click to expand...
 
colm o'brien said:
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
Click to expand...

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
 
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 said:
colm o'brien said:
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
Click to expand...

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
Click to expand...
 
colm o'brien said:
that has stopped the error but the c_ref field is not generating
Click to expand...

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
Click to expand...

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
 
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
 
Back
Top