Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Missing Semi Colon (A2000)

    An error msg is telling me I have a missing semi Colon ([img]/forums/images/smilies/wink.gif[/img] at end of SQL statement.
    I've put a semi colon everywhere I can think and still get the error.
    Can anyone see why I keep getting the problem.

    Private Sub CmbOtherCode_NotInList(NewData As String, Response As Integer)
    Dim intButSelected As Integer, intButType As Integer
    Dim strMsgPrompt As String, strMsgTitle As String
    strMsgPrompt = "Incorrect Code Used, Do You Want To Update The Database With This New Code ?"
    strMsgTitle = "!!"
    intButType = vbYesNo + vbQuestion + vbDefaultButton1
    intButSelected = MsgBox(strMsgPrompt, intButType, strMsgTitle)
    If intButSelected = vbYes Then
    Dim LinkCriteria As String
    Dim strSQL As String
    strSQL = "Insert Into tblOtherItems([code])values('" & NewData & "')" & Me!CmbOtherCode.Text & "'"
    CurrentDb.Execute strSQL
    LinkCriteria = "[code] = '" & Me!CmbOtherCode.Text & "'"
    DoCmd.OpenForm "frmOtherItemsAdmin", , , LinkCriteria
    Response = acDataErrAdded
    Else
    ' NO button code...
    MsgBox "Select An Item From The List"
    Response = acDataErrContinue
    End If
    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing Semi Colon (A2000)

    Dave, in the line

    strSQL = "Insert Into tblOtherItems([code])values('" & NewData & "')" & Me!CmbOtherCode.Text & "'"

    you are appending the new text twice: as NewData and as cmbOtherCode.Text. Omit that last part and change it to

    strSQL = "Insert Into tblOtherItems ([code]) values ('" & NewData & "')"

    (the extra spaces are not necessary but make it more readable)

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Semi Colon (A2000)

    Hans
    That works to a degree.
    Here's what happens now.
    The code is called from the combo which calls the form "frmOtherItemsAdmin"

    frmOtherItemsAdmin has a subform sbfOtherItemsAdmin.

    Whats happening here is.
    1. Call code from not in list
    2. Open frmOtherItemsAdmin
    3. Form opens and new code is in the subform (as it should be) sbfOtherItemsAdmin (but not SetFocussed)
    4. The code has gone missing from the original Combo

    ????????????

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Missing Semi Colon (A2000)

    Call the form as a dialog, otherwise the code will run on while the form is open.

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Missing Semi Colon (A2000)

    Hans
    Thanks
    Perfect

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •