Combo Box Data Entry Form Issue

[MintChipMadness]

I think I know what is going on. Since the row source for the category Cbo is filtering by what is in the combo boxes above, it gives null values right at the beginning which means it won't work to take the values that way. I think by creating a new query to sort by the Directory_ID in the category cbo. then having that populate the department, division, function, and category value. Although I haven't figured out how to do that in VBA. What do you think?

[June7]

These comboboxes are unbound? (they should be)

The code is not to populate fields, just set a value in unbound combobox. I do it successfully.

Ooops, think you nailed it. I knew something about this was troubling me, have dealt with this before with other posters.

I don't think another query is needed. Instead of using reference to combobox columns in code, options:

1. form has RecordSource that joins the two tables then use code to set combobox values by reference to the related fields in the RecordSource
Me.cbodept = Me!Department

2. DLookup with Directory_ID field as criteria:
Me.cbodept = DLookup("Department","Directory","ID=" & Me.Directory_ID)

[MintChipMadness]

The only combo box that is bound is the category one.

I did a query that sorted based on Directory_ID and it came up with one record. I just thought there might be a way to show the values in the combo boxes using that query. I didn't really know how that would happen. Maybe set the default values to the value in the query? I tried that though and it didn't work.

[June7]

I did edits on my previous post while you posted. Review again.

[MintChipMadness]

Maybe giving the row sources for the other combo boxes will help. I must be doing something wrong if it works for you.

Department Cbo

SELECT DISTINCT Directory.Department FROM Directory ORDER BY Directory.Department;

Division Cbo

SELECT DISTINCT Directory.Division, Directory.Department FROM Directory WHERE (((Directory.Department)=[dept])) ORDER BY Directory.Division;

Function Cbo

SELECT DISTINCT Directory.Function, Directory.Department, Directory.Division FROM Directory WHERE (((Directory.Department)=[dept]) AND ((Directory.Division)=[div])) ORDER BY Directory.Function;

Category Cbo

SELECT Directory.ID, Directory.Category, Directory.Department, Directory.Division, Directory.Function FROM Directory WHERE (((Directory.Department)=[dept]) AND ((Directory.Division)=[div]) AND ((Directory.Function)=[fun]));

[MintChipMadness]

Just saw your edits. You can have a join in the record source? You mean control source right? I always thought the only way would be to join them in a query and use the query as the source. How would I go about joining the tables in the record source?

This is a lot of great information. I am learning a lot. I can't thank you enough.

[June7]

I mean the form's RecordSource. Remember post #2?

A form or report RecordSource can be a table, query, or SQL statement. The query or SQL can include a join of multiple tables. Whether or not the resulting dataset is editable depends on the joint type and table relationships. In this case, the join is to a lookup table (as opposed to a dependent child table) and jointype would be 'Show all records from Boxes ...'

This should allow the form to remain editable and also make the related info from Directory available for display, either in textboxes ControlSource (Locked Yes, TabStop No) or by code to populate the comboboxes.

[MintChipMadness]

Thank you. Wow that was a lot of good information. I am still really new at this and have a lot to learn.

I wasn't sure how to write the SQL statment in the record source. I did create a query that included all the fields from the three tables though. I don't know how else to do it unless you can help with the SQL statement. I don't want the user editing the Directory table. That needs to be locked to them. I guess I can lock certain fields to edits? Is that is what you mean?

[June7]

Data controls have a Locked property. User can click on the control but cannot enter anything if it is set to yes. Also set TabStop property to no.

Click into the RecordSource (or RowSource) property and double click the ellipses (...). This will open the query designer and can build SQL statement that will live right in the property and nowhere else.

Should not include the BoxContents table in the form RecordSource.

[MintChipMadness]

Thank you so much. I got it working by doing what you said. Binding a field makes everything so much easier when cycling through the records.

I will change this thread to solved. Thank you!! I really appreciate the help you gave me.