Results 1 to 8 of 8
  1. #1
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283

    Button push pops prompt then returns record on form

    I am building a sign in -sign out database and am trying to get it to work right. I would like the user to hit the Sign out button, then be prompted to enter a badge number, then once they enter it return the data in the form for them to update the sign out date/time. I have a query created with the prompt for badge number and the Edit date is null criteria to return the correct record but it returns in the query datasheet result and not in a form result. I am not sure how to do this and need some help. Or even if they entered the badge number at the prompt and it displayed the info and said is this you and they hit ok and it set the exit date/time automatically.



    Also I can't seem to get the time to show up in my date time fields for Entry and Exit Date. It would be cool to get the Entry date and time autopopulated when the record is touched but I am working on that as well.

    Any insight would be awesome

    I added the database as well

    Thanks
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Sign Out button frm_main is designed to open query, not form.

    Time component does not show because it is not included in field value. Why are there Enter_Time and Exit_Time fields in table? They are not on form. Advise date and time be saved as a single value. Set Enter_Date DefaultValue property to Now(). Use code to set the Exit_Date value with Now(). Lock both textboxes and set TabStop to no. Change background color to grey.

    Because qry_main does not have calculations nor filter nor joins, really isn't necessary to use as RecordSource for form - just reference table.

    Textboxes for Badge_No and Purpose on signout form are showing error "No such field in the field list". Signout form does not need to be bound to table. Only need textbox to enter badge number and a button. I only use VBA so code would be like:

    Private Sub btnExit_Click()
    CurrentDb.Execute "UPDATE tbl_Main SET Exit_Date = Now() WHERE Badge_No='" & Me.tbxBadge & "' AND Exit_Date IS NULL"
    End Sub

    Embedded Macro could call an UPDATE action query object that references the form textbox. I advise against using query popup prompt parameters because they cannot be validated. Can validate textbox input - could even use a combobox that only offers valid badge numbers for selection.
    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.

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please have a look at the attached file.
    Cheers,
    Attached Files Attached Files
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by Gicu View Post
    Please have a look at the attached file.
    Cheers,
    Thank you for the help. After I enter the badge number I get an error saying you can;t assign a value to this object and in debug it highlights the me.exit_time -= Now line


  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Move the form_open code to form_load

    Code:
    Private Sub Form_Load() 
        If IsNull(Me.Exit_Date) Then
            Me.Exit_Date = Now()
            Me.Exit_Date.ForeColor = vbRed
            Me.Exit_Date.SetFocus
        End If
    End Sub
    Last edited by davegri; 04-28-2021 at 11:31 AM. Reason: *#*%%@ blasted format

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Sorry, yes there is where it should be (I was playing with the Open event to cancel it if there was no record but I moved that to the switchboard form and forgot to move this code to the load event).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  7. #7
    mindbender is offline Competent Performer
    Windows XP Access 2007
    Join Date
    May 2013
    Posts
    283
    Quote Originally Posted by davegri View Post
    Move the form_open code to form_load

    Code:
    Private Sub Form_Load() 
        If IsNull(Me.Exit_Date) Then
            Me.Exit_Date = Now()
            Me.Exit_Date.ForeColor = vbRed
            Me.Exit_Date.SetFocus
        End If
    End Sub

    Thanks for all you help everyone. I am now where I want to be.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You're welcome, good luck with your project!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 4
    Last Post: 08-25-2019, 12:16 PM
  2. Replies: 6
    Last Post: 07-01-2019, 08:37 AM
  3. Replies: 2
    Last Post: 01-26-2018, 02:43 PM
  4. Prompt Pop Up Alert Message on clicking Form Button
    By Alexandru Human in forum Forms
    Replies: 2
    Last Post: 09-28-2015, 07:12 PM
  5. Replies: 10
    Last Post: 04-10-2014, 08:24 AM

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