3 TYPE OF AUTONUMBERS ON THE SAME TABLE 3

[thumper808]

I have a form with 3 radio buttons parttime, fulltime,casual
I also have a database with 3 type of numbers
I also have an add button on a form. I would like if the user check part time an new record to be added in this format pt01000 , if fulltime ft01000 if casual 010001
the first two digits are the last digit of the current year. Is this possible to do in acces. Can somenone give some guidance as to how to star.

ps. Can anyone suggest also a good vb with access book. It almos seems that I have to know vb to acomplish the above task. Any suggestions you can give me would be
very much appreciated.

 

[PaulF]

It can be done using the DMax() Function and one field to maintain the ID. Attach code in the AfterUpdate Event of the "OptionGroup" that contains the 3 radio buttons. This code will look up the Max value of the ID field based on the first 2 characters, calculate the new ID, and store the results in a TextBox bound to the ID field .

Ex: you name the Option Group fraID
use the Field Name of ID
use the table name of tblData
use the TextBox Name of txtID

Private Sub fraID_AfterUpdate()
Dim strID As String
Dim intIncrement As Integer
Select Case fraID
Case 1 ' Partial Selected
strID = DMax("[ID]", "[tblData]", "Left([ID],2) = 'PT'&quot
intIncrement = Val(Right(Nz(strID, "0&quot, 3) + 1)
txtID = "PT" & Format(date, "yy&quot & Format(intIncrement, "000&quot
Case 2 ' Full Time Selected
strID = DMax("[ID]", "[tblData]", "Left([ID],2) = 'FT'&quot
intIncrement = Val(Right(Nz(strID, "0&quot, 3) + 1)
txtID = "FT" & Format(date, "yy&quot & Format(intIncrement, "000&quot
Case 3 ' Casual Selected
strID = DMax("[ID]", "[tblData]", "Left([ID],2) = '" & Format(date, "yy&quot & "'&quot
intIncrement = Val(Right(Nz(strID, "0&quot, 4) + 1)
txtID = Format(date, "yy&quot & Format(intIncrement, "0000&quot
Case Else 'Shouldn't Be any other Case
'Do Nothing
End Select

PaulF

 

[thumper808]

Hi Paul thank you for your help. this code works great one more thing, how do I add a record on the form with the new id.

 

[PaulF]

I'm not sure exactly how to answer your question. The code above will create the new ID for the record you are adding in your form, and will place it into a TextBox named txtID, which should be bound to your ID field. I assumed that you already had a form that was used to add new data into your table, and you just wanted to know how to get the correct ID number based on the radio button you selected. The code provided uses an OptionGroup with 3 buttons, instead of 3 separate buttons located on the form. So if you currently have a form that is used to add data, you need to ensure that you use an OptionGroup (fraID) instead of 3 separate buttons, and that the TextBox which is bound to your ID field is named txtID. You'd need to replace tblData in the code with the name of your table, and ID with the name of your field with the ID. Then ensure you Click on the AfterUpdate Event for the OptionGroup (fraID) and it shows [Event Procedure] in the box and paste the code into the form's module for that event. Other than that, the form should remain the same, which should save the record when you move off of it and go to add another one. If this doesn't answer your question, please be more specific as to the help you need.

PaulF

 

[thumper808]

Yes Paul you are correct I want to know how will add the next number, the problem is that when I clikc in any of the option group the text id shows the new record, the only problem is that the form does clear it still have the data with previous record. I gues what I am saying is that the form is not blank. Hopping you can help me one more time.

PS DO you know any good vb/access book or websites with samples

 

[PaulF]

What code is attached to the Add button?, are the TextBoxes on the form "Bound" to Fields? or are they Unbound?
If you need to reset fraID so that it doesn't "show" the previous selection when you move to a new record, add this just before the End Sub

fraID = 0

PaulF

 

[thumper808]

Hi Paul here is my code I just copy and paste waht you gave me and rename my variables

I put this code in the add button
Private Sub cmdadd_Click()
Dim strID As String
Dim intIncrement As Integer
if me!fraid = 1
strID = DMax("[ID]", "[tblData]", "Left([ID],2) = 'PT'&quot
intIncrement = Val(Right(Nz(strID, "0&quot, 3) + 1)
txtID = "PT" & Format(date, "yy&quot & Format(intIncrement, "000&quot
if me!fraid = 2
strID = DMax("[ID]", "[tblData]", "Left([ID],2) = 'FT'&quot
intIncrement = Val(Right(Nz(strID, "0&quot, 3) + 1)
txtID = "FT" & Format(date, "yy&quot & Format(intIncrement, "000&quot
else
strID = DMax("[ID]", "[tblData]", "Left([ID],2) = '" & Format(date, "yy&quot
& "'&quot
intIncrement = Val(Right(Nz(strID, "0&quot, 4) + 1)
txtID = Format(date, "yy&quot & Format(intIncrement, "0000&quot
endif
endif


this is the code I have under the after upate on the options


if me!fraid = 1
cmdadd.caption = "partime
if me!fraid = 2
cmdadd.caption = "fulltime"
else
cmdadd.caption = "Casual"
endif

endif


my main form and subforms are bound to a table. The code above gives me the next number but it does not create a blank form to add a new record. If I try to type over and when I finish entering data on the main form and tried to move to subform I get the following error message record can not be deleted or chage, empdetail includes related records. What I am doing wrong

 

[PaulF]

couple of things need changing here. First off, you can't use "If" unless you use "Then", so

If fraID = 1
becomes
If fraID = 1 Then

Also you need an "End IF" unless you are using an "ElseIf" or you include the step to perform on the same line as the "If"

If fraID = 1 Then cmdadd.caption = "partime"

using an ElseIf

If fraID = 1 Then
cmdadd.caption = "PartTime"
ElseIf fraID = 2 Then
cmdadd.caption = "FullTime
Else
cmdadd.caption = "Casual"
End If

Next, when do you save the record, and go to a New Record?

you may need to have a
DoCmd.RunCommand acCmdSaveRecord
and a
DoCmd.GoToRecord , , acNewRec

at which time you might want to reset the caption for the Command Button , and set fraID back to nothing or 0

Me!fraID = 0
cmdadd.caption = "ADD"

PaulF

 

[thumper808]

THANKS Paul I think I finally got it. I just have one syntax error

strID = DMax("[ID]", "[tblData]", "Left([ID],2) = 'PT' & Format(Date, "yy&quot &" &quot
I am trynig to add to also add the first two digits of the year to pt but I am getting an error.

 

[PaulF]

Change this
strID = DMax("[ID]", "[tblData]", "Left([ID],2) = 'PT' & Format(Date, "yy&quot &" &quot

To (where the last set of quotes is double quote, single quote and double quote)

strID = DMax("[ID]", "[tblData]", "Left([ID],4) = 'PT" & Format(Date, "yy&quot & "'&quot
or
strID = DMax("[ID]", "[tblData]", "Left([ID],4) = 'FT" & Format(Date, "yy&quot & "'&quot
or
strID = DMax("[ID]", "[tblData]", "Left([ID],2) = '" & Format(Date, "yy&quot & "'&quot

(Notice the Single Quote before PT and FT and also before the double quote after = )
PaulF

 

[thumper808]

thank you so very much Paul