Welcome to SPN

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

Sign Up Now!

NotinList event

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

  1. Tom

    Tom
    Expand Collapse
    Guest

    Hi,

    I looked at some of the posting regurding that but none could help me.

    I have an unbound comboBox called "Vendor". The source to that list is a
    table called "Vendors".
    When a user type in vendor`s name that is not on the list, I would like to
    have a massage says something. when the user choose "yes" it will be added
    to the list for next time. If the user will choose no it will do undo, so
    the comboBox will be empty.

    Thanks for the help.

    Tom
     
  2. Loading...


  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Hi Tom,

    Do you have a Vendors table with

    VendorID, autonumber
    Vendor, text

    and are you storing VendorID in a related table?

    Is the combobox bound to VendorID while displaying Vendor?


    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Tom wrote:
    > Hi,
    >
    > I looked at some of the posting regurding that but none could help me.
    >
    > I have an unbound comboBox called "Vendor". The source to that list is a
    > table called "Vendors".
    > When a user type in vendor`s name that is not on the list, I would like to
    > have a massage says something. when the user choose "yes" it will be added
    > to the list for next time. If the user will choose no it will do undo, so
    > the comboBox will be empty.
    >
    > Thanks for the help.
    >
    > Tom
    >
    >
     
  4. Tom

    Tom
    Expand Collapse
    Guest

    Hi Crystal,
    Thanks for responding fast.
    In the vendor table there is only 1 column which is the vendor name(text).
    (I know it`s not ideal)
    I`m storing it in another table with some other info.
    The combobox itself is unbound.

    Basically, the information from this form, populate fields
    in a different form.
    If you need more info, please ask.

    Thanks,
    Tom

    "strive4peace" <"strive4peace2006 at yahoo dot com"> wrote in message
    news:e0wdDlIfGHA.1272@TK2MSFTNGP03.phx.gbl...
    > Hi Tom,
    >
    > Do you have a Vendors table with
    >
    > VendorID, autonumber
    > Vendor, text
    >
    > and are you storing VendorID in a related table?
    >
    > Is the combobox bound to VendorID while displaying Vendor?
    >
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    > Tom wrote:
    >> Hi,
    >>
    >> I looked at some of the posting regurding that but none could help me.
    >>
    >> I have an unbound comboBox called "Vendor". The source to that list is a
    >> table called "Vendors".
    >> When a user type in vendor`s name that is not on the list, I would like
    >> to have a massage says something. when the user choose "yes" it will be
    >> added to the list for next time. If the user will choose no it will do
    >> undo, so the comboBox will be empty.
    >>
    >> Thanks for the help.
    >>
    >> Tom
     
  5. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: NotinList event -- use ID fields, update query to convert text

    Hi Tom,

    the first thing you should do is USE VendorID

    1. modify your Vendors table to include it as an autonumber

    2. modify your related tables and add VendorID as a number
    (long integer, defaltValue = Null)

    now you need to update the VendorIDs in your related tables
    based on the text.

    here are general instructions on how to do that:

    QUERY TO UPDATE RELATED ID

    you are storing a text field in a related table that relates
    to a text field in the main table
    now, you have added a long integer ID in the related table
    and want to populate it with the IDs from the main table

    make a new query based on the table you want to change

    add the main table to your query

    link the two tables on the common text field

    change the query type from a Select Query to an Update Query
    from the menu bar --> Query, Update

    on the grid:

    field --> ID_fieldname
    table --> related_tablename
    UpdateTo --> main__tablename.ID_fieldname

    then, RUN (!) your query

    a quick way check to ensure that all related IDs were filled
    out (without writing comparison query) is:
    1. open related table
    2. sort by common text field
    3. visually scan ID field to make sure it is filled out for
    every record where there was text

    Naturally, if you have thousands of records, you can design
    a query to show records where text Is Not Null and ID_field
    Is Null

    -- then, add those unmatched text values to the main table
    OR edit them so text text matches and run query again and/or
    manually fill IDs

    Then, when you are satisfied that all data has been linked,
    delete the text field from the related table
    compact/repair database to regain the space it was using

    naturally, back up your database before running any action
    queries on it

    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    on your form, here is an example with the properties you
    need to set for a combobox that stored the ID and displays
    the text

    combobox control

    Name --> VendorID
    ControlSource --> VendorID
    RowSource -->
    SELECT VendorID, Vendor
    FROM Vendors
    ORDER BY Vendor

    BoundColumn --> 1
    ColumnCount --> 2

    columnWidths --> 0;2
    (etc for however many columns you have
    -- the ID column will be hidden since its width is zero)

    ListWidth --> 2
    (should add up to the sum of the column widths)

    VendorID will be stored in the form RecordSource while
    showing you information from another table...

    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    here is code you can use for the NotInList event (remember
    to set LimitToList=Yes)

    Private Sub VendorID_NotInList( _
    NewData As String, _
    Response As Integer)

    Dim s As String
    Dim mVendorID As Long
    Dim mVendor As String

    If Len(Trim(NewData)) = 0 Then Exit Sub

    s = "INSERT INTO Vendors (Vendor) " _
    & " SELECT '" & NewData & "';"

    CurrentDb.Execute s

    CurrentDb.TableDefs.Refresh
    DoEvents

    mVendorID = Nz(DMax("VendorID", "Vendors"))

    If mVendorID > 0 Then
    Response = acDataErrAdded
    Me.VendorID = mVendorID
    Else
    Response = acDataErrContinue
    End If

    End Sub
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Tom wrote:
    > Hi Crystal,
    > Thanks for responding fast.
    > In the vendor table there is only 1 column which is the vendor name(text).
    > (I know it`s not ideal)
    > I`m storing it in another table with some other info.
    > The combobox itself is unbound.
    >
    > Basically, the information from this form, populate fields
    > in a different form.
    > If you need more info, please ask.
    >
    > Thanks,
    > Tom
    >
    > "strive4peace" <"strive4peace2006 at yahoo dot com"> wrote in message
    > news:e0wdDlIfGHA.1272@TK2MSFTNGP03.phx.gbl...
    >
    >>Hi Tom,
    >>
    >>Do you have a Vendors table with
    >>
    >>VendorID, autonumber
    >>Vendor, text
    >>
    >>and are you storing VendorID in a related table?
    >>
    >>Is the combobox bound to VendorID while displaying Vendor?
    >>
    >>
    >>Warm Regards,
    >>Crystal
    >>Microsoft Access MVP 2006
    >>
    >> *
    >> Have an awesome day ;)
    >>
    >> remote programming and training
    >> strive4peace2006 at yahoo.com
    >>
    >> *
    >>
    >>Tom wrote:
    >>
    >>>Hi,
    >>>
    >>>I looked at some of the posting regurding that but none could help me.
    >>>
    >>>I have an unbound comboBox called "Vendor". The source to that list is a
    >>>table called "Vendors".
    >>>When a user type in vendor`s name that is not on the list, I would like
    >>>to have a massage says something. when the user choose "yes" it will be
    >>>added to the list for next time. If the user will choose no it will do
    >>>undo, so the comboBox will be empty.
    >>>
    >>>Thanks for the help.
    >>>
    >>> Tom

    >
    >
    >
     
  6. Tom

    Tom
    Expand Collapse
    Guest

    Re: NotinList event -- use ID fields, update query to convert text

    Thanks a lot for the help I`ll try to work this.

    Tom
    "strive4peace" <strive4peace2006@EDITyahoo.com> wrote in message
    news:eAr$JgJfGHA.1276@TK2MSFTNGP03.phx.gbl...
    > Hi Tom,
    >
    > the first thing you should do is USE VendorID
    >
    > 1. modify your Vendors table to include it as an autonumber
    >
    > 2. modify your related tables and add VendorID as a number (long integer,
    > defaltValue = Null)
    >
    > now you need to update the VendorIDs in your related tables based on the
    > text.
    >
    > here are general instructions on how to do that:
    >
    > QUERY TO UPDATE RELATED ID
    >
    > you are storing a text field in a related table that relates to a text
    > field in the main table
    > now, you have added a long integer ID in the related table and want to
    > populate it with the IDs from the main table
    >
    > make a new query based on the table you want to change
    >
    > add the main table to your query
    >
    > link the two tables on the common text field
    >
    > change the query type from a Select Query to an Update Query
    > from the menu bar --> Query, Update
    >
    > on the grid:
    >
    > field --> ID_fieldname
    > table --> related_tablename
    > UpdateTo --> main__tablename.ID_fieldname
    >
    > then, RUN (!) your query
    >
    > a quick way check to ensure that all related IDs were filled out (without
    > writing comparison query) is:
    > 1. open related table
    > 2. sort by common text field
    > 3. visually scan ID field to make sure it is filled out for every record
    > where there was text
    >
    > Naturally, if you have thousands of records, you can design a query to
    > show records where text Is Not Null and ID_field Is Null
    >
    > -- then, add those unmatched text values to the main table
    > OR edit them so text text matches and run query again and/or manually fill
    > IDs
    >
    > Then, when you are satisfied that all data has been linked, delete the
    > text field from the related table
    > compact/repair database to regain the space it was using
    >
    > naturally, back up your database before running any action queries on it
    >
    > '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    >
    > on your form, here is an example with the properties you need to set for a
    > combobox that stored the ID and displays the text
    >
    > combobox control
    >
    > Name --> VendorID
    > ControlSource --> VendorID
    > RowSource -->
    > SELECT VendorID, Vendor
    > FROM Vendors
    > ORDER BY Vendor
    >
    > BoundColumn --> 1
    > ColumnCount --> 2
    >
    > columnWidths --> 0;2
    > (etc for however many columns you have
    > -- the ID column will be hidden since its width is zero)
    >
    > ListWidth --> 2
    > (should add up to the sum of the column widths)
    >
    > VendorID will be stored in the form RecordSource while showing you
    > information from another table...
    >
    > '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    >
    > here is code you can use for the NotInList event (remember to set
    > LimitToList=Yes)
    >
    > Private Sub VendorID_NotInList( _
    > NewData As String, _
    > Response As Integer)
    >
    > Dim s As String
    > Dim mVendorID As Long
    > Dim mVendor As String
    >
    > If Len(Trim(NewData)) = 0 Then Exit Sub
    >
    > s = "INSERT INTO Vendors (Vendor) " _
    > & " SELECT '" & NewData & "';"
    >
    > CurrentDb.Execute s
    >
    > CurrentDb.TableDefs.Refresh
    > DoEvents
    >
    > mVendorID = Nz(DMax("VendorID", "Vendors"))
    >
    > If mVendorID > 0 Then
    > Response = acDataErrAdded
    > Me.VendorID = mVendorID
    > Else
    > Response = acDataErrContinue
    > End If
    >
    > End Sub
    > '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    >
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    > Tom wrote:
    >> Hi Crystal,
    >> Thanks for responding fast.
    >> In the vendor table there is only 1 column which is the vendor
    >> name(text). (I know it`s not ideal)
    >> I`m storing it in another table with some other info.
    >> The combobox itself is unbound.
    >>
    >> Basically, the information from this form, populate fields
    >> in a different form.
    >> If you need more info, please ask.
    >>
    >> Thanks,
    >> Tom
    >>
    >> "strive4peace" <"strive4peace2006 at yahoo dot com"> wrote in message
    >> news:e0wdDlIfGHA.1272@TK2MSFTNGP03.phx.gbl...
    >>
    >>>Hi Tom,
    >>>
    >>>Do you have a Vendors table with
    >>>
    >>>VendorID, autonumber
    >>>Vendor, text
    >>>
    >>>and are you storing VendorID in a related table?
    >>>
    >>>Is the combobox bound to VendorID while displaying Vendor?
    >>>
    >>>
    >>>Warm Regards,
    >>>Crystal
    >>>Microsoft Access MVP 2006
    >>>
    >>> *
    >>> Have an awesome day ;)
    >>>
    >>> remote programming and training
    >>> strive4peace2006 at yahoo.com
    >>>
    >>> *
    >>>
    >>>Tom wrote:
    >>>
    >>>>Hi,
    >>>>
    >>>>I looked at some of the posting regurding that but none could help me.
    >>>>
    >>>>I have an unbound comboBox called "Vendor". The source to that list is a
    >>>>table called "Vendors".
    >>>>When a user type in vendor`s name that is not on the list, I would like
    >>>>to have a massage says something. when the user choose "yes" it will be
    >>>>added to the list for next time. If the user will choose no it will do
    >>>>undo, so the comboBox will be empty.
    >>>>
    >>>>Thanks for the help.
    >>>>
    >>>> Tom

    >>
    >>
     
  7. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: NotinList event -- use ID fields, update query to convert text

    you're welcome, Tom ;) happy to help

    post back if you run into trouble

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    Tom wrote:
    > Thanks a lot for the help I`ll try to work this.
    >
    > Tom
    > "strive4peace" <strive4peace2006@EDITyahoo.com> wrote in message
    > news:eAr$JgJfGHA.1276@TK2MSFTNGP03.phx.gbl...
    >
    >>Hi Tom,
    >>
    >>the first thing you should do is USE VendorID
    >>
    >>1. modify your Vendors table to include it as an autonumber
    >>
    >>2. modify your related tables and add VendorID as a number (long integer,
    >>defaltValue = Null)
    >>
    >>now you need to update the VendorIDs in your related tables based on the
    >>text.
    >>
    >>here are general instructions on how to do that:
    >>
    >>QUERY TO UPDATE RELATED ID
    >>
    >>you are storing a text field in a related table that relates to a text
    >>field in the main table
    >>now, you have added a long integer ID in the related table and want to
    >>populate it with the IDs from the main table
    >>
    >>make a new query based on the table you want to change
    >>
    >>add the main table to your query
    >>
    >>link the two tables on the common text field
    >>
    >>change the query type from a Select Query to an Update Query
    >>from the menu bar --> Query, Update
    >>
    >>on the grid:
    >>
    >>field --> ID_fieldname
    >>table --> related_tablename
    >>UpdateTo --> main__tablename.ID_fieldname
    >>
    >>then, RUN (!) your query
    >>
    >>a quick way check to ensure that all related IDs were filled out (without
    >>writing comparison query) is:
    >>1. open related table
    >>2. sort by common text field
    >>3. visually scan ID field to make sure it is filled out for every record
    >>where there was text
    >>
    >>Naturally, if you have thousands of records, you can design a query to
    >>show records where text Is Not Null and ID_field Is Null
    >>
    >>-- then, add those unmatched text values to the main table
    >>OR edit them so text text matches and run query again and/or manually fill
    >>IDs
    >>
    >>Then, when you are satisfied that all data has been linked, delete the
    >>text field from the related table
    >>compact/repair database to regain the space it was using
    >>
    >>naturally, back up your database before running any action queries on it
    >>
    >>'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    >>
    >>on your form, here is an example with the properties you need to set for a
    >>combobox that stored the ID and displays the text
    >>
    >>combobox control
    >>
    >>Name --> VendorID
    >>ControlSource --> VendorID
    >>RowSource -->
    >>SELECT VendorID, Vendor
    >>FROM Vendors
    >>ORDER BY Vendor
    >>
    >>BoundColumn --> 1
    >>ColumnCount --> 2
    >>
    >>columnWidths --> 0;2
    >>(etc for however many columns you have
    >>-- the ID column will be hidden since its width is zero)
    >>
    >>ListWidth --> 2
    >>(should add up to the sum of the column widths)
    >>
    >>VendorID will be stored in the form RecordSource while showing you
    >>information from another table...
    >>
    >>'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    >>
    >>here is code you can use for the NotInList event (remember to set
    >>LimitToList=Yes)
    >>
    >>Private Sub VendorID_NotInList( _
    >> NewData As String, _
    >> Response As Integer)
    >>
    >> Dim s As String
    >> Dim mVendorID As Long
    >> Dim mVendor As String
    >>
    >> If Len(Trim(NewData)) = 0 Then Exit Sub
    >>
    >> s = "INSERT INTO Vendors (Vendor) " _
    >> & " SELECT '" & NewData & "';"
    >>
    >> CurrentDb.Execute s
    >>
    >> CurrentDb.TableDefs.Refresh
    >> DoEvents
    >>
    >> mVendorID = Nz(DMax("VendorID", "Vendors"))
    >>
    >> If mVendorID > 0 Then
    >> Response = acDataErrAdded
    >> Me.VendorID = mVendorID
    >> Else
    >> Response = acDataErrContinue
    >> End If
    >>
    >>End Sub
    >>'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    >>
    >>
    >>Warm Regards,
    >>Crystal
    >>Microsoft Access MVP 2006
    >>
    >> *
    >> Have an awesome day ;)
    >>
    >> remote programming and training
    >> strive4peace2006 at yahoo.com
    >>
    >> *
    >>
    >>Tom wrote:
    >>
    >>>Hi Crystal,
    >>>Thanks for responding fast.
    >>>In the vendor table there is only 1 column which is the vendor
    >>>name(text). (I know it`s not ideal)
    >>>I`m storing it in another table with some other info.
    >>>The combobox itself is unbound.
    >>>
    >>>Basically, the information from this form, populate fields
    >>>in a different form.
    >>>If you need more info, please ask.
    >>>
    >>>Thanks,
    >>> Tom
    >>>
    >>>"strive4peace" <"strive4peace2006 at yahoo dot com"> wrote in message
    >>>news:e0wdDlIfGHA.1272@TK2MSFTNGP03.phx.gbl...
    >>>
    >>>
    >>>>Hi Tom,
    >>>>
    >>>>Do you have a Vendors table with
    >>>>
    >>>>VendorID, autonumber
    >>>>Vendor, text
    >>>>
    >>>>and are you storing VendorID in a related table?
    >>>>
    >>>>Is the combobox bound to VendorID while displaying Vendor?
    >>>>
    >>>>
    >>>>Warm Regards,
    >>>>Crystal
    >>>>Microsoft Access MVP 2006
    >>>>
    >>>>*
    >>>> Have an awesome day ;)
    >>>>
    >>>> remote programming and training
    >>>> strive4peace2006 at yahoo.com
    >>>>
    >>>>*
    >>>>
    >>>>Tom wrote:
    >>>>
    >>>>
    >>>>>Hi,
    >>>>>
    >>>>>I looked at some of the posting regurding that but none could help me.
    >>>>>
    >>>>>I have an unbound comboBox called "Vendor". The source to that list is a
    >>>>>table called "Vendors".
    >>>>>When a user type in vendor`s name that is not on the list, I would like
    >>>>>to have a massage says something. when the user choose "yes" it will be
    >>>>>added to the list for next time. If the user will choose no it will do
    >>>>>undo, so the comboBox will be empty.
    >>>>>
    >>>>>Thanks for the help.
    >>>>>
    >>>>> Tom
    >>>
    >>>

    >
     

Share This Page