By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,946 Members | 773 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,946 IT Pros & Developers. It's quick & easy.

counting records

P: 4
Newbie here - I need help. I am trying to calculate the number of time a particular date was requested. On the 5th request the user should get a message that states that the date is not available.

I have the following tables:

ScheduleTable
*Emp ID
*RequesteedDate
*Confirm
etc..

and

DateTable
*Date
*Tally (number of times requested)

Can anyone tell me how to do this?
Oct 22 '06 #1
Share this Question
Share on Google+
4 Replies


PEB
Expert 100+
P: 1,418
PEB
Hi where the user tries to introduce this date in a form? ;)

Or elsewhere?

If in a form this calculation need a VB to do the check and show the message...

In your field in Before Update property choose event procedure and type:

If dcount("[Mydate]", "Mytable","[Mydate]=Cvdate('"+Me![YourNewDate]+"')")>4 then
Msgbox "PLS STOP"
Cancel=true
End if

So you need to change some things:
Mydate is the field in the table where you do a search
Mytable is the place where you store your data
YourNewDate is the name of the control where you introduce your new date

:)
Oct 27 '06 #2

P: 4
Thanks for the reply. I finally have that part working but now I have another problem. The employee enters their employee ID in the form EmployeeIDInput. I need to take this ID and pull thje record from the Employee Table, which I have done with no problem. this table is has a one-to-many relationship with the schedule table. So one employee may have several days scheduled off. The form where I want to view this information is called PTO Request Date (which has fields from the employee table and the subform from the schedule table).
It tells me that the form PRO Request Date is not found. I suspect there is a step I have forgotten.

Here is what I have so far.

Dim strSQL As String
Dim strSQL_insert As String
Dim strRequest As String
Dim stLinkCriteria As String
Dim testString As String
Dim EMPID As String

EMPID = Me.EMP_ID1
strSQL = "SELECT * " _
& "FROM [EMPLOYEE TABLE] " _
& "WHERE (([EMPLOYEE TABLE].EMP_ID) = '" & EMPID & "') "


Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
Set rsData = dbCurrent.OpenRecordset(strSQL)
testString = rsData.Fields(1)


stLinkCriteria = "Forms![PTORequestForm]!EMP_ID = Forms![Employee ID Input]![EMP_ID1]"
DoCmd.OpenForm "PTO Request Form", , , stLinkCriteria, , ,acWindowNormal

Me.Refresh

rsData.Close
dbCurrent.Close
Set rsData = Nothing
Set dbCurrent = Nothing

Exit_SubmitButton_Click:
Exit Sub

Err_SubmitButton_Click:
MsgBox Err.Description
Resume Exit_SubmitButton_Click


End Sub


Hi where the user tries to introduce this date in a form? ;)

Or elsewhere?

If in a form this calculation need a VB to do the check and show the message...

In your field in Before Update property choose event procedure and type:

If dcount("[Mydate]", "Mytable","[Mydate]=Cvdate('"+Me![YourNewDate]+"')")>4 then
Msgbox "PLS STOP"
Cancel=true
End if

So you need to change some things:
Mydate is the field in the table where you do a search
Mytable is the place where you store your data
YourNewDate is the name of the control where you introduce your new date

:)
Nov 3 '06 #3

Expert 5K+
P: 8,434
It tells me that the form PRO Request Date is not found.
...
stLinkCriteria = "Forms![PTORequestForm]!EMP_ID = Forms![Employee ID Input]![EMP_ID1]"
DoCmd.OpenForm "PTO Request Form", , , stLinkCriteria, , ,acWindowNormal
Is it just me or do things not match up, here? I see three quite different strings which I believe are supposed to refer to the same form. Is this correct?
Nov 3 '06 #4

PEB
Expert 100+
P: 1,418
PEB
And is it:

PTORequestForm
or

PTO Request Form

?

What is the name of your form?
Nov 4 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.