Need help in VBA Codes

[FA]

I need help in automating sequential numbers
Here is my Function
Public Function FindingNo(S As String, D As Date, A As String) As
String
FindingNo = S & Format$(D, "dd\/mm\/yyyy") & A
End Function

Here is the command button that populating the concetenated value in
textbox FINDG_NO

Private Sub Command_Finding_Click()
If Me.AT.Value = "AT" Then
Me.FINDG_NO = FindingNo(Me.SYS_CODE, Me.TEST_BEGIN_DATE, Me.AT)
End If

If Me.AR.Value = "AR" Then
Me.FINDG_NO = FindingNo(Me.SYS_CODE, Me.TEST_BEGIN_DATE, Me.AR)
End If
End Sub
I want to add a functionality like this,,,
///// When the user click the command button Finding, it also should
checks the value in Me.SYS_CODE and for the first FINDG_NO for
Me.SYS_CODE ADD "001" to Me.FINDG_NO
For example if Me.SYS_CODE is PGL and Me.TEST_BEGIN_DATE is 01/01/2006
and Me.AT is AT then for the first record for Me.SYS_CODE, Me.FINDG_NO
should look like
PGL01/01/2006AT001. If the user comes back to the form with the same
SYS_CODE and click the command button to create the FINDG_NO, it should
be PGL01/01/2006AT002, and so on,,,.
For the above purpose i have created a tem table called tbltemp and
field called AutoSerial (number) to hold the values for each SYS_CODE.
I think i need to write some codes in the Command_Finding_Click Event.
if someone help me out with this i would be greatly appreciated.

Thanks Alot

 

[Guest]

Private Sub Command_Finding_Click()
If Me.AT.Value = "AT" Then
Me.FINDG_NO = FindingNo(Me.SYS_CODE, Me.TEST_BEGIN_DATE, Me.AT)
Me.FINDG_NO = Me.FINDG_NO & Format(Nz(DMax("[SomeField]","SomeTable", _
"Left([SomeField], 15) = '" & Me.FINDG_NO & "'",0)) + 1,"000")
End If

SomeField is the bound field of Me.FINDING_NO

The added code will find the highest number for the sys code and date, add 1
to it and format it with 3 digits. If no records exist for the finding_no
and date, it creates 001

 

[BruceM]

If you must post in several groups, cross-post one message to all of the
groups. Do not multi-post the same individual message to each of the
groups. I answered this question in the formscoding group, and now I see
you have a duplicate posting here.

 

[FA]

Me.FINDG_NO was an unbound field but then i realise i need to store the
values in some temporary table so i created a table called tblTemp with
the field FINDG_NUMBER.
The main form called frmNewFinding is based on table dbo_STD_FINDG,
which has no relationship with SYS_CODE but the subform form is based
on table dbo_FINDG which has a relationship with SYS_CODE. One SYS_CODE
can have many records in table dbo_FINDG.
There is another control text box called FINDG_NO on the subform which
is bound to table dbo_FINDG. Once i create the FINDG_NO on the main
form i am passing it to the subform's control in the textbox called
FINDG_NO
The only reason why i am creating the FINDG_NO on the main form is i am
coming to this form from another form that holds that values of
SYS_CODE and TEST_BEGIN_DATE, so i have botton on that form that opens
the form frmNewFindngs and populate the values of these two field into
the form just for the purpose of creating the FINDG_NO. so i created an
unbound textbox that hold the value of FINDG_NO and after its created,
pass it on to a bound control textbox on the subform called FINDG_NO.
Even i created a table tblTemp and field FINDG_NUMBER in the MS Access
not on server, i put that field name and table name in your code and
wheni compile it, its giving me error, wrong number of argument or
invalid property assignment and DMax function is highlited.

What should i do in order to get it running.

Thanks alot
Moe

 

[FA]

I think so far what i have figured out, that DMax function will only
work on MS Access tables. I have the table on sql server. i dont know
if i am thinking in a right direction.
I made a little changes and instead of creating the FINDG_NO on themain
form i am creating directly on the subform so that Me.FINDG_NO is bound
to field FINDG_NO in table dbo_FINDG. Subform data source is also table
dbo_FINDG. i dont know what to do know?

Moe

 

[BruceM]

You're not getting this. DO NOT POST SEPARATELY IN MULTIPLE GROUPS. You
asked the same question in two different groups. I answered in one, and
Klatuu answered in another. At first I could just figure you are not
familiar with newsgroup etiquette, but now I have told you about
multi-posting, and still you ask each of us follow-up questions. What you
are doing is considered rude at best.

 

[FA]

Also Klatuu i tested your function in an MS Access Database that i
created for testing, that has exactly same tables and same fields just
for testing, i am still getting the same error.

"wrong number of argument or
invalid property assignment" and DMax function is highlited

 

[FA]

OK i appologize for this. I assure you it wont happen next time.

Thanks Bruce

Moe

 

[FA]

OK i am not gona post any message about this problem in any other group
thats why i am copy pasting everything we discussed in the other group
over here so from now on i will check this group for this problem.
Thanks for everything Bruce.

Bruce M Reply:

It seems to me that you want a number that consists of SYS_CODE, the
date,
AT or AR, and a sequential number. If so, some variant of the
following
code should work. NextNumber is the field in which this value is
stored. A
caveat is that this only addresses the value from Me.AT. More comments
and
questions after the code.


Public Sub AssignNumber()


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


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


varResult = DMax("NextNumber", "tblDateIncrement", strSysNumber)


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


End Sub


Is Me.AT ever anything other than "AT"? Similar question for Me.AR.
If
not, what is the point of the field? Could a check box or option
button be
used to select either AT or AR?


It is not clear under what circumstances you would by implementing this

code. I expect that if you are creating a new record in which the
value
"AT" appears, you want this number incremented by one over the previous

number in which AT appears. It seems to me that you need to populate
SYS_CODE and AT, then call this Sub from a command button or an After
Update
event of one of the text boxes or whatever. I think you will want the
If.NewNumber line to prevent the code from rewriting the number after
the
record has been created. Otherwise, if the user clicks the command
button
(if that is how you are calling the Sub) on an existing record by
mistake,
the number will change. You could also prevent this by hiding the
command
button unless it is a new record, or something like that.


Give this a try, then post back with any further questions.

 

[FA]

3. FA
Jan 12, 12:16 pm show options

Newsgroups: microsoft.public.access.formscoding
From: "FA" <[email protected]> - Find messages by this author
Date: 12 Jan 2006 09:16:50 -0800
Local: Thurs, Jan 12 2006 12:16 pm
Subject: Re: Need Help in VBA Code
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Remove | Report Abuse

Me.AT Value will be AT forever and Me.AR value will be AR forever. I
have two check boxes for those two and a user select one check box, it
populate the AT or AR in the txtbox AT or txbox AR. I dont need to have

the txtbox for them but i could not write a code for check box. here is

what i am doing:
Private Sub A_AfterUpdate()
If Me.A.Value = "-1" Then
Me.AT.Value = "AT"
End If
End Sub


Private Sub R_AfterUpdate()
If Me.R.Value = "-1" Then
Me.AR.Value = "AR"
End If
End Sub


Where A and R are check boxes, if someway we can directly pass the AT
or AR from the check box to my concetenating function that would be
fine.


Do you want me to create a table called "tblDateIncrement" and Field
"NextNumber" in the database or its something from the Library. Let me
know.


- incrementing autonumber is based on SYS_CODE not AT or AR. If the
User enters the form with the same SYS_CODE, check the previous value
of FINDG_NO in the tble FINDG for that specific SYS_CODE if exist, add
1 to it else if it doesnt exist still add 1 to it for the first record.



User can either check on AT or AR not both. but the serial number has
to be according to SYS_CODE.


Also Me.FINDG_NO is an ubound textbox on main form but there is one
more FINDG_NO text box on the subform which is bound to table FINDG.
once the value is created on the main form, i am passing this value
into the control FINDG_NO of the subform. The reason why i am creating
this value on main form is i am coming to this form from another form
called frmSystem and linking the values of SYS_CODE, TEST_BEGIN_DATE
from that form to this main form called frmNewFindings.


SYS_CODE is field in tbleSYS and FINDG_NO is a field in tbleFINDG. tble

SYS has one to many relation with tblFINDG and both tables are joined
with SYS_ID_CODE (AutoNumber)


So do you want me to create another table that will hold the SYS_CODE
and FINDG_NO, how would be verify that if a FINDG_NO has been created
previously for that SYS_CODE?


Please let me know, its a hair pulling stage now ....


Thanks


Moe

 

[FA]

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

 

[BruceM]

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]

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

 

[BruceM]

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.

 

[BruceM]

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]

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

 

[FA]

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.

 

[BruceM]

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]

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

 

[FA]

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