Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2013
    Posts
    87

    Unanswered: NotInList code has a problem

    Hi, I have the following NotInList code for a combo box. It works, except that the Else part of the If statement fires every time. I get a new record in the table as expected, but the third box opens and asks me to choose a date from the list. Which I can do and I can go on. But it should only fire if I check no.

    Can anyone see what there my mistake is? I am sure it is probably a stupid little typo I have missed.

    Thanks, Scott


    Code:
    Private Sub cboDate_NotInList(NewData As String, Response As Integer)
        
        On Error GoTo cboDate_NotInList_Err
        
        Dim intAnswer As Integer
        Dim strSQL As String
        
        intAnswer = MsgBox("The Date " & Chr(34) & NewData & Chr(34) & " is not currently listed." & vbCrLf & _
        "Would you like to add it to the list now?", vbQuestion + vbYesNo, "CFD Operations Inventory")
        
        If intAnswer = vbYes Then
            strSQL = "INSERT INTO tblLUInvDate([invDate]) " & "VALUES (' " & NewData & " ');"
            DoCmd.SetWarnings False
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            MsgBox "The new Date has been added to the list.", vbInformation, "CFD Operations Inventory"
            'Response = acDataErrAdded
            Response = acDataErrAdded
        Else
            MsgBox "Please choose a Date from the list.", vbInformation ', "CFD Operations Inventory"
            Response = acDataErrContinue
        End If
    
    cboDate_NotInList_Exit:
    Exit Sub
    
    cboDate_NotInList_Err:
    MsgBox Err.Description, vbCritical, "Error"
    Resume cboDate_NotInList_Exit
    End Sub

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    Try formating the date value also

    as date is a number take the ' away and replace with the # this tell the SQL it a date value other wise it will try to (mm / dd / yyyy)


    strSQL = "INSERT INTO tblLUInvDate([invDate]) " & "VALUES (#" & format(NewData,"mm/dd/yyyy") & " #);"

    SQL like the DATES to be in USA format
    Last edited by myle; 03-15-17 at 15:30.
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  3. #3
    Join Date
    Mar 2013
    Posts
    87
    Quote Originally Posted by myle View Post
    Try formating the date value also

    as date is a number take the ' away and replace with the # this tell the SQL it a date value other wise it will try to (mm / dd / yyyy)


    strSQL = "INSERT INTO tblLUInvDate([invDate]) " & "VALUES (#" & format(NewData,"mm/dd/yyyy") & " #);"

    SQL like the DATES to be in USA format
    Hi, I tried this idea, with and without the format, andI still get the third message box asking me to choose a date. Hmmmmm. I have used code like this before and have never had this problem. Any other thoughts? Best, Scott

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,482
    Provided Answers: 11
    Not at my computer

    have a look at the Insert bit

    tblLUInvDate([invDate]) should it be tblLUInvDate.invdate
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.
    DONT WORRY ABOUT THOSE WHO TALK BEHIND YOUR BACK
    THEY'RE BEHIND YOU FOR A REASON

  5. #5
    Join Date
    Mar 2013
    Posts
    87
    Hi, thanks for the reply.

    table/fieldname:
    tblLUInvDate([invDate])

    Tried with the period,got an error saying could not find file

    It just dawned on me that I never mentioned that I am duplicating the function of a split form. So the date I am adding is also in the subform. Can that affect this? I have never done this before. Thanks.

  6. #6
    Join Date
    Mar 2013
    Posts
    87
    Ok, so I tried deleting the subform and I still get the 3rd message box. I used the code on a text field and it worked fine. It must be the field type. The field I am trying to add to is a date field. The combo box gets it data from dateID and invDate - Auto number and date field short date format

    I put in the #s that you suggested into the sqlStr and it adds the message box. Anyone have any thoughts. Thanks, Scott

    strSQL = "INSERT INTO tblLUInvDate([invDate]) VALUES (# " & NewData & " #);"

    I am in the USA so my date form is already mm/dd/eye

Posting Permissions

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