Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-15-2019, 03:44 AM   #1
Barbshop
Newly Registered User
 
Join Date: Aug 2018
Posts: 7
Thanks: 4
Thanked 0 Times in 0 Posts
Barbshop is on a distinguished road
Reset checkbox for a single record

Hi, I'm not a programmer, but have developed an access database to be used by church volunteers.


One table and corresponding form is used to enter basic information about a family coming for assistance at the church. The first time the family visits the church there is a checkbox called "new family" that is checked to indicate this is their first visit. If the family returns for a subsequent visit, the checkbox needs to be unchecked as they are no longer a "new" family. Currently the data entry person is asked to do this manually. They often forget. Is their a simple way to have the checkbox revert to unchecked when the record is opened a second time?


Sorry if this seems a bit vague.


Thanks for any advice.


Barbshop

Barbshop is offline   Reply With Quote
Old 08-15-2019, 04:02 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,336
Thanks: 158
Thanked 1,699 Times in 1,670 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Reset checkbox for a single record

Do you record a last visit date in your record?
That might be a better route than a check box, as it gives you a data point that could drive a notification flag on the screen, and also encourage updating of existing records.

Better still, you could add a visit history as a sub table for that family, then you would have useful data to analyse. This could be added to automatically every time the visit form was updated?
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Barbshop (08-15-2019)
Old 08-15-2019, 04:12 AM   #3
Barbshop
Newly Registered User
 
Join Date: Aug 2018
Posts: 7
Thanks: 4
Thanked 0 Times in 0 Posts
Barbshop is on a distinguished road
Re: Reset checkbox for a single record

Thanks Minty. Let me explain. This is a food pantry system, and the pantry produces a report after each food pantry day. That report needs to track the number of "new" families who visited that day. The field called "new family" is on the master intake record form. This form has all the information about the customer and also contains fields tracking their specific visit dates (called "service dates").



When the customer returns for a subsequent visit, they should not be counted as a "new" family. Therefore the need to remove the check mark in that field. Currently I have a message box that pops up when that filed has focus that reminds them to uncheck it. I was hoping to automate this somehow.


I've attached an image of the Intake Form

I'm probably over complicating this. Thanks for bearing with me.
Attached Images
File Type: png FPS_Intake_Form.png (57.2 KB, 6 views)


Last edited by Barbshop; 08-15-2019 at 04:48 AM.
Barbshop is offline   Reply With Quote
Old 08-15-2019, 05:29 AM   #4
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,336
Thanks: 158
Thanked 1,699 Times in 1,670 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Reset checkbox for a single record

Okay - In that case you can simply count the service dates and that will tell you that they are a new or existing family. In fact the need for the check box sort of disappears.

In the current event of the form you can use something like;


Code:
If DCount("*","YourServiceDateTable","CustomerID = " & Me.CustomerID) >1 Then
      Me.ChkNewCustomer = False
End iF
You'll need to use the relevant fields and control names, but hopefully that will get you started.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
Barbshop (08-15-2019)
Old 08-15-2019, 05:34 AM   #5
Barbshop
Newly Registered User
 
Join Date: Aug 2018
Posts: 7
Thanks: 4
Thanked 0 Times in 0 Posts
Barbshop is on a distinguished road
Re: Reset checkbox for a single record

Thanks much Minty, that sounds like it should work. Let me give it a try.
I love clever people who are smarter than I am (there are a lot of them.)


Minty, I apologize, but I'm much better at singing than I am at coding. I can't seem to make your suggestion work. I think I'm not putting it in correctly. Below is what is already in the On Current Event for this form:


Option Compare Database

Private Sub cmdPrint_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[Intake_ID] = " & Me.[Intake_ID]
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End If
End Sub


Private Sub Command122_Click()

End Sub

Private Sub Form_Current()
Me.Combo123.Value = Null
End Sub

Private Sub Form_Load()

End Sub


Private Sub Form_Open(Cancel As Integer)

End Sub


I'm not sure where to insert the additional code. Thank you for being patient with me.


Last edited by Barbshop; 08-15-2019 at 07:16 AM. Reason: Additional Information
Barbshop is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple Records from single form based upon checkbox values jeffj803 Forms 14 03-07-2015 09:36 PM
Storing a single record in a single dimension array? prabha_friend Modules & VBA 2 09-08-2014 02:02 AM
[SOLVED] Sending a single Email to a single record in a query Kregg Modules & VBA 9 05-15-2014 09:45 AM
Add multiple checkbox names into single table field thawstone Forms 5 08-19-2013 06:43 AM
Can I build a single use, single record form? Mimadocken Forms 17 03-28-2012 10:34 PM




All times are GMT -8. The time now is 01:02 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World