Form to add a unique entry to a table that it is using as a list

[coffeeman]

I have a Table Box Selection that contains all the different types of Boxes that we could receive in and use for production.

When a box is received in, there is a form that I enter in the box receive date, the type of box, the box lot code, etc.

When I go to the box type field in the form, there is a list box that pulls from the Table Box Selection so that I can enter in the same one and not spell it wrong so grouping is not an issue.

When we get a new customer/product, they may have a new kind of box that is not in the system.

I need to be able to enter this in the form, and then this unique type will be in the Table Box Selection from here on out. I have tried to set it up before, but all I get is duplicate results into the Table Box Selection everytime I select it in the form.

How can you make it only one unique entry in the table instead of multiple entries?

THANKS!

 

[coffeeman]

Yes, the row source is a table that has only box type in it.

What do I enter in as far as code for the NOT IN LIST event so it will add an entry to the box type table and requery the form?

Thanks again Pat!

 

[John Big Booty]

The way I have approached this sort of thing in the past is to have a message box in the NotInList event;

MsgBox "Double-click this field to add an entry to the list."
    Response = acDataErrContinue

Then to open the form you use to add new details put the following in the DoubleClick event;

    Dim stDocName As String


    stDocName = "FRM_YourFormName"
    
    DoCmd.OpenForm stDocName, , , , , , 
    
    Me.Requery

Exit_Command42_Click: [COLOR="DarkGreen"]'Rename to fit you click event name[/COLOR]
    Exit Sub

Err_Command42_Click: [COLOR="DarkGreen"]'Rename to fit you click event name[/COLOR]
    MsgBox Err.Description
    Resume Exit_Command42_Click [COLOR="DarkGreen"]'Rename to fit you click event name[/COLOR]

This is pretty much the same way they do it in the DB templates you get in Access.

 

[coffeeman]

I tried subbing the names into the code you wrote, but could not get it to work.

Here is my row source:
SELECT [Drop Down Box - Inner Box].[Boxes/Bags] FROM [Drop Down Box - Inner Box] ORDER BY [Drop Down Box - Inner Box].[Boxes/Bags];

My form name is:
Receiving: Boxes/Bags

What can I enter for it to work in the form to make the new box type entered in the form to populate in the "Drop Down Box - Inner Box" table?

Thanks and sorry for the confusion.

 

[John Big Booty]

Any chance of posting a copy of your DB?

 

[coffeeman]

Ok, but I must warn you. I didn't create this db and the naming of tables and labels are not done correctly. I cannot send the whole db because it is too large zipped and it has some confidentiality info.

Thanks for looking into it!



Just go to the form, Receiving Entry: Box/Bag.

Also, Product Description is the drop down list that I would like to add to for new possible types to select from.

 

[John Big Booty]

OK firstly that little piece of code I gave you before must open a new form that feeds into the source table for your Combo, this then lets you add new values to that table.

I've added that form to your sample DB. I've also added an OpenArgs to the Docmd that opens the form, that way when the form opens it tests the openArgs to see if it should go straight to a new record or just to the first record in the table.

I've also set the Limit to List property of your Combo to Yes. Other wise there is no point in the whole exercise.

Have a look I hope it helps.

 

[coffeeman]

Yeah, this works great!

I have one other question is there a way to make a popupwindow (small) to occur when the cursor is over the Product Description field?

This would explain to "double click for new entry"

Thanks again!

 

[coffeeman]

nevermind, found the Control Tip Text to type it in.