# Autoincrement field including year - restart each year



## smogens (Dec 19, 2010)

Hi,
I've read every post I could find on this so I'm sorry to ask another dumb question but here goes.

I created an access database (volunteer work) which has an autoincrement field to issue numbers to animals taken in at our shelter. My 'boss' (also a volunteer) wants this simple autoincrement changed to a value which begins with the two digit year and then has 4 digits, starting with 0001 which will increment sequentially and restart at 0001 with the new calendar year. Therefore, records will start with 10-0001, 10-0002, etc for this year and 11-0001, 11-0002 for next year. 

Any help would be greatly appreciated.


----------



## OBP (Mar 8, 2005)

smogens, welcome to the Forum.
I have posted about this on the Forum a few times.
There are 2 ways to do it, have an Autonumber Field and a Date field and combine them simple VBA to display the Number the way that you want. The only problem with this method is deleted or cancelled records increment the Number, so you have gaps.
The other method involves using a VBA recordset to increment the value.


----------



## smogens (Dec 19, 2010)

I saw one posting on the forum and tried your code since I'm NOT a VBA person. Got it to work and thank you. It told me to create another field, text, which I did and then use your VBA with my current autonumber field. It gave me YY-autonumber. 

However, I need to have this counter reset to one each year. Since the current code works off the autonumber, I can't reset it. 

Do I create another numeric field and have it increment each time the form is saved or each time the triggering field with first data input on the has data entered like the code you mentioned originally? 

Since I'm not a VBA person I'm unsure how to do that. 

Thanks again and sorry for making you chew your cabbage twice (or thrice.)

If you've already posted about this I'll just continue to review posts until I find it.

Thank you,
Shirley


----------



## OBP (Mar 8, 2005)

Shirley, well done in getting the "simpler" version to work. What you need is the more complicated VBA Recordset version. What is the name of your table and the field name of the field you want to use.
You will need to change the field to type text instead of autonumber.


----------



## smogens (Dec 19, 2010)

The table is called M_AnimalIntake and the new field is called ActualFWCNumber.


----------



## OBP (Mar 8, 2005)

OK, I will create a database with that table and field in it and generate the code you need.
What is the name of the first field on your Form where you actually enter data (ie not an Autonumber field)?


----------



## smogens (Dec 19, 2010)

You are awesome. The first field into which I add data is called Admit_Date.


----------



## OBP (Mar 8, 2005)

OK, here is the database, it auto-opens the form. The VBA code is on the Button's On Click Event Procedure.
Yours should go in the Admit_Date's After Update event procedure.
Open the database, go to a new Record and click the button and it will increment the ActualFWCNumber. Note that you also need the *M_AnimalIntake Query* which the VBA code uses.


----------



## smogens (Dec 19, 2010)

Thanks so much for your help. I took the database you sent me and placed your vba code into the admit date field's after update event. I also exported the M_AnimalIntake query to my database.

The first time I tried it, the number it came up with was not the number I expected which should have been 10-0001 for the first entry for 2010. It was something like 9-1423?

The second time it didn't work at all. That is, the actualnumber field remained blank.
 HMMMM! Obviously I did something wrong.

When I run the M_AnimalIntake query by itself, it comes up with 9-999 as the maximum number. I wonder if that's the problem.

The max number for 2009 should be 9-9999.
Any ideas what I might have done wrong?


----------



## OBP (Mar 8, 2005)

Well one thing that comes to mind is you have 9-9999 instead of 09-9999 which the VBA is designed to produce.
I have no idea why you get 9-999 when the highest value is 9-9999.
You can change the query by removing the Totals and sort Descending, that will list all records from highest down.
See the attached database Query1.

Is your field set to Text?


----------



## smogens (Dec 19, 2010)

Sorry It's been so long. I attribute it to the holidays.
I'm going to try again with the data you send me and I'll let you know what happens. 
As always, I appreciate your help.


----------



## smogens (Dec 19, 2010)

I haven't given up yet. I just tried this twice. The first time it gave me the 'actual number' of 11-0001 which seemed great since it should be the first record for 2011. However, when I tried it again with a 2010 record I still got 11-0001. I then tried it a third time and got nothing in the field. What in the world can I have done wrong?


----------



## OBP (Mar 8, 2005)

Does the version that I posted work OK?


----------



## smogens (Dec 19, 2010)

Hi,
I'm getting a two digit year and 4 digit number after a dash. however, I entered two records and got the same "actual number" both times, 11-0001. The date of the intake was 2010 in both cases so I expected it to be 10-0001 an 10-0002.


----------



## OBP (Mar 8, 2005)

Sorry I wasn't aware that you wanted it based on the Intake date, it is currently based on the date of entry.
What is the actual name of that field?


----------



## smogens (Dec 19, 2010)

The field is named Admit_Date. 
Sorry to put you through this. And Thanks.


----------



## OBP (Mar 8, 2005)

Try this version, the code is in the After Update event procedure of the Me.Admit_Date field.


----------



## smogens (Dec 19, 2010)

OK. THANK YOU!. I will try it tonight.


----------



## smogens (Dec 19, 2010)

Hi,
I feel so guilty making you chase around on this because I'm too unknowledgeable to follow your logic.
I tried the new entry. It appears you changed the Date fields to Me.Admit_Date in two places so that's what I typed in. Now I'm getting the correct year but it's not incrementing. I tried two admissions in 2010 and both came up with 10-0001. 
I know you're also using the M_AnimalIntake query. Strangely when I run it it always comes to 9-999 even through the highest number is actually 9-1421.

I don't know if this information helps. But again thanks for trying.


----------



## OBP (Mar 8, 2005)

It shouldn't matter what the query shows as it is comparing the 9 with the 10 in 2010 in the date 
You could email me your table so that can work with it.


----------



## smogens (Dec 19, 2010)

Hi,
I've attached the DB with one table only the AnimalIntake table because I thought that might be what you meant.

Thank you again. 

Shirley


----------



## OBP (Mar 8, 2005)

Shirley, sorry for being so slow, I obviously wasn't really reading what you were saying. The problem is that Text fields are sorted by position as well as value, So 9-1000 comes below 9-9, 9-8 etc.
I have run a couple of queries to format your data so that they all have 6 characters, ie. 9-9 is now 9-0009 etc.
They now sort as they should. Now you know why I use the formatting that I do for the numbers


----------



## smogens (Dec 19, 2010)

Well, I thank you for formatting my data. I wouldn't have been able to figure it out. Still not working but I think I might know why. Just as you had to add leading zeroes before the incremental numbers, I think we need to have a zero before the 9 in the year position. Does that make sense? Because when i run the query now, it still finds 9-1421 as the highest number, even after I've successfully added 10-0001. The next number is also 10-0001 and for 2011 the same things, each record gets 11-0001. 
Thanks as always,
Shirley


----------



## OBP (Mar 8, 2005)

Shirley, sorry again, well spotted. I have fixed it & tested it on this version. See the last 4 records


----------



## smogens (Dec 19, 2010)

Okay. The good news is it works! Yay! The bad news is it only works if the data is entered in year order. Since I know how strangely the records can be entered I experimented and found that if I enter some 2010 data (which is mostly what will be entered now) and then some 2011 data and then return to 2010 the query, which is only looking for a match on the highest number doesn't find it and numbers all of the 2010 data as 10-0001. 

I'm sure there is no easy fix to this. (Easy for you that is. ) 

Shall I just tell her to be careful not to enter any 2011 data until 2010 is done?


----------



## OBP (Mar 8, 2005)

This version does what you want.


----------



## smogens (Dec 19, 2010)

Hi,

I copied the new proc over the old one and tried to enter data again. This time I get an error.

Error #:3061
Too few parameters. Expected 1

I tried to run the debugger but don't know enough to know what the problem is. It showed the value of the last record and then went to the error.

Shirley


----------



## OBP (Mar 8, 2005)

To transfer it to your database you also need the "M_AnimalIntakeQuery" query as well as the code as it now has a Year column in it.


----------



## smogens (Dec 19, 2010)

It works. Thankyou so much. I'll leave you life in peace now.
Shirley


----------



## ade7985 (Jan 27, 2011)

hi, I'm new in access database and i need help generating next number using in text field which is my primary key. my format will be like YY-0000/10-0001, 11-0001. What should i use format to setup. Thanks


----------



## OBP (Mar 8, 2005)

ade7985, welcome to the Forum.
You can use the VBA code that is in the attachment at post #26.


----------



## ade7985 (Jan 27, 2011)

Thank you, I'd follow your VBA code to my database. But i get error after I enter date to suppose generate next number. Error says, Index or primary key cannot contain a Null value.
I have two field which is CASE_NR is text field also my Primary key and CRC_DATE is my date/time.


----------



## OBP (Mar 8, 2005)

Did you change the names in the VBA to reflect the names of your fields and Table?
They have to be exact for VBA code to work.


----------



## ade7985 (Jan 27, 2011)

I get compile error. on Me.CRC_NR = Left(rs![MaxOfCRC_NR], 3) & temps

Private Sub CRC_DATE_AfterUpdate()
Dim rs As Object, zeroes As String, temps As String, tempv As Integer, SQL As String
On Error GoTo Eventerror
If Me.NewRecord Then
zeroes = "0000"
SQL = "SELECT qryCRC.* " & _
"FROM qryCRC " & _
"WHERE year <= " & Format(Me.CRC_DATE, "yy")
Set rs = CurrentDb.OpenRecordset(SQL)
rs.MoveFirst
If rs.RecordCount <> 0 And Left(rs![MaxOfCRC_NR], 2) = Format(Me.CRC_DATE, "YY") Then
tempv = Val(Right(rs![MaxOfCRC_NR], 4)) + 1
temps = Str(tempv)
temps = Right(temps, Len(temps) - 1)
If Len(temps) < Len(zeroes) Then temps = Left(zeroes, (Len(zeroes) - Len(temps))) & temps
Me.CRC_NR = Left(rs![MaxOfCRC_NR], 3) & temps
Else
Me.CRC_NR = Format(Me.CRC_DATE, "YY") & "-0001"
End If
rs.Close
Set rs = Nothing
End If
Exit Sub
Eventerror:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub


----------



## OBP (Mar 8, 2005)

You could try enclosing the CRC_NR in square brackets ie
Me.[CRC_NR]

I can't see any other reason why you should get an error as the original code works OK.


----------



## ade7985 (Jan 27, 2011)

it work now , i create new form for testing and selrct only 2 field like date and casenumber. did the test it work. thank you so much. ade


----------



## ade7985 (Jan 27, 2011)

I will try that solution. thanks again


----------



## ade7985 (Jan 27, 2011)

Hi OBP,
How do you configure alert msg or Flash msg like a signal to know if is there an archive records.
i have a database that I added a archive (field) and i wanna know if is there a way to know that there is a archive record while youre in data entry form. example when our data entry person try to find a record on current database not in archive but once enter a record it suppose to alert show it already archive. 

Thanking you in advance,
ade


----------



## OBP (Mar 8, 2005)

Ade, I do not Archive records to a different table, I just mark them with an Archived Check box in the same table. In that way you can control duplicates & include them in any checks that you want to do.
The only way that you can access the data in an Archive table is to use VBA to obtain either a Dlookup or a Recordset.


----------



## ade7985 (Jan 27, 2011)

I will try Dlookup or recordset...Thank you very much for quick respond. Have nice day...


----------



## ade7985 (Jan 27, 2011)

Hello OBP,

Can you give example for Dlookup? Appreciate your help. ade


----------



## ade7985 (Jan 27, 2011)

OBP,

Nevermind i got it, Thanks so much...


----------



## ade7985 (Jan 27, 2011)

Hello OBP,

I get an error #3061, too few paramater. Expected 1. after I selected date and won't generated casenumber. 
Private Sub CRC_DATE_AfterUpdate()
Dim rs As Object, zeroes As String, temps As String, tempv As Integer, SQL As String
On Error GoTo Eventerror
If Me.NewRecord Then
zeroes = "0000"
SQL = "SELECT qryCRC.* " & _
"FROM qryCRC " & _
"WHERE year <= " & Format(Me.CRC_DATE, "yy")
Set rs = CurrentDb.OpenRecordset(SQL)
rs.MoveFirst
If rs.RecordCount <> 0 And Left(rs![MaxOfCRC_NR], 2) = Format(Me.CRC_DATE, "YY") Then
tempv = Val(Right(rs![MaxOfCRC_NR], 4)) + 1
temps = Str(tempv)
temps = Right(temps, Len(temps) - 1)
If Len(temps) < Len(zeroes) Then temps = Left(zeroes, (Len(zeroes) - Len(temps))) & temps
Me.CRC_NR = Left(rs![MaxOfCRC_NR], 3) & temps
Else
Me.CRC_NR = Format(Me.CRC_DATE, "YY") & "-0001"
End If
rs.Close
Set rs = Nothing
End If
Exit Sub
Eventerror:
MsgBox "Error #: " & Err.Number & vbCrLf & vbCrLf & Err.Description
End Sub

Thanks


----------



## OBP (Mar 8, 2005)

Do you have the "year" column in your query?
Usually that message means that either a Criteria or field is missing, or a name is different.


----------



## ade7985 (Jan 27, 2011)

Hi OBP,

I have another question on tab form. 
I setup tab form and place a subform on top of tab form, problem is when i try to use Find Record on subform it only have one record and it would not do search more record. I have 4000 records on it.
I can't able to search find record on subform above tab form. is there any setting that i need to enable. 

v/r,
alejo


----------



## OBP (Mar 8, 2005)

alejo, when you place a subform on a Mainform it links to the Mainform so that you only see records pertaining to the Mainform record that is showing.
For that reason you can't use the normal "Find" or "Find Combo" because the records are Filtered.
If you did have a Search of all the records you also need to know the "Mainform Record" that it is linked to to find it on the tabbed form.


----------



## ade7985 (Jan 27, 2011)

Thank you so much your help. I just remove my subform and I just start adding field from my query and redesign to make it look same the ones on my subform. And my Find record buttons working good. Thanks again. Take care, Alejo


----------



## ade7985 (Jan 27, 2011)

Hello OBP,

I have another question about exporting to word. I have a report wanted to export to word document. Is there a way to configure to have only one page to export. When i tried to export it gives me all pages and i only need one page. 

Thanking you in advance,
Alejo


----------



## OBP (Mar 8, 2005)

Alejo, you need to "Filter" the query that supplies the report, or filter the report itself.


----------



## ade7985 (Jan 27, 2011)

Thanks OBP,


----------



## ade7985 (Jan 27, 2011)

Hello OBP,

How to create a checkbox Yes or NO on access? I want design box for Yes and box for No on a form this is for data entry field. Thanks alot.


----------



## OBP (Mar 8, 2005)

You normally use just one field which is of a Yes/No check box type.
Or you can use an option group that has 2 check boxes or radio buttons, one for yes and one for no.


----------



## ade7985 (Jan 27, 2011)

Thanks OBP, I got it, I really appreciate you help me alot. Thank you so much for everything. Have a nice day.


----------



## ade7985 (Jan 27, 2011)

Hello OBP,

When i used radio buttons, one for POS and one for NEG selection. I looked into my table it show 1 or 2 I was hoping to see POS or NEG. How can i get make it shows list POS or NEG instead of 1 or 2 value.

Thanks, ALejo


----------



## OBP (Mar 8, 2005)

Alejo, it is normal to not store the words only the values in the table. You convert that in the output from the table using an extra column in the query like this
Pos/Neg: iif([name of field] = 1, "Pos", "Neg")
where name of field is the name of the field that holds the 1 or 2


----------



## ade7985 (Jan 27, 2011)

Hi OBP,

when you talking about convert that in the output. Do you mean in Default Value or input mask?

v/r,
alejo


----------



## OBP (Mar 8, 2005)

No in a query, using a new column Heading.


----------



## ade7985 (Jan 27, 2011)

OBP,
Pos/Neg: iif([name of field] = 1, "Pos", "Neg")

Is that two 'i' or just one 'i'

Thanks so much


----------



## OBP (Mar 8, 2005)

2 'i's


----------



## ade7985 (Jan 27, 2011)

OBP,

I get #Error on Pos/Neg field some reading Neg.

POS=1
NEG=2


----------



## OBP (Mar 8, 2005)

Do you have any that have nothing?


----------



## ade7985 (Jan 27, 2011)

yes, there is data on 'Pos/Neg: IIf([HBsAB Results]=1,"Pos","Neg")' , on Pos/Neg field in query showing #Error


----------



## OBP (Mar 8, 2005)

Can you post an Access 2003 copy of the database?
It does not need any data in it.


----------



## OBP (Mar 8, 2005)

Sorry, I can't open MS 2007 documents.
I really needed the table & query to work with.


----------



## ade7985 (Jan 27, 2011)

Hello OBP,

Can i send it to your email? Because zip file is way too big even without data on it.


----------



## OBP (Mar 8, 2005)

Have you Compacted & Repaired the database?
But you can send it to me if you like.


----------



## ade7985 (Jan 27, 2011)

Thanks OBP,
Ijust talk to my customer that they are okay with Yes or NO checkbox on other I just leave it with dropdown list for selection because I was thinking once it plan to change format it will mess up the reports. Thank you so much.


----------



## ade7985 (Jan 27, 2011)

Hello OBP,
Is there a way to disable printing of a report? I don't want staff printing a report but only they can printview on screen.

Thank you,


----------



## OBP (Mar 8, 2005)

see this tutorial, about 2/3rds down the page
http://msaccesstips.com/2009/07/hiding-report-lines-conditionally-2/


----------



## ade7985 (Jan 27, 2011)

Thank you, OBP


----------



## ade7985 (Jan 27, 2011)

Hi again OBP,
I have a another problem setting up date from start to end.
in my query I setup Between [start date:dd-mmm-yy]and[end date:dd-mmm-yy] (01-oct-09 - 31-sep-10) after i run query i suppost to get result between that date but insteat i get yy=04,05. I only want 09-10. is my format date range is wrong?


----------



## OBP (Mar 8, 2005)

I would use a form and format the datew and have an input mask.


----------



## ade7985 (Jan 27, 2011)

Thank you again, Have nice day....Alejo


----------



## ade7985 (Jan 27, 2011)

Hello OBP,

I have an error #3464 Data type mismatch in criteria expession.

After I enter new date and it does not genereate autonumber.

v/r,
Alejo



OBP said:


> Shirley, sorry for being so slow, I obviously wasn't really reading what you were saying. The problem is that Text fields are sorted by position as well as value, So 9-1000 comes below 9-9, 9-8 etc.
> I have run a couple of queries to format your data so that they all have 6 characters, ie. 9-9 is now 9-0009 etc.
> They now sort as they should. Now you know why I use the formatting that I do for the numbers


----------



## ade7985 (Jan 27, 2011)

Disregard this I found problem customer put * on autonumber field that why when create new date its mismatch next number. thank you.



ade7985 said:


> Hello OBP,
> 
> I have an error #3464 Data type mismatch in criteria expession.
> 
> ...


----------



## ade7985 (Jan 27, 2011)

Hello OBP,

Do you have example access database ''how to move data from one table to another with append queries? I was trying to move old data using date to another table using append queries. 

Thank you,
ade


----------



## OBP (Mar 8, 2005)

I do not move old data, I just mark it as old with a checkbox and then add the checkbox to the query and set it's criteria to 0 which ignores all the old records.
You can then create a second query with the criteria set to -1 and see only the old records.
How do you currently qualify the Record as "old"?


----------



## ade7985 (Jan 27, 2011)

Hi OBP,
Sorry for my delay response, I have a field named archive (Yor N). I'd created a query for that field and created buttons on a form for selecting archive or unarchive. Thank you,



OBP said:


> I do not move old data, I just mark it as old with a checkbox and then add the checkbox to the query and set it's criteria to 0 which ignores all the old records.
> You can then create a second query with the criteria set to -1 and see only the old records.
> How do you currently qualify the Record as "old"?


----------



## OBP (Mar 8, 2005)

Well I would modify the query for your normal form records so that it ignores any records with the archive (Yor N) field ticked.


----------



## ade7985 (Jan 27, 2011)

Thank you,
what is max you can store data record in access?


----------



## OBP (Mar 8, 2005)

I don't think that there is a limit on the number of records, as it depends on the size of each record. The limit is on the overall size of the database, which is 2 Gbs. So it is essential that you Compact & Repair the database at regular intervals.
It is also noy a good idea to actually store photos in the tables or use too many Graphics on the Forms etc.


----------



## ade7985 (Jan 27, 2011)

Thank you so much.
ade7985


----------



## ade7985 (Jan 27, 2011)

Hello OBP,
I having trouble setting up a Primary key on my table on SSN field. error says, 
"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. change the data in the field that contain duplicate data, remove duplicate data, remove the index, or redefine the index to permit duplicate entries and try again. i was trying to change setting in index to allow duplicate and still same error.. Help please


----------



## OBP (Mar 8, 2005)

If it is a key field, it must be "No Duplicates".
Run a find Duplicates query to find and remove the duplicates.


----------



## elancour (Apr 12, 2011)

Hi,

I'm new toVBA coding but I am trying to create a incident number in my db input form that will take my Fiscal Year that is entered and adding a 3 digit number to it and have that number change incrementally when new cases are logged. Help?

Thanks


----------



## OBP (Mar 8, 2005)

What are the exact names of the table and Fields?


----------



## elancour (Apr 12, 2011)

OBP said:


> What are the exact names of the table and Fields?


The name of the table is "Call Theft" and the Field Names are "FiscalYear" and "Incident#". Should they be formatted a certain way?


----------



## OBP (Mar 8, 2005)

The Incident field can be text,
Do you want 2011-001 or 2011001


----------



## elancour (Apr 12, 2011)

obp said:


> the incident field can be text,
> do you want 2011-001 or 2011001


2001-001


----------



## OBP (Mar 8, 2005)

OK, ideally you need to create a Query based on your Call Theft table using the "Incident#" field and sort it descending so that last number is the first record.


----------



## elancour (Apr 12, 2011)

But part of the problem is I don't know how to get the Incident # created.


----------



## ade7985 (Jan 27, 2011)

Hello OBP,

I am having problem sorting Lastname and firstname in alphabatized. I wanted to do is sort a to z on lastname also same a to z on firstname.

alphabatized by last and first name.

Thanking you in advanced,


----------



## OBP (Mar 8, 2005)

elancour, if you can create the Query, I can provide VBA to create the number.

ade, doesn't a query or Report do it for you?


----------



## ade7985 (Jan 27, 2011)

OBP,
I will try again on query sorting. Last time i did it only sort lastname from A to Z but on Firstname from A to Z it did not sort. thanks

Fixed. Thanks again, OBP


----------



## OBP (Mar 8, 2005)

Sorry, I can't open Access 2007 databases. Can you put the Table and Query in to an Access 2003 format database?


----------



## elancour (Apr 12, 2011)

OBP said:


> Sorry, I can't open Access 2007 databases. Can you put the Table and Query in to an Access 2003 format database?


Okay here it is in 2003. Thanks again for your help.


----------



## OBP (Mar 8, 2005)

To sort by both names you have to split the name in to First and Surname fields.


----------



## ade7985 (Jan 27, 2011)

Hi OBP,
how to set primary key that has a duplicate SSN? I import excel data to access that has SSN duplicate. I wanted to setup SSN as my primary key. data entry person will have duplicate SSN all the times. I get error when i setup SSN that has already duplicate. what is another way to do this.

thanking you in advance,
ADE


----------



## OBP (Mar 8, 2005)

Do not use the SSN as the Key field use an Autonumber instead, it does not need to be and shouldn't be visible to the user
You can still set the SSN field to "Indexed" for faster searching if required.


----------



## ade7985 (Jan 27, 2011)

OBP, 
I have this database with one table that has 9 fields. I can see 9 fields on design view but when i switch to regular view i only see 5 fields. I dont' know if 4 are hidden. I'd check setting on access 2007 nothing i can find to resolve issue. Please help. Thanking you in advance. ADE


----------



## ade7985 (Jan 27, 2011)

OBP, Please disregard I'd figure it out already. I just create a query to make new table and now i can see all field now. Thank you.


----------



## ade7985 (Jan 27, 2011)

OBP,
I have combo box list that has 4 selection. example: 1. Exam; date 2. labs 3. other 4. NA; If I selected #1 on my combo box list and I aslo need to input date field. How to do ...Please Help.. Thanking you in advance,

v/r
ade7985


----------



## OBP (Mar 8, 2005)

I am not sure what you mean by "I aslo need to input date field", can't you manually input it, or do you mean pull the date from some other data?


----------



## ade7985 (Jan 27, 2011)

If I select "EXAM; Date _______ then I need to fill-in dates on it. Or pull the date from some other date like you said.

v/r,
ade


----------



## OBP (Mar 8, 2005)

At the moment I can't visualise how or why you would need to do this, or where the "Exam" and Date will go.


----------



## ade7985 (Jan 27, 2011)

OBP, That fine, I just create another field name "Comments" so they can enter their whatever date it is into comment block. Thanks again. 

Another questions, I was implement a "autologout" database. I want to able to auto logout users on database whenever we need for maintenance. Do you have sample database for autologout? Thanking you in advance.


----------



## OBP (Mar 8, 2005)

I have this "Concept" posted by geekgirlau, she uses an external text file, it should also be possible to use a Table in the actual database as well.
It is also a bit Abrupt, just shutting the database, you could make another form visible with a message telling the users that they have 10 minutes exit, using that form on timer to shut the database instead.

Force Users out of a Database by geekgirlau
This idea has 2 parts. It assumes that you have a text file, where you type the word "lock" if you want to force users out of the database.

Function to read a text file to see whether to close the database (returns True if we need to close):
Function fGetOut() As Boolean 
Dim strValue As String

Const cstrLockout As String = "C:\DB_Path\Lockout.txt"

On Error Goto ErrHandler

fGetOut = False

If Dir(cstrLockout, vbNormal) <> "" Then 
Open cstrLockout For Input As #1

Do While Not EOF(1) 
Input #1, strValue

If UCase(strValue) = "LOCK" Then 
fGetOut = True 
End If 
Loop 
End If

ExitHere: 
On Error Resume Next 
Close #1 
Exit Function

ErrHandler: 
' no message box on error
Resume Next 
End Function

Part 2: A form that is open at all times, with a timer event. This can either be a switchboard-type form, or perhaps a hidden form that is opened on database launch without the user seeing it. The form's timer interval is set to check the function every few minutes.

VBA:

Private Sub Form_Timer() 
If fGetOut() = True Then 
Application.Quit 
End If 
End Sub


----------



## ade7985 (Jan 27, 2011)

Hi OBP,
Should I create module and copy&paste this code, 
Function fGetOut() As Boolean 
Dim strValue As String

Const cstrLockout As String = "C:\DB_Path\Lockout.txt"

On Error Goto ErrHandler

I am confuse...Do you have sample for autologout?

Thanking you inadvance,

fGetOut = False

If Dir(cstrLockout, vbNormal) <> "" Then 
Open cstrLockout For Input As #1

Do While Not EOF(1) 
Input #1, strValue

If UCase(strValue) = "LOCK" Then 
fGetOut = True 
End If 
Loop 
End If

ExitHere: 
On Error Resume Next 
Close #1 
Exit Function

ErrHandler: 
' no message box on error
Resume Next 
End Function 
Function fGetOut() As Boolean 
Dim strValue As String

Const cstrLockout As String = "C:\DB_Path\Lockout.txt"

On Error Goto ErrHandler

fGetOut = False

If Dir(cstrLockout, vbNormal) <> "" Then 
Open cstrLockout For Input As #1

Do While Not EOF(1) 
Input #1, strValue

If UCase(strValue) = "LOCK" Then 
fGetOut = True 
End If 
Loop 
End If

ExitHere: 
On Error Resume Next 
Close #1 
Exit Function

ErrHandler: 
' no message box on error
Resume Next 
End Function


----------



## OBP (Mar 8, 2005)

This part in the Form's On timer event closes the database
If fGetOut() = True Then
Application.Quit
End If 

The other code goes in a Function Module.


----------

