473,322 Members | 1,719 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

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

24 1953
PhilOfWalton
1,430 Expert 1GB
Have a look at https://bytes.com/topic/desktop-soft...-another-field

That should give you some pointers

Phil
Jul 25 '17 #2
jsmm91
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
1,430 Expert 1GB
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
jsmm91
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
1,430 Expert 1GB
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
jsmm91
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, 64 views)
Jul 26 '17 #8
PhilOfWalton
1,430 Expert 1GB
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
jsmm91
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
1,430 Expert 1GB
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
jsmm91
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
1,430 Expert 1GB
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
jsmm91
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
jsmm91
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
1,430 Expert 1GB
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
jsmm91
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
1,430 Expert 1GB
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
jsmm91
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
1,430 Expert 1GB
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
jsmm91
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
1,430 Expert 1GB
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
jsmm91
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
1,430 Expert 1GB
I have sent you a private message as this is getting complicated.

Phil
Jul 28 '17 #25

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

Similar topics

5
by: crispy | last post by:
On this page, you'll see that some PubID's are repeated several times: http://www.ihea.info/pages/website/test1.asp That's because some records have more than one attribute. For example, Title...
2
by: JMCN | last post by:
i am running into problems with is the recordset. i have exactly 104 records. when i tested the code, the end result was multiple records for the same loan(ET LN Shortname). where did i go wrong? ...
1
by: Stephen | last post by:
hi all, I am making form in access and I want to have a checkbox that when clicked will allow the user to not have to input all of the data again since some of the entered data will be the same...
2
by: Joost Kraaijeveld | last post by:
I have a table with column1, column2, column3 and column4. How do I get all records, sorted by column4 that have the same column1,column2 and column3? TIA Joost ...
1
by: KelHemp | last post by:
I'm trying to count the number of records within a form (and report) that have the same number in the "LNumber" field. If I had a set number I wanted, I'd just run a single query, but the number...
0
by: Frank Miverk | last post by:
Hi, I am not understanding how the Remember Me checkbox is supposed to work here. I have a LoginCtrl (asp.net 2.0, framework 2.0) and all I want to do is remember the user the next time if they...
1
by: visitram | last post by:
I am a newbie to Ruby and wondering how to insert multiple records at the same time. I want to create a form such that it accept multiple records and save it to DB upon submitting the form...
6
by: gurge | last post by:
Hello! I have a form in datasheet view with checkboxes. One of them is called Cancelled. In the SQL table it is a bit value. When a user keys in a line, the checkbox should be default...
0
by: Carlos | last post by:
Hi all, I have code that used to work but no longer does when trying to obtain the value of a field in a gridview. I do try to find the value of a field when a checkbox in the same row handles...
4
topher23
by: topher23 | last post by:
So I'm working on this new project that involves comparing the machine settings for a product against the machine settings for the next product run to see which machine settings need to be changed,...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.