strange table behavior Joanne <jobobbuss[ at ]sbcglobal.com> 27.09.2006 10:47:52 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

RE: strange table behavior TonyT 27.09.2006 19:07:02 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" wrote: [Quoted Text] > 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 > >

Re: RE: strange table behavior Joanne <jobobbuss[ at ]sbcglobal.com> 27.09.2006 23:51:13 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: [Quoted Text] >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" wrote: > >> 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 >> >>

Re: strange table behavior "J. Goddard" <jrgoddard[ at ]NO_cyberus_SPAM.ca> 28.09.2006 01:42:15 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: [Quoted Text] > 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 >> >>"Joanne" wrote: >> >> >>>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 >>> >>> > >

Re: strange table behavior "David F Cox" <nospam[ at ]please.com> 28.09.2006 13:43:24 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" <jobobbuss[ at ]sbcglobal.com> wrote in message news:eOp3iJi4GHA.4740[ at ]TK2MSFTNGP03.phx.gbl... [Quoted Text] > 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 > >

Re: strange table behavior Joanne <jobobbuss[ at ]sbcglobal.com> 29.09.2006 22:58:49 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: [Quoted Text] >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 >>> >>>"Joanne" wrote: >>> >>> >>>>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 >>>> >>>> >> >> >

Re: strange table behavior "J. Goddard" <jrgoddard[ at ]NO_cyberus_SPAM.ca> 30.09.2006 00:39:08 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 Joanne wrote: [Quoted Text] > 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 >>>> >>>>"Joanne" wrote: >>>> >>>> >>>> >>>>>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 >>>>> >>>>> >>> >>> >

Re: strange table behavior Joanne <jobobbuss[ at ]sbcglobal.com> 30.09.2006 12:19:26 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 J. Goddard wrote: [Quoted Text] >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 > > >Joanne wrote: >> 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 >>>>> >>>>>"Joanne" wrote: >>>>> >>>>> >>>>> >>>>>>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 >>>>>> >>>>>> >>>> >>>> >> >