Need help in VBA Codes

Thanks Millions Bruce your function works just fine: Now i have the
following function
If Me.NewRecord Then
Dim strSysNumber As String
Dim varResult As Variant
strSysNumber = "FINDG_NO Like """ & Me.SYS_CODE &
Format(Me.TEST_BEGIN_DATE, "mm/dd/yyyy") _
& Me.AT & "*"""
varResult = DMax("FINDG_NO", "dbo_FINDG", strSysNumber)
If IsNull(varResult) Then
Me.FINDG_NO = Me.SYS_CODE & Format(Me.TEST_BEGIN_DATE,
"mm/dd/yyyy") & Me.AT & "001"
Else
Me.FINDG_NO = Me.SYS_CODE &
Format(Me.TEST_BEGIN_DATE, "mm/dd/yyyy") & Me.AT & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If

/// One question still remains, i have two check boxes AT, and AR. IF
the user select AT it works just fine. what if the user select AR, do i
need an if statment for that? what do i add to your function to make it
work. User can select either AT or AR. if the user select AT, i want to
concetenate AT with the result, if the user select AR, i want to
contcetenate AR with the final result.

Thanks Millions Trillions again for your great help

Moe

It would be a help if you included the rest of the thread in the reply.
Otherwise I need to flip back and forth to see what was written previously.

You don't need to add Value after referring to a control or field. It is
assumed. tblIncrement is the name of a table I used in some previous
experimenting I did, and I forgot to change the name. NextNumber is just
the name of the field in which the string PGL01/01/2006AT001, etc. is
stored. Use your existing field and table names.

Rather than copying to a text box Me.AT and referring to that, use VBA to
define a string value based on the check boxes. You could do something like
this (watch out for line wrapping):

Public Sub AssignNumber()

If Me.NewRecord Then
Dim strSysNumber, strATAR As String
Dim varResult As Variant

strSysNumber = "YourField Like """ & Me.SYS_CODE & _
Format(Date, "mm/dd/yyyy") & Me.AT & "*"""

If Me.chkAT = True then
strATAR = "AR"
Else
strATAR = "AT"
End If

varResult = DMax("YourField", "tblYourTableName", strSysNumber)

If IsNull(varResult) Then
Me.NextNumber = Me.SYS_CODE & Format(Date, "mm/dd/yyyy") & _
strATAR & "001"
Else
Me.NextNumber = Me.SYS_CODE & _
Format(Date, "mm/dd/yyyy") & _
strATAR & Format(Val(Right(varResult, 3)) + 1,
"000")
End If
End If

End Sub

I have called your AT checkbox chkAT. You should have code like the
following in the chkAT After Update event to assure than only one box is
checked:

If Me.chkAT = -1 Then
Me.chkAR = 0
Else
Me.chkAR = -1
End If

Reverse chkAT and chkAR for the After Update code for chkAR. You can use
either -1 and 0 or True and False.

You could also use an If statement to refer to the values of the text boxes
containing AT or AR, but that seems like an unnecessary extra step.

I don't know what SYS_CODE is, or what different values it may contain.
However, the code
I provided should produce a number ending with 001 any time the preceding
part of the string is unique. If the part coming before the last three
characters (the number) already exists, then 1 will be added to the last
three digits.

You do not need another table. This can all happen in the same table in
which your data is stored. It would help if I knew what you are attempting
to do with the database. However, if you are getting it to work, then I
guess it's OK as it is.

Any questions, post back.

"FA" wrote in message
ups.com...
Thanks Millions Bruce your function works just fine: Now i have the
following function
If Me.NewRecord Then
Dim strSysNumber As String
Dim varResult As Variant
strSysNumber = "FINDG_NO Like """ & Me.SYS_CODE &
Format(Me.TEST_BEGIN_DATE, "mm/dd/yyyy") _
& Me.AT & "*"""
varResult = DMax("FINDG_NO", "dbo_FINDG", strSysNumber)
If IsNull(varResult) Then
Me.FINDG_NO = Me.SYS_CODE & Format(Me.TEST_BEGIN_DATE,
"mm/dd/yyyy") & Me.AT & "001"
Else
Me.FINDG_NO = Me.SYS_CODE &
Format(Me.TEST_BEGIN_DATE, "mm/dd/yyyy") & Me.AT & _
Format(Val(Right(varResult, 3)) + 1, "000")
End If
End If

/// One question still remains, i have two check boxes AT, and AR. IF
the user select AT it works just fine. what if the user select AR, do i
need an if statment for that? what do i add to your function to make it
work. User can select either AT or AR. if the user select AT, i want to
concetenate AT with the result, if the user select AR, i want to
contcetenate AR with the final result.

Thanks Millions Trillions again for your great help

Moe

Yes it worked just fine. Thanks again Bruce, this was a great experiece
for me and i hope u have forgiven me for my mistake.
I have one more issue that i am currently working on and may be you
have done something like this in the past or may be you how to handle
such situation.
I have a form called frmSystem which has two textboxes
txtSYS_NME, and txtSYS_CODE
txtSYS_NME holds a string. sample data for it may be "People Soft
Managment Tool"
If thats the data in the txtSYS_NME i want to autopopulate the
txtSYS_CODE taking first substring of each word so for instance if the
txtSYS_NME.Value = " People Soft Managment Tool" Then i want to
autopopulate the txtSYS_CODE with PSMT001 for the first time. There are
many cases where i have same system name thats why i want to
distinguish the sys_code for each system name. if the same system name
comes in the database again it SYS_CODE should be PMTS002, and so on.

Do you have any idea how would i go on achieving this?
Your reply would be greatly apreciated.

Thanks
Moe

Parsing text strings like that is rather beyond my experience. In general I
can tell you that you will probably need to use the LEFT and MID functions.
Help can provide more information. A Google groups search for Access (or
"microsoft.public.access") "first letter" "each word" or some such may turn
up some discussions on the topic.
You could be in for some difficulties unless data entry is very precise and
consistent. If one person writes People Soft and another writes PeopleSoft
you would end up with two different codes. A way to avoid that would be to
use a combo box instead of a text box for SYS_NME. Use the three dots next
to Row Source to set its row source to the SYS_NME field (sorted Ascending,
I would think). In the row source query, click View Properties to set
Unique Values to Yes. Back to the combo box properties, set Limit to List
to No. You will probably need to requery the form (Me.Requery, maybe in the
Not In List event) before the new entry will show up in the list.
As for appending 001, 002, DMax could handle that, but are you sure that's
the route you want to take? If you are entering the 57th record for People
Soft Management Tool, but the first one today, in the concatenated field we
discussed originally you will end up with (if AR is the selection):
PSMT05701/13/2006AR001. If you enter another record today you will end up
with:
PSMT05801/13/2006AR002. If you then choose AT today you will have:
PSMT05901/13/2006AT001. Tomorrow it will be (with AR selected):
PSMT06001/14/2006AR001
If in the life of the database you ever enter more than 999 records for PSMT
then you will need different code for the number. If that's what you need
to do, post back and we can figure out something specific.

"FA" wrote in message
oups.com...
Yes it worked just fine. Thanks again Bruce, this was a great experiece
for me and i hope u have forgiven me for my mistake.
I have one more issue that i am currently working on and may be you
have done something like this in the past or may be you how to handle
such situation.
I have a form called frmSystem which has two textboxes
txtSYS_NME, and txtSYS_CODE
txtSYS_NME holds a string. sample data for it may be "People Soft
Managment Tool"
If thats the data in the txtSYS_NME i want to autopopulate the
txtSYS_CODE taking first substring of each word so for instance if the
txtSYS_NME.Value = " People Soft Managment Tool" Then i want to
autopopulate the txtSYS_CODE with PSMT001 for the first time. There are
many cases where i have same system name thats why i want to
distinguish the sys_code for each system name. if the same system name
comes in the database again it SYS_CODE should be PMTS002, and so on.

Do you have any idea how would i go on achieving this?
Your reply would be greatly apreciated.

Thanks
Moe

On the other point of newsgroup etiquette, asking a question in one group,
then the same question in another group, and carrying on both conversations
independently, is rather like the person in the office who comes in to ask a
question, then you hear that person next door asking the same question of
somebody else.
Also, including the rest of the thread, or at least the relevant portions,
in your reply can be a big help to the person responding and to others
following the thread. Otherwise it can mean flipping back and forth between
several messages. I don't necessarily remember all the details of what I
wrote. This is a volunteer group, so as a person asking for help it would
be best to make things as easy as you can.

"FA" wrote in message
oups.com...
Yes it worked just fine. Thanks again Bruce, this was a great experiece
for me and i hope u have forgiven me for my mistake.
I have one more issue that i am currently working on and may be you
have done something like this in the past or may be you how to handle
such situation.
I have a form called frmSystem which has two textboxes
txtSYS_NME, and txtSYS_CODE
txtSYS_NME holds a string. sample data for it may be "People Soft
Managment Tool"
If thats the data in the txtSYS_NME i want to autopopulate the
txtSYS_CODE taking first substring of each word so for instance if the
txtSYS_NME.Value = " People Soft Managment Tool" Then i want to
autopopulate the txtSYS_CODE with PSMT001 for the first time. There are
many cases where i have same system name thats why i want to
distinguish the sys_code for each system name. if the same system name
comes in the database again it SYS_CODE should be PMTS002, and so on.

Do you have any idea how would i go on achieving this?
Your reply would be greatly apreciated.

Thanks
Moe

Hi Bruce, I have got the code for getting the abrevation
Public Function GetAbbrevs(sText As String) As String
Dim sTmp As String
sText = Trim(sText)
Do While InStr(1, sText, " ") 0
sTmp = sTmp & Left(sText, 1)
sText = Mid(sText, InStr(1, sText, " ") + 1)
Loop
GetAbbrevs = sTmp & Left(sText, 1)
End Function
I modified this code so that i get my result
Private Sub Command566_Click()
If Me.NewRecord Then
Dim strSysNumber
Dim varResult As Variant
Dim sTmp As String
'Dim Me.SYS_NME.value As String

Me.SYS_NME.Value = Trim(Me.SYS_NME.Value)


Do While InStr(1, Me.SYS_NME.Value, " ") 0
sTmp = sTmp & Left(Me.SYS_NME.Value, 1)
Me.SYS_NME.Value = Mid(Me.SYS_NME.Value, InStr(1,
Me.SYS_NME.Value, " ") + 1)
Loop

strSysNumber = "SYS_CODE Like """ & Me.SYS_CODE & "*"""

varResult = DMax("SYS_CODE", "dbo_SYS_INFO", strSysNumber)

If IsNull(varResult) Then
Me.SYS_CODE = sTmp & Left(Me.SYS_NME.Value, 1)
Else
Me.SYS_CODE = sTmp & Left(Me.SYS_NME.Value, 1)
& Format(Val(Right(varResult, 3)) + 1, "00")
End If
End If
End Sub

Its not working, its not even giving me any compile error, so that has
to be a logical error. i dont know where i am mistaken. Can see if you
can find it.
Thanks
Moe

Hi Bruce i have made some more changes to the code and i was certain
that it would work but its only giving me the Abrevation with V01
Private Sub Command566_Click()
Me.SYS_CODE = GetAbbrevs(Me.SYS_NME) _
& "V" & "01"
If Me.NewRecord Then
Dim strSysNumber
Dim varResult As Variant

strSysNumber = "SYS_CODE Like """ & Me.SYS_CODE & "*"""

varResult = DMax("SYS_CODE", "dbo_SYS_INFO", strSysNumber)

If IsNull(varResult) Then
Me.SYS_CODE = Me.SYS_CODE
Else
Me.SYS_CODE = Me.SYS_CODE _
& Format(Val(Right(varResult, 2)) + 1, "00")
End If
End If
End Sub
Where GetAbbrevs
Public Function GetAbbrevs(sText As String) As String
Dim sTmp As String
sText = Trim(sText)
Do While InStr(1, sText, " ") 0
sTmp = sTmp & Left(sText, 1)
sText = Mid(sText, InStr(1, sText, " ") + 1)
Loop
GetAbbrevs = sTmp & Left(sText, 1)
End Function
I entered a duplicate System name and click the command button to see
if it checks the table and add 1 but it didnt do it, it gaveme the same
result as the first time.
I dont know what i am doing wrong.

I take it you did a Google search and found the code below from a Graham R
Seach posting. If so, you may have noted the following:
"If you need to call this from a query, use the following syntax:
SELECT field1, field2, GetAbbrevs(field3) As somename FROM tblMyTable"

The difficulty you are having, I think, is that you are running the code,
but not doing anything with the result. The point of the SELECT statement
(in the quoted text I added), I think, is to run the function in such a way
that the result is a query field which can be used as a control source or as
part of a calculation or anything else for which a record source field can
be used. In other words, if you add all of the fields in your table to a
query, then switch to SQL view and add GetAbbrevs, except substitute
SYS_NAME for field3 in the quote I added above, then you should have a query
with a field (GetAbbrevs) that contains the first letter of each word. You
can then refer to GetAbbrevs in code. I really am not familiar with how
this works, though.

Another thing you could probably do is to create GetAbbrevs as a sub rather
than as a function:

Public Sub GetAbbrevs()

Then add the rest of the code in Get Abbrevs, except use End Sub at the end.
One way to text this is to make a new command button. In its click event,
call GetAbbrevs, then add the line:
msgbox GetAbbrevs

If it worked, you will see the abbreviation in the message box. Then you
can go ahead with your code. One thing that I would suggest with your code
(assuming Get Abbrevs works) is that you define a string:
strSysCode = GetAbbrevs & "V01"
rather than a field. Once the code is complete you can write the result to
the field.

I cannot stress enough that I am doing a lot of guessing here. This is
actually rather interesting, and I would like to look at it some more, but
that won't happen before Monday. Good luck.

"FA" wrote in message
oups.com...
Hi Bruce i have made some more changes to the code and i was certain
that it would work but its only giving me the Abrevation with V01
Private Sub Command566_Click()
Me.SYS_CODE = GetAbbrevs(Me.SYS_NME) _
& "V" & "01"

If Me.NewRecord Then
Dim strSysNumber
Dim varResult As Variant

strSysNumber = "SYS_CODE Like """ & Me.SYS_CODE & "*"""

varResult = DMax("SYS_CODE", "dbo_SYS_INFO", strSysNumber)

If IsNull(varResult) Then
Me.SYS_CODE = Me.SYS_CODE
Else
Me.SYS_CODE = Me.SYS_CODE _
& Format(Val(Right(varResult, 2)) + 1, "00")
End If
End If
End Sub
Where GetAbbrevs
Public Function GetAbbrevs(sText As String) As String
Dim sTmp As String
sText = Trim(sText)
Do While InStr(1, sText, " ") 0
sTmp = sTmp & Left(sText, 1)
sText = Mid(sText, InStr(1, sText, " ") + 1)
Loop
GetAbbrevs = sTmp & Left(sText, 1)
End Function
I entered a duplicate System name and click the command button to see
if it checks the table and add 1 but it didnt do it, it gaveme the same
result as the first time.
I dont know what i am doing wrong.

Thanks Bruce i follow what you are suggesting. Here is what i have.
frmSystem ---- Control Source ---- dbo_SYS_INFO
on that form i have
text box --- SYS_NME ---- Control Source SYS_NME
text box --- SYS_CODE---Control Source SYS_CODE
cmmand button --- Command566 (In the Onclick event of the command
button i have the following code)

Private Sub Command566_Click()
Dim strCode As String
Dim strSysNumber
Dim varResult As Variant

If Me.NewRecord Then
strCode = GetAbbrevs(Me.SYS_NME) & "V"

strSysNumber = "SYS_CODE Like """ & strCode & "*"""
varResult = DMax("SYS_CODE", "dbo_SYS_INFO", strSysNumber)
If IsNull(varResult) Then
Me.SYS_CODE = strCode & "01"
Else
Me.SYS_CODE = strCode &
Format(Val(Right(varResult, 3)) + 1, "00")
End If
End If
I created one more variable strCode that is holding the value of
GetAbvrevs temporarily. Now strSysNumber should look the value of
strCode and the DMax function should find the highest value.
I think it should work but nothing is heppaning when i click the
command button.
So let me know what should i do?
Thanks Bruce

Moe



End Sub

I tried one more thing Bruce,
I made a unbound textbox called CODE which will not be vissible to the
user. In the On Current Event of the form i put
Me.CODE = GetAbbrevs(Me.SYS_NME) & "V"

Now in the OnClick event of the command button
i have something like this
Private Sub Command566_Click()


If Me.NewRecord Then
Dim strSysNumber, strATAR As String
Dim varResult As Variant

strSysNumber = "SYS_CODE Like """ & Me.CODE & "*"""

varResult = DMax("SYS_CODE", "dbo_SYS_INFO",
strSysNumber)

If IsNull(varResult) Then
Me.SYS_CODE = Me.CODE & "01"
Else
Me.SYS_CODE = Me.CODE &
Format(Val(Right(varResult, 2)) + 1, "00")
End If
End If
I was very sure that it would work because thats exactly what i am
doing in the Finding Number but nothing happen. Now i have a textbox
which has the CODE, why cant the function look the value in the txtCODE
and matches with the SYS_CODE in the table and pick the highest one and
add 1 to it.

I would really like this function to be working before monday because i
have a presentation on tuesday and i dont know if i can make it work.

Your reply would be highly appreciated.
Thanks
Moe
End Sub