Combo boxes and append query or what?

[Elmar]

For a contact list I need to get (amongst other things) State,
Locality and Postcode (Australia). I've downloaded the current listing
and modified it to a table tblPcode5col with unique records and 5
columns:
fldP5ID Autonumber Primary Key
fldState Text 3
fldLocality Text 40
fldPcode Text 4 (has to be text as some post codes have a
leading zero)
fldComments Text 40

As this table is rather large ,16,000+ records, I figured that it
might be better for whoever has to update data if there were an
intermediate table tblLocality (fldLocalityID, fldP5ID [link to
tblPcode5col], fldState, fldLocality and fldPcode) which stores
State,Locality,Pcode records that are actually being used in that
application (at best a few hundred). Naturally, every now and again,
particularly in the early stages, tblLocality wont have the desired
record as yet, therefore it needs to be retrieved from tblPcode5col.

So far I'm thinking of using a form with three combo boxes:
cboState ,cboLocality and cboPcode to get to the desired record in
tblPcode5col. Next would be to do an append query to retrieve the
found record and place it into tblLocality, the table which in day to
day running is being looked up from contact updates.

Is this a feasible approach or do I have to do a complete rethink?

So far I've only got the first two lookups going, state and Locality
and even there is a hiccup: Once the AfterUpdate of cboState fires a
dialog box "Enter Parameter Value" pops up with the selected State
abbreviation selected in cboState. Enter ANY state abbreviation and
that states' localities show up in cboLocaliity.

Private Sub cboState_AfterUpdate()

Me.cboLocality.RowSource = "SELECT tblPcode5col.fldLocality " & _
"FROM tblPcode5col " & _
"WHERE tblPcode5col.fldState = " &
Me.cboState & " " & _
"ORDER BY tblPcode5col.fldLocality;"
Me.cboLocality = Me.cboLocality.ItemData(1)

End Sub

From this behaviour I conclude that the query appears to be working
but ???

I'm looking forward for someone shining some light into this to help
me clear the fog of ignorance.

Elmar

 

[Mrs. Ugh]

Elmar-
Sounds more complicated than necessary. I assume the person who "has to
update the data" is adding a new contact and needs to enter the appropriate
state, locality and postal code (or changing the address of an existing
contact). Why not keep your one large table and have a series of combo boxes
(linked to queries) on your data entry form to narrow the search? Have one
for "Select State" (call it fldSelectState) with a control source of a query
containing only fldState (grouped so you don't show duplicates), one for
"Select Locality" with a control source of a query containing fldLocality,
etc. Add code to the "After Update" property of each combo box to refresh the
data in the subsequent boxes so they only include data that works with the
selections they have made (i.e. after updating fldSelectState, the
fldSelectLocality's control source includes WHERE fldState =
Me![fldSelectState]). You should also add code to cover instances where (for
example) the data-entry person starts with the Select PostCode box first (and
the others are null).

It is never a good idea to have two tables with the same data in one database!
Jill

For a contact list I need to get (amongst other things) State,
Locality and Postcode (Australia). I've downloaded the current listing
and modified it to a table tblPcode5col with unique records and 5
columns:
fldP5ID Autonumber Primary Key
fldState Text 3
fldLocality Text 40
fldPcode Text 4 (has to be text as some post codes have a
leading zero)
fldComments Text 40

As this table is rather large ,16,000+ records, I figured that it
might be better for whoever has to update data if there were an
intermediate table tblLocality (fldLocalityID, fldP5ID [link to
tblPcode5col], fldState, fldLocality and fldPcode) which stores
State,Locality,Pcode records that are actually being used in that
application (at best a few hundred). Naturally, every now and again,
particularly in the early stages, tblLocality wont have the desired
record as yet, therefore it needs to be retrieved from tblPcode5col.

So far I'm thinking of using a form with three combo boxes:
cboState ,cboLocality and cboPcode to get to the desired record in
tblPcode5col. Next would be to do an append query to retrieve the
found record and place it into tblLocality, the table which in day to
day running is being looked up from contact updates.

Is this a feasible approach or do I have to do a complete rethink?

So far I've only got the first two lookups going, state and Locality
and even there is a hiccup: Once the AfterUpdate of cboState fires a
dialog box "Enter Parameter Value" pops up with the selected State
abbreviation selected in cboState. Enter ANY state abbreviation and
that states' localities show up in cboLocaliity.

Private Sub cboState_AfterUpdate()

Me.cboLocality.RowSource = "SELECT tblPcode5col.fldLocality " & _
"FROM tblPcode5col " & _
"WHERE tblPcode5col.fldState = " &
Me.cboState & " " & _
"ORDER BY tblPcode5col.fldLocality;"
Me.cboLocality = Me.cboLocality.ItemData(1)

End Sub

From this behaviour I conclude that the query appears to be working
but ???

I'm looking forward for someone shining some light into this to help
me clear the fog of ignorance.

Elmar
.

 

[Elmar]

Elmar-
Sounds more complicated than necessary. I assume the person who "has to
update the data" is adding a new contact and needs to enter the appropriate
state, locality and postal code (or changing the address of an existing
contact). Why not keep your one large table and have a series of combo boxes
(linked to queries) on your data entry form to narrow the search? Have one
for "Select State" (call it fldSelectState) with a control source of a query
containing only fldState (grouped so you don't show duplicates), one for
"Select Locality" with a control source of a query containing fldLocality,
etc. Add code to the "After Update" property of each combo box to refreshthe
data in the subsequent boxes so they only include data that works with the
selections they have made (i.e. after updating fldSelectState, the
fldSelectLocality's control source includes WHERE fldState =
Me![fldSelectState]). You should also add code to cover instances where (for
example) the data-entry person starts with the Select PostCode box first (and
the others are null).

It is never a good idea to have two tables with the same data in one database!
Jill

For a contact list I need to get (amongst other things) State,
Locality and Postcode (Australia). I've downloaded the current listing
and modified it to a table tblPcode5col with unique records and 5
columns:
fldP5ID          Autonumber Primary Key
fldState         Text 3
fldLocality      Text 40
fldPcode        Text 4 (has to be text as some post codes have a
leading zero)
fldComments  Text 40

As this table is rather large ,16,000+ records, I figured that it
might be better for whoever has to update data if there were an
intermediate table tblLocality (fldLocalityID, fldP5ID [link to
tblPcode5col], fldState, fldLocality and fldPcode) which stores
State,Locality,Pcode records that are actually being used in that
application (at best a few hundred). Naturally, every now and again,
particularly in the early stages, tblLocality wont have the desired
record as yet, therefore it needs to be retrieved from tblPcode5col.

So far I'm thinking of using a form with three combo boxes:
cboState ,cboLocality and cboPcode to get to the desired record in
tblPcode5col. Next would be to do an append query to retrieve the
found record and place it into tblLocality, the table which in day to
day running is being looked up from contact updates.

Is this a feasible approach or do I have to do a complete rethink?

So far I've only got the first two lookups going, state and Locality
and even there is a hiccup: Once the AfterUpdate of cboState fires a
dialog box "Enter Parameter Value" pops up with the selected State
abbreviation selected in cboState. Enter ANY state abbreviation and
that states' localities show up in cboLocaliity.

Private Sub cboState_AfterUpdate()

    Me.cboLocality.RowSource = "SELECT tblPcode5col.fldLocality "& _
                                "FROM tblPcode5col " & _
                                "WHERE tblPcode5col.fldState = " &
Me.cboState & " " & _
                                "ORDER BY tblPcode5col.fldLocality;"
    Me.cboLocality = Me.cboLocality.ItemData(1)

End Sub

From this behaviour I conclude that the query appears to be working
but ???

I'm looking forward for someone shining some light into this to help
me clear the fog of ignorance.

Elmar
.

Thanks Jill,

that should make life easier, since with type-ahead even a large set
of records narrows down quite quickly. Which just leaves me with that
pesky little "Enter Parameter Value" dialog box. Any thoughts on that?

Elmar

 

[John W. Vinson]

Which just leaves me with that
pesky little "Enter Parameter Value" dialog box. Any thoughts on that?

That usually means that there is something in your query in square brackets
which Access doesn't recognize. When do you get it? Could you post the current
SQL view of your query, and indicate which parameter it's asking about?

 

[Elmar]

Elmar:

You might like to take a look at:

http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&ts....

which demonstrates ways of handling this type of hierarchical data by means
of correlated combo boxes while maintaining normalized table structures.

Ken Sheridan
Stafford, England

For a contact list I need to get (amongst other things) State,
Locality and Postcode (Australia). I've downloaded the current listing
and modified it to a table tblPcode5col with unique records and 5
columns:
fldP5ID          Autonumber Primary Key
fldState         Text 3
fldLocality      Text 40
fldPcode        Text 4 (has to be text as some post codes have a
leading zero)
fldComments  Text 40

As this table is rather large ,16,000+ records, I figured that it
might be better for whoever has to update data if there were an
intermediate table tblLocality (fldLocalityID, fldP5ID [link to
tblPcode5col], fldState, fldLocality and fldPcode) which stores
State,Locality,Pcode records that are actually being used in that
application (at best a few hundred). Naturally, every now and again,
particularly in the early stages, tblLocality wont have the desired
record as yet, therefore it needs to be retrieved from tblPcode5col.

So far I'm thinking of using a form with three combo boxes:
cboState ,cboLocality and cboPcode to get to the desired record in
tblPcode5col. Next would be to do an append query to retrieve the
found record and place it into tblLocality, the table which in day to
day running is being looked up from contact updates.

Is this a feasible approach or do I have to do a complete rethink?

So far I've only got the first two lookups going, state and Locality
and even there is a hiccup: Once the AfterUpdate of cboState fires a
dialog box "Enter Parameter Value" pops up with the selected State
abbreviation selected in cboState. Enter ANY state abbreviation and
that states' localities show up in cboLocaliity.

Private Sub cboState_AfterUpdate()

   Me.cboLocality.RowSource = "SELECT tblPcode5col.fldLocality " & _
                               "FROM tblPcode5col " & _
                               "WHERE tblPcode5col.fldState = " &
Me.cboState & " " & _
                               "ORDER BY tblPcode5col.fldLocality;"
   Me.cboLocality = Me.cboLocality.ItemData(1)

End Sub

From this behaviour I conclude that the query appears to be working
but ???

I'm looking forward for someone shining some light into this to help
me clear the fog of ignorance.

Elmar

Hello Ken,

the third approach looks like what I need, even though all State,
Locality and Pcode reside in the one table. I'm thinking to just store
the fldP5ID in the Address table, i.e., top down not bottom up.

Elmar

 

[Elmar]

That usually means that there is something in your query in square brackets
which Access doesn't recognize. When do you get it? Could you post the current
SQL view of your query, and indicate which parameter it's asking about?

Hello John,

I place a breakpoint on the first line of:

Private Sub cboState_AfterUpdate()

Me.cboLocality.RowSource = "SELECT tblPcode5col.fldLocality,
tblPcode5col.fldPcode, " & _
"tblPcode5col.fldComments,
tblPcode5col.fldP5ID " & _
"FROM tblPcode5col " & _
"WHERE tblPcode5col.fldState = " &
Me.cboState & " " & _
"ORDER BY tblPcode5col.fldLocality;"
Me.cboLocality = Me.cboLocality.ItemData(0)

End Sub

The dialog box appears once I hit F8 on the query block. It wants the
parameter value of whatever I had selected in cboState, however, if I
put in a different state abbreviation, that state's localities will be
displayed once I open cboLocality. Does this mean the WHERE clause
gets ignored? I would find that hard to believe as I have changed the
SELECT clause since the first post and this change is reflected by
displaying 4 columns while in the cboLocality.

One more thing: what does ItemData() do? The help file is not clear to
me. Is this line needed at all? I changed the number from 0 to 1 etc.
with no apparent effect.

TIA Elmar