Results 1 to 8 of 8
  1. #1
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73

    Command button that will check for the presence of record with a field of current date

    and then create a new record if one doesn't already exist. Or open the existing record if one already exists.

    Hello. I would like to create a daily log database. It will be used by multiple people who work independently. I need to prevent them from creating multiple records for each day, one per user and/or per entry. There can only be one record per day, that everyone uses.


    Ideally, I would like the command button that they click to enter the data to be able to check to see if a record in the table that holds this data already has a record with the current date in it. If the current date exists in the date field of that table. If a record that matches this exists, it will open the record and data can be entered. If not it will create a new record, and the current date will populate the field for that. And data will then be entered. It will also be available for additional entries throughout the day.
    Is this possible in access?
    I would like the most efficient way possible. The best I can come up with is a command that takes the current date and does a search through the table holding the data. If it finds a record with today's date you see it in the results. Then the result would be clicked on to open it and data would be entered. If nothing comes up, click on another command button to create a new record. But that would have to only be able to be executed if there is not a duplicate record.
    Does this sound like a good way of doing it? Is there a better, more efficient way of doing it?
    Thanks in advance!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    it is certainly possible, whether it is a wise thing to do is another matter

    in your log table ensure the default value for the date field you mentioned is set to Date(). Also ensure the field is indexed, no duplicates

    in your form (I presume it is a single form, not a continuous one), the recordsource would be something like

    Code:
    SELECT *
    FROM myTable
    WHERE datefield=Date()
    and the form data properties would be

    allowadditions=true
    allowdeletions=false
    allowedits=true
    allowfilters=false

    in the form format properties, set the navigation buttons to false

    and disable the datefield control so users cannot change the date


    that should be sufficient without any code

  3. #3
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73
    Hello Ajax. Thank you for your input. It looks like you saved me a bunch of time by preventing troubles that I would have had to deal with down the road. As far as the approach I have in mind, do you think that's a decent solution?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    So the data could be modified by anyone and there is no history of changes.

    Ajax's suggestion is fine for just data entry of new records on the current date. But does not have feature for searching and retrieving existing older records for review/edit.

    If you want flexibility to enter new records as well as edit older existing with one form, design will be more elaborate and most likely involve VBA coding.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Without knowing the purpose not possible to say. My suggestion meets the needs per the thread title but not sure your table meets the needs of logging. You normally log actions by users that you want to track - typically add/amend or delete data so you would want to know date, time, user and the pk of the affected record plus perhaps what was changed. So don’t see how a single log record per day can achieve that

  6. #6
    sheckay is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    May 2022
    Posts
    73
    I'm sorry, I'm not logging actions or user activity. It would just be a daily journal of users' observations. A log of that kind of data, manually provided by the user.

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    same thing applies. two observations would have two records. So is the idea user1 enters an observation, then user2 overwrites it? or adds something below the previous observation? or do you have a column for each user (which is a bad design)

    The risk you have is if user1 opens the form and makes a change but doesn't close the form, they will have locked out the other users. Or perhaps they just open the form and don't make a change. User2 then opens the form adds an observation and then closes the form. User1 then enters their observation and closes the form. User1's observation will then overwrite user2's observation - they may get a 'record has changed' message and should either have the option of overwriting or aborting.

    What is wrong with having multiple observations by multiple users in different records all with the same date?

    Still not enough info to be certain but in answer to

    As far as the approach I have in mind, do you think that's a decent solution?


    my instinct is No

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Options:

    1) a record for each user observation

    2) one record with memo (long text) field set as AppendOnly Yes and then code using ColumnHistory to view all entered observations

    I recommend option 1.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 04-01-2022, 08:10 PM
  2. Replies: 9
    Last Post: 09-17-2013, 11:08 PM
  3. Check for the presence of a picture.
    By sergran in forum Programming
    Replies: 4
    Last Post: 08-08-2013, 09:22 AM
  4. Replies: 13
    Last Post: 12-17-2012, 07:52 PM
  5. Replies: 3
    Last Post: 08-16-2012, 11:25 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums