Problem with third cascading combo box - please help.

[orange]

There are good, free videos on several topics at Datapig.

Here're 2 dealing with comboboxes
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox2.html



He deals with 2 cascading combos, but the technique should be similar.

They're a little older (Access 2003 or so) but the concepts are the same.
There are other combo examples at techOntheNet

[warren0127]

Orange - Thanks for the links!

ItsMe,


I have another question about the original issue. So it is working in the original form. But now, I want to add this as a Sub Form into a Main Form tab. When I embed the subform in the main form on a tabbed page, the comboboxes are now prompting me to enter a value giving me the message:

"Enter parameter value"

Forms!frmTransitionQuery!cboCustomer


Any idea how to get around this? I am using the combobox form as a subform into another tabbed form.

Thanks
- Warren

[ItsMe]

... When I embed the subform in the main form on a tabbed page...

As long as it is not a Navigation Form/Control and it is a basic Form with a Subform control, you can reference the subform and its control by referencing the name of the subform container.

So there is a control that contains the Form object. When you create this subform container Access gives it a name that is similar to the form itself. This is the name of the subform control.

http://access.mvps.org/access/forms/frm0031.htm

So something like this
Forms!frmTransitionQuery!SubformControl.Form!cboCustomer

[warren0127]

ItsMe,

Thanks for the tip!

So I'm not sure if my criteria statement is correct, but I have modified the subform Row Source criteria through the main form and looks like this:

EDIT:

[Forms]![frmTransitionQuery]![subfrmTransitionQuery].[Form]![cboCustomer]


frmTransitionQuery = the subform name

subfrmTransitionQuery = the subform container control name

frmTabbedProject = the main form


Its giving me the parameter error again, which of course includes the new criteria statement.

Is my criteria statement backwards?

[ItsMe]

If you are trying to reference a control in a subform you would do it like this
Forms!frmTabbedProject!subfrmTransitionQuery.Form! cboCustomer

The part that gets confusing is subfrmTransitionQuery.Form. It is the control name you want, yet, as far as Access is concerned, it behaves like a form. That is why I sometimes refer to the subform control as a "Container".

That aside, if you write code in a Form that will be used in a subform. You reference controls like you normally would. You could use the Me shortcut. Me.ControlName

I hope that helps...

[warren0127]

ItsMe,

I knew I had the criteria wrong - it seems to be filtering just like it did before - and now works in the main form that has the subform control embedded. Thank you once again.

Now I guess, since the combobox controls were unbound, and assigned combobox/control names (cboCustomer, cboSubOrganization, cboPOR) - it isnt pulling the data into the table after the save is made because the control source is empty . I know this is because, it isnt recognizing the field names in the tables since the controls are unbound (Dur, should have thought about this earlier!). This means it cannot identify the table the data is supposed to go to after entered. Without the cascading combos, I would just pull in the fields into the form which are bound to the table, but in this case, looks like there isnt anything bound to them.

When I fill out the form, and go to save the form, it gives the error that I cant save: The MS Access database engine cannot find a record in the table 'PORs' with the matching field 'POR_ID'. The Primary Key in the POR table is "POR_ID" which is a foreign key in my Transitions table.. but it isnt taking the data since the combobox is unbound. I'm not entirely sure if I can still send the data to the proper table its supposed to go through the combobox fields.

Is there a way it can still put the data into the table?

[ItsMe]

It seems you understand that the error is because of a referential integrity rule that you created in the Relationships Window. When you append a record to a table and that table has a constraint defined in the relationships window, you will not be able to save the record until the rules of the constraint are met.

So, why, if the combo boxes are unbound, are you seeing this message??? Most likely you created a new record somewhere. If one of the forms is bound to a table, it may have a property setting that creates a new record. I think it is Data Entry = Yes

Another thing would be if you changed the value of a Foreign Key field, updated it some how. So look at your forms and determine if you created a record or are changing the value of an FK. If you create a PK, you will need an FK in the Relative table. Or remove the referential integrity rule...

[warren0127]

Definitely weird! I dont use referential integrity in my relationships - but most of them are one to many, which is what I want.

When creating a new entry in the Transition subform, from the main form, I am creating a new record in the transitions table. I built that transitions subform from a query. In the query, I pulled fields from three different tables -- but the form is essentially only updating one table: Transitions. All of the other fields are bound to other tables.

For example, before the transitions subform, there is a product subform. I can create a new product, fill out the description fields, and save it. It then creates a new product record in the Products table. Then, when I click the transitions tab, it brings me to the transitions subform. From there, I can select the Product I just previously entered (ProductID - but displays the ID/PK as the Product Name), and then I can fill out the transition information for that Product. The issue is in the cascading comboboxes at the end of the Transitions subform (the three you just helped me with). It is telling me it cant find any records in the PORs table that matches the POR_ID (primary key). I ensured that, the POR_ID foreign key is in the transitions table, with no referential integrity rules.

Its basically allowing me to enter everything but the POR in the transitions subform. Perhaps I need to mess with the data type/indexing for the POR_ID in both POR and Transitions table? Looks like something small. I also ensured that the Parent/Child table relationships are established in each of the subform I drag into the mainform.

[ItsMe]

Did you define a relationship in the relationships window? If you did and you did not define a referential integrity rule, delete the relationship and see if you still get the error. Do a compact and repair before testing the form again.

I may not be following your explanation perfectly and I do not usually see that error message. However, I am not imagining any other reason than a referential integrity rule. I would expect a different error message if it was an index property/constraint at the table level.

[warren0127]

I'll give it a try and let you know what happens. I'm also using lookup columns to connect my primary keys to foreign keys in other tables - which automatically establishes a relationship in the relationship DB view. PK and FK are both the same data type/ID name. Example: TransitionID is a PK and TransitionID is a FK in another table, etc.

Thanks for your help

[ItsMe]

... I'm also using lookup columns to connect my primary keys...

Hmmm, are you referring to lookup fields in a table? I do not use those but I think I recall the ability to create them and opt for the wizard creating a table vs. storing the values in the current table. Not sure what the wizard does but there may be a referential thing going on there. Make sure you work with a copy of your DB when testing.

I usually try to keep my tables as simple as possible and not use intrinsic constraints at the table layer. Just a few of the basic properties, index, duplicates no, etc. I feel that it gives me more freedom when developing the application. However, at times, it is a double edged sword. Generally, lookup fields are avoided by most developers.

[warren0127]

Thanks ItsMe! Appreciate your help! I'm still working out this issue - but I hope to eventually found out what is causing it.

[warren0127]

Hi ItsMe,

I'm bringing back this post because I've encountered an issue with the me.cboSubOrganization1.Value = "" in the VBA code as described above. It appears the value does in fact get stored in the table backend, but when reopening the form, some record comboboxes are blanked out.. but if you look in the tables, the values the user entered are still stored in the table.

If you recall from above, I have three comboboxes: Customer, CustomerSubOrg, and POR.

Here is the VBA code I have setup:

Private Sub cboCustomer1_AfterUpdate()

Me.cboSubOrganization1.Requery
Me.cboSubOrganization1.Value = ""


Me.cboPOR1.Requery
Me.cboPOR1.Value = ""


End Sub


Private Sub cboSubOrganization1_AfterUpdate()


Me.cboPOR1.Requery
Me.cboPOR1.Value = ""


End Sub

I'm understanding the purpose of the .Value = "" but am not sure how to get the combobox fields to "Stick" on the form. It seems that, if a user enters three different records in separate forms, and picks different organizations (values) than the first record on the first form, the second and third disappear on the other two forms when scrolling through them.

For example, here's what I'll enter on the first form:

Organization: A
Suborganzation: B
POR organization: C

And here is what I'll enter on the Second form for my second entry/record:

Organization: B
Suborganization: whatever
PORorganization: whatever

In the third form I'll enter the SAME organization in the organization combobox as the first form:

Organization: A
Suborganization: D
POR organization: E


Now, when I scroll back to the three form entries, the SECOND form has blank 2nd and 3rd comboboxes, and the FIRST and THIRD forms still appear fine because of the organization field was the same.

(and remember everything still shows up in the tables and still somehow gets stored).

What am I doing wrong here?