Being able to enter new information in a lookup field

[Zipster1967]

I am not sure I am stating this right but I want to be able to enter information in an inventory database I have created but do not yet have all the categories entered into the category table. I would like to be able to add new categories in the data entry form and have it update the category table with the new categories. The same thing with sub-categories, sections, Areas, and shelves. (This is a shop inventory database to be able to locate everything in the shop without having to look through a bunch of different containers. Is there an easy way to do this without having to go into VBA code or macros? (Not fluent in macro programming yet.) I will try and give all the information I have in the database already and see if I am on the right track here.
Tables: Contain:
Categories Woodworking,Automotive,Chemicals,etc...
ShopInventory item information with links to all other tables
Sections Areas of shop (Quadrants)
Shelves Shelving unit name
Containers If item is in a container like a toolbox, plastic tub, cardboard box
Levels Shelf number item is on 1 to 7 for most shelving units
ContainerTypes Container types set as Cardboard box, plastic tub, compartmentalized case, etc...
Fields in each table:
ShopInventory:
1.ItemID Numeric autonumber
2.CategoryID Lookup to categories table
3.SectionID Lookup to Sections table
4.ShelfID Lookup to Shelves table
5.ItemName Name of item
6.ItemDesc Detailed description of item
7.PurchaseDate Date purchased
8.LevelNumber Lookup to Levels table
9.ContainerName Lookup to Containers table

What I am trying to create is a form that I can enter new items into that will list all the categories in a dropdown box but allow entry of new category if it doesn;t already exist. Same with the section, shelving unit, Level, containerName.

The rest of the table are very simple.
Categories:
1.CategoryID Autonumber
2.CategoryName Entered Name for category
Section:
1.SectionID Autonumber
2.SectionName Entered Name for section
Shelves:
1.ShelfunitID Autonumber
2.ShelfUnitName Name of the shelving unit
Levels:
1.LevelID Autonumber
2.LevelName Entered name of shelf or floor or wall or ceiling
Containers:
1.ContainerID Autonumber


2,ContainerName entered name of container
3.Containertype Lookup to containertype table
Containertype:
1.COntainerTypeID Autonumber
2.ContainerTypeName entered as cardboard Box, Plastic Tub, Compartmentalized case, etc.

I hope that isn;'t too confusing. Any help in structure hints or how I should go about getting the form built would be great. Or any pointers on where to get the instructions for how to do this online would be great.

[Bob Fitz]

Hi Zipster1967

Adding items to tables from an entry in a list box can be done, but I don't know of any way to do it without using VBA.

You would need code in the "Not In List" event of the combo box.
It is not hard to do if you have some knowledge of VBA coding.

Below is some code that I have used to add a new category to one of my tables (called "tblCat"). It should be quite simple to make the changes required for you to be able to use it.
If you want to use it, you're welcome
If you need some more help, post back there are plenty of people here that are able to help.

 
Private Sub CatID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_CatID_NotInList_Error
Dim rec As DAO.Recordset
 
NewData = StrConv(NewData, vbProperCase)
If MsgBox("Add '" & NewData & "' to the list?", vbQuestion + vbYesNo + vbDefaultButton2, "Confirmation Required") = vbYes Then
Set rec = CurrentDb.OpenRecordset("tblCat")
With rec
.AddNew
.Fields("Category") = NewData
.Update
End With
Set rec = Nothing
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
Exit_ErrorHandler:
Exit Sub
Err_CatID_NotInList_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CatID_NotInList of VBA Document Form_frmSalesDet at Line " & Erl
Resume Exit_ErrorHandler
End Sub