Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2014
    Posts
    5

    Post Unanswered: [Code Request] prevent adding new record, but after a period of date

    Dear friend Hello everybody,
    Could you please help me to find a solution for this: -

    I want code with message that can prevent user to adding a new record for an employee within specific of date through a form in the Ms Access database, but after a period of date i can,
    For example (after two months of the last record on the main table i can add the new record, otherwise the message will popup tell the user that this employee didn't complete tow months of last adding)

    I appreciate any help with this problem
    Many thanks in advance.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so place some coide in the forms BEFORE INSERT event, that checks if it meets your criteria

    not knowing you db schema / table design its hard to say, but I think you will probably need to use a dlookup or dmax

    you'd probably need to set the domain function parameters to
    expression to be the date of the row you are interested in
    the domain will be the table (or query containing the relevant data
    the criteria to be the user name/employee's name

    if the retruned value is within your two mionth period (use dateadd/datesub to determine that. set a message box AND set the cancel variable = TRUE. cancel is the return parameter in the before insert event, which if = true stops a row being inserted
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2014
    Posts
    5
    Quote Originally Posted by healdem View Post
    so place some coide in the forms BEFORE INSERT event, that checks if it meets your criteria

    not knowing you db schema / table design its hard to say, but I think you will probably need to use a dlookup or dmax

    you'd probably need to set the domain function parameters to
    expression to be the date of the row you are interested in
    the domain will be the table (or query containing the relevant data
    the criteria to be the user name/employee's name

    if the retruned value is within your two mionth period (use dateadd/datesub to determine that. set a message box AND set the cancel variable = TRUE. cancel is the return parameter in the before insert event, which if = true stops a row being inserted
    I appreciate your reply but i am a beginner in Ms Access, i have small project for my shope and i want to complete it, i don't know how to program your suggestion inside the form, if you have any example or templet for what you mean it will help too much,,,
    Thanks again

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so in your forms BEFORE INSERT event

    Code:
    dim MostRecentDate as date
    #ok so find out if we have a row in the table for this employee. for that we need to know the id of the employee
    # the name of the table, the name of the column that contains the piece of date specific information
    MostRecentDate = dmax(mydatecolumn, 'mytable', 'EmployeeID = ' & avaraibaleholdingtheemployeeid)
    #DMAX will return the highest value for the specified date column for the specified employee id in the table mytable
    #possible return values are NULL (where no row(s) were found for this employee
    #or the most recent date 
    #if the most recent date isn't NULL and the returned date + 2 months is greater than todays date then don't allow additions
    if MostRecentDate is not NULL and dateadd("mm",2,MostRecentDate) > date() then
      cancel = vbtrue
    else #otherwise we can add a row
      cancel = vbfalse
    endif
    you will of course need to specify:-
    the name of the table in place of mytable
    the name of the datecolumn you are interested in (in place of mydatecolumn
    the name of the variable holding the employee ID. in palkce of avaraibaleholdingtheemployeeid

    if employee ID is string then use
    Code:
    MostRecentDate = dmax('mydatecolumn', 'mytable', 'EmployeeID = "' & avaraibaleholdingtheemployeeid & '"')
    as string values must be delimited by a pair of ' or "
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2014
    Posts
    5
    Quote Originally Posted by healdem View Post
    so in your forms BEFORE INSERT event

    Code:
    dim MostRecentDate as date
    #ok so find out if we have a row in the table for this employee. for that we need to know the id of the employee
    # the name of the table, the name of the column that contains the piece of date specific information
    MostRecentDate = dmax(mydatecolumn, 'mytable', 'EmployeeID = ' & avaraibaleholdingtheemployeeid)
    #DMAX will return the highest value for the specified date column for the specified employee id in the table mytable
    #possible return values are NULL (where no row(s) were found for this employee
    #or the most recent date 
    #if the most recent date isn't NULL and the returned date + 2 months is greater than todays date then don't allow additions
    if MostRecentDate is not NULL and dateadd("mm",2,MostRecentDate) > date() then
      cancel = vbtrue
    else #otherwise we can add a row
      cancel = vbfalse
    endif
    you will of course need to specify:-
    the name of the table in place of mytable
    the name of the datecolumn you are interested in (in place of mydatecolumn
    the name of the variable holding the employee ID. in palkce of avaraibaleholdingtheemployeeid

    if employee ID is string then use
    Code:
    MostRecentDate = dmax('mydatecolumn', 'mytable', 'EmployeeID = "' & avaraibaleholdingtheemployeeid & '"')
    as string values must be delimited by a pair of ' or "
    Many many thanks for the effort,
    i have already apply the code on my Project BUT i think there is some mismatch & error inside code,
    could you please take a look to the DataBase on the attachment to see the error

    Thanks Again my dear
    Best Regards
    Attached Files Attached Files

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    What fault / error are you getting
    what is or isnt happening
    what steps have you taken to diagnose the problem(s)?
    What have you investigated and eliminated.

    At first glance im going to suspect you will need to replace the # with '. I ve been experimenting in python this weekend and it uses # as the comment symbol, access uses '
    Last edited by healdem; 12-14-14 at 16:45.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2014
    Posts
    5
    Quote Originally Posted by healdem View Post
    What fault / error are you getting
    what is or isnt happening
    what steps have you taken to diagnose the problem(s)?
    What have you investigated and eliminated.

    At first glance im going to suspect you will need to replace the # with '. I ve been experimenting in python this weekend and it uses # as the comment symbol, access uses '
    What fault / error are you getting?
    compile error
    Type mismatch

    what is or isnt happening?
    The code is not running

    what steps have you taken to diagnose the problem(s)?
    modifying the code according to my data base that i have already posted before to be:-

    Code:
    Dim MostRecentDate As Date
    MostRecentDate = DMax(txtDate, "EMP", "EmployeeID = " & ID)
    If MostRecentDate Is Not Null And DateAdd("mm", 2, MostRecentDate) > Date Then
    Cancel = vbTrue
    Else
    Cancel = vbFalse
    End If
    What have you investigated and eliminated?.
    eliminating all the unnecessary hashing code to fix the code inside the Visual Basic inside MS Access , but not succeeded

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    On which line
    The is not null probably should be
    Code:
    if not isnull (MostRecentDate)
    It could be that the data type for mostrecentdate should be variant which is more null friendly.

    Txtdate in the dmax should probably be delimited 'txtDate'
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Dec 2014
    Posts
    5
    Thanks for the help Guys

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •