UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> On Click Commands To Flip Y/n, Access 2016    
 
   
rukichiki
post Mar 5 2020, 01:53 PM
Post#1



Posts: 4
Joined: 5-March 20



I am working on a project converting a table to a form. The table provides a list of meetings with two columns "GT and Milestone." Each column offers a yes or no for the row category. This is input into the form. In form view, I would like to add an on click command to change the specific column of a specified row from a yes to a no or vice versa without it changing both columns simultaneously. I am not sure if this would be too difficult to accomplish, but I was thinking of an on-click command to change the "GT" of the clicked row and a double-click to change the "milestone" of the clicked row.
Would appreciate any guidance. Thank you!
Go to the top of the page
 
theDBguy
post Mar 5 2020, 02:09 PM
Post#2


UA Moderator
Posts: 78,481
Joined: 19-June 07
From: SunnySandyEggo


Hi. Welcome to UtterAccess! welcome2UA.gif

It's hard to say what would work for you without seeing your setup, but this might give you an idea.

Me.YesNoField= Not Me.YesNoField


--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post Mar 5 2020, 02:12 PM
Post#3


UtterAccess VIP
Posts: 10,584
Joined: 25-October 10
From: Gulf South USA


welcome2UA.gif

Just curious ... couldn't you just click in the appropriate yes/no control box to change it? Or are you saying the field is text?

HTH
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
rukichiki
post Mar 6 2020, 10:39 AM
Post#4



Posts: 4
Joined: 5-March 20



CODE
SELECT tblMeetingTypes.Type, IIf([tblmeetingtypes].[Milestone]=-1,"Y","N") AS Milestone, IIf([tblMeetingTypes].[GT]=-1,"Y","N") AS [Ground Test]
FROM tblMeetingTypes
ORDER BY tblMeetingTypes.Type;


The form appears as:
Type Milestone GT
Admin Yes.......No
Faculty No.......No
Group No.......Yes

The idea is for a user to be able to click "yes" or "no" and it swap to its opposite.
The field is text

Thank you for the quick responses
This post has been edited by rukichiki: Mar 6 2020, 10:42 AM
Go to the top of the page
 
cheekybuddha
post Mar 6 2020, 10:56 AM
Post#5


UtterAccess Moderator
Posts: 13,042
Joined: 6-December 03
From: Telegraph Hill


Hi,

In the controls' Click() events you can try:
CODE
Private Sub txtMilestone_Click()

  Dim strSQL As String

  strSQL = "UPDATE tblMeetingTypes SET Milestone = NOT Milestone WHERE [Type] = " & Me.txtType
  CurrentDb.Execute strSQL, dbFailOnError
  Me.Recalc

End Sub

Private Sub txtGT_Click()

  Dim strSQL As String

  strSQL = "UPDATE tblMeetingTypes SET GT= NOT GT WHERE [Type] = " & Me.txtType
  CurrentDb.Execute strSQL, dbFailOnError
  Me.Recalc

End Sub


hth,

d

You will need to adjust for the actual names of your controls

--------------------


Regards,

David Marten
Go to the top of the page
 
rukichiki
post Mar 6 2020, 12:22 PM
Post#6



Posts: 4
Joined: 5-March 20



How should I edit to identify for my controls?

Sorry, I am still fairly new to access and coding in general.

Thank you!
Go to the top of the page
 
cheekybuddha
post Mar 6 2020, 12:25 PM
Post#7


UtterAccess Moderator
Posts: 13,042
Joined: 6-December 03
From: Telegraph Hill


What is the name of the control (textbox) that has Milestone as its ControlSource?

What is the name of the control (textbox) that has GT as its ControlSource?

What is the name of the control (textbox) that has Type as its ControlSource?

--------------------


Regards,

David Marten
Go to the top of the page
 
RJD
post Mar 6 2020, 12:54 PM
Post#8


UtterAccess VIP
Posts: 10,584
Joined: 25-October 10
From: Gulf South USA


Hi: Actually, this can be done without an update query. Just create the query with the fields you want (no logic, just the yes/no underlying fields), then deal with the logic on the form. This means you include the yes/no field on the form together with a textbox with the logic. Make the yes/no field not visible and the textbox locked but enabled. Then put the change logic in the On Click event procedure of the text Yes or No control... for example ...

CODE
Private Sub txtMS_Click()
Me!Milestone = Not Me!Milestone
Me!MeetingType.SetFocus
End Sub

I recommend you not use On Click and On DblClick both on the same control, since it tends to react to both when you double click. Just a single click on the visible Y or N (or Yes or No as you wish - you showed it two ways in your posts). The click also sets the focus back to the row "root" of MeetingType.

Note that I changed the field named Type to MeetingType, since Type is a reserved word and can cause you difficulty in some cases.

You also in your posted query named the Milestone field with the alias Milestone. Not a good idea. It can confuse the system. This is eliminated in my approach.

See the demo attached, using data from your posts. No update query required. Click on an N and see Y appear. Click on a Y and see N appear. See if this meets your requirements.

Oh, almost forgot ... welcome2UA.gif

HTH
Joe
Attached File(s)
Attached File  FlipYN.zip ( 23.67K )Number of downloads: 6
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
rukichiki
post Mar 10 2020, 12:01 PM
Post#9



Posts: 4
Joined: 5-March 20



Thank you all for the responses! You've given me some excellent perspectives and insight into how I should go about this. I really appreciate the help.
Go to the top of the page
 
RJD
post Mar 10 2020, 03:36 PM
Post#10


UtterAccess VIP
Posts: 10,584
Joined: 25-October 10
From: Gulf South USA


You are welcome, from all of us. thumbup.gif Good luck with your project. Let us know if we can be of further assistance as you apply one of these solutions.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
tina t
post Mar 10 2020, 05:13 PM
Post#11



Posts: 6,689
Joined: 11-November 10
From: SoCal, USA


as others have shown, there's a way to manipulate the form to achieve what you want to see.

but i'd recommend that you normalize your table. the current structure is storing data ("Milestone", "GT") in fieldnames. what happens when you need a third column, a fourth, etc? you'll have to rebuild everything from the table on up.

i've never regretted the time i spent to normalize something that i thought was likely to "not change". every time i've convinced myself that something wouldn't change, and skipped normalizing, i've invariably regretted it, and kicked myself, at some point down the road. usually when i had to bite the bullet and do it right the second time around, with all the attendant extra work i could have avoided by doing it right the first time.

hth
tina

--------------------
"the wheel never stops turning"
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    11th July 2020 - 01:36 PM