Combo Box Data Entry Form Issue

[MintChipMadness]

I attached two pictures. One is the form I am having issues with and the other shows my database relationships.

Question 1 - The first issue I am having is when the category is updated on the form, I want to populate the [Boxes].[Directory_ID] with the correct [Directory].[ID]. The directory ID is in the hidden column of the Category combo box which has the Directory table as its source. What I did was try to setvalue of Directory_ID by the amount hidden in the other column of the category combo box. It didn't work. Was I on the right track? I did check to see if I was getting a value for the hidden column by using a text box on my form.

Extra Information = The form source is the Boxes table.
The Box Number text field, Category combo box, RecordFYE text field, scanned field, and subform are bound to the boxes table but the rest are unbound.

Question 2 - Since three of the combo boxes are unbound (Department, Division, and Function), they always start blank. What I would like them to do is default to the value based on the category_ID of the record. In the picture it shows them filled in but that was because I selected the information.

Extra Information = The division, function and category combo boxes are all sorted based on the entry from the combo box above them. For example, what you select in Department will only show the Divisions for that department. I do this by filtering the row source.

[June7]

Not understanding data and form structure. I don't see a Category_ID field in the tables. Why would Directory_ID from Boxes be in the Category combobox RowSource?

Show the RowSource sql of the Category combobox. Show BoundColumn and ControlSource properties.

Is the Category RowSource controlled by selections of Department, Division, Function (cascading comboboxes)?

Want to provide db for analysis? Follow instructions at bottom of my post.


Options to display related data that is dependent on key in a combobox:

Form RecordSource is a query that joins the tables, jointype 'Show all records from ...". Then fields are available to ControlSource in textboxes.

The combobox includes the fields in its RowSource. Textbox ControlSource expression refers to the relevent column of combobox.

Textbox ControlSource is a DLookup expression (doable but not recommended).

[MintChipMadness]

Thank you for your reply. I apologize for the mistype. Category_ID field is the old name of the Directory_ID field.

I listed all the requested code at the bottom . I also listed the answer to your question below and a question I had about one of your solutions. I really appreciate your help. The only thing I could think of is to get the default values to pull from a query that filters based on the 2nd column of the category field (This has the [Directory].[ID] information). I have been unsuccessful.

Is the Category RowSource controlled by selections of Department, Division, Function (cascading comboboxes)?

Yes. The row sources for Department, Division, Function, and category are all based on the same query but each combo box has the query filtered based on the sections from the boxes above.

Form RecordSource is a query that joins the tables, jointype 'Show all records from ...". Then fields are available to ControlSource in textboxes.

I understand how to do this but wouldn't this not be a data entry form then? The Boxes table is the one that needs to have data entered into it. Also, the user won't be able to figure out what Directory_ID (Category) they need unless they figured it out using the combo boxes that comes from the query.

Category Row Source

SELECT DISTINCT Directory.Category, Directory.ID FROM Directory WHERE (((Directory.Department)=[department]) AND ((Directory.Division)=[division]) AND ((Directory.Function)=[function]));

Control Source = Category <--This is the field on the Boxes Table
Bound Column = 1
Column Count = 1
Column Width = Null

[June7]

You are saving the Category value into Boxes, not the primary key ID yet you have the table relationship defined between ID and Directory_ID fields. Is the Directory_ID field being populated somehow? If not, this relationship is meaningless and any suggestion involving a join on the PK/FK fields will not work. No query could join these tables on the ID and Directory_ID fields and return meaningful data. Is Category a unique value in the Directory table? If so, this should be the PK/FK link and a join in the form's RecordSource would still allow data entry/edit.

The ColumnCount is 1 so the Directory.ID column in the RowSource query is not even accessible.

[MintChipMadness]

It is true that the Boxes.Category field is not needed on the boxes table so it looks like question #1 is incorrect. The unique ID in the Directory table is linked by the same number in the Directory_ID field by a one to many relationship.

Ok let me ask question #1 another way. How would I make the combo boxes work so when a user figures out what category they want through the cascading combo boxes, it would enter the ID (unique) from the Directory table into the Directory_ID field of the boxes table.


Then question 2. If there is already a record in the boxes table, how can I make it so the values in the cascading combo boxes are already filled out based on the Directory_ID in the boxes table. Saying it in a different way, for new records the combo boxes would be blank but when a user is looking through the records, the combo boxes would fill in based on what the Directory_ID is. Also, if the user made a mistake they can change the Directory_ID field in the boxes table to the correct ID based on the new selection in the cascading combo boxes.

Hopefully that makes sense. Please let me know if you have any questions.

[June7]

Try:
Row Source = SELECT ID, Category, Department, Division, Function FROM Directory WHERE Department=[department] AND Division=[division] AND Function=[function];
Control Source = Directory_ID
Bound Column = 1
Column Count = 5
Column Width = 0";1";1";1";1" (or 0";1";0";0";0")

Might be able to use VBA code to populated the 3 comboboxes based on the values in the Category combobox. Can't put expression in ControlSource because then could not select from list. Use form Current event. Column index starts with 0. Something like:
Me.cbxDepartment = Me.cbxCategory.Column(2)
Me.cbxDivision = Me.cbxCategory.Column(3)
Me.cbxFunction = Me.cbxCategory.Column(4)

How many records in Directory table? Is Category unique in Directory table? Consider one combobox to show all. Just select the appropriate Category and the other info tags along.

[MintChipMadness]

Category is unique in the directory table. The second solution worked perfectly. It shows the categories from the directory in the combo box but it is really a number in the background. A lookup but I was trying to avoid that because of one issue. How would I pull the value from column 1 into a text box? I want to bind that control to a control on another form (to printout a label for the box) but it just shows the ID for the category when I try to do it and [category].[column](1) returns null.

I am curious what is going on in your first solution. I didn't understand it so I tried the second one first.

Also, did you have any suggestions for question 2 in my previous post?

Thank you so much for all your help.

[MintChipMadness]

Ok so I figured out why the text box wasn't filling in with the column information. I needed all the cascading boxes to be filled in before it would know what value to put in. Once question #2 is figured out then it work perfectly. I think it has something to do with the default value.

[June7]

The VBA code suggestion is response for question 2.

What default value?

What data do you want on the label? A RecordSource that is a join of the two tables on the PK/FK fields will make the related info from both tables available.

If Directory_ID is the FK field, no need for Category in Boxes table.

[MintChipMadness]

Thank you for the code. I'll try to put that in. Regarding the default value, I was thinking of putting an expression in the default properties of the combo boxes that, if (iif) there is a Directory_ID, then pull the value from a sort of a query by ID (which always returns one record due to unique ID). If not return null. I tried it but it doesn't work. I was trying not to use VBA if I could but it seems I can't at this point.

This might seems like a stupid question but what does FK mean. I am guessing PK means primary key. Also would you be able to explain 'other' label solution? I wanted it to be bound to the data entry form because whatever record the user is on, it would be able to print the correct information on the label.

[MintChipMadness]

Sorry forgot to answer one of your questions. Category in the Directory table is not unique and there will be a lot of records in the table. The cascading combo boxes were requested to keep the lists in the boxes small.

[June7]

PK/FK
Primary key/Foreign key

Actually, if you want to print, should print report instead of form. Forms can be printed but reports are intended vehicle for printing, more formatting control.

What is your confusion about printing form/report? Build a RecordSource that has the necessary fields. Filter the dataset for desired records. The filtering can be done various ways. One is to use VBA to open the form/report and pass filter info by reference to control on form, like:

DoCmd.OpenReport "reportname", , , "ID=" & Me.Directory_ID

This code could be in Click event of a button on form.

[MintChipMadness]

Thank you for your explanation. I will use a report instead for my label.

I am having issues with your VBA code for the current event. It gave a pop up that said, "Compile error: Method or data member not found." I posted what it looks like in the code builder below. This is the first time using it so I probably did something wrong.

Private Sub Form_Current()
Me.cbxdept = Me.cbxcat.Column(2)
Me.cbxdiv = Me.cbxcat.Column(3)
Me.cbxfun = Me.cbxcat.Column(4)
End Sub

dept = Department Cbo
div = Division Cbo
fun = Function Cbo
cat = Category Cbo

[June7]

Are those the exact names of comboboxes? You have the names with the cbx prefix? Prefix is not required, just a naming convention that many developers use. Use the actual names of your controls.

[MintChipMadness]

Yup, those are the exact names. Ok I fixed the code and didn't get and error but it also didn't populate the fields. Any idea why that happened? All the combo boxes are blank at first but when I set a text field equal to the category box it does show the Directory_ID. Should I create a new query that only sorts by Directory_ID and populate the fields that way?