Cascading combobox problems

[New2VB]

Hi Gurus,

Mailman has been helping with this in the vb forum but I decided to move it here because I don't think this is a vb problem.

Looking at the attached example I am trying to get the employee combobox (combo2) to show only those employees who work at the company selected in the listbox (list2).

I can't work out why the record selectors display the right data but the combo-dropdown doesn't. I have tried refreshes, requeries and some really weird combinations but with no luck.

I have seen:
.pcreview.co.uk/forums/thread-2826331.php
.fontstuff.com/access/acctut10.htm
.access-programmers.co.uk/forums/showthread.php?t=116226

amongst others but cannot see what I am doing wrong. Can anyone help please?

 

[G37Sam]

You don't need a subform, the employees form with an unbound list should do

You need to requery the combobox everytime the list is updated.. I uploaded it for you check it out

View attachment test.mdb

 

[New2VB]

Hi G37Sam,

I'm sorry, I don't understand.

If I try your example and select (for e.g. NorthWind) the wrong employee names are displayed, also when I select the name I get a type mismatch error in rs.FindFirst "[ID] = " & Str(Nz(Me![Combo2], 0))

 

[G37Sam]

Did you download the database I attached?

 

[New2VB]

the one with the listbox on the right of the form, yes

 

[G37Sam]

It's working fine with me.. Everytime I select a company from the listbox, the combo box gets filtered to show employees only working in that company

 

[New2VB]

I am opening the "employees" form, clicking on "NorthWind", selecting "Barney" and getting a Run-time error:'13' error message.

I then click "end" to quit the debugger. I then click on SouthWind and select "Charley" which brings up the error message again. I click end.

I then go back to "NorthWind" and "Bob's" name has disappeared. When I look in the Employees table "Bob" has been overwritten by "Barney"

 

[vbaInet]

Are you trying to filter the Name combo box in the subform from the main form?

 

[New2VB]

Hi vbaInet,

I don't understand your question, the idea is to be able to click on the company name in the listbox and only have the employees who work at that company available for selection in the combobox

 

[vbaInet]

That's not possible I'm afraid. For everytime you try to "cascade" the combo box you are basically updating that name field. Once it's updated and you click another item from the list, that new value becomes saved.

If the combo was unbound then it would be possible, but that would mean you would have to programatically save any changes.

 

[New2VB]

Thanks vbaInet

I started to get the feeling I was going around in circles. Ok then, if I may ask your advice:-

The end-goal is to write all the information I want into a table, let's call it "Orders", where I want to make it as easy as possible for the user to enter the form information.

I would like a listbox containing company names which, when one is selected, populates a second listbox or combobox with the names of the employees who work at that company. When a name is selected, textboxes containing their contact tel. numbers, departments, addresses are automatically populated and a blank textbox is left available to enter their requirements.

How who you recommend I achieve this as I have something similar already but the existing model does not have an employee name/department/contact info dropdowns - everything is entered manually.

 

[vbaInet]

I see what you're trying to achieve. My advice is, in a scenario like that you wouldn't use cascade combo boxes. The best way to approach this is to make a search form.

So you would have a few text boxes - Orders_Search, Employee_Name_Search, Department_Search, and a Search button.

The list of possible matches will show up in the detail section of the form or you can use a listbox to list out the details (because it seems you want to use the employee name to assign them to a task??).

 

[New2VB]

Sort of but not quite, I 'll knock up a demo.mdb and upload it to give you a better idea but I'm too knackered to do it tonight. I 'll put it up tomorrow. I have had an idea though whereby I will try to create a form using CompanyName as a listbox. That will contain an Employees subform with the name, contact details etc. That in turn will contain a 2nd subform containing the details of their requirements.

Your thoughts?

 

[vbaInet]

It will only work if subform 1 is a Single Form.

What is the whole idea behind having a cascade? What will you do with the streamlined record?

 

[G37Sam]

I am opening the "employees" form, clicking on "NorthWind", selecting "Barney" and getting a Run-time error:'13' error message.

I then click "end" to quit the debugger. I then click on SouthWind and select "Charley" which brings up the error message again. I click end.

I then go back to "NorthWind" and "Bob's" name has disappeared. When I look in the Employees table "Bob" has been overwritten by "Barney"

Oh.. sorry I missed that. I never tried actually selecting but yes, since the combo box is bound, it will cause an error, especially once you navigate through records.

You can set the combo box to unbound, and have the value in the table updated.. I'll edit the DB again for you and re-upload (after Spain's game lol)

 

[New2VB]

vbaInet - I am trying to build a form based on 3 linked tables to record:
CompanyName>EmployeeName + details>What is ordered, when oredser received etc. etc. Sort of an "internal" requisition form

G37Sam - There is no rush, I've been up since 4 a.m. and have had enough for 1 day.

I look forward to your company and advice again tomorrow, thanks for all your input. It is appreciated.

 

[G37Sam]

Ok here's the deal, I added an orders table and form, this is where all the orders info will be stored. The employee form will only contain employees information (I added contactno and address fields)

I also added a mainform to choose whether you want to add a new order or view current orders.

- New order: opens an unbound form [singleorder] with unbound controls (this way, you won't get error everytime you choose an employee once filtered). Once the save order button has been clicked, the info will be saved into orders table using DAO.

- View orders: opens a continuous bound form [order] listing all the orders which you have added, you can't add or edit them from here, you can only delete them. There's an edit button on the side that will open another unbound form [singleorder] for you where you can edit that record and save it again.

Hope this helps, and let me know if there's anything more you need

Sam

 

[New2VB]

Hi Guys,

Sorry to have been away longer than intended...

G37SAM, thanks for the db, it gave me some ideas on how to solve some problems, but I am still left with a couple left.

If you have a look at the attached db you will see what I mean. If you open the "Company" form it is displaying as intended except that the "EmployeeNo" field in "OrdersSub" subform does not display the correct corresponding employee no. for the correct employee, I though this should have been a textbox with a preset ControlSource. Am I completely wrong?

The second thing is that when you double-click on one of the names in the "EmployeesSub" suform it launches an "Orders" form. The combobox on the orders form displays the correct name in relation to the company selected in the "Company" form but doesn't display the correct EmployeeNo (which I have again removed due to frustration) taken from the Employee table.

Finally, when the Save/Close button is clicked on the Orders form, although the new record is written into the Orders table, the "OrdersSub" subform does not update although I have tried requerying just about everything.

If you have the time I would be very grateful if you could point me in the right direction please. Many thanks.