Fetch last record number then + 1 automatically

[deanvilar]

Gurus ...in desperate need of your help =)

situation is this, i have 1 combo box for FileCode (Alpha) and 1 text box for FileCode (Number). When saving it in the table it becomes FileCodeAlpha & FilecodeNumber e.g.: CAM0001

I wanted to do something like this:
after selecting from combo box the FileCode(Alpha) last number of FileCodeNumber + 1 will automatically display in the textbox for FileCode(Number).

to have a clearer picture, I attached the form of this question, thank you in advance.

 

[Solo712]

Gurus ...in desperate need of your help =)

situation is this, i have 1 combo box for FileCode (Alpha) and 1 text box for FileCode (Number). When saving it in the table it becomes FileCodeAlpha & FilecodeNumber e.g.: CAM0001

I wanted to do something like this:
after selecting from combo box the FileCode(Alpha) last number of FileCodeNumber + 1 will automatically display in the textbox for FileCode(Number).

to have a clearer picture, I attached the form of this question, thank you in advance.

In the After_Update Event for the File Code (Alpha) insert the something like this:

If Me.NewRecord Then
  FileCode  = Nz(DMax("FileCodeNumber", "TableName", "FileCodeNumber   LIKE '" & FileCodeAlpha & "*'"), "") 
  If FileCode = "" Then 
      FileCode = "0001" 
    Else
      FileCode = Right(Cstr(Cint(FileCode)+10001),4)
  End If
End If

Note that I am unsure of your naming of the fields. I assume that "FileCode" is an unbound text field that keeps the numeric string attaching to the alpha prefix. "FileCodeAlpha" would be the unbound combobox holding the prefixes. "FileCodeNumber" is the presumed name of the field in the table that holds both components. Change the names to fit your naming scheme.

Best,
Jiri

 

[deanvilar]

thanks for the reply, yes it is unbound .... i will try this now ... i'll give you a feedback..

 

[deanvilar]

bro i tried, but number is not incrementing =(

this is the actual fields and object names (also as per attached photo):
combo box = cmbFileCode
text box = txtFileCodeNo
table = mainTableArchive
table field = fileCode (values combined from cmbFileCode & txtFileCodeNo)

Private Sub cmbFileCode_AfterUpdate()
If Me.NewRecord Then
Me.txtFileCodeNo = Nz(DMax("fileCode", "mainTableArchive", "fileCode LIKE '" & Me.cmbFileCode & "*'"), "")
If Me.txtFileCodeNo = "" Then
Me.txtFileCodeNo = "0001"
Else
Me.txtFileCodeNo = Right(CStr(CInt(filecode) + 1001), 4)
End If
End If
End Sub

 

[Solo712]

bro i tried, but number is not incrementing =(

this is the actual fields and object names (also as per attached photo):
combo box = cmbFileCode
text box = txtFileCodeNo
table = mainTableArchive
table field = fileCode (values combined from cmbFileCode & txtFileCodeNo)

Private Sub cmbFileCode_AfterUpdate()
If Me.NewRecord Then
Me.txtFileCodeNo = Nz(DMax("fileCode", "mainTableArchive", "fileCode LIKE '" & Me.cmbFileCode & "*'"), "")
If Me.txtFileCodeNo = "" Then
Me.txtFileCodeNo = "0001"
Else
Me.txtFileCodeNo = Right(CStr(CInt(filecode) + 10001), 4)
End If
End If
End Sub

You've got a zero missing in the highlighed place.

Best,
Jiri

 

[deanvilar]

still not incrementing =( as am reading your codes .. it must already have incremented =(

 

[deanvilar]

still not incrementing =( as am reading your codes .. it must already have incremented

Me.txtFileCodeNo = Right(CStr(CInt(filecode) + 10001), 4)=(

highlighted fileCode is the field in the table right?

 

[deanvilar]

i also tried using txtFileCodeNo as the value is in the textbox ... but error says that TYPE MISMATCH

Me.txtFileCodeNo = Right(CStr(CInt(me.txtFileCodeNo) + 10001), 4)

 

[deanvilar]

i did it!

Me.txtFileCodeNo = CInt(Right(Me.txtFileCodeNo, 4)) + 1
Me.txtFileCodeNo = Format(Me.txtFileCodeNo, "0000")

thank you @Jiri for the help and idea ....

 

[Solo712]

i did it!

Me.txtFileCodeNo = CInt(Right(Me.txtFileCodeNo, 4)) + 1
Me.txtFileCodeNo = Format(Me.txtFileCodeNo, "0000")

thank you @Jiri for the help and idea ....

You are welcome, deanvillar. You got the mismatch because I proceeded on the assumption that you were storing txtFileCodeNo as a string. You figured it out: great !

Best,
Jiri

 

[deanvilar]

yes, I noticed it ... but still without your help bro, I wont figure anything else maybe till a week ha ha ha .. thanks again ....