Need Help in VBA Code

[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

 

[BruceM]

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]

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