How to lock auto number for 24 hours

[hrdpgajjar]

Hi,
We've inward/outward register in excel sheet at my office. I've to convert it to access form.

But the problem is,

1. I've set primary key as auto number
2. Multiple entries done under same number during day.

So how do i restrict or allow multiple users to do entry in a single primary key only for a day (i.e. for 24 hours).

New number will generated next day..


Thanks !!!

 

[theDBguy]

Hi. Not sure I understand what you're asking but it sounds like you're talking about using a child table to store all the entries for the same parent primary key.

 

[arnelgp]

autonumber is not static.
it grows when new record is added.

you need another field (SeqNo) (long) on that table.

also add another table (tblInitDate) with
one Date/Time field (CounterDate).
on CounterDate field type the date
when the serial number will be used

eg: today (09/25/2019)

add SeqNo field to your form.
on Property Sheet->Data->Locked: Yes.

add code to your form's Before Insert Event:

Private Sub BeforeInsert(Cancel As Integer)
    Dim dte As Date
    dte = DLookup("CounterDate", "tblInitDate")
    Me.SeqNo = DateDiff("d", dte, Date()) + 1
End Sub

 

[hrdpgajjar]

Hi. Not sure I understand what you're asking but it sounds like you're talking about using a child table to store all the entries for the same parent primary key.

You get it right. I have to do multiple entries under a same primary key


How can i do it?


Thanks

 

[hrdpgajjar]

autonumber is not static.
it grows when new record is added.

you need another field (SeqNo) (long) on that table.

also add another table (tblInitDate) with
one Date/Time field (CounterDate).
on CounterDate field type the date
when the serial number will be used

eg: today (09/25/2019)

add SeqNo field to your form.
on Property Sheet->Data->Locked: Yes.

add code to your form's Before Insert Event:

Private Sub BeforeInsert(Cancel As Integer)
    Dim dte As Date
    dte = DLookup("CounterDate", "tblInitDate")
    Me.SeqNo = DateDiff("d", dte, Date()) + 1
End Sub

Sir,
I've a table with columns as under

1. ID
2. Applications
3. Other documents


Now many applications are received in a day. I want to enter all applications in one ID only, So that I can mark it as a inward id and find all applications received on that day by entering just a inward ID..


any suggestions ?

 

[arnelgp]

see my last post.
ID must not be autonumber.
and you need another table.

maybe others have better approach.

 

[The_Doc_Man]

hrdpgajjar, you asked a question using nomenclature that shows you do not understand a fine point. A primary key is NEVER EVER repeated even once in a table. One key value = one record, end of story.

To be able to make multiple entries under a unique day number, though, is trivial if you don't actually care what that day number happens to be. If you want it to start from some particular value, that can be an issue, but to just have a unique number for a given 24-hour period, use:

Daycode = CLNG( Now() )

This will give you the system's internal number for today. It will change automatically to the next number in sequence starting at midnight plus one system clock-tick. BUT... you cannot use this as a prime key because PKs require uniqueness and you want multiple entries. Therefore, don't use it as a PK. You can set another field to be Autonumber to provide what we call a "synthetic" PK. Then just record the day number. Though the truth is, if you already include the date of an entry in the same record, you already have the ability to do this without an extra field, just by using a query that does the computation on the fly.

The other way to deal with this depends on whether you have something unique about the day that you wanted to store besides a date or day number or other info that can be computed strictly from the date. Then you would have a single record holding the other uniquely day-oriented number and create what is called a child table to hold the individual records that you wanted to use that same number. You would need a child table IF AND ONLY IF there is something else unique about that day that needed to be stored as such.

 

[theDBguy]

You get it right. I have to do multiple entries under a same primary key

How can i do it?

Thanks

Hi. If you carefully read my post, I mentioned two tables: a parent table and a child table. Each table will have its own primary key, but the child table will also have a foreign key to reference the primary key of the parent table.