Closing and Access DB after a certain date has been reached

Cosmoman

Registered User.
Local time
Today, 07:17
Joined
Feb 26, 2012
Messages
11
I was wondering and have been racking my brains for this. I have a table called Expire and a field called Date. Now I want my Access DB to not open after that date has been reached but to display a message saying project time has expired then close when they press ok. Is this possible?
 
I imagine DoCmd would close the database.

Set it within an If Then Statement. Maybe check who the user is first or some other method so the database could be opened by an administrator.
 
Thanks PNGBill,

That's what i'm trying to figure out how to write out.
 
For MS Access 2010 you can set a form to display when the database is opened.

This form could then have Events for On Open that would compare the data in your table field and display a message and then close down or alternativly, welcome the user to the database.

Maybe the form would first get the user to Login and then check the date ??

What version do you use ?
 
Im using access 2007. I have a form called splash2 which comes up when the database is double clicked on when tells them when the project will close then that closes after 10 seconds and brings up the main page. I'm just trying to figure the code to put into the spash2 form that will prevent the database from opening once the date in the tabel called expire has been reached and display a message.
 
If the form already opens and closes after 10 secs then it appears you have some knowledge of what is required.

Can you post the code on the form that causes the message and closure of the form ?

This sounds like where you would include an If Then to perform an activity if the Expire Date has been exceeded (or reached ?)

Use the hash symbol when posting code.
 
This is the code that closes my splash form and

#Private Sub Form_Close()
#DoCmd.OpenForm "Main Menu"
#End Sub

#Private Sub Form_Timer()
#DoCmd.Close acForm, Me.Name, acSaveNo
#End Sub

Right now there is no message that is what I am trying to do if the date has reached the expiry date in the table and then give a message the project is closed?
 
When you post code it is best to click on the hash symbol, just above where you type and then post your code in the place provided.

Your post will then show code better.

You use DLookup to get the data from the table - use ms access vba help for this function.

You need to Dim ExpireDate As Date in your code to create a variable to store the result of DLookup.

Then an If Then will compare ExpireDate to Date() (today's date) and run code as a result.

Sort out what options you want.

On the Form you create Labels with the different messages and make these Visible, no. in the form control property.

In your IF Then Statement (or Case Statement) you can change the control property so for different instances of the date, a different message will be displayed on the same form.

Have you done any vba code work yet ??

It would be good if you can attempt some and post it for critique and it won't be too long before the result is achieved.

I will try and find something we use for similar situations and post same.
 
This code is in the Open Event of a Form.

It sets the Form Control FilterLabel to read All Applications

It also changes some subform labels to be invisible (.visible = false)


Code:
Private Sub Form_Open(Cancel As Integer)        'Form On Open Properties when Form is first opened
    Me.FilterLabel.Caption = "All Applications"
    Me!FrmLoanApplicationsSubForm.Form.cmdProcessLoan.Caption = "Member Details"
    Me.FrmLoanApplicationsSubForm.Form.OrderBy = "APLPK Desc"
    Me.FrmLoanApplicationsSubForm.Form.OrderByOn = True
    Me!FrmLoanApplicationsSubForm.Form.cmdProcessLoan.Width = 1600
 '   Me!FrmLoanApplicationsSubForm.Form.cmdProcessLoan.SetFocus
    Me.FrmLoanApplicationsSubForm.Form.ForwardLoan.Visible = False
    Me.FrmLoanApplicationsSubForm.Form.cmdCancelLoan.Visible = False  
    
 End Sub
 
This code on another form first sets a subform as visible.
Then it checks the value in the txtRefinanceAmount text box control on the main form. If the value is gretare then zero, 8 commands are actioned. If not, just one is.

Code:
Private Sub Form_Open(Cancel As Integer)
       
        Me!FrmRefinanceSubForm.Form.Visible = True
     If Me!FrmRefinanceSubForm.Form!txtRefinanceAmount > 0 Then
         Me!cmdRefresh.SetFocus
         Me!FrmRefinanceSubForm.Form.Visible = True
         Me!FrmRefinanceSubForm.Form.AllowAdditions = False
         Me!FrmRefinanceSubForm.Form!OldLoanID.Enabled = False
         Me!FrmRefinanceSubForm.Form!NewApplicationID.Enabled = False
         Me!FrmRefinanceSubForm.Form!DateInitiated.Locked = True
         Me!FrmRefinanceSubForm.Form!txtRefinanceAmount.Enabled = False
         Me!FrmRefinanceSubForm.Form!txtRefinanceID.Visible = False
     Else
         Me!FrmRefinanceSubForm.Visible = False
     End If
     
End Sub
 
In you OP you mention you have a field called Date. Date is a Reserved Word and using reserved words for object and controls will cause you no end of grief further down the line if not already.

Now lets assume your field is actually called ExpDate, if you bind your current splash screen to the table holding ExpDate, you could use the following code;
Code:
Private Sub Form_Close()
DoCmd.OpenForm "Main Menu"
End Sub

if Me.ExpDate >= [URL="http://www.techonthenet.com/access/functions/date/date.php"]Date()[/URL] Then [COLOR="SeaGreen"]'You can see right there why a field called date will cause confusion[/COLOR]
     MsgBox "Sorry Project Time expired"
     DoCmd.Quit
End If

Private Sub Form_Timer()
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

Also avoid using spaces and other special charters in object and control names. Limit yourself to alpha and numeric charters and the underscore. Also consider implementing a naming protocol along the lines of TBL_TableName, FRM_FormName, QRY_QueryName etc. This then makes it clear in your code what type of object you are dealing with.
 

Users who are viewing this thread

Back
Top Bottom