Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    NotInList Event - Add Value to List (Access 2000)

    Can someone please help me with sample code that could be triggered by the "NotInList" event if user enters a value that is not in the list when "LimitToList" property of combo box control is set to False. We need a custom function to allow the user to add a new item to the look-up list from the main form. The lookup table has same field name as the combo box field in main table. For example, in main table, user chooses from a drop-down list of chemicals or raw materials, but if the chemical or raw material is not in the list, we want the user to be able to add the name of the new chemical or raw material in the form field, and then behind the scenes add the new chemical or raw material to the look-up list table so in future it will be included in combo-box's drop-down list. Any help with a snippet of code would be greatly appreciated. Many thanks...Mary

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList Event - Add Value to List (Access 2000)

    Does this help:
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList Event - Add Value to List (Access 2000)

    Many thanks for your quick response! Your attachment looks very promising. I'll read it and see if I'm able to modify for my database purposes. Will be back in touch soon. Thanks again...Mary

  4. #4
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList Event - Add Value to List (Access 2000)

    Dave, thank you so very much! The Attachment you sent was exactly what I needed, and it certainly did the trick. I really appreciate the tip. May I ask you another question...My lookup table has two columns--RawMaterial and Code. The RawMaterial column is the combo box's lookup list. The next field on my main form is a textbox field for the Code, which I have programmed to populate automatically if the RawMaterial is selected in the list [Me!Code = Me!RawMaterial.Column(1)]. However, if user types in a new item that is not in list, how do I get the textbox's new "Code" to append to the lookup table in Code column next to the new rawMaterial? Do you have another snippet of code I can add to the Append2Table function, or a separate procedure that would do the trick? Many thanks for helping me out with this dilemma....Mary

  5. #5
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList Event - Add Value to List (Access 2000)

    Mary, Yes I do have some extra code for this by way of a dialog promt, but I;ll have to find it first.
    The other way would to have an extra pop up form where, if not in list, the pop up form opens and allows the user to enter the code manually, is this more of what you were looking for.

    Post back if you need an example.

  6. #6
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList Event - Add Value to List (Access 2000)

    Hello, Dave...Yes, thank you, I would appreciate the additional code. The second option you mention might be helpful, too. Did I understand correctly that if the NotInList event occurs that we could program the event to pop up the form so that user can enter the new raw material and its code directly into the lookup list table? Would the user then be brought back to the combo box on main form to select that new item from the list, or could it be populated automatically? Many thanks for all your help....Mary

  7. #7
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList Event - Add Value to List (Access 2000)

    Mary
    A couple of questions here.
    Option 2 seemes better for your purpose.

    1. Are you using a main form with the combo on it ? (Form name and underlying table name please)

    2. The combo, is its rowsource the same table as main form, if not whats its name.

    Please supply table names for the above and any relevant field names and I'll sort you a demo.

  8. #8
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList Event - Add Value to List (Access 2000)

    Dave, my main table is named "tbl Main Table" and it has a combo box named "RawMaterial". My lookup table is named "tbl List of Raw Materials". The forms are called "frm Main Table" and "frm List of Raw Materials". Sure do appreciate your helping me with this. Many thanks...Mary

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList Event - Add Value to List (Access 2000)

    Mary
    The demo will be quite easy to sort but give me a little time to do so.

    A little pointer for you, How far into your database are you ?
    You naming method isn't a desirable way.
    later you will have problems with spaces in form names and tables.
    If you do a search on "Naming Convention in Access", try Google, you'll find plenty of info on this.

    tbl Main Table really ought to be tblMainTable

    RawMaterial = cboRawMaterial
    tbl List of Raw Materials = tblListOfRawMaterials etc etc etc

    Is it too late to change these ??
    Just a pointer anyway.

    I'll post the demo when I've done it.

  10. #10
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList Event - Add Value to List (Access 2000)

    Mary
    Here's a demo specifically how you asked it to be.
    This populates the list of materials table.
    If you need to populate the main table also, you can add some code to the after update event of the combo.
    ie
    Me.txtMyFieldForCode = Me.cboRawMaterial.Column(1)
    Me.txtMyFieldForMaterial = Me.cboRawMaterial.Column(0)
    DoCmd.RunCommand acCmdSaveRecord
    Forms!frmMainTable.Refresh


    Hope this helps
    Attached Files Attached Files

  11. #11
    2 Star Lounger
    Join Date
    Jan 2003
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NotInList Event - Add Value to List (Access 2000)

    Hello, Dave...Thank you very much...I appreciate your advice and you're right about using the naming convention. Actually, I know better, and luckily wasn't that far along on this new database, so I renamed all my objects as you suggested. I've put the database aside for awhile, though, as I was getting tired and a bit confused. I will soon go back to it and try the code you provided to populate the lookup list table, as well as the main table. Will be in touch soon to let you know how it goes. In the meantime, I wanted you to know that I appreciate your help very much! Thanks again...Mary

Posting Permissions

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