Updating a table from a cascading combo box(s) fox

[Navigator02]

Greetings,
New to the forum and have a question about updating my table when I use cascading combo boxes in my form.
What is happening is that my table is being populated by the xxxxID column vice from the xxxxName column that is being used from that specific table.
here is my visual basic code that I am using to determine what the subsequent combo box will display.

Option Compare Database
Option Explicit
Private Sub cboPlanktonID_AfterUpdate()
' Set the Family combo box to be limited by the selected Plankton Type
Me.cboFamilyID.RowSource = "SELECT tblFamily.FamilyID, tblFamily.FamilyName FROM tblFamily " & _
" WHERE OrderID = " & Nz(Me.cboPlanktonID) & _
" ORDER BY FamilyName"
Me.cboFamilyID = Null

EnableControls

End Sub
Private Sub cboFamilyID_AfterUpdate()
' Set the Organism combo box to be limited by the selected Family
Me.cboOrganismID.RowSource = "SELECT tblType.TypeID, tblType.TypeName FROM tblType " & _
" WHERE FamilyID = " & Nz(Me.cboFamilyID) & _
" ORDER BY TypeName"
Me.cboOrganismID = Null

EnableControls

End Sub
Private Sub cboOrganismID_AfterUpdate()
' Set the Species combo box to be limited by the selected Organism
Me.cboSpeciesID.RowSource = "SELECT tblSpecies.SpeciesID, tblSpecies.SpeciesName FROM tblSpecies " & _
" WHERE TypeID = " & Nz(Me.cboOrganismID) & _
" ORDER BY SpeciesName"
Me.cboSpeciesID = Null

EnableControls

End Sub

Private Sub EnableControls()
' Clear the combo boxes
If IsNull(Me.cboWaterbodyID) Then
Me.cboLocationID = Null
End If
If IsNull(Me.cboPlanktonID) Then
Me.cboFamilyID = Null
End If

If IsNull(Me.cboFamilyID) Then
Me.cboOrganismID = Null
End If

If IsNull(Me.cboOrganismID) Then
Me.cboSpeciesID = Null
End If


' Enable or disable combo boxes based on whether the combo box preceeding it has a value.
Me.cboLocationID.Enabled = (Not IsNull(Me.cboWaterbodyID))
Me.cboFamilyID.Enabled = (Not IsNull(Me.cboPlanktonID))
Me.cboOrganismID.Enabled = (Not IsNull(Me.cboFamilyID))
Me.cboSpeciesID.Enabled = (Not IsNull(Me.cboOrganismID))


End Sub
Private Sub cboWaterbodyID_AfterUpdate()
' Set the Sampling Location combo box to be limited by the selected Waterbody Type
Me.cboLocationID.RowSource = "SELECT tblLocation.LocationID, tblLocation.LocationName FROM tblLocation " & _
" WHERE WaterbodyID = " & Nz(Me.cboWaterbodyID) & _
" ORDER BY LocationName"
Me.cboLocationID = Null

EnableControls
End Sub
Private Sub Form_Load()
' When the form loads, enable/disable the combo boxes. Combo boxes are only enabled if the preceeding combo box has a value.
EnableControls
End Sub

Example of the Combo box Row Source is: SELECT [tblWaterbody].[WaterbodyID], [tblWaterbody].[WaterbodyName] FROM tblWaterbody;

My Control Source is PlanktonAnalysis.WaterbodyName

When I fill in the form with the data, The Waterbody name is visable for selection (example: I see "Lake Lillinonah" in the cascading combo box, But when I save the record in the PlanktonAnalysis Table I get a number in the WaterbodyName column vice the name of the waterbody


PlanktonAnalysisIDWaterbodyName172
311411511677118397104113
tblWaterbodyWaterbodyIDWaterbodyName2Ashland Pond3Bantam Lake4Crystal Lake5Gardner Lake6Hungerford Park Pond7Lake Lillinonah8Lower Bolton Lake9Middle Bolton Lake10Pocotopaug Lake11West Thompson Reservoir

Any help would be greatly appreciated

Thanks
Bruce g

 

[CJ_London]

The Waterbody name is visable for selection (example: I see "Lake Lillinonah" in the cascading combo box, But when I save the record in the PlanktonAnalysis Table I get a number in the WaterbodyName column vice the name of the waterbody

It looks like the source for the combo box is

SELECT [tblWaterbody].[WaterbodyID], [tblWaterbody].[WaterbodyName] FROM tblWaterbody;

So any reference to this combobox will pick up the id. Either change the bound column of the combo box from 1 to 2 or change your refereence to ...combobox.column(1)

 

[Navigator02]

Thank you, That fixed one issue, by changing the Bound column to a 2, the Table now is populated by the waterbodyName vice ID.
Not sure how to change the reference to combobox.column (1)
In the Visual basic?? event procedure?

But now my subsequent cascading combo box results in an error msg:
Syntax error (missing operator) in query expression 'WaterbodyID=Lake Lilinonah'

This combo box also needs to fill a column in the PlanktonAnalysis Table based on the Waterbody selected.

Ultamately I have another set of 4 cascading combo boxes that allow me to make a selection that populates the next combo box and populates the Plankton analysis table with the data selected when saved.

Your assistance in this matter is greatly appreciated.
Bruce g

 

[CJ_London]

But now my subsequent cascading combo box results in an error msg:
Syntax error (missing operator) in query expression 'WaterbodyID=Lake Lilinonah'

This is because of the change of bound column - in the circumstances probably better to revert to bound column =1 and change the following:

But when I save the record in the PlanktonAnalysis Table I get a number in the WaterbodyName column vice the name of the waterbody

where you assign the waterbody name in the above action change it from form!myform!waterbody to form!myform!waterbody.column(1)

 

[Navigator02]

:banghead:

No luck so far.

Not sure where I would even enter the 'form!myfor!waterbody.column(1) statement. I have tried in various places but to no avail.

Let me try to explain it a little more in depth.
I have 7 tables
A) tblWaterbody and tblLocation
B) tbl Order, tblFamily, tblType, and tblSpecies
My final table is "PlanktonAnalysis"
I have one form "Plankton Analysis" which contains cascading combo boxes.
One set for the tblWaterbody and tblLocation, where the selected waterbody determines what is displayed in the Location combo box.
The second set of cascading combo boxes is for the Order, Family, Type, and species tables
1) tblwaterbody contains the fields WaterbodyID and WaterbodyName
2) tblLocation contains the fields LocationID, WaterbodyID, and LocationName
This is the same for the second group of four tables, where the susequent table contains the ID field of the previous table.
tblOrder: OrderID and OrderName
tblFamily: FamilyID, OrderID, FamilyName
tblType: TypeID, FamilyID, TypeName
tblSpecies: SpeciesID, TypeID, SpeciesName

Ultimately I am trying to populate the PlanktonAnalysis table with the data selected from the Plankton Analysis Form, using the column from the respective tabe that contains the Name.

My Event Procedure SQL statements work and populate the boxes based on the prior selection.

My problem is that I still can not get my PlanktonAnalysis table to update with the WaterbodyName vice the WaterbodyID. (I haven't even tried to do this with the second set of cascading combo boxes... I figure that once I correct the first set, I can apply the fix to the second set.)

For the cboWaterbodyID
Property Sheet:
Control Source: PlanktonAnalysis.WaterbodyName
Row Source: SELECT tblWaterbody.WaterbodyID, tblWaterbody.WaterbodyName FROM tblWaterbody;
Row Source Type: Table/Query
Bound Column: 1

All other values in the property sheet are the default values

My Event Procedure for this Combo box is:
Private Sub cboWaterbodyID_AfterUpdate()
' Set the Sampling Location combo box to be limited by the selected Waterbody Type
Me.cboLocationID.RowSource = "SELECT tblLocation.LocationID, tblLocation.LocationName FROM tblLocation " & _
" WHERE WaterbodyID = " & Nz(Me.cboWaterbodyID) & _
" ORDER BY LocationName"
Me.cboLocationID = Null

EnableControls
End Sub

For the cboLocationID Property Sheet:
Control Source: Sampling Location
Row Source: SELECT [tblLocation].[LocationID], [tblLocation].[LocationName] FROM tblLocation;
Row Source Type: Table/Query
Bound Column: 2

There is no event procedure for this combo box

When I 'Save and New' in my Plankton Analysis form my PlanktonAnalysis table populates as:
PlanktonAnalysisIDWaterbodyNameDate CollectedCollector(s)Sampling Location111
#6 Sandy Beach211

near boat launch311


411


511


67


711


83


97


104


113


1211

1311

1511


16




Thank you for your help in this matter.
R/
bruce g