Database event macros

[SA10014321]

I am building a database and I want to be able to enter booking hours into a form, the system calculate the total hours for that month on a specific account and if it goes above 100 stop the booking going through



Can any one help please?

[CJ_London]

in your form beforeupdate event you could use something like

if dsum("hours","myTable","[Account]=" & me.txtaccount & " AND format(bookdate,'mmyyyy')=format(date(),'mmyyyy')) +me.txthours>100 then
msgbox "too many hours"
cancel=true
end if

[ranman256]

In the acct table, you have a MaxHrs field, set to 100.

In the entry form, when you enter the Acct (combo) this also pulls the Max.
have a field that has the sum of past hrs entered. ... Dlookup("[sumOfHrs]","qsHrs1AcctMo")
This query , qsHrs1AcctMo, reads the text boxes,month and acct to sum hrs.

then every entry,check
If txtHrs+txtSum < txtMax then
SaveEntry
else
MsgBox "Max hrs reached."
endif

[SA10014321]

in your form beforeupdate event you could use something like

if dsum("hours","myTable","[Account]=" & me.txtaccount & " AND format(bookdate,'mmyyyy')=format(date(),'mmyyyy')) +me.txthours>100 then
msgbox "too many hours"
cancel=true
end if

&

• ranman256
  
  In the acct table, you have a MaxHrs field, set to 100.
  
  In the entry form, when you enter the Acct (combo) this also pulls the Max.
  have a field that has the sum of past hrs entered. ... Dlookup("[sumOfHrs]","qsHrs1AcctMo")
  This query , qsHrs1AcctMo, reads the text boxes,month and acct to sum hrs.
  
  then every entry,check
  If txtHrs+txtSum < txtMax then
  SaveEntry
  else
  MsgBox "Max hrs reached."
  endif
• 12-03-2015, 05:01 AM
  Ajax
  
  in your form beforeupdate event you could use something like
  
  if dsum("hours","myTable","[Account]=" & me.txtaccount & " AND format(bookdate,'mmyyyy')=format(date(),'mmyyyy')) +me.txthours>100 then
  msgbox "too many hours"
  cancel=true
  end if
  
  Really sorry guys I am completely new at this I have one form which means requires you to book hours against each booking, I want the database to total the hours for the month on each account and then if the hours reaches more than 100 for that month then let me know I need to make the booking on the other account and if the other account is over 100 stop me making the booking at all for that month only. So far I have built the form and done queries that total the amounts on each account would be grateful if you could let me know a solution to this problem and forgive me as I dont understand the abbreviations yet?

[CJ_London]

both of us have provided solutions - we have used abbreviations/aliases because you haven't provided details like table and field names - so for example - myTable - what have you called yours? Substitute it. same goes for all the others