Combo Box Error on New Record

[MintChipMadness]

Hi Everyone,



I received a weird error when I tried to create a new record in my form. The error is quoted below. I also attached a picture of my relationships and the data entry form. What I am having issues with is when I select the Category from the combo box, it is giving me the error. The category combo box has 7 columns with 1 bound. The widths are 0";1";0";0";0";0";0". The only one shown is the Category Name. The first column is the [Directory].[ID] field and the second is the [Directory].[Category] field.The control source for the combo box is [Boxes].[Directory_ID]. That way it will enter the correct ID from Directory in the boxes table. Please let me know if you have any questions. Thank you.

The current field must match the join key 'Directory.ID' in the table that serves as the 'one' side of one-to-many relationship. Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table.

Row Source for Category Combo Box

SELECT DISTINCT Directory.ID, Directory.Category, Directory.Department, Directory.Division, Directory.Function, Directory.RetentionType, Directory.RetentionPeriod
FROM Directory
WHERE (((Directory.Department)=[deptcbo]) AND ((Directory.Division)=[divcbo]) AND ((Directory.Function)=[funcbo]));

[June7]

Looks like the relationship is INNER jointype "Only include rows where the joined fields from both tables are equal."

Change it to "Include all records from Boxes ..."

Same goes for the form RecordSource query if it is a join of the two tables.

Does that help?

[MintChipMadness]

Looks like the relationship is INNER jointype "Only include rows where the joined fields from both tables are equal."

Change it to "Include all records from Boxes ..."

Same goes for the form RecordSource query if it is a join of the two tables.

Does that help?

I can only find one setting which is to show all records for both tables. I can't find one to "Include all records from Boxes". Is that the Boxes.* for the table?

Edit - When a new record is created there is no information in Boxes.Directory_ID field. We are setting that information based on the Category chosen. Could that be the reason? A null doesn't match any primary key?

[MintChipMadness]

Additional Information:
1. The 'Output All Fields' property for the data entry form (record source) is set to 'Yes'. I attached the sql for the record source query below.

SELECT Boxes.ID, Boxes.[OldBox#], Directory.Department, Directory.Division, Directory.Function, Directory.Category, Boxes.RecordFYE, Boxes.EnteredBy, Boxes.[Scanned?], Boxes.DateDestroyed, BoxContents.[Description of Contents], *
FROM Directory INNER JOIN (Boxes INNER JOIN BoxContents ON Boxes.ID = BoxContents.[Box ID]) ON Directory.ID = Boxes.Directory_ID
WHERE (((Boxes.[OldBox#]) Like "*" & [Forms]![DataEntry]![number] & "*") AND ((Directory.Department) Like "*" & [Forms]![DataEntry]![dept] & "*") AND ((Directory.Division) Like "*" & [Forms]![DataEntry]![div] & "*") AND ((Directory.Function) Like "*" & [Forms]![DataEntry]![fun] & "*") AND ((Directory.Category) Like "*" & [Forms]![DataEntry]![cat] & "*") AND ((BoxContents.[Description of Contents]) Like "*" & [Forms]![DataEntry]![contents] & "*"))
ORDER BY Boxes.ID;

2. The boxes table is not included in the Category combo box control because the row source is just to get the correct ID from Directory. I am confused about why we are putting the boxes table in the row source. The control source for the combo is Directory_ID from the boxes table.

[June7]

I have not suggested that Boxes table be included in any combobox RowSource.

I was suggesting a change in the jointype of the table relationship setting and the form RecordSource.

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

[MintChipMadness]

I am sorry but I wouldn't be able to attach the project.

When I right click the relationship between boxes and Directory to change the join type I have three options:

1. Only include rows where the joined fields from both tables are equal.
2. Include ALL records from 'Directory' and only those records from 'Boxes' where the joined fields are equal.
3. Include ALL records from 'Boxes' and only those records from 'Directory' where the joined fields are equal.

I am guessing I should put it on #3?

[June7]

Yes, that is what I meant. It actually is not critical for this to be set in the Relationships (relationships are not even required for a db to function). It is the form RecordSource that must be fixed. Setting it in Relationships will facilitate creating the join in queries that include both tables.

[MintChipMadness]

I changed the join properties in the record source to #3. I tried to create a new record and it gave me the same error as before. I don't understand why I get this error. The Category combo gives a number that comes from the Directory table so they should match. I can edit records to a new Directory_ID but I can't create a new record.

Maybe the reason for the error is the record isn't saved yet. A null in that field would mean it doesn't match a Directory ID. What do you think?

[June7]

That should not be issue. As soon as value is selected for the Directory_ID field, the related record in Directory table should link.

Again, without looking at db I can't help much. Don't need real data.

[MintChipMadness]

I attached my database. I took out all the confidential stuff and things that weren't needed to show for this problem out.

[June7]

I modified the DataEntry query:
1. removed BoxContents table
2. set jointype to "Include ALL records from 'Boxes'..."

Dang, I keep losing track that you are trying to make comboboxes do double duty as filter management for Category and to display related data.

The cascading Department, Division, Function comboboxes cannot be bound. If they are bound then you will change the data in Directory table. Purpose of these controls is primarily to restrict the choices of Category. Department, Division, Function values are not to be saved.

Secondary purpose of the 3 comboboxes is to display info related to selected Directory_ID (bound Category combobox) in existing record. This is why code (previously suggested in other thread) needed to populate the comboboxes with related info. I only use VBA, no macros:

Private Sub Form_Current()
If Not IsNull(Me.Directory_ID) Then
Me.deptcbo = Me.Department
Me.divcbo = Me.Division
Me.funcbo = Me.Function
End If
Me.catcbo.Requery
End Sub

Alternative to this code is to have textboxes (Locked Yes, TabStop No) next to the Category combobox that can be bound to the Directory fields for display of info related to the Directory_ID (including the Category value).

[MintChipMadness]

The code got me past the first error and now when I go in to enter the Description of contents, access gives me this error:

The LinkMasterFields property setting has produced this error: 'The object doesn't contain the Automation object 'Boxes."

I am guessing because the subform needs the Box_ID from the new Box.

[June7]

I found field wildcard (*) in the DataEntry query. Could only see it in SQL View. I removed it then pulled the Directory_ID field into the query design grid. Changed the MasterLink property of the subform to ID.

[MintChipMadness]

I found field wildcard (*) in the DataEntry query. Could only see it in SQL View. I removed it then pulled the Directory_ID field into the query design grid. Changed the MasterLink property of the subform to ID.

I believe the wildcard came from selecting the "Output All fields property" in the DataEntry query. I set that to no to fix that error. I then put Directory_ID in the query design grid.

I am confused why you needed to change the master field of the subform to ID. It was already set to the ID of Boxes.

[MintChipMadness]

I found another issue while trouble shooting the subform. Each box can have many Box Content records so when I have more then one content record for a box, my data entry query has multiple records. This is bad because I really only have one box. To fix that, I took out box contents from the DataEntry query and created a new query called DataEntryContents. That will be used as the source for the subform. The reason I had that is I needed to search based on the contents. I originally searched in the DataEntry entry so by creating a new query I can use that to search by.

Edit 1- I broke the search for box contents. I filtered the DataEntryContents query based on what was entered in the Box Contents but it only searches the current record in the DataEntry Query (parent form source). I haven't been able to find a fix for this.

Edit 2- I am thinking the Box Contents search control should be taken out. From what I understand, the child does not tell the parent what to do, so telling the parent to sort based on what is in the child fields seems like a difficult undertaking. I should just get my data entry form to function properly before even trying to get that to work.