strange table behavior

[Joanne]

Winxp and access 2003

I have a table with these fields
CoID CoName Location Street

I pull the info into the form using cascading cboboxes
Choose CoName
get Location (Cities) relevant to CoName
get Street (addresses) relevant to CoName & Location

I have approx 1500 records, and on most of them this works great - but
on some records, after choosing CoName it will not bring up the
Location and/or street. I have looked at the table at these records
and they are in proper field sequence, no blank spaces, nothing looks
out of sort on the record, yet it doesn't work.
I am baffled and am hoping someone out there has seen this before and
can tell me how to fix it so that my app is more reliable.

TIA
Joanne

 

[Guest]

Make sure that during testing you haven't created new records with empty
location and street fields fro the same company name - not an uncommon cause.

TonyT

 

[Joanne]

thanks for you input Tony
I did a close examination of my table and there are no records that do
not have the proper fields filled in.

Any other ideas?

 

[J. Goddard]

Hi -

Can you post the code you are using to fill the cascading combo boxes,
and perhaps an example or two of names that don't work?

John

 

[David F Cox]

are you sure that there are not any leading blanks in any of the fields? It
can be hard to spot. You could try a trim statement to be sure.

 

[Joanne]

Here is the code for the cbo boxes:

This one fills the Location CboBox
Private Sub cboCoName_AfterUpdate()
Me!cboLocation.RowSource = "select Distinct tblmain.[location]
from tblmain where tblmain.[CoName] ='" & Me!cboCoName & "' order by
[location]"
Me.cboCoName.Requery
End Sub

This one fills the Street CboBox
Private Sub cboLocation_AfterUpdate()
Me!cboStreet.RowSource = "select distinct tblmain.[street] from
tblmain " & _
"where tblmain.[coname] = '" & Me!cboCoName & _
"'and tblmain.[location] = '" & Me!cboLocation & "' order by
[street]"
Me.cboStreet.Requery
End Sub

CoNameID CoName
1024 Fuller's Home & Hardware

Street Location
35 E First Street Hinsdale

This is one record in the table that returns no street or location -
it happens on a few other records but I didn't take notes on any of
the other ones and I can't remember which ones they are, so this is
the only one I can offer. I have checked the table to be sure that
there are no empty spaces leading in the fields.

I hope this helps you to help me, which I appreciate muchly
Joanne

 

[J. Goddard]

Hi Joanne -

Fuller's Home & Hardware has a single quote in it (a.k.a. an
apostrophe!) - I thinks that's the problem.

You can remove the apostrophe and see what happens, or you can change
all the text delimiters to double quotes, so that:

where tblmain.[CoName] ='" & Me!cboCoName & "' order by ..
becomes
where tblmain.[CoName] =""" & Me!cboCoName & """ order by ..

etc.
or you could remove the apostrophe from the company name and see what
happens.

Are the other ones that don't work the same?

John

Here is the code for the cbo boxes:

This one fills the Location CboBox
Private Sub cboCoName_AfterUpdate()
Me!cboLocation.RowSource = "select Distinct tblmain.[location]
from tblmain where tblmain.[CoName] ='" & Me!cboCoName & "' order by
[location]"
Me.cboCoName.Requery
End Sub

This one fills the Street CboBox
Private Sub cboLocation_AfterUpdate()
Me!cboStreet.RowSource = "select distinct tblmain.[street] from
tblmain " & _
"where tblmain.[coname] = '" & Me!cboCoName & _
"'and tblmain.[location] = '" & Me!cboLocation & "' order by
[street]"
Me.cboStreet.Requery
End Sub

CoNameID CoName
1024 Fuller's Home & Hardware

Street Location
35 E First Street Hinsdale

This is one record in the table that returns no street or location -
it happens on a few other records but I didn't take notes on any of
the other ones and I can't remember which ones they are, so this is
the only one I can offer. I have checked the table to be sure that
there are no empty spaces leading in the fields.

I hope this helps you to help me, which I appreciate muchly
Joanne



J. Goddard wrote:

Hi -

Can you post the code you are using to fill the cascading combo boxes,
and perhaps an example or two of names that don't work?

John



Joanne wrote:

 

[Joanne]

Thanks for the heads up John
I will look at my table again and see if the apostrophe is the cause
I think I will change all text delimiters to double quotes as you
suggest - it seems to me to be a better way to design the statement,
eliminating this kind of trouble in the first place.
I appreciate the knowledge you share on this forum
Joanne

Hi Joanne -

Fuller's Home & Hardware has a single quote in it (a.k.a. an
apostrophe!) - I thinks that's the problem.

You can remove the apostrophe and see what happens, or you can change
all the text delimiters to double quotes, so that:

where tblmain.[CoName] ='" & Me!cboCoName & "' order by ..
becomes
where tblmain.[CoName] =""" & Me!cboCoName & """ order by ..

etc.
or you could remove the apostrophe from the company name and see what
happens.

Are the other ones that don't work the same?

John

Here is the code for the cbo boxes:

This one fills the Location CboBox
Private Sub cboCoName_AfterUpdate()
Me!cboLocation.RowSource = "select Distinct tblmain.[location]
from tblmain where tblmain.[CoName] ='" & Me!cboCoName & "' order by
[location]"
Me.cboCoName.Requery
End Sub

This one fills the Street CboBox
Private Sub cboLocation_AfterUpdate()
Me!cboStreet.RowSource = "select distinct tblmain.[street] from
tblmain " & _
"where tblmain.[coname] = '" & Me!cboCoName & _
"'and tblmain.[location] = '" & Me!cboLocation & "' order by
[street]"
Me.cboStreet.Requery
End Sub

CoNameID CoName
1024 Fuller's Home & Hardware

Street Location
35 E First Street Hinsdale

This is one record in the table that returns no street or location -
it happens on a few other records but I didn't take notes on any of
the other ones and I can't remember which ones they are, so this is
the only one I can offer. I have checked the table to be sure that
there are no empty spaces leading in the fields.

I hope this helps you to help me, which I appreciate muchly
Joanne



J. Goddard wrote:

Hi -

Can you post the code you are using to fill the cascading combo boxes,
and perhaps an example or two of names that don't work?

John



Joanne wrote:


thanks for you input Tony
I did a close examination of my table and there are no records that do
not have the proper fields filled in.

Any other ideas?
Joanne
TonyT wrote:



Make sure that during testing you haven't created new records with empty
location and street fields fro the same company name - not an uncommon cause.

TonyT

:



Winxp and access 2003

I have a table with these fields
CoID CoName Location Street

I pull the info into the form using cascading cboboxes
Choose CoName
get Location (Cities) relevant to CoName
get Street (addresses) relevant to CoName & Location

I have approx 1500 records, and on most of them this works great - but
on some records, after choosing CoName it will not bring up the
Location and/or street. I have looked at the table at these records
and they are in proper field sequence, no blank spaces, nothing looks
out of sort on the record, yet it doesn't work.
I am baffled and am hoping someone out there has seen this before and
can tell me how to fix it so that my app is more reliable.

TIA
Joanne