IS THERE AN EASY ALTERNATIVE TO AUTONUMBERING? 2

[BLBurden]

Hello again guys:
Here's my delima. I have a form that I enter records each day. I have the system date set on the form as the day I entered the record. I aslo have an ID field where I need it to show what record I entered. On the next day I need to do the same thing, but I need the ID # to start back for # 1 and count until finish. I usally dont have but 1-10 record to put in daily. See example below

Date: ID #: ( this needs to be autonumber)

2003-06-09 01
02
03 etc.. until finish
Next Day same form

2003-06-10 01
02
03 etc...

If I can get any input in this it will very much appreciated, I have tried and tried again to make it work, if it's not possible will someone out there let me no so I can continue on with other steps. I have tried making a table for everyday of the week and append it to the form each work day, but there has got to be a better way.


Thanks ever more for your help.


BLB
Always Grateful

 

[jmeadows7]

I will make the assumption from your query that the ID# is NOT the primary key on the table. If it is - do you delete the previous day's input?

ASSUMING the id# in your question is NOT the primary key:
Suggestion 1:
If you are the one doing the input - can you just add the id field to the input and type it in yourself?

Suggestion 2: (Requires some coding skill)
Add another field to the table to create the 1 to 10 ID#.
Add a button to allow you to click when input is finished.
In the on-click event of the button - logical procedure - not debugged code follows:
Dim myId as integer
myId = 1
Dim strSQL as string

Select the records from the current date into a record set order by the autonumber field where the date = current date.

rst = OpenRecordset

Do while not(EOF)
rcdId = rst(0)

strSQL = "Update rcd
set dailyinputid = myId"
where id = autonumber = rcdId
DoCmd.RunSQL "strSQL"
myId = myId + 1
rst.MoveNext

end loop

Note: In suggestion 2, I do assume that the autonumber
field can uniquely identify the record.

Again -it's just the logic and some pseudo code and not syntatically correct code.

HTH
Jimmy

 

[fneily]

Or, if you want to be simple, since you're only going to have ten numbers, create a combobox using the wizard. Click on type my own values, and place the numbers 1 to 10 on separate lines. After it's created, go to its property sheet and in the Control Source box place the name of the field you want to save it in. Of course, it doesn't increment automatically, but 10 numbers - give me a break.

Neil

 

[BLBurden]

Thanks Jimmy, I will try this. And to answer your questions No the ID is not the primary key, and No and when I am on vacation, my Boss enters the records. He is the one that want the numbers to reset daily. (don't make sense to me, but I am not the boss) I had already set up a form to let AutoNumber just continue on from 1 - whaterver, and it works great

Again Thanks for your help - I will let you know the oucome.

BLB
Always Grateful

 

[BLBurden]

I have already done that to Neil, but that is not what my boss wants, but I appreciate your input. And 10 was just an most months example. In some months I have had as high as 100 or more records to input. I know I did not say that before, but was trying to keep it short.

BLB
Always Grateful

 

[hymn]

Below is a procedure of mine for adding a sequential number to field varing on the first field

Private Sub Category_AfterUpdate()
Dim Db As DAO.Database
Dim rds As DAO.Recordset


'to set sequential number
Set Db = CurrentDb()
Set rds = Db.OpenRecordset("Select distinctrow Max(tbl_stock.CatNo) as MaxOfcatno from tbl_stock Where((tbl_stock.category='" & Forms![frm_stock]![Category] & "'));", DB_OPEN_DYNASET)
Me![CatNo] = IIf(IsNull(rds![Maxofcatno]), 1, rds![Maxofcatno] + 1)
Me![CatNo].Requery
Me!PartNo = [Category] & [CatNo]
Description.SetFocus

End Sub

The red indicates my fielde Tables and Forms

Some people do notlike Max

Hope this helps
Hymn

 

[BLBurden]

Thanks for all the help - so far I haven't gotten any thing to work for me. But if there is a will I will make a way.

Love always

BLB
Always Grateful

 

[MikeTompkins]

BLBurden - How about something like this? Make a button for your form and put this code in the on click property.



Me.Filter = (([date_entry] = date))
Me.FilterOn = True
DoCmd.GoToRecord , , acNewRec

'intnum is the name of the field I used to hold your daily counter
IntNum = [Form].[RecordsetClone].[RecordCount] + 1

This does leave a filter on the form, but you can always just click the filter button to remove the filter to see other days entries.

HTH - Michael

 

[BLBurden]

Thanks Michael - this is been this is almost working - but I keep getting the runtime error 2465 Microsoft can't find the field you are referring to in your expression.

BLB
Always Grateful

 

[BLBurden]

Here's what I come up with so far. Still need more help. Thanks in advance.

Private Sub Form_BeforeInsert(Cancel As Integer)
Forms![SIPP DATABASE-Form]!ID1 = DMax("[ID1]", "APPEND&quot + 0

How do I increase the number to 1, 2, 3, 4, 5 etc.. for the day I am in and the number of records I am inputing. And when the date change to the next day , I need
to start over with the number of inputs for that day. 1, 2 3, 4, 5 etc. Is
this possible?

BLB
Always Grateful

 

[hymn]

I see your still looking! guessing you didn't try mine if you want a sample db let me know

Hope this helps
Hymn

 

[MichaelRed]

? easy ? Aparently the dialog above isn't, so take a LOOK at faq700-184. I do not think it is "Easy", but then the ones you are looking at here apearat TO easy.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[BLBurden]

I am sorry Hymn I can't seem to get nothing to work. I tried everything and every body code. I finaly got Dmax to work and incrementing my numbers, but I can not master the part about making it start over from #1. Yes if you dont mind, please send me a sample DB.

And to you Michael, I am just a single user, and yes your code is way above my head, I dont need to pretend that I understand all that coding, cause I clearly don't. But I want to just take this time to thank all of you guys for your time in trying to get me straight.

Thanks always and forever.

email is brenda.burden@st.com or bbofcolony@aol.com. Thanks again Hymn

BLB
Always Grateful

 

[genaccess]

If I have understood your question...

If you require only the number of records you entered today, you can build a query with count as the function and Date() (Today's date as parameter). and requery this query on the opening of the form.You can have a text box that displays the number.

GA

 

[scriverb]

Brenda: I think this may be a viable solution for you.

First, let's let your table continue to have an autonumber field and let's let that be the Primary Key. This way the last record in the table will be the key to determining what ID# we assign to the next record.

Second, my example here includes two fields: TransDate(Date/Time - Default: Date() ) and ID_Numb(Number - Long Integer ). Both have they Locked property set to Yes and their Tab Stop set to No. I created a form with the RecNumber(Autonumber), TransDate, and ID_Numb at the top of the form. All of which are just for viewing with no actual input.

Now in the Forms BeforeInsert Event Procedure put the following code:
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("tblYourTableName", dbOpenDynaset)
If Not rs.EOF Then
rs.MoveLast
If rs("TransDate&quot = date Then
Me.ID_Numb = rs("ID_Numb&quot + 1
Else
Me.ID_Numb = 1
End If
Else
Me.ID_Numb = 1
End If
rs.CLOSE
db.CLOSE
End Sub

What happens here is that when you start a new record the cursor will begin in the first enterable control on the form. Upon entering the first character the BeforeInsert event procedure executes. The VBA code above opens the table as a recordset and moves to the last record entered. If the there are no records like in the beginning an empty table it assigns 1 as the ID_Number. If there are records then it compares the last records TransDate value to todays system date. If equal then it adds 1 to the ID_Number of the last record and assigns that to the current new record. If the last records TransDate value is not equal to the current system date then it assigns 1 to the current new records ID_numb control.

I think this should work for you. Just update the code with the table and fields names that are appropriate for your situation. Let me know if you have any questions.


Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[ReluctantDataGuy]

You want simple? Here's simple! All you need to do is replace the table and field names with your own.

Assume you have a table named Table1.

Assume Table1 has 2 fields:

DateToday (date)
RecsToday (Long Integer)


DateToday Default = Date()


Take note of which field gets the focus first when you open the form (or look at View / Tab Order). Whatever the first enabled field is, add the following code to its Enter event.

Me.RecsToday = DMax("[RecsToday]", "Table1", "DateToday = #" & Date & "#&quot + 1

DMax can get slow in a big database (which according to your description, its not) so an alternative to DMAX is:

Dim strSQL as String

strSQL = "Select.... "

DoCmd.RunSQL

 

[scriverb]

ReluctantDataGuy's suggestion won't work as as posted because there is no reset of the transaction number back to 1 when the day changes. But, if you wanted to pursue that avenue the following will work:

Me.ID_Numb = IIf(Me.ID_Numb = 0, Nz(DMax("[ID_Numb]", "tblYourTableName", "[TransDate] = #" & date & "#&quot, 0) + 1, Me.ID_Numb)

The IIF checkes to see if this is a new record. A new record will have a zero(0) value for the ID_Numb as a default for a numeric field. If not zero then it returns its own value. If the DMAx function returns a null because it happens to be a new day the NZ function returns a zero(0) which is then incremented by 1 to start the numbering over. If the DMax returns other than zero(0) then it just increments it by 1 to give the next ID_Numb in sequence for the day.

Now you have two ways of doing this.

Brenda, See y'aalll later.

Bob Scriver

Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[BLBurden]

Bob as always you bailed me out you are a true friend. A million stars for you. And a star for you also relucantdataguy your help was dearly appreciated.


BLB - Always Grateful
A good friend will bail you out of jail. A true friend will be sitting at you side saying, "Boy - didn't we fun?"

 

[Rolliee]

WOW! Many words!!!

Let me put another oar in the water. Inasmuch as you cannot (and do not want to change) an autonumber, why not use a number that is related to the autonumber and new everyday - a diffNum - which sets you to today with little effort!

rollie@bwsys.net

 

[MichaelRed]

but all of the above are a bit risky unless the db is strictly a single user (as far as creating NEW records are concerned). Getting any 'value' from the db w/o locking is prone to 'simultaneous' access / retrievial -resulting in 'duplication'.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places