Check Box Control

[penfold1992]

What appeared to be a simple task has turned into a nightmare...

In a current form I want to display a checkbox that can be checked or unchecked. Based on if the box is checked or not, a value will be placed in a field when the record gets updated or created...

however ive fallen at the first hurdle.

I have created a check box but when I click it, I am unable to uncheck it, Im pretty confident I can do the later part in coding it into the database... its just having the free ability to check it or not!

 

[billmeye]

Is your checkbox Unbound or bound? If it is bound to a data type that is not boolean it may be getting hung up on not knowing what to do.

 

[penfold1992]

I think the data type is now unbound...

I have managed to get everything to work apart from getting the record into the database...

strSQL = "INSERT INTO [Completed] VALUES ( " + strCode + "," + strOperation + ",'" + strCompleteDate + "'," + _
        strParts + "," + strSize + ",'" + strComments + "'," & checkbox12 & "," + strTime + ",'" + strPASSUP + "')"

everything in there works apart from the checkbox12 part... ive tried replacing the &'s with +'s but I get "Type Mismatch"

checkbox12 = Me.chkbox12.Value

 

[billmeye]

Your checkbox is a boolean and it looks like your trying to use it as a string. What information are you trying to generate from the checkbox, Yes/No or True/False? The actual value stored is -1 for True/Yes and 0 for False/No. If you want the actual words you'll have to create a string variable and assign it Yes/No as needed and use that in your query.

 

[penfold1992]

the column Im wanting to fill is a "yes/no" column, not a string (but I could change it to a string i guess...)
is it just a case of doing something like...

if me.chkbox12.value = -1 Then
checkbox12 = "Yes"
Else
checkbox12 = "No"
End If

however that is still putting them as strings...

 

[billmeye]

I wasn't suggesting you convert it to a string, I just thought the type mismatch was a result of trying to put a boolean into a string. Are you certain your problem is CheckBox12? I'm not sure exactly, but I see a field named strTime and strCompleteDate and if these are date fields, then the variable would need to be surrounded by # # and I don't see that in your query. I'm not familiar with the syntax you are using so I can't say. I've used Insert Into queries but never with the VALUES statement so I don't know exactly what you are doing.

 

[penfold1992]

strTime is a variable which is a date, im not 100% sure how its working now but, i got the UPDATE code to work, just not the INSERT to work.. tomorrow I will post the code for UPDATE that I used and also post what the date is from so you can see how that works

and yeah im sure its the checkbox12, because it worked perfectly before i decided to add it but I am not sure why its not working now. I have two tables which basically look at mandatory information and secondary information, the checkbox goes into the secondary information table.

when I create a record, the mandatory information gets entered but not the secondary information (the sql that i posted) so i can limit it down to just those at least.

 

[CJ_London]

Your code:

INSERT INTO [Completed]

means that what is inserted in Values must be in the same order as the fields in the table - is this the case?

Also, you should be using & not + to concatenate strings

 

[penfold1992]

ok i got it to work,

here was the issue:

the sql command is set to a string so you can execute it later,
so everything must be a string (even the checkbox12), then the value that the chkbox12 is needs to be assigned to that string to add it onto the rest of the string

the other issue was the way the table was set up, the columns need to be arranged correctly in the correct order (which was the case on the front end but not on the back end, once i changed that, i recieved less errors)