Problem referring to form and subform combo box

[AShaw]

Hello,

I have a form named frmMain that contains five buttons that lead to subforms, one of which is named frmDeceased.

In frmDeceased, I needed to create a combo box that relied on selections from the combo box before it. With the help of someone on this forum named vbaINet, I was able to do this successfully in frmDeceased, but then today when I opened the database and opened frmMain, I realised that the query fails when frmDeceased is opened via frmMain. It works perfectly if I just open frmDeceased directly.

The first combo box is ROHE and the second is Iwi (which is dependent on the Rohe selected). The query for Iwi has this criteria: Forms!frmDeceased!cbo_ROHE

When I open frmDeceased through frmMain, I get the 'enter parameter value' message showing "Forms!frmDeceased!cbo_ROHE". I tried changing this query to: Forms!frmMain!frmDeceased!cbo_ROHE, but it didn't help. Could someone please tell me what I should write to correct this problem? Thanks.

 

[GinaWhipp]

It sounds like you're going to need a If This Then That statement since you're opening two different ways. Please post the code you are using to open the Form. Oh, what version of Access?

 

[AShaw]

Hi Gina - I'm using Access 2010. The code in frmMain that opens up frmDeceased is:

Private Sub cmdOpenDeceasedform_Click()
Me.Main_SubForm.SourceObject = "frmDeceased"
Me.Refresh
End Sub

Once I am in frmDeceased, the code that I have for populating the Iwi combo with the selection from Rohe is (just in case you need this):

Private Sub cbo_ROHE_AfterUpdate()
Me.cbo_Iwi = vbNullString
Me.cbo_Iwi.Requery
End Sub

Thanks, Alison.

 

[GinaWhipp]

Wait, I think I missed something... you have criteria in the query that is the Record Source of the Form?

 

[AShaw]

The first combo box (Rohe) is just populated by a simple table named tbl_ROHE_IWI_lookup. The query is:
SELECT tbl_ROHE_IWI_lookup.ROHE, tbl_ROHE_IWI_lookup.Order
FROM tbl_ROHE_IWI_lookup
GROUP BY tbl_ROHE_IWI_lookup.ROHE, tbl_ROHE_IWI_lookup.Order
ORDER BY tbl_ROHE_IWI_lookup.Order;

And I added an Event Procedure (after update) to allow people to change their Rohe selection which will then change their iwi selection (in case they chose the wrong Rohe):
Private Sub cbo_ROHE_AfterUpdate()
Me.cbo_Iwi = vbNullString
Me.cbo_Iwi.Requery
End Sub

The second combo box is for Iwi and it's selection is dependent on the choice in Rohe. Here is the code from it's query:
SELECT tbl_ROHE_IWI_lookup.IWI
FROM tbl_ROHE_IWI_lookup
WHERE (((tbl_ROHE_IWI_lookup.ROHE)=[Forms]![frmDeceased]![cbo_ROHE]))
ORDER BY tbl_ROHE_IWI_lookup.Order;

So that all worked without issues on frmDeceased, but then when I opened frmMain and pressed the button for frmDeceased, it allows me to select Rohe correctly (the first combo) but then on the second combo (Iwi) is says 'enter parameter value' and shows "Forms!frmDeceased!cbo_ROHE".

So I just need to correct it so that Iwi populates correctly on frmDeceased when I open it from frmMain.

 

[GinaWhipp]

Yep, I missed that! This is the problem...

SELECT tbl_ROHE_IWI_lookup.IWI
FROM tbl_ROHE_IWI_lookup
WHERE (((tbl_ROHE_IWI_lookup.ROHE)=[B][COLOR=red][Forms]![frmDeceased]![cbo_ROHE][/COLOR][/B]))
ORDER BY tbl_ROHE_IWI_lookup.Order;

It's the Row Source of the Combo Box that needs changing. So, that is where the issue comes in and the statment has to control it. What is the path when open from the Main Form (that would be the part in red).

 

[AShaw]

When I open it from the main form (frmMain) it has the same path:
[Forms]![frmDeceased]![cbo_ROHE]

I thought that I should somehow refer to the frmMain in the path, so I tried:
[Forms]![frmMain]![frmDeceased]![cbo_ROHE]

But that didn't work. Am I missing some combination of exclamation marks or the order I'm writing it in? I tried a few things but just can't get it to work. This also didn't work:[Forms]![frmMain]![cbo_ROHE]

 

[GinaWhipp]

See if you can find the right combination here...

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

 

[AShaw]

See if you can find the right combination here...

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

Yes, that is the link that I have been referring to and as I've already said from the beginning, I can't get it right after trying several combinations.

Is there anyone who can help me with this please? Anyone? ANYONE?!!!

I thought this was a fairly simple beginners question and that someone might be able to help a newbie with referring to forms and subforms? Is there really no one who can help with this?!

 

[GinaWhipp]

Okay, can you upload the file? I don't need data just the database.

 

[AShaw]

Thanks - I've attached the database as a compressed file (hope it works ok). It has no data except for a few test records.

The team paid a guy to build this db for them but it was full of errors and spelling mistakes etc, so they asked me to go through and fix it all for them. I use SAS not Access, but I'm the only employee who has any database skills and there is no one else who can help them with their Access problems.

They have a frmMain, and you'll see a green button 'Deceased Form' to open frmDeceased. On Deceased, it asks for Ethnicity. Under Ethnicity you'll see Rohe (tribal location) and Iwi (tribe). A deceased person will often be related to three different tribes which is why there are three Rohe and Iwi options. The team asked me to create an Iwi combo box that is based on the choice of Rohe. They will open frmDeceased from frmMain and start entering everything about the victim there. I don't have enough knowledge about Access to do all of these changes they wanted, but if I can't do it they'll have to pay an external IT person to do this and they already used up all their budget on the guy who built it.

 

[GinaWhipp]

Okay, I will look at in a while... long day!

 

[AShaw]

Thanks, will look forward to any ideas. I know SQL because its used in SAS, but I don't understand the form and subform references that Access uses.

 

[GinaWhipp]

Okay, the problem was that the *Name* of the Subform is not frmDeceased*, it's Main_Subform. So, when referring to a Control within the Subform cannot use frmDeceased. Almost made me nuts until I *looked* at the name of the Subfrom for which you were setting the SourceObject.

I had to REM out quite a few lines so I could Compile but it looks like those Controls are no longer there so shouldn't cause you any issues. To see what I did, review the On_Open event of frmDeceased. I set the Row Source of the Combo Boxes based on whether the Main was open or not.

I am also going to suggest you take the time to give your Controls on Forms some meaningful names. A month from now you won't know what Text2 or Combo101 does.

 

[AShaw]

Thank you so much for looking into it. There were so many poorly designed features in it that I really struggled to fix some of these things for them. I'll try to fix up the form names and control names and then hand it back to them.
They've hired a data entry person to start work on it but they also want her to do all of these db changes in Access. Poor data entry person.

 

[GinaWhipp]

Glad to help!

Unfortunately, most folks don't understand *fixing* Access is not a 5 minute job and depending on the database, at times, requires a Pro. I read a saying somewhere...

"If you think it's a expensive to hire a professional, wait until you hire an amateur!"