Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

'bound' Cascading combos revisited

I'm still having much trouble with my three combos on my form for data entry.  All three combos need to be 'bound' as they will change the data as the records are brought up.  Nico attempted to resolve this >http://www.experts-exchange.com/Databases/MS_Access/Q_21770290.html < but I'm receiving a compile error:Method or data not found on this line of code.  If Len(Nz(Me.cmbUnitID)) > 0 Then   >cmbUnitID<.

Thanks

0
zubin6220
Asked:
zubin6220
  • 12
  • 11
  • 7
  • +1
2 Solutions
 
John Mc HaleCommented:
If Len(Nz(Me.cmbUnitID, vbNullString)) > 0
0
 
dqmqCommented:
What is this supposed to do?  Looks like something that would confuse my compiler.

... Then   >cmbUnitID<
0
 
John Mc HaleCommented:
Sure would,

Nico's code with modification:

dim rs as DAO.recordset
IF Len(nz(Me.cmbUnitID, vbNullString)) > 0 then
   ' get all data needed in rs
   set rs = currentdb.openrecordset("select * from qryStateCityUnit where UnitID=" & Me.cmbUnitID)
   ' reset the rowsource
   Me.cmbCity.rowsource = "Select CityID, CityName from tlkpCity where stateid=" & rs!StateID
   Me.cmbCity.requery
   ' assign value City
   Me.cmbCity = rs!City
   ' assign value State
   Me.cmbState = rs!State
else
   Me.cmbState = ""
   Me.cmbCity = ""
   Me.cmbUnit = ""
endif
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
zubin6220Author Commented:
still receiving error on cmbUnitID.
>cmbUnitID< this earlier was just to show the area where the error occured
0
 
John Mc HaleCommented:
What error?

Number/Text?
0
 
John Mc HaleCommented:
Looks like you need to change the names of the combo boxes in Nico's code to their actual names on your form:

e.g.

In Nico's code, IF Len(nz(Me.cmbUnitID, vbNullString)) > 0 then

' you need to change cmbUnitID to match the actual name of the Unit combo box on your form. Likewise for any other occurances.


Hope this helps!
0
 
zubin6220Author Commented:
now I'm receiving run time error:items not found in this collection
on this line of code
  Me.cmbCity.rowsource = "Select CityID, CityName from tlkpCity where stateid=" & rs!StateID
0
 
John Mc HaleCommented:
Does the query 'qryStateCityUnit' retrieve a field called 'StateID'. If not, then this would appear to be your problem. If not, then you will need to post additional info about the EXACT structure of your three tables (field names).
0
 
zubin6220Author Commented:
yes it does and table field structure is:

tlkpState
    StateID       pk
    StateName
tlkpCity
    CityID         pk
    CityName
    StateID      fk
tlkpUnit
    UnitID         pk
    UnitName
    CityID         fk

The three combos are named:  cmbState, cmbCity and cmbUnit
   
I've had a heck of a time with this.  For the life of me I cannot find any examples of editing records on a form which has three cascading combos.  Everything seems to deal with filtering records.  All three combos (I would assume) need to bound - and there lies the rub.
Your help on this is greatly appreciated.
0
 
John Mc HaleCommented:
No, all 3 combos do NOT need to be bound, the secret lies in setting the RowSource property of the other combo(s) after making a selection. As Nico pointed out, with your relationships set up as they are (which incidentally is absolutely correct in terms of schema design), given the UnitID, we can then determine what it's City is, and also it's state. But this is not exactly what you want to do, is it?

You want to your user to be able to pick a State from the state combo, upon which, the city combo box will have a list of 'related' Cities.

Then when the user selects a City from the city combo, the unit combo box will show only those Units within the selected city.

If my assumptions are thusfar correct, my approach would be as follows:

Create a query 'qSelStates' with the SQL "SELECT * FROM tlkpState"

Create a query 'qSelCities' with SQL "SELECT * FROM tlkpCity WHERE((1)=1)"

Create a query 'qSelUnits' with SQL "SELECT * FROM tlkpUnit WHERE((1)=1)"

Open the Form you are developing in Design View and remove the Control Source for both cmbState and cmbCity and set the Control Source for cmbUnit to be the UnitID field of the Form's underlying data source

Code the AfterUpdate event handler for cmbState as follows:

Private Sub cmbState_AfterUpdate()
    sqlcities = CurrentDb().QueryDefs("qSelCities").SQL
    sqlunits = CurrentDb().QueryDefs("qSelUnits").SQL
    Me.cmbCity = Null
    Me.cmbUnit = Null
    If Not IsNull(cmbState) Then
        sqlcities = Replace(sqlcities, "1", "StateID", , 1, vbTextCompare)
        sqlcities = Replace(sqlcities, "1", Me.cmbState, , 1, vbTextCompare)
        Me.cmbCity.RowSource = sqlcities
        Me.cmbUnit.RowSource = vbNullString
    Else
        Me.cmbCity.RowSource = sqlcities
        Me.cmbUnit.RowSource = sqlunits
    End If
End Sub
... and code the cmbCity AfterUpdate event handler thus:


Private Sub cmbCity_AfterUpdate()
    sqlunits = CurrentDb().QueryDefs("qSelUnits").SQL
    If Not IsNull(cmbCity) Then
        sqlunits = Replace(sqlunits, "1", "CityID", , 1, vbTextCompare)
        sqlunits = Replace(sqlunits, "1", cmbCity, , 1, vbTextCompare)
    End If
    Me.cmbUnit = Null
    Me.cmbUnit.RowSource = sqlunits
End Sub

I think this is good as it gets!
0
 
nico5038Commented:
Hmm, strange. Did you check the properties of the combobox and see that under the Other tab the Name is indeed "cmbUnitID".
Also make sure that you have a reference to the DAO library by opening Tools/References when in VBA mode and have a checked library: "Microsoft DAO version #.##"

Nic;o)
0
 
zubin6220Author Commented:
fredthered;

I'm with you so far........
I'm not clear on .....and set the Control Source for cmbUnit to be the UnitID field of the Form's underlying data source
0
 
John Mc HaleCommented:
If UnitID is to be a bound control on the Form, then the control's control source must be part of the Form's recordset.

Perhaps if you elabourated on the nature of the Form, and what relationship it has to State, City and Unit, then maybe I will be able to give you a more definitive answer.
0
 
zubin6220Author Commented:
The main form is set up to search records for editing.  When a record is pulled up I need for the three combos to limit values based on the other combo.
cmbState is set up with states.  The user selects the correct state which is bound to tblAllDistro.  cmbCity is then selected based on which state was selected and also is bound.  Finally, cmbUnit is selected based on what city was selected.  All three combos based on the selection (as limited by this whole process I'm having trouble with) will be entered into the form record source tblAllDistro.  The three combos have control sources.  I'm just wanting to limit the selection of cmbCity to those available cities in the selected State.  Same for cmbUnit (based on selection of cmbCity).  In essence, whatever is selected from the three combos will be bound to the main tblDistroAll.  
0
 
John Mc HaleCommented:
If all three combo boxes are bound the same record source underpinning the form, there is still no problem. The solution I posted still stands (except that you need not remove the record source binding for cmbState and cmbUnit.

...so in essence, you have your 3 original tables

tlkpState (StateID, Autonumber, Pk: State, Text, Unique Index + any other required fields)
tlkpCity (CityID, Autonumber, Pk: City, Text, Unique Index: StateID, Number, Indexed + any other required fields)
tlkpUnit (UnitID, Autonumber, Pk: Unit, Text, Unique Index: CityID, Number, Indexed + any other required fields)

In addition, you have a table tblAllDistro, which has among it's fields StateID, CityID & UnitID + other fields.
Furthermore, your form's recordsource is bound to this table.

If this is the case, then my earlier post should solve your problem.

The only other difficulty I could see you having, is that you will have to ensure that the CityID and UnitID fields have their 'Required' property set = 'No' and any other Validation Rules are removed at the tabble level in tblAllDistro. They can be validated to ensure that they are not Null in the Form_BeforeUpdate event.

If you are still experiencing difficulties, please elabourate on these.

Regards.
0
 
John Mc HaleCommented:
Should have StateName, CityName, UnitName in place of State, City, Unit above respectively, sorry for typo
0
 
zubin6220Author Commented:
As far as I know things are set up correctly.  My form is now only showing: cmbState 0, cmbCity 0 and cmbUnit blank.

Also you mention ..."you will have to ensure that the CityID and UnitID fields have their 'Required' property set = 'No' and any other Validation Rules are removed at the tabble level in tblAllDistro. They can be validated to ensure that they are not Null in the Form_BeforeUpdate event".
   I'm not real clear on this

Thanks for your patience.


0
 
nico5038Commented:
Can you upload a (zipped) part of the .mdb so we can have a look ?
Try www.ee-stuff.com for the upload when you have no other site.

Nic;o)
0
 
zubin6220Author Commented:
nico5038,
I have uploaded the file per your request.

thanks
0
 
nico5038Commented:
Link ?

Nic;o)
0
 
zubin6220Author Commented:
I'm not clear what you mean by Link?  The file was uploaded on the ee site.  Let me know if it did not upload properly.
0
 
nico5038Commented:
From the tool description:
Welcome to the file upload tool for Experts-Exchange. Here you can upload a file or files for use with Experts-Exchange. Simply type the complete URL of the question, or the Question ID into the text box labeled "Question", and then click "Browse..." to select the desired file to upload. Type a comment describing the file, so that others have an idea what it is when they are downloading. Then click "Upload". This will upload the file and on the next page, you will be presented with a URL you can post in the Experts-Exchange Question so that others can download your file.

It's the URL I need to be able to download the file.

Nic;o)
0
 
nico5038Commented:
0
 
zubin6220Author Commented:
nico5038,
Your example works fine for capturing the 3rd combo, but I need for all combos to be bound to capture State, City and Unit.  

0
 
zubin6220Author Commented:
fredthered,
Finally!  I about gave up on this.  

thanks to you and Nico.

I'm going with your answer because it gave me what I was needing from the start & dividing points with Nico.  If I've divided wrong please let me know.

Thanks again
0
 
nico5038Commented:
I'm afraid fredthered's database isn't working properly for the situation where only the UnitID is recorded.
Also selecting a Unit (without state/city) is possible...
Just a warning :-)

Nic;o)
0
 
John Mc HaleCommented:
I should have clarified,

the example file I posted is when all 3 fields UnitID, CityID & StateID are bound to the recordsource.
0
 
nico5038Commented:
The objective was to have just the unique UnitID recorded :-)

Nic;o)
0
 
John Mc HaleCommented:
Not according to Zubin6220

....All three combos need to be 'bound' as they will change the data as the records are brought up.  Nico attempted to resolve this >http://www.experts-exchange.com/Databases/MS_Access/Q_21770290.html < but I'm receiving a compile error:Method or data not found on this line of code.  If Len(Nz(Me.cmbUnitID)) > 0 Then   >cmbUnitID<


... and

....All three combos based on the selection (as limited by this whole process I'm having trouble with) will be entered into the form record source tblAllDistro
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 12
  • 11
  • 7
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now