Pop Up Box

[louisa]

I have a field on my form called DocumentsReceived. On opening the db it is set to open a form called General (DocumentsReceived) is not on this form.
After three working days the DocumentsReceived field should have been completed with a date. If the field hasnt been completed within 3 working days of the record being created i would like it to pop up with a box advising which records havent been updated. Does anybody no how i can arrange this?

 

[louisa]

Anyone???????

 

[namliam]

The function date() will return todays date,
then using your "creation" date you can find out the difference using datediff function.

Use a query and some code to see if they are there.

 

[louisa]

I am a total novice when it comes to the db, i havent ever used code, querys or macros so not sure how i would go about this? As i am a novice i thought it would be simple, just set it to any new records added from now, if that field doesnt have data entered within three working days then it will display a msg box???

 

[John Big Booty]

You could use something like the following in your form's on load event;

If DCount("[YourRecordID]", "[YourTableName]", "[RecordCreatedDate] < Date()-3  And  IsNull([DocumentsReceived])")>=1 Then
     DoCmd.OpenForm "YourFormName", , , "[RecordCreatedDate] < Date()-3  And  isnull([DocumentsReceived])"
End If

The code searches the table containing DocumentsReceived and counts any records which were created more than three days ago and wich still have no date in the DocumentsReceived field. It then opens a form YourFormName that would be a form that uses the table containing the records related to DocumentsReceived as it's Record Source, showing only those records if it counts one or more records meeting the criteria.

 

[louisa]

Thank you so much, when you say to place the code in the on load event of the form, do i just copy and paste into the properties of on load event and my general form is set to open on opening the db but would i place the code on the general form or the form that contains the DocumentsReceived field?

 

[louisa]

Also i am not very clued up with code should i say so can you advise what parts of the code i need to amend, i take it i would need to put the name of the table where it states YourTableName, and the name of my form in YourFormName but where it states YourRecordID and RecordCreatedDate i am not sure if i need to change?

 

[John Big Booty]

You will need to change anything between [ and ] to reflect the names in your DB.

 

[louisa]

Thanks John, i dont understand what needs to go in RecordCreatedDate?
Can you advise where i put this code. I opened my general form and hit the code builder button and pasted the code next to the OnOpen and saved. When i went to open my form it advised that there isnt a macro saved??? any help you can give will be excellent thanks for all so far,

 

[louisa]

please can someone help me????

 

[highandwild]

Hi Louisa

Create a calculated field in the query upon which the form is based:

DaysSinceReceipt: DateDiff("d",[RecordCreated],Date())

Reference:
http://www.techonthenet.com/access/functions/date/datediff.php

In the criteria row put >3 if you want to display records where the document was received more than 3 days ago.

Add the criteria Null to the [DocumentReceived] field.

Replace [RecordRecreated] with the field in your table that holds the date that the record was created. If you did not record when the record was created then you are stuck.

When you load the form the records that are missing a [DocumentReceived] date and where the record was created more than three days ago will be displayed. You may want to sort the query to show the oldest record first.

Let me know how you get on.

HighAndWild

 

[louisa]

Hi thank you for coming back to me, i have about 70 records in my current db that do not have the date completed....i was hoping to be able for it to work from any new records created. I am new to querys, code, macros etc so would really need help advising where to put the information in order for it to work..

 

[highandwild]

One step at a time then.

Do you record when the record was created?

If not then you need a date field called [RecordCreated] or similar in the table and give it a default value of date(). This field does not need to be on the form when the record is created. The current date will automatically be entered into this field when a new record is created.

Are you OK about setting up the query behind the form?

 

[louisa]

ok i have created the date field, not really sure what you mean about setting up a query behind the form sorry????

 

[highandwild]

Are you able to tell me what the Record Source of the form is?

Open the form in design mode, right click on the dark grey area and view properties, On the data tab is Row Source

Are you on Skype video?

 

[louisa]

the record source is contacts and no unfortunately not on Skype. thank you for helping me with this.

 

[highandwild]

OK

SO... instead of having contacts as the forms record source have a query based upon contacts.

Can you build a new query based upon contacts?

1. Add all of the fields from table contacts to the new query

2. Sort the [recordcreated] date field in Ascending order

3. Create a calculated field in the query upon which the form is based:

DaysSinceReceipt: DateDiff("d",[RecordCreated],Date())

4. In the criteria row of this field put >3 if you want to display records
where the document was received more than 3 days ago.

5. Put Null (no quotes) in the criteria line of the [documentreceived] date field

6. Save the query as 'qry_DocumentNotReceived'

7. Use this query as the Row Source of the form.

8. You should now just get records you need.

Let me know how you get on.

Are you able to attach / post a database with just the form and the contacts table in it?

 

[louisa]

ok i will give that a go, in section 4 you have said to put >3 if you want to display records
where the document was received more than 3 days ago.

but i want it to be a reminder if the field hasnt had a date put in 3 days after creating the record. from above it seems it would be displaying all records that have had the date completed???

 

[highandwild]

5. Put Null (no quotes) in the criteria line of the [documentreceived] date field

Will display records where the [documentreceived] date field is empty, has not had a date entered

DaysSinceReceipt: DateDiff("d",[RecordCreated],Date())
and
4. In the criteria row of this field put >3 if you want to display records
where the document was received more than 3 days ago.

Will limit those records found in 5 above to those that have been entered more than 3 days ago.

Remember that there will be records where:

a. entered on any date and no document received
b. entered on any date and document received on any subsequent date
c. entered more than three days ago and no document received
d. entered more than three days ago and document received on any date
e. entered less than three days ago and no document received
f. entered less than three days ago and document received

I think thats all.

You need those in group c so there are two criteria based upon between between the date entered and the current date and the fact that the document has not yet been received. You may need to view other groups for other purposes.

22:56 in the UK now so must do some ZZzzz's.

See how you get on.

 

[louisa]

indeed it is im in the UK to lol, thank you for all your help and i will give this a go in the morning....