MS Access 2010 Insert Into CheckBox syntax

[f15e]

I am having trouble finding a good example of adding a new record into a table that has a CheckBox (yes/no). I keep getting errors in my insert into query.


CurrentDb.Execute "INSERT INTO Weight_Data_Tbl([ITEM NO], [TAG], [DESCRIPTION], [EXISTS])" & _
"VALUES(" & Me.wtItem_txtbx & ", '" & Me.tag_txtbx & "', '" & Me.descrip_txtbx & "', " & Me.exists_chkbx & ")"

ITEM NO = integer value, TAG = string value, DESCRIPTION = string value, and EXISTS = yes/no checkbox

My issue is the red highlighted part. What is the correct syntax for inserting check boxes? Single quotes, double quotes, nothing? Thanks in advance.

[ranman256]

just build a query. no need for code.

but , you could try, (the space at the VALUE word to not run together the command)
" VALUES(" & Me.wtItem_txtbx & ", '" & Me.tag_txtbx & "', '" & Me.descrip_txtbx & "', " & me.exists_chkbx.value & ")"
IF that is the correct name of the box

[ssanfu]

Just an FYI....

[ITEM NO] <--- should not use spaces in object names.

[TAG], [DESCRIPTION] & [EXISTS] are all reserved words in Access and shouldn't be used as object names. Plus, they are not very descriptive.
See http://allenbrowne.com/AppIssueBadWord.html


Do you have a control named "exists_chkbx" on the form?

[f15e]

Yes I see they are reserved words but that's not my problem. I could change them but at this point they work fine without errors. exists_chkbox is a checkbox control on my main form. What I am looking to do is have the user either check, or not, the exists_chkbx checkbox control along with other info and when they click the save button, the checkbox status will be reflected in the tables yes/no field (along with the other info but I'm specifically interested in the checkbox syntax in the Insert Into / Values query that keeps producing errors). I have a corresponding checkbox on my datasheet view subform. So there's a checkbox on the main form for user interface and a checkbox on my subform to capture the response where it will be saved in my table as yes/no and displayed in the datasheet view. If I take out the checkbox from the query, it works perfectly fine. It's not liking the checkbox part b/c apparently I'm not using the correct syntax. How do you show a true or false in the VALUES part (non-SQL)? I know for string it's '" & someString& "' and a number it's " & someNum & ", but can't really find an answer when the value is a boolean.

[f15e]

I got it worked out. I had an issue in my subform and just used [FieldName] = '" & Me.MyCheckBoxControl & "'. Thanks for the responses!!

[ssanfu]

Great that you worked it out.

Yes I see they are reserved words but that's not my problem. I could change them but at this point they work fine without errors

Well... OK, good luck with that. They are called reserved words for a reason.