Need help in VBA Codes

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

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

"FA" wrote:

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

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" wrote in message
oups.com...
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

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

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

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" wrote in message
oups.com...
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

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

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

Thanks Bruce

Moe

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.

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

Newsgroups: microsoft.public.access.formscoding
From: "FA" - Find messages by this author
Date: 12 Jan 2006 09:16:50 -0800
Local: Thurs, Jan 12 2006 12:16 pm
Subject: 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