blank records only if new day otherwise show old records!

U

umair434

Registered User.
Local time
Today, 03:39
Joined
Jul 8, 2011
Messages
186
Hi guys,

I have a form where which is bound on an Employee table with the following fields:

EmployeeID
first name
Last name

On this form, I have a subform where there is a date text box with a default value of date(). Employee of a specific ID enters information in the subform (filling in numbers for a report).

Now what I want is that: The user will open the form for the first time on
October 1st, for example, and the form will be blank. Any subsequent
times the user opens the form on Oct 1st it will default to the
same record in the table for that day so that more information
can be added to that record. On October 2nd, the form will be
blank once again

Also, the main form is linked to subform using employeeID!!


Now from searching on google n stuff, I found that using

"DoCmd.OpenForm "subform", , , "txtDate = date()" works for the subform! but it doesn't work when I open the main form!!

how should I make this happen? thanks
smile.gif

progress.gif
 
I don't understand your requirement. Are users able to go back to the past? ;)

Please elaborate!
 
vbaInet said:
Are users able to go back to the past? ;)

Please elaborate!
Click to expand...

hey vbaInet! Here is the situation:

the user may enter the data on multiple times during a day. For example, he opens the database first at 9:00am, he enters data - Now if he comes back at 11:00am on the same day, he should see what he had entered and should be able to make changes as well!

BUT, if he comes on a new day then he sees a blank record because there has been no data entry for that day!

The user information is on the main form, while subform contains the text boxes which should be filled. Both are connecting using EmployeeID


does this make sense?
 
umair434 said:
BUT, if he comes on a new day then he sees a blank record because there has been no data entry for that day!
Click to expand...
How is this handled? Does the user not log off the database everyday?
 
umair434 said:
BUT, if he comes on a new day then he sees a blank record because there has been no data entry for that day!
Click to expand...

this is a requirement. that is what I want the application to do. Right now what's happening is that everytime the user opens the application, they see a blank record because it assumes a new record is to be entered

what I want is the application to do is to find if data has been entered for date() before (i.e if data has been entered for today before). If so, then show the records which can be edited as well. If not, then create a new record for the date!
 
So just use a DCount() function in the Click event of the button that opens the form:
Code: 
If DCount("*", "TableName", "EmployeeID = " & [EmployeeID] & " AND DateField = Date()") = 0 Then
     DoCmd.OpenForm "FormName", acNormal, ,"EmployeeID = " & Me.EmployeeID
     DoCmd.SelectObject acForm, "FormName"
     DoCmd.RunCommand acCmdRecordGoToNew
Else
     DoCmd.OpenForm "FormName", acNormal, ,"EmployeeID = " & Me.EmployeeID & " AND DateField = Date()"
End If
Does that all make sense?

Oh, by the way that's all aircode so there might be a few typos ;)
 
thanks!! but EmployeeID is on the main form and date() is on the subform. does that matter?
 
Let's take a step back here.

1. The user enters data for Joe Bloggs yesterday, saves and closes the database.
2. He comes back today and opens the form and it should go to a new record for whom?

... how do you know what record the user was working on yesterday?
 
the user only enters the data for himself!! I have placed a code that recognized the users windows name and then I have assigned an EmployeeID to it. So when a user logs in the machine and then the database, he can only enter data for himself using his own EmployeeID

user enters his data today!! he comes back 2 hours later and he sees what he had entered. If he comes back tomorrow morning, he sees a blank record because that is the first time he is opening the database to enter data!!
 
Right makes sense. The subform is linked to the main form via EmployeeID isn't it? And the records are entered in the subform?

List out the fields in the subform. If they are too many just list out the the PKs and/or FKs
 
Yes the subform is linked via EmployeeID!

the fields in the subform are text boxes!! these text boxes belong to a table called Activities with primary key of ActivitiesID

now this where I screwed up royally! my subform table is not normalized! I realized it way after. hence my subform is based on 1 table!! with each field representing a text box!

i hope i can recover from this :(
 
It's up to you to sort out the normalization early enough before it becomes a burden :)

Code: 
If DCount("*", "[COLOR=red]SubformTableName[/COLOR]", "EmployeeID = " & [COLOR=blue][EmployeeID][/COLOR] & " AND [COLOR=darkgreen]DateField[/COLOR] = Date()") = 0 Then
    DoCmd.OpenForm "[COLOR=indigo]MainFormName[/COLOR]", acNormal, ,"EmployeeID = " & [COLOR=blue][EmployeeID][/COLOR]
    Forms![COLOR=indigo]MainFormName[/COLOR]![COLOR=darkorange]SubFormControlName[/COLOR].SetFocus
    DoCmd.RunCommand acCmdRecordsGoToNew
Else
    DoCmd.OpenForm "[COLOR=navy]MainFormName[/COLOR]", acNormal, ,"EmployeeID = " & [COLOR=blue][EmployeeID][/COLOR]
 
    With Forms![COLOR=indigo]MainFormName[/COLOR]![COLOR=darkorange]SubFormControlName[/COLOR]
         .SetFocus
         .Form.Recordset.FindFirst "ActivityID = " & DMax("ActivityID", "[COLOR=red]SubformTableName[/COLOR]", "EmployeeID = " & [COLOR=blue][EmployeeID])[/COLOR]
    End With
End If
So I've colour coded it, just follow the code and amend accordingly. [EmployeeID] should be the EmployeeID you derived from the windows login.
 
wow! that worked. thank you so much :) they won't let me add to your reputation as they say I should add to someone else's first now lol

p.s 1 less important issue :P

is there anyway I can set the time for date() function. I know it's from 12:00am to 12:00am. Some of the employees work till 1:45 am. so after 12:00, the database will show the next day!! Is there any way that can be altered?

again, thank you so much!! you always come and save the day :)
 
umair434 said:
wow! that worked. thank you so much :) they won't let me add to your reputation as they say I should add to someone else's first now lol
Click to expand...
They must be hating! :D


umair434 said:
is there anyway I can set the time for date() function. I know it's from 12:00am to 12:00am. Some of the employees work till 1:45 am. so after 12:00, the database will show the next day!! Is there any way that can be altered?

again, thank you so much!! you always come and save the day :)
Click to expand...
You mean add an extra time part to the Date criteria?
 
vbaInet said:
They must be hating! :D
Click to expand...

hahaha!
vbaInet said:
You mean add an extra time part to the Date criteria?
Click to expand...

i mean somehow prevent the next date to come till 1:45am :O - i sound so weird lol. first I wanted to go back in time and now I want to stop time!

their shift ends at 1:45am. I want the same date to show up from 5:45am to 1:45am!! as that is considered to be 1 day when it comes to business here :/
 
umair434 said:
first I wanted to go back in time and now I want to stop time!
Click to expand...
Trying to act like a supreme being :p


umair434 said:
i mean somehow prevent the next date to come till 1:45am

their shift ends at 1:45am. I want the same date to show up from 5:45am to 1:45am!! as that is considered to be 1 day when it comes to business here :/
Click to expand...
I still don't follow. Can you give me an example or a scenario.
 
okay!! here's a situation

John is a night shift employee! he comes in at 4:00pm and works till 1:45am!! He will be using this application to enter data for his shift. After 12:00am, the date text box will show a new date on the subform where John will be entering data. That means any data entered after 12:00am by John will go on to next record (with a new date).

I want to avoid that. Everything that john enters from 4:00pm to 1:45am should go to the same date!!

since Im using date() function, this is not possible, or is it :S
 
They all have different shifts right? Different start and finish times. Give me three more example shifts.

Not a scenario, just the shifts ;)
 
two shifts!

first shift from 5:45am to 3:45pm

second shift from 3:45pm to 1:45am

4 employees for each shift
 
There's a time element in your date field too right?

Plus do you have a Shift Start field somewhere in any table related to the employee?
 
You must log in or register to reply here.

Similar threads

A
Label not showing on subform if value is 0
2
Replies
32
Views
1,033
isladogs
isladogs
D
Subform requery is starting a new record
Replies
2
Views
856
dullster
D
J
Solved Troubleshooting Subform
Replies
10
Views
388
jamesave
J
D
Solved Subform adding blank record
Replies
15
Views
1,233
dullster
D
A
Form with subform in transaction
2
Replies
39
Views
1,644
Albert D. Kallal
A

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom