Data Entry Form with Cascading Combo Boxes

[Kcweir]

I have been through many examples of cascading combo boxes, and have succeeded in creating a form in my database that works, but I don't know how to use this form for data entry instead of just looking values up.

My database contains:
tblCategories
-CategoryID
-CategoryName

tblSubCategories
-SubCategoryID
-SubCategoryName
-CategoryID

tblVendors
-VendorID
-VendorName
....

tblVendorCategories
-VendorCategoryID
-VendorID
-CategoryID
-SubCategoryID

I need to be able to enter data to the VendorsCategories table using the cascading combo boxes I made in a form. The wizard creates a subform on it's own that does not narrow down choices for subcategories based on category choice.

I'm sure this is an easy problem for most, but I'm stumped and my boss is getting a little sick of waiting on me!

 

[yippie_ky_yay]

Hey Kcweir,

one way would be to add a button to your form - ie "New Vendor" - that could open a textbox "Enter new vendor:" - take that text and use it in an Insert Statement.

Add code like this to the new button:

Dim txtNewVendor As String
txtNewVendor = InputBox("Name of new vendor:")
DoCmd.RunSQL "INSERT INTO tblVendors ( VendorName ) VALUES ( """ & txtNewVendor & """ );"

Here though, I was assuming that the VendorID was an autonumber - if not, then you'll need to manually add that in too (ie using DMax).

Hope that helps!

-Sean

 

[Kcweir]

I don't think I explained the problem very well. All the vendors are already entered in the database. I now need to enter what category and subcategory each belong, many of them belonging to more than one. I created a form that looks up the vendor at the top and has a subform at the bottom. The problem with this data entry form is that it does not contain the cascading combo boxes, so a user could actually enter one category with a subcategory that does not belong with the original category.

I was able to make another form using a Microsoft article that created cascading combo boxes in Northwind Traders, along with help from this forum. The problem with those combo boxes is that they only look up data, they don't allow me to enter the category information to my VendersCategories table.

Sorry to be a pest, I really appreciate the help.
Kimberly

 

[WayneRyan]

Kimberly,

Why can't you just put your cascading combo boxes on your data
entry form, but make their control source the fields in your
table?

One set for searching (unbound), one set for data entry (bound).

Wayne

 

[Kcweir]

I think the main problem is that I don't really understand the criteria that I used to make them cascade. I just copied what was in the article and viola! it worked. Maybe I'm just in over my head.

 

[WayneRyan]

Kimberly,

Can you: Tools --> Database utilities --> Repair/Compact
ZIP your db
Post it here.

Wayne

 

[Kcweir]

Here you go, please let me know if you see any glaring mistakes I'm making too.

frmSelector is the cascading combo boxes that work for viewing.

frmVendorCategoriesDE is the form I'd like to use for Category and SubCategory data entry, but the subform does not work the way it should.

Thank you again.
Kimberly

 

[WayneRyan]

Kimberly,

I tried and failed.

I put two unbound combos on your subform to choose the
category and subcategory. I added two new bound fields
to store the values in column 0 of the combo.

It almost works, but something is very strange with it.

Here is the latest copy. I'll have another try and hopefully
someone else will take a peek.

Wayne

 

[Kcweir]

Thanks Wayne, you got much further than I did. I appreciate the try.

Kimberly

 

[WayneRyan]

Kimberly,

Don't give up yet ...

Wayne

 

[Kcweir]

Wayne, the form you made up works if I ignore the funny error that keeps flashing at me. I can't figure out what the problem is, the error tells me that I've entered the wrong type of information in a field, but I don't even know what field it refers to. That will probably be pretty darn annoying for my boss to enter these categories with that silly thing flashing the whole time. Can I disable the error message and move on?

If this ever works, I'm going to throw a party!

Kimberly

 

[WayneRyan]

Kimberly,

I lost my copy.

How about if you compact, ZIP and post it again on a new thread.

It is now technically a different problem ...

Maybe we'll get some fresh eyes to look at it.

Wayne