Cascading Combo Boxes not seeing info in previous boxes

[klarae99]

Hello,

I am working on an Access 2003 Database. The tables that pertain to this issue are tblOrg, tblState, tblCity, and tblZip. I have posted the table structure with only the pertinant fields below.

tblOrg
OrgID, AutoNumber, PK
ZipID, Number, FK

tblState
StateID, AutoNumber, PK
State, text

tblCity
CityID, AutoNumber, PK
City, text
StateID, Number, FK

tblZip
ZipID, AutoNumber, PK
Zip, text
CityID, Number, FK

In working with cascading combo boxes in a different database it was suggested to me that recording the StateID, CityID and ZipID in the Organization table was redundant (and not good database practice) and that I only had to store the Zip ID in the tblOrg and the broader levels could be recalled later through the relationships of the tblState, tblCity and tblZip. Upon considering this it makes sense to me so I have set up my tables to reflect this logic, but now I am having difficulty getting my cascading comboboxes to work.

I am now creating a form (frmCom), bound to tblOrg, that I plan to use for data entry and review. My hope is that a user will be able to select the State, then the City and finally the Zip from cascading combo boxes and that the information entered in the zip field will store in the tblOrg and used to fill in the broader categories when the information is reviewed.

I have placed two unbound combobox fields on this form, one for state and the other for city. The zip combobox is bound to zipID of tblOrg. The rowsource for cboState is tblState(bound coloum StateID, state name, widths 0;1). This box seems to work fine. The rowsource for cboCity is tblCity (bound colum CityID, city name, StateID from FrmCom.cbostate , widths 0;1;0) However, when I click on the city field I am prompted for frmCom.cbostate . If I enter the StateID into the prompt, the list propogates correctly but I'm would like it to pull that information from the form and not require manual entry.

A Similar thing is happening with the zip combobox, however the prompt for the city occurs when I open the frmCom, not when I click on the zip field. If I enter the CityID then, the zip list propogates, if I cancel the prompt when I click on the Zip list it is left blank.

I'm not sure why my cascade is not seeing the information in the forms fields. I'm not sure if it has to do with how the form is bound to information (it is looking to run the cascase in the other direction aka retreval, but not forward for dataentry), or if it has to do with the way I have my cascade set up.

Any advice on what I should be looking at or what might be occuring would be appreciated.

[PianoMan64]

Hello,

I am working on an Access 2003 Database. The tables that pertain to this issue are tblOrg, tblState, tblCity, and tblZip. I have posted the table structure with only the pertinant fields below.

tblOrg
OrgID, AutoNumber, PK
ZipID, Number, FK

tblState
StateID, AutoNumber, PK
State, text

tblCity
CityID, AutoNumber, PK
City, text
StateID, Number, FK

tblZip
ZipID, AutoNumber, PK
Zip, text
CityID, Number, FK

In working with cascading combo boxes in a different database it was suggested to me that recording the StateID, CityID and ZipID in the Organization table was redundant (and not good database practice) and that I only had to store the Zip ID in the tblOrg and the broader levels could be recalled later through the relationships of the tblState, tblCity and tblZip. Upon considering this it makes sense to me so I have set up my tables to reflect this logic, but now I am having difficulty getting my cascading comboboxes to work.

I am now creating a form (frmCom), bound to tblOrg, that I plan to use for data entry and review. My hope is that a user will be able to select the State, then the City and finally the Zip from cascading combo boxes and that the information entered in the zip field will store in the tblOrg and used to fill in the broader categories when the information is reviewed.

I have placed two unbound combobox fields on this form, one for state and the other for city. The zip combobox is bound to zipID of tblOrg. The rowsource for cboState is tblState(bound coloum StateID, state name, widths 0;1). This box seems to work fine. The rowsource for cboCity is tblCity (bound colum CityID, city name, StateID from FrmCom.cbostate , widths 0;1;0) However, when I click on the city field I am prompted for frmCom.cbostate . If I enter the StateID into the prompt, the list propogates correctly but I'm would like it to pull that information from the form and not require manual entry.

A Similar thing is happening with the zip combobox, however the prompt for the city occurs when I open the frmCom, not when I click on the zip field. If I enter the CityID then, the zip list propogates, if I cancel the prompt when I click on the Zip list it is left blank.

I'm not sure why my cascade is not seeing the information in the forms fields. I'm not sure if it has to do with how the form is bound to information (it is looking to run the cascase in the other direction aka retreval, but not forward for dataentry), or if it has to do with the way I have my cascade set up.

Any advice on what I should be looking at or what might be occuring would be appreciated.

From what it sounds like to me, is that you've created the table relationships with referencial integrety activated as well as updated.

This doesn't work for what you're trying to do because it simply doesn't have the information there, and it demandsw that it is. that is the reason you're getting all those errors. If you simply take all that cascading settings off of your database, then you'll see what it will work how you'd like it to work.

[klarae99]

Dear PianoMan64,

If I understood your post correctly my relationships between city state and zip that had referential entegrity and update cascade and delete cascade were causing the problems with my cascading comboboxes and you suggested that I remove the cascade settings from my database.

I have removed the cascade update and cascade delete from my relationships between those three tables but am still getting the prompt to enter Forms!frmcomp!S tate when I click on the city box and the enter Forms!frmcomp!C ity when I click on the zip box (though now I do not get the prompt for the city when I open the form).

Did I misunderstand what you wanted me to do? If I did please reexplain it to me, if I didn't than I still have a problem, any other ideas?

[klarae99]

OK, I have continued to work on this database while awaiting a response to this question, and have discovered a few things. First of all, there now has to be an additional box in the Cascade, Country, as some of the enteries will be in Ireland. So now I am looking at having the user select a country from an unbound combobox based on tblCountry (bound to col. 1 [CountryID] with [CountryName] in column 2, with widths of 0;1). I have placed the following code into the AfterUpdate event for the combobox [Country] (as was outlined in Rabbit's Howtos for cascading Combo/List boxes).

Private Sub Country_AfterUpdate()
    With Me![State]
        If IsNull(Me!Country) Then
            .RowSource = ""
        Else
            .RowSource = "SELECT [State] " & _
                        "FROM tblState " & _
                        "Where [CountryID]=" & Me!Country
        End If
        Call .Requery
    End With
         
End Sub

My combobox [State] is also unbound and I have eliminated the rowsource for this combobox.

Now I am able to view my Country list but as soon as I select one I am given the following error messsage

The Expression After Update you entered as the event property setting produced the following error: A problem occured while Microsoft Office Access was communicating with the OLE server or ActiveX control.

I have double checked all my references and they seem to be correct. The only thing I can think is that I need a module for the requery. I have not moved onto the other two boxes, I figured if I could get this to work than the other two should follow the same pattern.

Please let me know if you have any ideas as to why I am getting this error I would appreciate any continueing advise.

[klarae99]

Hello Everyone,

I am still working on the cascading combobox question that I outlined in my last post but have not gotten any farther. I do not see where the problem is with the code that I used. If anyone could give me a pointer I would really appreciate it. Thanks!