Results 1 to 6 of 6
  1. #1
    Hell_1934 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    25

    Mandatory values in the Form / populating values in the continuous Form after clicking on a button

    Hi,

    In this (# 1000 version of my Project) I have 2 goals, I can't achieve:

    1. After clicking on "Comma" button, I want to populate all "Yes" values of my Complaint
    This Form is tied to EHRReview_Category-Objective Table, where Complaint is the caption of YesNoFk field.
    And the YesNoFk field's values are Yes (1), No(2)

    Click image for larger version. 

Name:	Form.JPG 
Views:	17 
Size:	129.4 KB 
ID:	40378
    Click image for larger version. 

Name:	YesNotbl.JPG 
Views:	17 
Size:	16.3 KB 
ID:	40379



    2. I also want - not to be able to enter a new record, unless ALL values are populated in the Complaint (YesNoFK)
    If there at least 1 empty record I want message "Please enter all Complaints", so that user will not be able to add a new record clicking on the "pencil" button, unless ALL values are filled.
    Click image for larger version. 

Name:	FormNewRec.JPG 
Views:	17 
Size:	98.5 KB 
ID:	40380

    Attached is also an original database

    Thank you, as always.
    Attached Files Attached Files

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Run an update query to change all values.

  3. #3
    Hell_1934 is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    25
    Quote Originally Posted by ranman256 View Post
    Run an update query to change all values.
    Do I have to do it on the [Event Prosedure] associated with the "Comma" button?

    What's the syntax in vba?

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Your db is probably full of 'traps' - minor annoyances for someone trying to help. Like clicking a button to close a form and the whole db closes - poof!
    Or your use of special characters in object names, thus your button code fails because of that, and the fact that you don't wrap such (bad) names in brackets. If the brackets get fixed, the code alters the form record source, which doesn't sound anything like what you asked for, which is to set a field value across a set of records.
    I've stopped there for the moment but I'll poke around some more.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    It took me about an hour to sort through what's involved and come up with this for the button click event:
    Code:
    Private Sub Command36_Click()
    Dim str As String
    str = "UPDATE [EHRChartReview_Category-Objective] SET [EHRChartReview_Category-Objective].YesNoFK = 1 "
    str = str & "WHERE [EHRChartReview_Category-Objective].[EHRChartReviewCategoryFK] = "
    str = str & "forms![ehrchartreview main]!sfReviewDetail.Form!EHRChartReviewCategoryFK"
    DoCmd.RunSQL str
    Me.Requery
    End Sub
    That's about all I've got the gumption for. Please don't be insulted but it's quite difficult to figure out things, partly due to embedded macros, but mainly because the naming is umm, real bad.

    WRT problem 2, I don't know how to use a macro to loop through a form recordset to look for any field that doesn't have a value. I don't even know if macros are capable of that. Assuming there isn't any other way to navigate except for your custom nav buttons, in nav button code, I'd create a form recordset clone, loop through a field looking for Null or zls and exiting the button code if any were found. You'd need to allow for every nav button, because usually they come as a quad set - forward/backward 1 record + to beginning + to end.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Gicu's Avatar
    Gicu is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have a look at the attached and see if that is what you're after. Please note that you might need to apply a patch from Microsoft (if you haven't already done so) to fix an issue with update queries they introduced in a recent update: https://support.office.com/en-us/art...3-f21636caedec


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

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

Similar Threads

  1. combo box values in the Continuous form
    By Shamli in forum Forms
    Replies: 4
    Last Post: 01-14-2019, 06:09 PM
  2. Replies: 1
    Last Post: 03-07-2017, 06:32 AM
  3. Replies: 2
    Last Post: 09-15-2015, 03:08 AM
  4. Replies: 45
    Last Post: 06-26-2015, 09:35 AM
  5. Replies: 7
    Last Post: 09-24-2013, 06:01 PM

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