Pop up Form

[louisa]

Hi all,

I have a form called "Network" and a field within called "EndDate" when it gets to 45 days prior to the EndDate i want a message box/form to appear (when i open the database not just the form) to state the name of the company and display the end date.

Does anybody know how this can be done?

 

[Privateer]

Louisa,
Access is an event driven database, so something has to happen as the database opens to cause a date check. There is an option to have a form open when the database opens, you could put the code there. Many people have a "Main Menu" that opens first so you can hide the code there, in the Form Open event, have it check, and pop the form it there are results. I have Access 2002 on this laptop, and the option can be set in Tools, Startup, in 2007 I think it is in Access Options, Current Database, form to open.
As far as the code is concerned your form can be bound to a simple query and the results displayed. Just change the form's default view property to continuous in case there are multiple records. The query would look something like this:

SELECT tblNewtork.CompanyName, tblNetwork.EndDate, DateDiff("d",EndDate,Date()) AS DaysOld FROM tblNetWork WHERE (DateDiff("d",EndDate,Date())>44));

The datediff function here uses "d" for days, there are many other options. The Date() is the date set on the computer being used. Be careful on the order, reversing date with enddate will get you a negative number which is somethings necessary but can produce confusing results. Hope that helps you.
Good Luck,
Privateer

 

[vbaInet]

The datediff function here uses "d" for days, there are many other options. The Date() is the date set on the computer being used.

This function was also mentioned in Louisa's post of yesterday:

http://www.access-programmers.co.uk/forums/showthread.php?t=220433

Obviously Louisa hasn't taking the initiative nor did she respond.

 

[louisa]

Hi all,

The kind of thing i am looking for is similar to the attached which was given to me a while ago. The coding is for if a field is empty then a form will open once i have opened the db however in this occasion i want it to open if the EndDate field is prior to 45 days the coding for this is what i am struggling with.

 

[vbaInet]

I have a form called "Network" and a field within called "EndDate" when it gets to 45 days prior to the EndDate i want a message box/form to appear (when i open the database not just the form) to state the name of the company and display the end date.

Basically, how do you know what record the form will open up onto? Secondly, you either need an AutoExec macro or set the form as Startup Form.

 

[louisa]

I want it to execute exactly as the db i attached, on startup the attached finds any records which does not have a date in the documents received box and displays them. I need to change it so that it displays any records has a date in the "EndDate" box and it is 45 days prior.

 

[vbaInet]

I need to change it so that it displays any records has a date in the "EndDate" box and it is 45 days prior.

We've mentioned a couple of times already that you need the DateDiff() function and you were also given a link that explains how the function works.

 

[louisa]

I understand that part. The problem i have now is the coding, on the attached DB the code is written so if a box is empty to produce the records, i also no how i will view the records, from the code
If DCount("[EventID]", "[TBL_JobDates]", "[actdate] < Date()-3 And isnull([rcvddate])") >= 1 Then

MsgBox "there are " & DCount("[EventID]", "[TBL_JobDates]", "[actdate] < Date()-3 And isnull([rcvddate])") & " records that are older thand 3 days without a doc's recieved date"
DoCmd.OpenForm stDocName, , , "[actdate] < Date()-3 And isnull([rcvddate])"


End If
I need to change < Date()-3 to < Date()-45 but the next part is if rcvddate is empty to load the form, i dont need it to say is null i need it to if EndDate is less than 45 days then to load the form but i cant get that part to work.

 

[vbaInet]

I would like to see your use of DateDiff() to get the date less of 45 first.

 

[louisa]

I have it working now, thank you for your help.

 

[vbaInet]

Good work! What did you do?