Cascading Combo boxes- bound; Cant select

[AishlinnAnne]

I am so new at this, I need my form to automatically transfer the data I enter to the table connected. However, the only way I can get my combo boxes to even allow me to make a selection, they have to be unbound, which doen't add that record to the connected table. If I try to assign a control source to the combo box, access lets me pull down the menu, but will not allow me to make a selection in order to move on to the next combo box.

PLEASE HELP

[ssanfu]

Not a lot of info to go on......

What is the field data type for the field you want to bind the control to? (Text, Integer, Long,...)
What is the row source for the combo box?
What is the bound column of the combo box?
Is the bound column the same data type as the field bound to the combo box?

[AishlinnAnne]

I have 4 Cascading Combo boxes in this order, each drop-down list depends on the above's selection:
1. cboBranch (UNBOUND; row source: 'SELECT [tblBranch].[BranchID], [tblBranch].[BranchName] FROM tblBranch ORDER BY [BranchID];')
2. cboManager (UNBOUND; row source: 'SELECT [tblManager].[ManagerID], [tblManager].[ManagerName] FROM tblManager ORDER BY [ManagerName];')
3. cboCustomer (UNBOUND; row source: 'SELECT [tblCustomer].[CustomerID], [tblCustomer].[CustomerName] FROM tblCustomer ORDER BY [CustomerName];')
4. cboBuyer (UNBOUND; row source: 'SELECT [tblBuyer].[BuyerID], [tblBuyer].[BuyerName] FROM tblBuyer ORDER BY [BuyerName];')

Input Location
1. Auto fill into tblBidLog.BranchID [I used Lookup Wizard to use data from tblBranch.BranchID]= Data Type for both: Number-Long Integer (they're all the Primary Key of each table)
2. Auto fill into tblBidLog.ManagerID [I used Lookup Wizard to use data from tblManager.ManagerID]= Data Type for both: Number-Long Integer (they're all the Primary Key of each table)
3. Auto fill into tblBidLog.CustomerID [I used Lookup Wizard to use data from tblCustomer.CustomerID]= Data Type: for both Number-Integer (they're all the Primary Key of each table)
4. Auto fill into tblBidLog.BuyerID [I used Lookup Wizard to use data from tblBuyer.BuyerID]= Data Type for both: Number-Long Integer (they're all the Primary Key of each table)

[ssanfu]

It doesn't look like the combo boxes are cascading. For instance, the row source for cboManager should/would look like

SELECT [tblManager].[ManagerID], [tblManager].[ManagerName] 
FROM tblManager 
WHERE [tblManager].[BranchID] = Forms!MyFormName.cboBranchID
ORDER BY [ManagerName];

plus some code to requery the combo box.

Any chance you would post your dB for analysis? Munge any sensitive data - only needs a few records.
Do a Compact and Repair, then zip it.

[CJ_London]

something else to look at - when you bind the combo to a field - what datatype is the field, per the table? If the bound column in the combo is 1 it needs to be a number type (long).

[AishlinnAnne]

I have gone through and changed all my important info on this. Please keep in mind that this is my first attempt at making a database. I just started Access like 2 weeks ago, so dont be too harsh on me.


Forum.zip

[CJ_London]

your relationships were causing the problem. Simplify those and it all works - see attached

note there are a number of controls on your form I could not see what to bind to, so they are still unbound

[AishlinnAnne]

THANK YOU!!! I had a feeling it had to do with the Relationships. Every time I created a new query/form and deleted in all my relationships were all screwy..
Okay, please just ignore if its too much to ask. I just re-created a form based off of my tblJobs sheet[frmAddJobs]. This has EVERYTHING I need on the form.

Could you please take one more look and tell me how to go about making these dropboxes dependent on eachother?

I need to select branch, then have that limit list for Manager, then limit list for Customer, then limit list for Buyer.

Forum2.zip

[CJ_London]

first I would remove all the lookups from your table - in time they will only confuse the issue and you will waste a lot of time trying to figure out why something isn't working as expected. You have already started down that path with your field called BranchName - which implies and displays a text value, actually it is a number so should be called BranchID. If you don't I can guarantee that it won't be long before you are back on a forum saying something isn't working - and the culprit will be forund to be the lookup. Not sure if you have been sent this link before, but take a look at this link to understand why

http://access.mvps.org/access/lookupfields.htm

second, the code you have in your dropdown1 form can be copied across

[AishlinnAnne]

remove all the lookups from your table

Thank you, however, I am not sure how all my data will populate correctly if there are no lookups?

[CJ_London]

lookups are not required in tables - you use them as combos or listboxes in forms. The only time tables should be looked at is during development - users will not (or should not) ever be able to see the tables directly.

although lookups in table design will force a combo or listbox in form design, you are not limited to this in the form. right click on a control and you can change it to another control type.

Also, although the default control is a combo or listbox for a field that is a lookup, subsequent changes to the table will not cascade through to forms or reports. You will discover over time that tables are for storing data, not for designing the way your forms work. Even for your requirement, you are changing the way it works because of the need for the control do what is required on the form, not the limited way that has be 'specified' in the table - so why have a lookup in a table.

It is a bit like field names (a big bugbear for me) - new developers to access 99% of the time use spaces in field names because they think that dictates the label that will appear on a form. The problem with spaces is it causes problems with coding (SQL or VBA)- they have to be surrounded with square brackets and if you forget, you get errors. That is fine if you are just using wizards but at some point you move beyond those and then the problems begin. Look at a field/control with spaces and you decide to have an event based on that control. You will see that the space has been replaced with an underscore = so now you have a difference between what the control is called and the name of the event. Instead you should use the caption property of a field and include spaces there - that is what it is for. If left blank, it defaults to the field name, so the developer is missing a step. Appreciate you do not fall into this group, you have not used spaces and long may you continue to do so

[ssanfu]

I totally agree with Ajax - do not use look up fields.

Thank you, however, I am not sure how all my data will populate correctly if there are no lookups?

The look up fields are only useful if you are looking at a table. NO ONE should be entering data directly into a table!! Use forms.
You set up a look up field, then in a form you have to set the same thing in a combo box - why waste the time setting up a look up field in a table when the table should NEVER be used for data entry (except the developer during design)???


Table "tblJobs" is designed like a spreadsheet. I think the fields: "FloorWeb", "FloorJoist", etc (the Yes/No fields) should be in a separate table. "Floors", "Roof", etc is actually data.


I noticed you have a text field as the PK field for "tblJobs".
IMO, the PK field should always be an autonumber of numeric field.

Other changes I would make:
have "ManFirstName" and "ManLastName" instead of "ManagerName" (same for other names)
all forms record source would be a query, not a table.

"tblAddJobs" is a bad name for a form


I played around with your dB. I changed form "DropDown1" to display records and "tblAddJobs" to add new jobs (with cascading combo boxes)

Look at the combo box row sources and after update events......


---------------------
More reading:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers


Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp


Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

[AishlinnAnne]

Thank you!! This is GREAT!!

A few questions/reasons

I noticed you have a text field as the PK field for "tblJobs".
IMO, the PK field should always be an autonumber of numeric field.

The reason that my PK for Jobs is set that way is because the # I am inputing in that QuoteID field is generated from another system and I need to use that Quote # to locate each job in Access and our system at work.

also,

Table "tblJobs" is designed like a spreadsheet. I think the fields: "FloorWeb", "FloorJoist", etc (the Yes/No fields) should be in a separate table. "Floors", "Roof", etc is actually data.

Who do you think this is a good idea? If I do break tblJobs up into tblJobDetails and tblJobOptions, would I need to create a query to mash the two back together and change my source for the form?


**I am sorry if these are stupid questions, I am literally learning as I go. Thanks again!

[AishlinnAnne]

Great, thank you!!!!I went ahead and deleted those out, I appreciate ALL of your help!

I am sure it is only a matter of time before you see my next issue posted.

I will get the hang of this.. slowly.


I like talking it out instead of just having someone fix it, I will need to know how to fix it if something goes wrong down the road.

Thanks again!!

[ssanfu]

The reason that my PK for Jobs is set that way is because the # I am inputing in that QuoteID field is generated from another system and I need to use that Quote # to locate each job in Access and our system at work.

It appears that the field "QuoteID" is not being used to link tables, so it might be OK. But I would have a field, "JobsID_PK" (Autonumber), as the primary key field and have "QuoteID" as Indexed - Yes, No Duplicates.... still a Text type field.

I think the fields: "FloorWeb", "FloorJoist", etc (the Yes/No fields) should be in a separate table. "Floors", "Roof", etc is actually data.

Who do you think this is a good idea? If I do break tblJobs up into tblJobDetails and tblJobOptions, would I need to create a query to mash the two back together and change my source for the form?

Spreadsheet table design: Lets say that you leave the table as is with all of the Yes/No fields.
You get the forms, queries and reports completed. Then you realize that you forgot to have an option for "Ceilings". What happens?
You have to change the table,
which means you have to change the queries,
which means you have to change the forms,
which means you have to change the reports and quite possibly
you will have to change a lot of VBA code.

A total dB rewrite just to add an option.

Normalized Table design: You add "Ceilings" to a table. Done! Nothing else needs to be changed!

I like talking it out instead of just having someone fix it, I will need to know how to fix it if something goes wrong down the road.

I totally agree.
Sometimes just writing it down helps clarify the problem and can lead to a solution..



You should take the time to work through the tutorials at Rogers Access Library. Not just look at them - actually follow along and do the tutorials.
http://www.rogersaccesslibrary.com/forum/forum46.html
Might help answer questions..........