Welcome to SPN

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

Sign Up Now!

NO IDEAS...

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

Tags:
  1. lmv

    lmv
    Expand Collapse
    Guest

    I have a suppliercode that comes from a tblSupplierCode referenced on the
    Product table BUT I have a potential need for a different number to be
    entered.
    I don't want to overwrite the current code. I want to be able to enter a
    different code altogether and have it bound to the order details table which
    will eventually be archived.

    I thought maybe there was a way to have one control over the other but I
    can't figure out any way to do this.

    Any ideas or examples I can look at?
    Thanks
     
  2. Loading...

    Similar Threads Forum Date
    Opinion IDEAS: Revolution, Evolution Or Slow Death Sikh Sikhi Sikhism Jul 16, 2016
    "Convert's Corner" - Ideas for Retitle? Convert's Corner Oct 21, 2015
    Sikh Names... Ideas? Sikh Sikhi Sikhism Jul 5, 2014
    Sikhism Sikhs Today: Ideas & Opinions by I.J. Singh, Book Review by Ravinder Singh Taneja Book Reviews & Editorials Jan 28, 2013
    Help with Ideas to Celebrate Vaisakhi (April 2013) for Young People Get Involved Feb 4, 2013

  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    lmv wrote:
    > I have a suppliercode that comes from a tblSupplierCode referenced on
    > the Product table BUT I have a potential need for a different number
    > to be entered.
    > I don't want to overwrite the current code. I want to be able to
    > enter a different code altogether and have it bound to the order
    > details table which will eventually be archived.
    >
    > I thought maybe there was a way to have one control over the other
    > but I can't figure out any way to do this.
    >
    > Any ideas or examples I can look at?
    > Thanks


    Sorry, but it is not clear, at least to me, what the relationship is
    between the supplier code and the product. Are you saying one product may
    have more than one supplier code. That would not be unusual. You should
    have a supplier table and a product table. Now we need to know if a
    supplier can have more than one product and a product have more than one
    supplier and do you keep records of individual like products (like cartons
    of milk or would all cartons of milk come from one supplier?


    --
    Joseph Meehan

    Dia duit
     
  4. lmv

    lmv
    Expand Collapse
    Guest

    > Sorry, but it is not clear, at least to me, what the relationship is
    > between the supplier code and the product.


    I have a qry that links the supplier code to the productID
    > Are you saying one product may have more than one supplier code. That would not be unusual. You should have a supplier table and a product table.


    I do

    > Now we need to know if a supplier can have more than one product and a product have more than one supplier and do you keep records of individual like products (like cartons of milk or would all cartons of milk come from one supplier?


    Yes they can BUT I decided I didn't want to keep all of the codes for all of
    the supplier products because I am not in their business so I don't care what
    there codes are EXCEPT for the few I already have saved which are the ones we
    use the most. So, sometimes I may want to just add a different code to the
    Order details tbl which I also have a place for supplier code. I thought
    about having a ck box where I could turn the field off but I couldn't get it
    to work... it turned off all of the codes for all of the fields when I only
    wanted one. Then I tried making it a bound ck box but that still didn't work
    right and it didn't remove it from the report.

    I guess I could make it a combo or list box and remove the limit to list and
    then you could just add another one to the suppliercode tbl... but I am so
    tired I can't think any more. I have to think about it tomorrow I guess.
    Thanks
    > --
    > Joseph Meehan
    >
    > Dia duit
    >
    >
    >
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Mon, 24 Jul 2006 20:14:02 -0700, lmv
    <lmv@discussions.microsoft.com> wrote:

    >Yes they can BUT I decided I didn't want to keep all of the codes for all of
    >the supplier products because I am not in their business so I don't care what
    >there codes are EXCEPT for the few I already have saved which are the ones we
    >use the most. So, sometimes I may want to just add a different code to the
    >Order details tbl which I also have a place for supplier code.


    I'd suggest using a Combo Box based on your existing supplier-code
    table; set the combo's Limit to List property to No. This will let you
    pick from the stored set of codes, but also to type in a new code at
    will.

    John W. Vinson[MVP]
     
  6. lmv

    lmv
    Expand Collapse
    Guest

    ok I tried to just add a combo box. It keeps adding this false code to the
    end I don't know where it is coming from. I have tried every combination to
    get rid of it but my notinlist code will not work. My suppliercode comes from
    the choice made from the cboProductName. My notinlist event is at the bottom.
    Thanks for any help! I have other notinlist events and they work fine can't
    figure out what is wrong with this one. (By the way if I need to put in the
    ProductID as well as the Supplier Code I don't know the syntax...)

    SELECT tblSupplierCodes.SupCodeID, tblSupplierCodes.ProductID,
    tblSupplierCodes.SupplierCode
    FROM tblSupplierCodes
    WHERE (((tblSupplierCodes.ProductID)<>False));

    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
    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
     
  7. lmv

    lmv
    Expand Collapse
    Guest

    I just tried the following and it doesn't work either

    Private Sub cboProductName_AfterUpdate()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String

    cboProductName.SetFocus
    If cboProductName.Value > 0 Then
    strSQL = "SELECT * FROM ProductsTTL WHERE ProductID = " &
    cboProductName.Value

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    If Not rs.BOF Then

    Me.UOM = rs("UOM")
    Me.UnitPrice = rs("UnitPrice")

    Me!cboSupplierCode.RowSourceType = "Table/Query"
    Me!cboSupplierCode.RowSource = strSQL
    End If
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
    End If

    'Requery the combo
    Me!cboSupplierCode.Requery
    Me!cboSupplierCode.SetFocus

    End Sub

    "lmv" wrote:

    > ok I tried to just add a combo box. It keeps adding this false code to the
    > end I don't know where it is coming from. I have tried every combination to
    > get rid of it but my notinlist code will not work. My suppliercode comes from
    > the choice made from the cboProductName. My notinlist event is at the bottom.
    > Thanks for any help! I have other notinlist events and they work fine can't
    > figure out what is wrong with this one. (By the way if I need to put in the
    > ProductID as well as the Supplier Code I don't know the syntax...)
    >
    > SELECT tblSupplierCodes.SupCodeID, tblSupplierCodes.ProductID,
    > tblSupplierCodes.SupplierCode
    > FROM tblSupplierCodes
    > WHERE (((tblSupplierCodes.ProductID)<>False));
    >
    > 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
    > 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
    >
     
  8. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 25 Jul 2006 17:39:02 -0700, lmv
    <lmv@discussions.microsoft.com> wrote:

    >ok I tried to just add a combo box. It keeps adding this false code to the
    >end I don't know where it is coming from. I have tried every combination to
    >get rid of it but my notinlist code will not work. My suppliercode comes from
    >the choice made from the cboProductName. My notinlist event is at the bottom.
    >Thanks for any help! I have other notinlist events and they work fine can't
    >figure out what is wrong with this one. (By the way if I need to put in the
    >ProductID as well as the Supplier Code I don't know the syntax...)
    >
    >SELECT tblSupplierCodes.SupCodeID, tblSupplierCodes.ProductID,
    >tblSupplierCodes.SupplierCode
    >FROM tblSupplierCodes
    >WHERE (((tblSupplierCodes.ProductID)<>False));


    I'm sorry, but I have NO clue where all this came from, nor what it
    has to do with my suggestion - which included *no code at all*, nor
    any suggestion to use code.

    My suggestion may have been unclear: it was to use a Combo Box to
    return the valid SupplierCodes, and to set the Limit to List property
    of the combo to No. This setting would PROHIBIT the use of the On Not
    In List event, which only fires if you *do* limit the combo entries to
    the list; instead, it would allow you to type a free-format,
    uncontrolled SubCodeID into the table field if the supplier code was
    not in tblSupplierCodes. I would expect that a RowSource like

    SELECT DISTINCT tblSupplierCodes.SupCodeID
    FROM tblSupplierCodes;

    would work... but again *I don't know your database structure*. I may
    be blowing smoke here as a result!

    John W. Vinson[MVP]
     

Share This Page