Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271

    Unanswered: Very frustrated about NotInList

    I have a NotInList function to add a new product.

    Once I enter the new product, It will not requery or refresh. What am I missing??

    I am sending a zipped copy of the Purchase Order Database so someone can help.

    I know I am green when it comes to coding... But I really need some help.

    Thank You,
    Michael
    Attached Files Attached Files
    Gotta to do some code

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is some code that I use to insert records into a table when a new value is typed into a dropdown list:

    Code:
    Private Sub UserID_NotInList(NewData As String, Response As Integer)
        If MsgBox("'" & NewData & "' is not in the database.  Do you wish to add it now?", vbQuestion + vbYesNo) = vbYes Then
            DoCmd.RunSQL "Insert into Entities (EntityName) Values ('" & NewData & "')"
            Response = acDataErrAdded
        Else
            Response = acDataErrDisplay
        End If
    End Sub
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    What are entities?How does this open my form so i can enter the product and all its data??

    Michael
    Gotta to do some code

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    a) Set your AddNewProductsFrm Form to open as Dialog.

    b) Remove the premature form close in the AfterUpdate event for the Price field located within the AddNewProductsFrm.

    c) Re-initiate the RowSource property for the ComboBox in subForm SbfrmOrderssubform once the AddNewProductsFrm is closed.

    See the routine below (reworked):
    Code:
    Private Sub ProductID_NotInList(NewData As String, Response As Integer)
    	If MsgBox("The Product " & NewData & " you entered, does not exist yet." & vbCrLf & vbCrLf & "Do you wish to add it?", _
    	vbQuestion + vbYesNo) = vbYes Then
    		DoCmd.OpenForm "AddNewProductsFrm", acNormal, , , acFormAdd, acDialog, NewData
    		Me.ProductID.RowSource = Me.ProductID.RowSource 
    		Response = acDataErrContinue
    	 Else
    		Response = acDataErrAdded
    		ProductID.Undo
    		DoCmd.Save
    		ProductID.Requery
    	End If
    End Sub
    Last edited by CyberLynx; 01-21-05 at 02:53.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    OK, here is the whole solution I implement. If you set this up correctly, users who enter new data into your dropdown will be prompted as to whether they want to add a new record to the list. If they accept, then the form you specify will open and the value they entered will be automatically entered into it. They can edit additional information, and then close the form and be returned to the original form where the new value will be accepted in the dropdown list.

    Put this code in a shared module:
    Code:
    Function ADD_NEW_DATA(NewData As String, ENTRY_FORM As String)
    'Verify new data to add
    'Call this function from the dropdown list 'on not in list' event with
    'this syntax: Response = ADD_NEW_DATA(NewData, "NAME_OF_FORM_TO_OPEN")
        If NewData = "" Then
            If MsgBox("This choice is not in the database.  Do you wish to add it now?", vbQuestion + vbYesNo) = vbYes Then
                On Error GoTo OPEN_FORM_ERROR
                DoCmd.OpenForm ENTRY_FORM, acNormal, , , acFormAdd, acDialog
                On Error GoTo 0
                ADD_NEW_DATA = acDataErrAdded
            Else
                ADD_NEW_DATA = acDataErrDisplay
            End If
        Else
            If MsgBox("'" & NewData & "' is not in the database.  Do you wish to add it now?", vbQuestion + vbYesNo) = vbYes Then
                On Error GoTo OPEN_FORM_ERROR
                'DoCmd.OpenForm ENTRY_FORM, acNormal, , , acFormAdd, acDialog, NewData
                DoCmd.OpenForm ENTRY_FORM, acNormal, , , acFormAdd, acDialog, "Newdata:" & NewData
                On Error GoTo 0
                ADD_NEW_DATA = acDataErrAdded
            Else
                ADD_NEW_DATA = acDataErrDisplay
            End If
        End If
    Exit Function
    OPEN_FORM_ERROR:
        MsgBox "Unable to open form '" & ENTRY_FORM & "'"
        ADD_NEW_DATA = acDataErrDisplay
    End Function
    
    Function PARSE_OPEN_ARGS(open_arg_string As String, param_num As Integer) As String
    'This function parses a string passed to a form's open_args parameter into
    'separate parameters delimited by a pipe (|), and returns the parameter
    'specified by param_num as a String.  The function returns an empty string if there are
    'are fewer parameters than param_num.
    
    Dim param_count As Integer
    Dim tempstring As String
    Dim delimiter As Integer
    
    tempstring = open_arg_string
    param_count = 1
    
    'param_num must be greater than or equal to 1
    If param_num < 1 Then
        PARSE_OPEN_ARGS = ""
        Exit Function
    End If
    
    'strip off any leading delimiter
    If Left(tempstring, 1) = "|" Then
        tempstring = Right(tempstring, Len(tempstring) - 1)
    End If
    
    Do Until param_count >= param_num
        delimiter = InStr(tempstring, "|")
        If delimiter = 0 Then
            'there are no more parameters left, so return an emtpy string
            PARSE_OPEN_ARGS = ""
            Exit Function
        End If
        tempstring = Right(tempstring, Len(tempstring) - delimiter)
        param_count = param_count + 1
    Loop
        
    'strip off any hanging delimiter before returning the string
    delimiter = InStr(tempstring, "|")
    If delimiter = 0 Then
        PARSE_OPEN_ARGS = tempstring
    Else
        PARSE_OPEN_ARGS = Left(tempstring, delimiter - 1)
    End If
    End Function
    
    Function PARSE_COMMAND(CommandString As String, ReturnPart As String) As String
        'This function takes a command string in the form "command:value" and returns
        'either the command or the value.  It is used to process form openargs.
        Dim delimiter As Integer
        delimiter = InStr(CommandString, ":")
        
        Select Case ReturnPart
            Case "Command"
                If delimiter = 0 Then
                    'This command has no associated value
                    PARSE_COMMAND = CommandString
                Else
                    'Return the first part of the string
                    PARSE_COMMAND = Left(CommandString, delimiter - 1)
                End If
            Case "Value"
                If delimiter = 0 Then
                    'This command has no value, so return an empty string
                    PARSE_COMMAND = ""
                Else
                    'Return the second part of the string
                    PARSE_COMMAND = Right(CommandString, Len(CommandString) - delimiter)
                End If
            Case Else
                'The ReturnPart is invalid, so return an empty string
                PARSE_COMMAND = ""
        End Select
    
    End Function
    Tie this code to your target form's LOAD event:
    Code:
    Private Sub Form_Load()
    Dim ArgCounter As Integer
    Dim CommandString As String
    DropdownListChanged = False
    
    If Not IsNull(Me.OpenArgs) Then
        ArgCounter = 1
        CommandString = PARSE_OPEN_ARGS(Me.OpenArgs, ArgCounter)
        Do Until CommandString = ""
            Process_Command (CommandString)
            ArgCounter = ArgCounter + 1
            CommandString = PARSE_OPEN_ARGS(Me.OpenArgs, ArgCounter)
        Loop
    End If
    End Sub
    Include this code in your target form's module:
    Code:
    Sub Process_Command(CommandString As String)
        Select Case PARSE_COMMAND(CommandString, "Command")
            Case "Newdata"
    	    'Replace NEWDATAFIELD with the name of the field on your form that should recieve the new data value.
                NEWDATAFIELD = PARSE_COMMAND(CommandString, "Value")
            Case Else
                MsgBox "Unrecognized command: " & PARSE_COMMAND(CommandString, "Command")
        End Select
    End Sub
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    CyberLynx

    I did exactly what you said!! It worked fine till I tried to enter the data. My form locked up. IS there ANY WAY to do this without using Dialog. Everytime I have used dialog my computer locks up???

    Thank You
    Michael
    Gotta to do some code

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Look at the parameters for DoCmd.OpenForm. There are other choices besides acDialog, so pick one that doesn't freeze your computer. Or figure out why your computer is freezing. Makes me wonder whether you might have a load of spyware or adware on it interfering with Windows.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    CyberLynx,

    I think it may be because I am adding data to the Product table but my subform uses the ProductID in OrderDetails. Should I requery this also??

    Thank You,
    Michael
    Gotta to do some code

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Try it and see if it solves your problem.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Mar 2004
    Location
    Greenville, SC
    Posts
    271
    Thank You Blindman for responding,

    Where should I do this? Or I guess I should say how do I do this? On my OnChange? On Enter?

    I really am bad at code, but I really want to learn

    Michael
    Gotta to do some code

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Look up the order of events in the MS Access help file. Then, if you are unsure of what is happening in the background, create a snippet of code for each event that triggers a message box to let you know when it is fired. That will help you find the proper place to put your code.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    May 2005
    Posts
    3

    Thumbs up Thank you!!!

    OMG, you guys are awesome, this has been bugging me all day!!!!

Posting Permissions

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