By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,762 Members | 1,838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,762 IT Pros & Developers. It's quick & easy.

Checkbox changes all records and does not keep it same selection.

P: 14
Hi Everyone

I am building a small Form that links some information with some checkbox, it means, that when I have a TRUE value in the file, this action will trigger that another field, located in a table that was brought into a query, will be overwrite with a text "-1"

so when I tick it, it will have "-1" and no tick means "-1".

I wrote this in VB:

If (East_Checkbox.Value = True) Then
East = "-1"
Else
East = "1"
End If

The problem is that when I start changing between records, the value stays as the previous way, so if in the previous record it had a tick, in the next one it will also have it, and if in the third one I want to take the tick, it will also remove it in the other two records.

Is there any code I can write so that the value gets saved and do not changed in the next records?

I tried to paste the same code I wrote in the AfterUpdate but still it does not work.

Thank you.
Jul 25 '17 #1

✓ answered by PhilOfWalton

OK solved.
I have only done the code for the North_Checkbox, the rest I leave to you.

Firstly if you want to use a Null value, you must set the Triple State to "Yes"

You don't want the On Click Event
You do need an After Update Event which sets the value
Expand|Select|Wrap|Line Numbers
  1. Private Sub North_Checkbox_AfterUpdate()
  2.  
  3.     If North_Checkbox = True Then
  4.         North = -1
  5.     ElseIf North_Checkbox = False Then
  6.         North = 1
  7.     ElseIf IsNull(North_Checkbox) Then
  8.         North = Null
  9.     End If
  10.  
  11. End Sub
  12.  
You Also need an On Current of the form to display the check box

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.     If North = 1 Then
  4.         North_Checkbox = False
  5.     ElseIf North = -1 Then
  6.         North_Checkbox = True
  7.     Else
  8.         North_Checkbox = Null
  9.     End If
  10.  
  11. End Sub
  12.  
Hope that solves your problem

Phil

Share this Question
Share on Google+
24 Replies


PhilOfWalton
Expert 100+
P: 1,430
Have a look at https://bytes.com/topic/desktop-soft...-another-field

That should give you some pointers

Phil
Jul 25 '17 #2

P: 14
Hi Phil

Thanks for the link, I tried to change the formula but with no result,
please check the image, if you see the sequence, if on the 3rd image I change the checkboxes, on the fourth image, when I return to the previous record (record 2) you see that the checkbox is ticked even though I did not do it.



What I would like to achieve is that my checkboxes stay in the position that I selected, and if the next record is changed, the previous should not change.

Thank you again, and sorry I am just starting with Access.
Jul 26 '17 #3

PhilOfWalton
Expert 100+
P: 1,430
I am unclear as to whether your check boxes are bound or not. So please send details of your table design and let me know about the binding.

Additionally, in the case of check boxes, 0 = False, any other value (Including -1 or 123) = True

So even if your code were to work, you should be using -1 and 0 without quotation marks as you want a binary value rather than a text value

Phil
Jul 26 '17 #5

P: 14
Hi Phil

Thanks for the following

I use a program Called revit, in which I created a yes no checkbox, but once I export it to Access, instead of being a YES/NO, it changes to Data Type Numbers, which -1 is check and 1 not check. This program exports the data into native access file.

What I want to create is a Form where I have options of having checkboxes, that when is checked, the value will change in the table, and it will not be displayed in the Form or the Report so that later that information can be imported back into that program.

I cannot add any more Field Names into the Table I work with, since later on the program will not allow me to import the table, basically I cannot touch the Table, only to add information.

If the user of the Form places a tick in the checkbox in one of the records, I would like to keep that checkbox intact when moving to the next record.

As far as I see, the information changes once is clicked, but in the case if the previous record was not checked and no information has been written in the next one, you would have to tick, then untick and then tick again to write the information.

It would be the best to have as initial value nothing, but also the information changed as nothing.

Does that mean that maybe by creating a table for only this checkboxes and then giving the control source to the name of each of the field names this may run?

I attach download link so you can see what I developed in access.

https://uploadfiles.io/1gfpr


than you again.
Jul 26 '17 #6

PhilOfWalton
Expert 100+
P: 1,430
I am having problems with your links

Please use the types available on this website. You can show images and send attachments.

Phil
Jul 26 '17 #7

P: 14
Hi Phil

I attach my file, maybe it would be easier to see how it works

Thanks
Attached Files
File Type: zip Database_Test_V4.zip (64.8 KB, 36 views)
Jul 26 '17 #8

PhilOfWalton
Expert 100+
P: 1,430
Will look at it later this evening.

In the mean time, can you give a description of what the DB is for. I am concerned that a lot of text information seems to be duplicated in different tables esp. in Rooms & RoomAssociations.

Also, 3 minor points.
1) Name is a reserved word & should be avoided (Table Rooms & Levels)
2) I advise against spaces in field names The need square brackets[] round them, and that is something else to go wrong.
3) ID is not a good name for a field. RoomID, LevelID, RoomAssociationID is much clearer.

Phil
Jul 26 '17 #9

P: 14
Hi Phil

The Database is created automatically from the program called Revit, is something called Building Information Modelling,mostly for management of all the pieces, elements, and quantification required for construction. All the data shown in the records comes directly from the program, and only few fields can be modified, those fields are the one that I created inside the program(Revit), but the general structure of the database cannot be changed since otherwise when importing back the information of the program may not work.

I tried to modify for example the value of north, south, west and east to have a different data type, like for example changing it from number to yes/no parameter but when importing it again back to the program, this fields are not recognized, and all the information is lost.

The ID is created automatically from the program, meaning that if there is a wall that has it's own id, or floor or window, so changing the id to another value again is not possible, since if I try to bring back the information, nothing will be brought back to the program.

The main goal is to have a simple-easy-to-work interface with access, that latter on can be imported to this program.

Many of the Names with spaces comes from the program, I have been trying to create my values based on names without spaces.

Also the relationships come automatically from the project, I have tried to modify them but again the program do not recognize it when bringing it back.

What I last attach is a short version of the file, I send you
the link of the native file exported from the program.

https://we.tl/57pcnhbOHY

That is why my point is to only modify certain values with the information that this program allows. like having a checkbox that writes back in the way revit allows information.

Thank you.
Jul 26 '17 #10

PhilOfWalton
Expert 100+
P: 1,430
Wow, I thought I wrote databases with lots of tables, but in comparison, I'm a complete amateur.

What you want is perfectly do-able, but there is some confusion that needs resolving.

In your table Rooms, then values that appear for the various N,S.E.W include:-
Null, 0, -1, 1, 2.
Your initial post indicates that only 1 and -1 are acceptable. Please clarify.

Phil
Jul 26 '17 #11

P: 14
Hi Phil

Basically I just need -1, 1, and null.

When I import it to my program(Revit), -1 is yes and 1 is no.

I do not know why 2 is there, it shouldn't be there, neither 0, i believe it was imported from my program as Triple state.

Thank you very much

Sam
Jul 26 '17 #12

PhilOfWalton
Expert 100+
P: 1,430
OK solved.
I have only done the code for the North_Checkbox, the rest I leave to you.

Firstly if you want to use a Null value, you must set the Triple State to "Yes"

You don't want the On Click Event
You do need an After Update Event which sets the value
Expand|Select|Wrap|Line Numbers
  1. Private Sub North_Checkbox_AfterUpdate()
  2.  
  3.     If North_Checkbox = True Then
  4.         North = -1
  5.     ElseIf North_Checkbox = False Then
  6.         North = 1
  7.     ElseIf IsNull(North_Checkbox) Then
  8.         North = Null
  9.     End If
  10.  
  11. End Sub
  12.  
You Also need an On Current of the form to display the check box

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.  
  3.     If North = 1 Then
  4.         North_Checkbox = False
  5.     ElseIf North = -1 Then
  6.         North_Checkbox = True
  7.     Else
  8.         North_Checkbox = Null
  9.     End If
  10.  
  11. End Sub
  12.  
Hope that solves your problem

Phil
Jul 26 '17 #13

P: 14
Hi Phil,

Yes it works!!!!, I will run it in the next values.

I extremely appreciate your help, thank you so much!!!

I will continue with the next values and let you know later how it went with the program.

Again Thank you
Jul 26 '17 #14

P: 14
Hi Phil

Following the same topic we have been discussing before, how would you make a formula where there is a checkbox inside the Form, that will not be linked to anything, but its status will remain if it was ticked or unticked when moving to next records?

Thank you.

Sam
Jul 28 '17 #15

PhilOfWalton
Expert 100+
P: 1,430
Just add an unbound check box. As there is no data associated with it, nothing changes as you move between records.

Phil
Jul 28 '17 #16

P: 14
Hi Phil

Well, I actually tried that previously, but if you see the image, even if there is no data associated, it changes from one record to another one, like what I had at the beginning of our conversation, I think is related to The Form that has to re-query the On Current Event, but I tried a couple of times to write a code and with no success.

Please check the image I attach.




Thank you
Jul 28 '17 #17

PhilOfWalton
Expert 100+
P: 1,430
What code are you running on the On Current. If you re-query the form, you should be back to the first record.

It works for me. Are you sure you have added an unbound check box?

Phil
Jul 28 '17 #19

P: 14
I tried to create a code similar to what you previously sent me but without linking it with any other value and I was placing it in Form Event On Current, but I was not able to generate it.

I actually have been trying with two new empty databases to have an unbound check box but when I move to the next record, the tick changes, and even when I close the form, when you open it again, the information of what was ticked, is lost and the button returns to null option.
Jul 28 '17 #20

PhilOfWalton
Expert 100+
P: 1,430
OK, I am confused.

You say you want a check box that retains it's value from record to record. If so there should be no code associated with it. When you first open the form, it's value will be Null, but once set it shouldn't change.

If you don't want the initial value to be Null, set the default value to True or False, but note that a new record will keep the current value of the check box.

Phil
Jul 28 '17 #21

P: 14
Hi Phil

About check box, yes I have no code associated, yet when moving from record to record, the value is lost.

I created a quick video so you can see what is the process I follow to create a checkbox and to see what is happening.

https://streamable.com/2e7ft

Thank you

Samuel
Jul 28 '17 #22

PhilOfWalton
Expert 100+
P: 1,430
Samuel, I like the video.

Obviously there is a misunderstanding.

You said you wanted a check box that retains it's value from record to record. So if you set the check box to True, it will be True for ALL records until you set it to False when it will appear False for ALL records.

Don't forget it isn't changing. It is similar to a label, once it's set it doesn't change.

So what do you really want it to do?

Phil
Jul 28 '17 #23

P: 14
I would like to have that unbounded checkbox just for the user to activate it, if he wants to have something from the list, he can check it and later have it for a next value deactivated.

Let me show you another video.

https://streamable.com/ks9so

My point is that, since many values come from the Program I bring everything from (Revit), Creating a Parameter so that it can be listed in rooms, takes some time, and actually that information should not necessarily be shown, is just for the user to say, yes I selected the option and I want this element with a specific characteristic, in the case of the example, having the check box for yes no, and if yes, then the user select option fire rating of a wall for 60 min.

For me is not necessary to bring back the information to the program if the user wanted an option or not, what I use is the values of the option chosen, if there is nothing selected, I understand that the user does not want any special requirement.

That is why an unbound check box would fit for me and that has the ability to store the information from record to record.

Thank you.
Jul 28 '17 #24

PhilOfWalton
Expert 100+
P: 1,430
I have sent you a private message as this is getting complicated.

Phil
Jul 28 '17 #25

Post your reply

Sign in to post your reply or Sign up for a free account.