Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    4

    Unanswered: How To Write Vb Code For This?

    Hi Guys/Experts..

    Currenly i am having a testing application.:
    Database coloums : ID, Createddate, TimesheetDate, TagDate
    Form fields : Createddate(not visible, auto today date), Timesheetdate(need user input), tagDate(not visible, for later use), radioButton(user choose New or Add Record) option.

    What i want is, when user fill up "TimesheetDate" in the form and if the the "timesheetDate" already exist in the database, than the system automatically store today's "CreatedDate" into the original record's "TagDate".

    Looking for VB code something like:

    Code:
    If Me!Timesheetdate = [TimeSheetTable].[TimeSheetCoulum] AND Me!radioButton="AddRecord"Than update [Timesheet].[TagDate] as Me!Createdate] ]
    Included the created databse and the interface.
    Kindly need all of you help to come up with such a code.
    Thank you
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use the DLookUp or the DCount function:
    Code:
    If DCount("TimesheetDate", "TimeSheetTable", "TimesheetDate = #" & Format(Me.Timesheetdate.Value,"mm/dd/yyyy") & "#") > 0 Then    ' The date already exists.
    Have a nice day!

  3. #3
    Join Date
    Sep 2011
    Posts
    4
    Hi Sinndho,

    The code not taking conditions from the "comboBox". And once identify the particular record which meet the criteria : same date, and "add record' option selected in comboBox, how to WRITE the "currentDat"e into "TagDate" ??


  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by townguy View Post
    The code not taking conditions from the "comboBox".
    I don't understand, what's this supposed to mean?

    Quote Originally Posted by townguy View Post
    And once identify the particular record which meet the criteria : same date, and "add record' option selected in comboBox
    Which option and from which combo? You mentionned a radio button in your former post, not a combo.

    As for changing the date already present in TagDate to the one contained in CurrentDate, I would first change the current record of the form, so it becomes the one with the already existing date:
    Code:
    Dim rst as DAO.RecordSet
    Set rst = Me.RecordsetClone
    rst.FindFirst "TagDate=#" & Format(Me.CurrentDate.Value, "mm/dd/yyyy") & "#"
    Me.Bookmark = rst.Bookmark
    rst.Close
    Set rst = Nothing
    Then I would simply use:
    Code:
    Me.TagDate.Value = Me.CurrentDate.Value
    But where does CurrentDate come from, I don't see any in your description?
    Quote Originally Posted by townguy View Post
    Currenly i am having a testing application.:
    Database coloums : ID, Createddate, TimesheetDate, TagDate
    Form fields : Createddate(not visible, auto today date), Timesheetdate(need user input), tagDate(not visible, for later use), radioButton(user choose New or Add Record) option.
    Have a nice day!

  5. #5
    Join Date
    Sep 2011
    Posts
    4
    Hi Sinndho,

    I am extremely sorry for the confusion.

    Mistakenly say "comboBox". Actually i am referring to my "Radio Button".

    AND

    referring to "CreatedDate" when i mention "CurrentDate".
    The correct one is "CreatedDate"

  6. #6
    Join Date
    Sep 2011
    Posts
    4
    And Sinndho,

    the requirement is not populate the form with existing records.

    The scenario i can give :

    When the user key in the timesheet date and choose "New" and click the submit button , the new record goes into the database into database as:

    Created Date || TimeSheet Date || Project Code || Hours Spend || Tag Date -------------> original
    ....13/09/11..||.....13/09/11........||......ASK123.....||......8............ ..||...NULL


    If the user key in timesheet date and choose "Add" and click submit button, than the function able to go thought the database find the old record and update "NULL" with "Tag Date" new createdDate(assume that the record created for same "TimesheetDate" in future:

    Created Date || TimeSheet Date || Project Code || Hours Spend || Tag Date -------------> original
    ....13/09/11..||.....13/09/11........||......ASK123.....||......8............ ..||...21/09/11

    And at same time create new row of record as:
    Created Date || TimeSheet Date || Project Code || Hours Spend || Tag Date
    ...21/09/11..||.....13/09/11........||......ASK123.....||......8............ ..||...NULL

Posting Permissions

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