Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

NotInList INSERT INTO 2 Fields HOW?

Discussion in 'Information Technology' started by lmv, Jul 28, 2006.

  1. lmv

    lmv
    Expand Collapse
    Guest

    I have a combobox that triggers the next box to only list suppliercode with
    that ProdID. The supplierCode field then has the number SOMETIMES the ProdID
    can have a different number if it comes from a different supplier I want to
    use my NotInList event to add the info. I know how to add just the supplier
    code but what is the syntax to add the prodID as well? (this tblsupplierCode
    has an auto number set as PK)

    Thanks!

    Private Sub cboSupplierCode_NotInList(NewData As String, Response As Integer)
    On Error GoTo SupplierCode_NotInList_Err
    Dim intAnswer As Integer
    Dim strSQL As String
    intAnswer = MsgBox("The Supplier Code " & Chr(34) & NewData & _
    Chr(34) & " is not currently listed." & vbCrLf & _
    "Would you like to add it to the list now?" _
    , vbQuestion + vbYesNo, "Purchase Order Subform")
    If intAnswer = vbYes Then
    strSQL = "INSERT INTO tblSupplierCodes([SupplierCode]) " & _
    "VALUES ('" & NewData & "');"
    DoCmd.SetWarnings False
    'CurrentDb.Execute strSQL, dbFailOnError
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    MsgBox "The new Supplier Code has been added to the list."
    Response = acDataErrAdded
    Else
    MsgBox "Please choose a Supplier Code from the list." & vbCrLf & _
    "Use the ESC to return value to the box."
    Response = acDataErrContinue
    End If
    SupplierCode_NotInList_Exit:
    Exit Sub
    SupplierCode_NotInList_Err:
    MsgBox err.Description, vbCritical, "Error"
    Resume SupplierCode_NotInList_Exit
    End Sub
     
  2. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    You could either Open a Form in Dialog mode to add the two fields,
    SupplierCode prefilled, or use a input box to prompt for a deviant ProdID

    Pieter

    "lmv" <lmv@discussions.microsoft.com> wrote in message
    news:93ECF387-65E9-4343-8CC0-F4FF311F154B@microsoft.com...
    >I have a combobox that triggers the next box to only list suppliercode with
    > that ProdID. The supplierCode field then has the number SOMETIMES the
    > ProdID
    > can have a different number if it comes from a different supplier I want
    > to
    > use my NotInList event to add the info. I know how to add just the
    > supplier
    > code but what is the syntax to add the prodID as well? (this
    > tblsupplierCode
    > has an auto number set as PK)
    >
    > Thanks!
    >
    > Private Sub cboSupplierCode_NotInList(NewData As String, Response As
    > Integer)
    > On Error GoTo SupplierCode_NotInList_Err
    > Dim intAnswer As Integer
    > Dim strSQL As String
    > intAnswer = MsgBox("The Supplier Code " & Chr(34) & NewData & _
    > Chr(34) & " is not currently listed." & vbCrLf & _
    > "Would you like to add it to the list now?" _
    > , vbQuestion + vbYesNo, "Purchase Order Subform")
    > If intAnswer = vbYes Then
    > strSQL = "INSERT INTO tblSupplierCodes([SupplierCode]) " & _
    > "VALUES ('" & NewData & "');"
    > DoCmd.SetWarnings False
    > 'CurrentDb.Execute strSQL, dbFailOnError
    > DoCmd.RunSQL strSQL
    > DoCmd.SetWarnings True
    > MsgBox "The new Supplier Code has been added to the list."
    > Response = acDataErrAdded
    > Else
    > MsgBox "Please choose a Supplier Code from the list." & vbCrLf & _
    > "Use the ESC to return value to the box."
    > Response = acDataErrContinue
    > End If
    > SupplierCode_NotInList_Exit:
    > Exit Sub
    > SupplierCode_NotInList_Err:
    > MsgBox err.Description, vbCritical, "Error"
    > Resume SupplierCode_NotInList_Exit
    > End Sub
    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4367 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  3. lmv

    lmv
    Expand Collapse
    Guest

    Thanks for the response but I don't know what you mean by dialog mode. And I
    don't know what you mean by input box. Isn't there anyway to have VBA code to
    add to 2 fields instead of only one there is already a productID code
    available on the form I am using and it is what is triggering the info in the
    cbosuppliercode?

    strSQL = "INSERT INTO tblSupplierCodes([SupplierCode]) " & _
    "VALUES ('" & NewData & "');"

    Thanks!

    "Pieter Wijnen" wrote:

    > You could either Open a Form in Dialog mode to add the two fields,
    > SupplierCode prefilled, or use a input box to prompt for a deviant ProdID
    >
    > Pieter
    >
    > "lmv" <lmv@discussions.microsoft.com> wrote in message
    > news:93ECF387-65E9-4343-8CC0-F4FF311F154B@microsoft.com...
    > >I have a combobox that triggers the next box to only list suppliercode with
    > > that ProdID. The supplierCode field then has the number SOMETIMES the
    > > ProdID
    > > can have a different number if it comes from a different supplier I want
    > > to
    > > use my NotInList event to add the info. I know how to add just the
    > > supplier
    > > code but what is the syntax to add the prodID as well? (this
    > > tblsupplierCode
    > > has an auto number set as PK)
    > >
    > > Thanks!
    > >
    > > Private Sub cboSupplierCode_NotInList(NewData As String, Response As
    > > Integer)
    > > On Error GoTo SupplierCode_NotInList_Err
    > > Dim intAnswer As Integer
    > > Dim strSQL As String
    > > intAnswer = MsgBox("The Supplier Code " & Chr(34) & NewData & _
    > > Chr(34) & " is not currently listed." & vbCrLf & _
    > > "Would you like to add it to the list now?" _
    > > , vbQuestion + vbYesNo, "Purchase Order Subform")
    > > If intAnswer = vbYes Then
    > > strSQL = "INSERT INTO tblSupplierCodes([SupplierCode]) " & _
    > > "VALUES ('" & NewData & "');"
    > > DoCmd.SetWarnings False
    > > 'CurrentDb.Execute strSQL, dbFailOnError
    > > DoCmd.RunSQL strSQL
    > > DoCmd.SetWarnings True
    > > MsgBox "The new Supplier Code has been added to the list."
    > > Response = acDataErrAdded
    > > Else
    > > MsgBox "Please choose a Supplier Code from the list." & vbCrLf & _
    > > "Use the ESC to return value to the box."
    > > Response = acDataErrContinue
    > > End If
    > > SupplierCode_NotInList_Exit:
    > > Exit Sub
    > > SupplierCode_NotInList_Err:
    > > MsgBox err.Description, vbCritical, "Error"
    > > Resume SupplierCode_NotInList_Exit
    > > End Sub
    > >

    >
    >
    >
    > --
    > ----------------------------------------
    > I am using the free version of SPAMfighter for private users.
    > It has removed 4367 spam emails to date.
    > Paying users do not have this message in their emails.
    > Get the free SPAMfighter here: http://www.spamfighter.com/len
    >
    >
    >
     
  4. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    O,k below is an example on how to use InputBox

    Pieter

    Private Sub cboSupplierCode_NotInList(NewData As String, Response As
    Integer)
    On Error GoTo SupplierCode_NotInList_Err
    Dim intAnswer As Integer
    Dim strSQL As String
    Dim ProdID As String
    intAnswer = MsgBox("The Supplier Code " & Chr(34) & NewData & _
    Chr(34) & " is not currently listed." & vbCrLf & _
    "Would you like to add it to the list now?" _
    , vbQuestion + vbYesNo, "Purchase Order Subform")
    If intAnswer = vbYes Then
    ProdID = InputBox "Add new ProdID?"
    If Len(ProdID) Then
    strSQL = "INSERT INTO tblSupplierCodes([SupplierCode], ProdID) " &
    _
    "VALUES ('" & NewData & "','" & ProdID & "');"
    else
    strSQL = "INSERT INTO tblSupplierCodes([SupplierCode]) " & _
    "VALUES ('" & NewData & "');"
    End If
    DoCmd.SetWarnings False
    'CurrentDb.Execute strSQL, dbFailOnError
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    MsgBox "The new Supplier Code has been added to the list."
    Response = acDataErrAdded
    Else
    MsgBox "Please choose a Supplier Code from the list." & vbCrLf & _
    "Use the ESC to return value to the box."
    Response = acDataErrContinue
    End If
    SupplierCode_NotInList_Exit:
    Exit Sub
    SupplierCode_NotInList_Err:
    MsgBox err.Description, vbCritical, "Error"
    Resume SupplierCode_NotInList_Exit
    End Sub


    "lmv" <lmv@discussions.microsoft.com> wrote in message
    news:AC297918-FF6C-49E4-AE1C-3C6D65EE589B@microsoft.com...
    > Thanks for the response but I don't know what you mean by dialog mode. And
    > I
    > don't know what you mean by input box. Isn't there anyway to have VBA code
    > to
    > add to 2 fields instead of only one there is already a productID code
    > available on the form I am using and it is what is triggering the info in
    > the
    > cbosuppliercode?
    >
    > strSQL = "INSERT INTO tblSupplierCodes([SupplierCode]) " & _
    > "VALUES ('" & NewData & "');"
    >
    > Thanks!
    >
    > "Pieter Wijnen" wrote:
    >
    >> You could either Open a Form in Dialog mode to add the two fields,
    >> SupplierCode prefilled, or use a input box to prompt for a deviant ProdID
    >>
    >> Pieter
    >>
    >> "lmv" <lmv@discussions.microsoft.com> wrote in message
    >> news:93ECF387-65E9-4343-8CC0-F4FF311F154B@microsoft.com...
    >> >I have a combobox that triggers the next box to only list suppliercode
    >> >with
    >> > that ProdID. The supplierCode field then has the number SOMETIMES the
    >> > ProdID
    >> > can have a different number if it comes from a different supplier I
    >> > want
    >> > to
    >> > use my NotInList event to add the info. I know how to add just the
    >> > supplier
    >> > code but what is the syntax to add the prodID as well? (this
    >> > tblsupplierCode
    >> > has an auto number set as PK)
    >> >
    >> > Thanks!
    >> >
    >> > Private Sub cboSupplierCode_NotInList(NewData As String, Response As
    >> > Integer)
    >> > On Error GoTo SupplierCode_NotInList_Err
    >> > Dim intAnswer As Integer
    >> > Dim strSQL As String
    >> > intAnswer = MsgBox("The Supplier Code " & Chr(34) & NewData & _
    >> > Chr(34) & " is not currently listed." & vbCrLf & _
    >> > "Would you like to add it to the list now?" _
    >> > , vbQuestion + vbYesNo, "Purchase Order Subform")
    >> > If intAnswer = vbYes Then
    >> > strSQL = "INSERT INTO tblSupplierCodes([SupplierCode]) " & _
    >> > "VALUES ('" & NewData & "');"
    >> > DoCmd.SetWarnings False
    >> > 'CurrentDb.Execute strSQL, dbFailOnError
    >> > DoCmd.RunSQL strSQL
    >> > DoCmd.SetWarnings True
    >> > MsgBox "The new Supplier Code has been added to the list."
    >> > Response = acDataErrAdded
    >> > Else
    >> > MsgBox "Please choose a Supplier Code from the list." & vbCrLf &
    >> > _
    >> > "Use the ESC to return value to the box."
    >> > Response = acDataErrContinue
    >> > End If
    >> > SupplierCode_NotInList_Exit:
    >> > Exit Sub
    >> > SupplierCode_NotInList_Err:
    >> > MsgBox err.Description, vbCritical, "Error"
    >> > Resume SupplierCode_NotInList_Exit
    >> > End Sub
    >> >

    >>
    >>
    >>
    >> --
    >> ----------------------------------------
    >> I am using the free version of SPAMfighter for private users.
    >> It has removed 4367 spam emails to date.
    >> Paying users do not have this message in their emails.
    >> Get the free SPAMfighter here: http://www.spamfighter.com/len
    >>
    >>
    >>


    --------------------------------------------------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4382 spam emails to date.
    Paying users do not have this message in their emails.
    Try SPAMfighter for free now!
     

Share This Page