OnChange,Increase the number

[emmim44]

I had a similiar previous post unfurtunatly, I didnt get it work...my code now doesnt do anything...I am not gettting any error either..

I am trying increment the confirmation number based on the current date for each new request.....Means that the code should get the max number from table then increment for the same day for each some event click or select on the main table...Your help is great...The format yyyymmdd is ok...



Dim newvalue As String, rs As Object, zeroes As String, old As String, L As String
Set rs = Me.RecordsetClone
If Me.NewRecord Then

If rs.RecordCount = 0 Then
confnum = Format(Date, "yyyymmdd") & "01"

Else

L = Nz(DMax("[confnum]", "ClientRequestsTbl"), 0)

If L = 0 Then
confnum = Format(Date, "yyyymmdd") & "01"

Else
If Format(Date, "yyyymmdd") = Format(Left(L, 6), "yyyymmdd") Then
confnum = Format(Date, "yyyymmdd") & "01"
Else
confnum = DMax("[confnum]", "ClientRequestsTbl") + 1
End If
End If

End If

End If
rs.Close
Set rs = Nothing

End Sub

[TonyJollans]

What is "L"?

A numeric string will not be interpreted as ddmmyy or mmddyy or yymmdd or whatever you are hoping for.

[emmim44]

Maybe I should define L as numeric...what else is wrong ? it drives me nut..

[TonyJollans]

I meant what are the contents of L - not what data type is it?

[OTWarrior]

also, there are many variable that you have "dim"ed that aren't in use and vice versa, for example, what type is "confnum"?

L is meant to be the ammount of records in "ClientRequestsTbl" right?

if you want to increment each number, shouldn't this be in a loop? from what I can understand of that code, it would only run once

[emmim44]

The idea is get the max number from db table and increment the max number by one for the same day then insert into db table

The first record of day will be yyyymmdd01...then insert into db table.
If there are no records in db, again the the first record will be 2007112601..vice versa...
The confum is the db field and field name on the subform...ClientRequestsTbl is the table name...

[TonyJollans]

I know the idea - bur what is L?

[emmim44]

L should hold the retrieved value of max confnum from table..

[TonyJollans]

Oops! Sorry. I was misreading the code.
Change ..
If Format(Date, "yyyymmdd") = Format(Left(L, 6), "yyyymmdd") Then
.. to ..

If Format(Date, "yyyymmdd") = Left(L, 8) Then

[emmim44]

oki. That could be one fix...

[DarkSprout]

The following Function will return then Next/or Newest available number

The table stores then number in the format yyyymmdd.nn
TABLE PROPERTIES
Field Size: Double
Format: Fixed
Decimal Places: 2


Public Function IncTableUID() As Double
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT Max([TableName].FieldName) AS MaxOfFieldName FROM TableName HAVING (((Max([TableName].FieldName)) Like '*' & Format(Date(),'yyyymmdd') & '*'));", dbOpenDynaset)
Select Case rs.EOF
Case True
IncTableUID = Format(Date, "yyyymmdd") + 0.01
Case Else
IncTableUID = rs![MaxOFFieldName] + 0.01
End Select
End Function


IncTableUID = 20071129.01

[emmim44]

I am using onChange function on a main form to do what I want,
Your function doesnt update the db table field....I am new to vba...

The senerio is I have a main and sub form, when one picks a customer from the drop-down menu on main form, it will do things I want as I mentioned above...The db has to be updated too..Thank 4 your help

[DarkSprout]

You did not memtion that in your brief.
Use an SQL INSERT Query to place the IncTableUID value into the field.


Public Function IncTableUID() As Double
'// Looks for then last record with the format yyyymmdd.nn in table
'// places value + 0.01 into the table and returns said value
Dim rs As DAO.Recordset
Dim sql As String

Set rs = CurrentDb.OpenRecordset("SELECT Max([TableName].FieldName) AS MaxOfFieldName FROM TableName HAVING (((Max([TableName].FieldName)) Like '*' & Format(Date(),'yyyymmdd') & '*'));", dbOpenDynaset)
Select Case rs.EOF
Case True
IncTableUID = Format(Date, "yyyymmdd") + 0.01
Case Else
IncTableUID = rs![MaxOFFieldName] + 0.01
End Select

sql = sql & "INSERT INTO tbl_TableName (FieldName)
sql = sql & "SELECT '" & IncTableUID & "'; "
DoCmd.RunSQL (sql)
End Function

[emmim44]

Sorry...will the new value show on the subform text box called "confum"?

[DarkSprout]

Sorry...will the new value show on the subform text box called "confum"?

It will now look for the last entry then add 0.01 or return date.01 - add it to the table, then place the value in the text box

[confum] = IncTableUID()

[emmim44]

lets say,the drop-down is called "combo_64" and I want to place your function under event[onChange] of combo_64, How will i do that ?

And what is "CurrentDb " in your function ?
And your function will increase the number for the same day ?
And how about the initial record,lets say there is no data in db at all ?
And defualt value of db field is null

[DarkSprout]

Set rs = CurrentDb.OpenRecordset("SELECT Max([TableName].FieldName) AS MaxOfFieldName FROM TableN......)
CurrentDb: Run SQL In Current Data Base

Private Sub combo_64_AfterUpdate()
Dim nValue as Single
nValue = IncTableUID()
[confum] = nValue

' maybe filter here

' other events...

End Sub


Use (To make the subform Filter to the new record)

Me.Filter = "[FieldName]=" & nValue
Me.FilterOn = True
' - or -
Me![subfrm].Filter = "[FieldName]=" & nValue
Me![subfrm].FilterOn = True

___

IncTableUID will increase for the current day. Yes: by .01 - (for 99 records) or use 0.001 for 999 record per day

And how about the initial record, lets say there is no data in db at all - then it will create the first entry.

And defualt value of db field is null - will place the first yyyymmdd.nn upon request - as above

(Please read the code its self - your questions are answered there)

[emmim44]

Thank you soo much..:D

[emmim44]

One thing, I just checked the data...my data is like this using your function...20071130,20071131,etc... instead it should be 2007113001,2007113002,etc...next day it will be 2007120101,2007120102,etx,,,

[DarkSprout]

My Function Uses yyyymmdd.nn
So the output would be 20071129.01, it does not conflict with the date format itself.
The decimal keeps it clean, simple and apart.

[emmim44]

That is a requirement...come on...

[emmim44]

oki...I got it.....

[emmim44]

opps..I am getting an error here...Line 4...wrong reference...
Dim nValue As Single
nValue = IncTableUID()
'MsgBox nValue
Line 4 [confnum] = nValue

[DarkSprout]

Is the Function IncTableUID() in a public module
that's what it is trying to call, a public object.

[emmim44]

I placed the function as other private sub functions...