Problem listing info from cascade boxes correctly...

[Goobz]

Hey all...

Here's the deal... I just got done building combo boxes today. They
are working 50/50 right now, and here's my problem...

cboBuildingNumber is showing the DISCTINCT Building Number
(BuildingNumber) correctly. However, when I go to populate the 2nd
field, I have absolutely nothing showing, instead of just the fields
for that specific building.

I have included the code I am using below, so hopefully someone can
make a change or two and help me figure this thing out... I know it's
probably something so damn stupid I'll be embarassed!

cboBuildingNumber = This is the main selection to populate the 2nd
combo box, and is a DISTINCT Query.
cboInBuildingLocation = This should show the information of all the
records showing the same value in "BuildingNumber" field in the table,
based on the DISTINCT query.

-----------------------------------------------------------------------------------------------------------------------------------------
Option Compare Database

' THIS IS THE UPDATE RAN AFTER THE SELECTION FOR THE CUSTOMERS
BUILDING HAS BEEN MADE '

Private Sub cboBuildingNumber_AfterUpdate()
On Error Resume Next
cboInBuildingLocation.RowSource = "Select
911Zones.InBuildingLocation " & _
"FROM 911Zones " & _
"WHERE 911Zones.BuildingNumber = '" & cboBuildingNumber.Value &
"' " & _
"ORDER BY 911Zones.InBuildingLocation;"

' Query The cboInBuildingNumber box if the building ID is changed '

Me!cboInBuildingLocation = Null
Me!cboInBuildingLocation.Requery

End Sub

' THIS IS THE UPDATE RAN TO FORCE THE COMBOS TO SYNC UP AFTER A CHANGE
TO THE BUILDING HAS BEEN MADE '

Private Sub Form_Current()
On Error Resume Next

' Synchronise Building (If Existing) with 911 Zone ID '

cboBuildingNumber = DLookup("[BuldingNumber]", "911Zones",
"[InBuildingLocation]='" & cboInBuildingLocation.Value & "'")

' Synchronise 911 Zone combo with existing 911 Zone if exists '

cboInBuildingLocation.RowSource = "Select
911Zones.InBuildingLocation " & _
"FROM 911Zones " & _
"WHERE 911Zones.BuildingNumber = '" & cboBuildingNumber.Value
& "' " & _
"ORDER BY 911Zones.InBuildingLocation;"

End Sub

' THIS IS THE SEARCH BUTTON SHOWN ON THE SCREEN '

Private Sub Search_Button_Click()
On Error GoTo Err_Search_Button_Click

Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Search_Button_Click:
Exit Sub

Err_Search_Button_Click:
MsgBox Err.Description
Resume Exit_Search_Button_Click
End Sub

' THIS IS THE UPDATE FUNCTION WHEN A USER DOUBLE CLICKS THE "LAST
UPDATED" FIELD '

Private Function SetDate()
Me.Updated = Date
End Function

 

[Goobz]

In fact, one thing I forgot, is the fact that now Once I select the
first combo box and populate it, As soon as I click on the arrow for
the 2nd I get.....


Syntax error (missing operator) missing operator in query expression
'911Zones.InBuildingLocation",

 

[Goobz]

I figured out my problem with that... Don't know exactly what it is,
but it's working! hehe...

Now my next problem... In order for me to get everything to populate
correctly, I had to do a DLookup.. It was the only thing I knew to
use...

My problem is, on the 2nd combo box, I need to pull up 2 columns, or
at least pull up 1 display column, but write a different field to the
ControlSource. The way that I have this code, I honestly am stumped...
Can someone asist me in how to do this!? I need to actualyl write
"Zone" instead of "InBuildingNumber" which is a different column in
the "InCaseofEmergency" table. I would just have it pull that up to
use as populate, but it's a specific 9 character code (B1F0-2DGF),
instead of actually showing someone what they are choosing (McDonalds,
JackInTheBox, etc).

' THIS IS THE UPDATE RAN AFTER THE SELECTION FOR THE CUSTOMERS
BUILDING HAS BEEN MADE '

Private Sub cboBuildingNumber_AfterUpdate()
On Error Resume Next
cboInBuildingLocation.RowSource = "Select
911Zones.InBuildingLocation " & _
"FROM 911Zones " & _
"WHERE 911Zones.BuildingNumber = '" & cboBuildingNumber.Value &
"' " & _
"ORDER BY 911Zones.InBuildingLocation;"

' Query The cboInBuildingNumber box if the building ID is changed '

Me!cboInBuildingLocation = Null
Me!cboInBuildingLocation.Requery

End Sub


' THIS IS THE UPDATE RAN TO FORCE THE COMBOS TO SYNC UP AFTER A CHANGE
TO THE BUILDING HAS BEEN MADE '


Private Sub Form_Current()
On Error Resume Next

' Synchronise Building (If Existing) with 911 Zone ID '


cboBuildingNumber = DLookup("[BuldingNumber]", "911Zones",
"[InBuildingLocation]='" & cboInBuildingLocation.Value & "'")

' Synchronise 911 Zone combo with existing 911 Zone if exists '

cboInBuildingLocation.RowSource = "Select
911Zones.InBuildingLocation " & _
"FROM 911Zones " & _
"WHERE 911Zones.BuildingNumber = '" & cboBuildingNumber.Value
& "' " & _
"ORDER BY 911Zones.InBuildingLocation;"

End Sub

 

[John W. Vinson]

My problem is, on the 2nd combo box, I need to pull up 2 columns, or
at least pull up 1 display column, but write a different field to the
ControlSource. The way that I have this code, I honestly am stumped...
Can someone asist me in how to do this!? I need to actualyl write
"Zone" instead of "InBuildingNumber" which is a different column in
the "InCaseofEmergency" table. I would just have it pull that up to
use as populate, but it's a specific 9 character code (B1F0-2DGF),
instead of actually showing someone what they are choosing (McDonalds,
JackInTheBox, etc).

You don't need ANY code to do this.

A combo box has several interrelated properties:

RowSource - the query providing the records displayed
ColumnCount - how many fields in the query are actually displayed
ColumnWidths - a series of numbers, separated by semicolons, indicating how
wide on the screen (in inches or centimeters) each column is displayed. A zero
width gives a column which is invisible but still there.
BoundColumn - which of the columns is actually stored
ControlSource - name of the field into which it is stored

So if your query includes the Zone (anywhere in the combo), just set that
column as the Bound Column and it will be stored.

Note that what you see onscreen when the combo is not dropped down is the
first nonzero width column.

John W. Vinson [MVP]

 

[Goobz]

You don't need ANY code to do this.

A combo box has several interrelated properties:

RowSource - the query providing the records displayed
ColumnCount - how many fields in the query are actually displayed
ColumnWidths - a series of numbers, separated by semicolons, indicating how
wide on the screen (in inches or centimeters) each column is displayed. A zero
width gives a column which is invisible but still there.
BoundColumn - which of the columns is actually stored
ControlSource - name of the field into which it is stored

So if your query includes the Zone (anywhere in the combo), just set that
column as the Bound Column and it will be stored.

Note that what you see onscreen when the combo is not dropped down is the
first nonzero width column.

             John W. Vinson [MVP]

That makes sense, and I thought that's the way it should work... I
just didn't know if the way I built the code in the post above, had
anything to do with the results I was getting...

I double checked my settings, and here's what I get now... I select
the Building in cbo 1, and that result pops down to give me only what
is included in that building - beautiul.. However, when I select the
option I need, it doesn't populate the field at all, unless I take my
2nd column out, and only let it view and bind one column. The code I
have setup is... "SELECT tblAll.InBuildingLocation,
tblAll.ToneCommanderInfo FROM tblAll ORDER BY
tblAll.InBuildingLocation;", where ToneCommanderInfo is the column
that has the value I need written to tblDN.911IDField.

Rigth now, my colums are:

Name: cboInBuildingLocation
Control Source: ZoneIDField
Row Source Type: Table/Query
Row Source: SELECT tblAll.InBuildingLocation, tblAll.BuildingNumber
FROM tblAll ORDER BY tblAll.InBuildingLocation;
Column Count: 2
Column Heads: Yes
Column Widths: 3.5";0"
Bound Column: 2
List Rows: 12
List Width: 4"
Limit To List: Yes
Auto Expand: Yes
IME Sent. Mode: Phrase Predict

And by looking at this, I don't see anything wrong at all, with why it
isn't working correctly...

 

[Goobz]

You don't need ANY code to do this.

A combo box has several interrelated properties:

RowSource - the query providing the records displayed
ColumnCount - how many fields in the query are actually displayed
ColumnWidths - a series of numbers, separated by semicolons, indicating how
wide on the screen (in inches or centimeters) each column is displayed. A zero
width gives a column which is invisible but still there.
BoundColumn - which of the columns is actually stored
ControlSource - name of the field into which it is stored

So if your query includes the Zone (anywhere in the combo), just set that
column as the Bound Column and it will be stored.

Note that what you see onscreen when the combo is not dropped down is the
first nonzero width column.

             John W. Vinson [MVP]

Important Thing I just Noticed...

When I select something from cboBuildingLocation, it is actually
changing the SQL query for InBuildingLocation on its own. I just
selected "Building 1", and when I looked at the design code for
InBuildingLocation, it unchecked "show box", and changed the criteria
to 'Building 1'.

What the heck did I do wrong!?