Newbie needs help with form

[colm o'brien]

I want to code a form text box to create a field in table from two others
and a number.

field1 client forename
field2 client surname
field3 client code

when field1 and field 2 are entered i want field3 to be first two letters of
surnamethen first two letters or forename and two digit number.

eg obrien, colm would produce obco01 this will be primary index in table so
no duplicates so i want next obco eg obrien connor to give obco02 so code
should check if this exists and increment digits.

any help appreciated

thanksin advance


colm

 

[Rick B]

This is not proper design. You should not store a calculated field in a
table. What if a lady gets married and changes her name? Or, if she gets
divorced? It is a very bad idea to include initials for a person as part of
a key.

You can create a combined key in your table that will look at a combination
of fields to establish a unique key.

 

[Klatuu]

Ok, here is some untested "air code".

The trick here is putting it in the right place. You may consider making
this code a sub in you form's module and using the code that follows in the
Before Update event of both the text boxes for field 1 and field 2.

Sub MakeClientKey
Dim strClientCode as String
Dim strClientNumber as String

strClientNumber = "01"
strClientCode = Left(Me![client surname],2) & Left(Me![client forename] _
& strClientNumber

Do While True
If IsNull(DLookup([Client_Key], "ClientTable", "[Client_Key] = '" _
& strClientCode & "'") Then
Me![client code] = strClientCode
Exit Do
Else
strClientNumber = Format(CInt(strClientNumber) + 1, "00")
strClientCode=replace(strClientCode,right(strClientCode,2),_
& strClientNumber)
End If
End Do
End Sub

In the events:

Dim blnMakeKey as Boolean
blnMakeKey = IsNull(Me![client surname]) or Me![client surname] = ""
blnMakeKey = IsNull(Me![client forename]) or Me![client forename] = ""
if blnMakeKey Then
Call MakeClientKey
End If

One other thing. I recommend not using spaces in any kind of name. That is
why in the code above, the bracketing is necessary. There are two common
techniques that make the names clear without spaces. One is Upper casing:
ClientForename, The Other is unerlines: Client_Forename. Either is
acceptable. My preference is to use Upper casing for everything except table
field names. That way I know when I look at my code what I am dealing with.
There are some other good naming conventions. I think if you search through
MSDN, you will find a good VB/Acces naming convention document.

Good Luck!

 

[Rick B]

Klatuu:

Please read my post to this. You are setting this poor guy up for failure
down the road. It is not always a good idea to simply answer a question as
asked. You should also think about if it is a good idea or not and how it
affects normalization. Especially when the subject has the word "NEWBIE" in
it.


--
Rick B

Ok, here is some untested "air code".

The trick here is putting it in the right place. You may consider making
this code a sub in you form's module and using the code that follows in the
Before Update event of both the text boxes for field 1 and field 2.

Sub MakeClientKey
Dim strClientCode as String
Dim strClientNumber as String

strClientNumber = "01"
strClientCode = Left(Me![client surname],2) & Left(Me![client forename] _
& strClientNumber

Do While True
If IsNull(DLookup([Client_Key], "ClientTable", "[Client_Key] = '" _
& strClientCode & "'") Then
Me![client code] = strClientCode
Exit Do
Else
strClientNumber = Format(CInt(strClientNumber) + 1, "00")
strClientCode=replace(strClientCode,right(strClientCode,2),_
& strClientNumber)
End If
End Do
End Sub

In the events:

Dim blnMakeKey as Boolean
blnMakeKey = IsNull(Me![client surname]) or Me![client surname] = ""
blnMakeKey = IsNull(Me![client forename]) or Me![client forename] = ""
if blnMakeKey Then
Call MakeClientKey
End If

One other thing. I recommend not using spaces in any kind of name. That is
why in the code above, the bracketing is necessary. There are two common
techniques that make the names clear without spaces. One is Upper casing:
ClientForename, The Other is unerlines: Client_Forename. Either is
acceptable. My preference is to use Upper casing for everything except table
field names. That way I know when I look at my code what I am dealing with.
There are some other good naming conventions. I think if you search through
MSDN, you will find a good VB/Acces naming convention document.

Good Luck!

I want to code a form text box to create a field in table from two others
and a number.

field1 client forename
field2 client surname
field3 client code

when field1 and field 2 are entered i want field3 to be first two letters of
surnamethen first two letters or forename and two digit number.

eg obrien, colm would produce obco01 this will be primary index in table so
no duplicates so i want next obco eg obrien connor to give obco02 so code
should check if this exists and increment digits.

any help appreciated

thanksin advance


colm

 

[Klatuu]

You can create a combined key in your table that will look at a combination
of fields to establish a unique key.

So explain to me how your statement above is any different that what he is
doing? I considered the name change problem, but I decided explaining to a
newbie how to deal with that problem a little too involved. I don't think it
is the best design possible, but then how do you know he has control over the
design? Also, in your statement above, there is no assurance that a
combination of fields will always produce a unique key. The easy answer is
an autonumber field, but then I don't trust autonumbering - it comes with
it's own problems.

Klatuu:

Please read my post to this. You are setting this poor guy up for failure
down the road. It is not always a good idea to simply answer a question as
asked. You should also think about if it is a good idea or not and how it
affects normalization. Especially when the subject has the word "NEWBIE" in
it.


--
Rick B

Ok, here is some untested "air code".

The trick here is putting it in the right place. You may consider making
this code a sub in you form's module and using the code that follows in the
Before Update event of both the text boxes for field 1 and field 2.

Sub MakeClientKey
Dim strClientCode as String
Dim strClientNumber as String

strClientNumber = "01"
strClientCode = Left(Me![client surname],2) & Left(Me![client forename] _
& strClientNumber

Do While True
If IsNull(DLookup([Client_Key], "ClientTable", "[Client_Key] = '" _
& strClientCode & "'") Then
Me![client code] = strClientCode
Exit Do
Else
strClientNumber = Format(CInt(strClientNumber) + 1, "00")
strClientCode=replace(strClientCode,right(strClientCode,2),_
& strClientNumber)
End If
End Do
End Sub

In the events:

Dim blnMakeKey as Boolean
blnMakeKey = IsNull(Me![client surname]) or Me![client surname] = ""
blnMakeKey = IsNull(Me![client forename]) or Me![client forename] = ""
if blnMakeKey Then
Call MakeClientKey
End If

One other thing. I recommend not using spaces in any kind of name. That is
why in the code above, the bracketing is necessary. There are two common
techniques that make the names clear without spaces. One is Upper casing:
ClientForename, The Other is unerlines: Client_Forename. Either is
acceptable. My preference is to use Upper casing for everything except table
field names. That way I know when I look at my code what I am dealing with.
There are some other good naming conventions. I think if you search through
MSDN, you will find a good VB/Acces naming convention document.

Good Luck!

I want to code a form text box to create a field in table from two others
and a number.

field1 client forename
field2 client surname
field3 client code

when field1 and field 2 are entered i want field3 to be first two letters of
surnamethen first two letters or forename and two digit number.

eg obrien, colm would produce obco01 this will be primary index in table so
no duplicates so i want next obco eg obrien connor to give obco02 so code
should check if this exists and increment digits.

any help appreciated

thanksin advance


colm