Locking the results of a checkbox

[jsmm91]

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.

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.

 

[isladogs]

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".

This isn't clear to me:
1. Are the fields boolean (Yes/No) or text?
2. Surely no tick means '0' - otherwise what is the point?

If the checkboxes are bound to specific fields in your table, the value will automatically be saved

 

[jsmm91]

Hi Ridders

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.

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.

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 intial value nothing, but also the information changed as nothing.

I attach link

h t t p s://ufile.io/1gfpr


thank you

 

[isladogs]

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.

If you are going to use Access, you should work with Access datatypes.
In this case boolean

I've never heard of revit, but if you can exchange data between it & Access then you can also convert the data when you do so.

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 thought I understood, but I'm confused now....

Are your checkboxes bound to fields in your table?

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.

Yes that's possible

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.

Try requerying the data

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

OK so set a default value of whatever 'nothing' is - normally it would be 0

I attach link

h t t p s://ufile.io/1gfpr

Sorry but I don't follow up links to databases hosted elsewhere

If you want to upload it here, I can look at it later
As you are a new member, you will need to zip it first

I'm going out for the rest of the day so unlikely to respond again for a while
Hopefully someone else will assist you in the meantime

 

[Minty]

To follow up you would need to run the code in the On_Current event to change all the values in the record when you move between them.

As an aside Access treats any value other than 0 as True so you can probably simplify your code.

 

[jsmm91]

Hi Minty

Please find attach my example of what I have been working.

How to make the Checkbox run the code with On_Current? or is only that I have to requery the whole form?


The thing about the value 0 or placing it in with "" is that the information is been written to the table.

Thank you

 

[isladogs]

Hi
Got delayed so I've had a quick look

I've changed North, South, East, West to boolean fields & bound the 4 checkboxes to those fields
See if you're happy with that.

I haven't changed any of the AfterUpdate code but if you're happy with the above you would need to do so.

Really am going out now! Back to Minty...?

 

[jsmm91]

Hi Ridders

Thank you very much for your help.

Unfortunately I have already tried that option before and yes it works in Access, the problem is that when I try to import the database back to the Program I use called Revit, it does not allow me to bring this data, since it does not allow to modify the structure of the information as how originally was exported.

Let me give you a little bit of context why I want to do this.

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.

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

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.

h t t p s://we.tl/57pcnhbOHY

I understand the file is hosted elsewhere, but is the original file as how the program (Revit) export the information as a native Access file, and is quite large (9 mb) even though I try to compress it.

Due to all I previously described, 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 for the time and sorry for the long post.

 

[Minty]

Do it as Ridders suggested, then create an export query that takes all the other data as is but takes the NSEW fields and converts them. now you can handle the data in access correctly but simply change it back to the desired format/data type on export. The field as a calculated field in access would be something like;

EastExport: IIf([East] = True, -1 ,1)

 

[jsmm91]

Hi Minty

Sorry but I do not understand what should I follow from Ridders comments, also by export the query, you mean I create another query that will host other values different from the query that has those tables?

Another question, the code you mentioned, EastExport, should it be written as on click event or as after update event?

Finally from your previous comment regarding the On_Current, should I run a requery there but as a form or as a checkbox even option?

Thank you.

 

[Minty]

I can't download your database - it's blocked.
We appear to be talking at crossed purposes - I thought Ridders example was working with the change of datatype. But having had a re-read everything, that is probably not correct.

I could try and explain what I meant but (take no offence) your current experience level will mean having to write war and peace to get over what I'm trying to explain.

Strip out everything from your database except 20 or so data lines from the relevant tables. If the table isn't related to this issue then simply delete all the data.
Compact and repair it. Then Zip it. It should be tiny by now. Upload it.

 

[jsmm91]

Hi Minty,

Thank you for your help, please find attach file.

Basically, I do not if it is possible, but the best would be not to have a table just for the checkboxes.

Also, 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. and if data is not written, that the checkbox appears empty.

Best.

Samuel

 

[Minty]

Okay - The data in that would indicate that you have -1,0,1,2 and null as possible values for the North, South, East and West fields. This is not what you described initially.

What do those values indicate?

 

[jsmm91]

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.

 

[Minty]

When you import are there any null values ?

 

[jsmm91]

Yes there are null values.

 

[jsmm91]

I think I found the solution

I had to place Event as After Update for the Checkbox

then I created the following code

If North_Checkbox = True Then
North = -1
ElseIf North_Checkbox = False Then
North = 1
ElseIf IsNull (North_Checkbox) Then
North = Null
End If

End Sub

Also that applied in the or as OnCurrent to display the check box

Private Sub Form_Current ()
If North = 1 Then
North_Checkbox = False
ElseIf North = -1 Then
North_Checkbox = Null
End If

End Sub

So when I go to the next Record, it saves my selection and if it gets changed in the next one, the previous stays.

What do you think?

thank you.

 

[jsmm91]

Hi Minty.

there was a part missing in the code I wrote.

Private Sub Form_Current()

If North = 1 Then
    North_Checkbox = False
ElseIf North = -1 Then
    North_Checkbox = True
Else
    North_Checkbox = Null
End If

End Sub

This is the right one, do you think it works properly?

Thank you

 

[Minty]

That looks about right to me.
You could probably pass the value into a function for each control, rather than repeating the code, but it is only 4 controls. Depends if you are likely to repeat the process elsewhere.

 

[jsmm91]

To be honest I want to do it with a list with many more checkboxes following the same idea (approximately 200-300 Times).

How can I reduce this formula?

Thanks