sub or function not defined error

I am trying to trap for blank control but I am getting error "sub or function not defined" when compiling the code
Private Sub btnSave_Click()
bSaveClicked = True
If Len(Me.cboSubcat & "") = 0 Then
        MsgBox "You must Select a Subcategory", vbOKOnly + vbCritical
    Me.cboSubcat.SetFocus
   Cancel = True
End If
Else
CurrentDb.Execute "Insert into item_cat_subcat(ITEMNMBR,CAT_ID,SUBCAT_ID) Values ('" & Me.txtItem & "','" & Me.txtCatid & "','" & Me.cboSubcat & "');"
CurrentDb.Execute "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID) Values ('" & Me.txtItem & "','" & Me.cboAttrb & "','" & cboAttribvl & "');"
DoCmd.GoToRecord , "", acNext
Me.cboSubcat = Null
Me.cboAttrb = Null
Me.cboAttribvl = Null
End Sub

Open in new window

skull52Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Where is cancel defined?

   Cancel = True

A hold over from code copied from a BeforeUpdate event?

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Could use a Me. on this at the end of the second SQL statement too:

 & cboAttribvl & "');"

  But other then that, I can't see where you'd get an un-defined.   What line does it come up with?

Jim.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and BTW, on this:

CurrentDb.Execute

always do it like this:

 Dim strSQL as string

 strSQL = "Insert into item_cat_subcat(ITEMNMBR,CAT_ID,SUBCAT_ID) Values ('" & Me.txtItem & "','" & Me.txtCatid & "','" & Me.cboSubcat & "');"
 CurrentDB.Execute strSQL, dbFailOnError

 and put an error handler in always.

 This lets you accomplish two things:

a. You can inspect the SQL statement before you try to execute it.   Makes it easy to troubleshoot when you get errors (you can hover over it or print it in the debug window).

b. If an error occurs while executing the SQL, right now you won't know it happened.

Jim.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

skull52Author Commented:
Jim
Thanks should I do a string for each insert, how would I add error handling?
I want to make sure that the user selects a value in the cboSubcat combo box
Yes I took this cod from another project
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Yes I took this cod from another project>>

1. Drop the line:

 Cancel = True

2. Make sure you have a Option Explicit at the top of every module.   This will catch things like #1, mis-spellings, etc.

3. Procedure will look like this:

Private Sub btnSave_Click()

 Dim strSQL as string

On Error Goto Error_Procedure 

bSaveClicked = True

If Len(Me.cboSubcat & "") = 0 Then
  MsgBox "You must Select a Subcategory", vbOKOnly + vbCritical
  Me.cboSubcat.SetFocus
Else
  strSQL = "Insert into item_cat_subcat(ITEMNMBR,CAT_ID,SUBCAT_ID) Values ('" & Me.txtItem & "','" & Me.txtCatid & "','" & Me.cboSubcat & "');"
  CurrentDB.Execute strSQL, dbFailOnError

  strSQL =  "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID) Values ('" & Me.txtItem & "','" & Me.cboAttrb & "','" & Me.cboAttribvl & "');"
  CurrentDB.Execute strSQL, dbFailOnError

  DoCmd.GoToRecord , "", acNext

  Me.cboSubcat = Null
  Me.cboAttrb = Null
  Me.cboAttribvl = Null

End If

Exit_Procedure:
  
  Exit Sub

Error_Procedure:
   msgbox err.number & " - " & err.description
   Resume Exit_Procedure

End Sub

Open in new window


and BTW, you had an un-matched IF/END IF as well.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
skull52Author Commented:
Jim,
That worked perfectly, however i added some checkboxes to my form the default value is set to NO on the tables, when I click save with nothing checked i get 346 - Datatype mismatch in criteria expression

  strSQL = "Insert into item_cat_subcat(ITEMNMBR,CAT_ID,SUBCAT_ID,SubCatMod) Values ('" & Me.txtItem & "','" & Me.txtCatid & "','" & Me.cboSubcat & "','" & Me.chkSub & "');"
  CurrentDb.Execute strSQL, dbFailOnError

  strSQL = "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID,AttribMod,AttribVlMod) Values ('" & Me.txtItem & "','" & Me.cboAttrb & "','" & Me.cboAttribvl & "','" & Me.chkAttr & "','" & Me.chkAttrbvl & "');"
  CurrentDb.Execute strSQL, dbFailOnError

Open in new window

0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<nothing checked i get 346 - Datatype mismatch in criteria expression>>

  When a check box holds nothing (not true or false), it's a NULL, which in VBA you need a Variant to work with.   With SQL, the field must allow null as well.

  I'm not sure if your showing me everything or not code wise, but there are a couple of things you might do:

1. On the controls, don't allow triple state (Yes, No, NULL).
2. Make sure the tables will support a NULL.  

Let me know if that helps you or not.

Jim.
0
skull52Author Commented:
Ok, how do I insert a checkbox value into my table I keep getting a 3464 - Datatype mismatch in criteria expression
  strSQL = "Insert into item_cat_subcat(ITEMNMBR,CAT_ID,SUBCAT_ID,SubCatMod) Values ('" & Me.txtItem & "','" & Me.txtCatid & "','" & Me.cboSubcat & "','" & Me.chkSub & "');"
  CurrentDb.Execute strSQL, dbFailOnError

  strSQL = "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID,AttribMod,AttribVlMod) Values ('" & Me.txtItem & "','" & Me.cboAttrb & "','" & Me.cboAttribvl & "','" & Me.chkAttr & "','" & Me.chkAttrbvl & "');"
  CurrentDb.Execute strSQL, dbFailOnError

Open in new window

0
skull52Author Commented:
I have 3 check boxes on my form chkSub, chkAttr, and chkAttrbvl and they are a yes/No datatype on my table, i know that  '" & Me.chkAttr & "' is a string but i have tried removing the double quotes then the single quotes, gives a different error what is the correct way to express a boolean value.
0
skull52Author Commented:
Jim, sorry I missed your last post

1. On the controls, don't allow triple state (Yes, No, NULL). not sure how to do that
2. Make sure the tables will support a NULL.  where would i do that i can't find anything in the design that says allow nulls as in SQL server where I am more familiar . I think i may be better off just changing the control to a combobox with a yes no drop down
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<1. On the controls, don't allow triple state (Yes, No, NULL). not sure how to do that>>

 This would be the best approach as NULL's in bit fields in SQL server can be a problem.

 On the property sheet for the control, data tab, set the triple state property to No.   Also give it a default value of 0 (false).  Note that after you do this, if you reset the control, that must be true or false.   You'll no longer be able to set it to null.

Jim.
0
skull52Author Commented:
ok, i think i got it, if i check all 3 boxes it it doesn't throw the error, if I set all the boxes to 0 it doesn't throw the error , it only throes it if there is no value there so if i set the value to 0 on load, and after saving the record that should fixit. BTW, The triple state is set to no.
0
skull52Author Commented:
Nope, still problem, so I am going to may backup plan and changing the check boxes to combo boxes  with yes no
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sorry, I didn't catch it before; your passing the values as strings.  This:

  strSQL = "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID,AttribMod,AttribVlMod) Values ('" & Me.txtItem & "','" & Me.cboAttrb & "','" & Me.cboAttribvl & "','" & Me.chkAttr & "','" & Me.chkAttrbvl & "');"

Needs to be:

  strSQL = "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID,AttribMod,AttribVlMod) Values ('" & Me.txtItem & '"," & Me.cboAttrb & "," & Me.cboAttribvl & "," & Me.chkAttr & "," & Me.chkAttrbvl & ");"

Jim.
0
skull52Author Commented:
Thanks but I changed it to the combo box. Now I have another issue, this thing is killing me. I have the 2 insert expressions the first one enters the values into the item_cat_subcat table which can have only 1 entry for the same Item Number. I would assume that i would use a WHERE NOT EXISTS in the first expression but not sure how to do that in this string.
strSQL = "Insert into item_cat_subcat(ITEMNMBR,CAT_ID,SUBCAT_ID,SubCatMod) Values ('" & Me.txtItem & "','" & Me.txtCatid & "','" & Me.cboSubcat & "','" & Me.cboAddSub & "');"
  CurrentDb.Execute strSQL, dbFailOnError

Open in new window


The 2nd can have multiple entries for the same Item Number as it can have multiple Attributes
  strSQL = "Insert into item_attrib_attribvl(ITEMNMBR,ATTRIB_ID,ATTRBVLU_ID,AttribMod,AttribVlMod) Values ('" & Me.txtItem & "','" & Me.cboAttrb & "','" & Me.cboAttribvl & "','" & Me.cboAddAttrib & "','" & Me.cboAddAttribvl & "');"
  CurrentDb.Execute strSQL, dbFailOnError

Open in new window

0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Now I have another issue,>>

 We really like to avoid having multiple questions/solutions in a single thread.   It makes it confusing to read later on.

 Please open another question for this.

Jim.
0
skull52Author Commented:
Ok, I will close this one and open a new one.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.