SteveHumby
Posts: 20
|
| Posted: 05/02/2008, 12:17 PM |
|
Hi
I am at a complete loss and wondered if anyone else has had a similar experience.
I am trying to populate a listbox from an Access Query which contains 99 records. When in Visual Query Builder mode, on testing the query, all 99 records are displayed but for some reason, when running the page live, the ListBox displays only 46...
I wondered if anyone has had a similar occurence and had a solution.
Thanks in advance.
Steve :)
Further to this... There are 46 unique records within the DataSet, the ListBox seems to be suppressing the duplicate records...
:)
|
 |
 |
wkempees
|
| Posted: 05/03/2008, 6:28 AM |
|
No pun intended, but:
are your test and live database in sync?
Walter
"SteveHumby" <SteveHumby@forum.codecharge> schreef in bericht
news:8481b68dcd1b7b@news.codecharge.com...
> Hi
>
> I am at a complete loss and wondered if anyone else has had a similar
> experience.
>
> I am trying to populate a listbox from an Access Query which contains 99
> records. When in Visual Query Builder mode, on testing the query, all 99
> records
> are displayed but for some reason, when running the page live, the ListBox
> displays only 46...
>
> I wondered if anyone has had a similar occurence and had a solution.
>
> Thanks in advance.
>
> Steve :)
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.yessoftware.com/
>
|
|
|
|
SteveHumby
Posts: 20
|
| Posted: 05/03/2008, 7:24 AM |
|
Hi Walter
Yes they are...
As I mentioned, in Visual Query Builder, it displays 99 records but something in CodeCharge when building the datatable to assign to the ListBox is only taking the first record, in this case the name of a person, but leaving all subsequent records where the person has the same ID.
The trouble is is that I need all of the records as they differ with an AreaID, so that when I assign a dependent ListBox, subsequent records do not exist, ergo they are only displayed when one record is chosen in the dependent ListBox but no others..
I have tried building my own datatable and assigning that to a ListBox and that displays all 99 records, unfortunately I'm not sure what I need to do to include an AreaID, to include within a dependency query.
Hope this makes sense and that you can help.
Thanks 
|
|
|
|
SteveHumby
|
| Posted: 05/03/2008, 7:24 AM |
|
Hi Walter
Yes they are...
As I mentioned, in Visual Query Builder, it displays 99 records but something
in CodeCharge when building the datatable to assign to the ListBox is only
taking the first record, in this case the name of a person, but leaving all
subsequent records where the person has the same ID.
The trouble is is that I need all of the records as they differ with an AreaID,
so that when I assign a dependent ListBox, subsequent records do not exist, ergo
they are only displayed when one record is chosen in the dependent ListBox but
no others..
I have tried building my own datatable and assigning that to a ListBox and that
displays all 99 records, unfortunately I'm not sure what I need to do to include
an AreaID, to include within a dependency query.
Hope this makes sense and that you can help.
Thanks
---------------------------------------
Sent from YesSoftware forum
http://forums.codecharge.com/
|
|
|
|
wkempees
|
| Posted: 05/03/2008, 7:44 AM |
|
Post you listbox query here as well as the table layout with indexes.
Also the following:
your sql might be displaying the correct 99 records.
The Listbox takes (id, value) and does not show duplicates......
reason being that it needs to show a meaningfull list to the user as well as
being able to move the unique 'id' to the target record, in a subsequent
display of the target record it will use the value to query the
(listboxes') table to retreive the text value, which explains for the needed
uniqueness.
That said, you might need to unique-ify your table by changing the id
content
or use a different mechanism altogether.
Walter
|
|
|
|
SteveHumby
Posts: 20
|
| Posted: 05/03/2008, 10:23 AM |
|
Thanks Walter
Here is the SQL for the ListBox:
SELECT tblIntRec.IntRecID AS SupervisorID, [Forenames] & ' ' & [Surname] AS Supervisor, tblIntRec.IsSupervisor, tblIntRecArea.AreaID
FROM tblIntRec INNER JOIN tblIntRecArea ON tblIntRec.IntRecID = tblIntRecArea.IntRecID
GROUP BY tblIntRec.IntRecID, [Forenames] & ' ' & [Surname], tblIntRec.IsSupervisor, tblIntRecArea.AreaID
HAVING (((tblIntRec.IsSupervisor)=True))
What I could do if you were willing is to create a small Application and post a link to the files?
Many thanks again.
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/03/2008, 10:59 AM |
|
Quote :
What I could do if you were willing is to create a small Application and post a link to the files
Please do (weekend + 1 here though)
As I suspected it is a multitable join.
The problem is, the SupervisorID returning duplicates, to the listbox which needs unique id's.
Also I like to see the fields you display in the listbox.....
SupervisorID - Supervisor, would be the listbox content.
Do you have a reason to do the select so complex?
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
SteveHumby
Posts: 20
|
| Posted: 05/03/2008, 12:42 PM |
|
Hi Walter
Unfortunately I can find no other way of acheiving my requirement.
Anyway, i've uploaded a copy of the files (and the source code too), you can download it from:
http://www.stevehumby.co.uk/ListBox.zip
I have built the Query into the database rather than in Visual Query Builder, however it won't work either way.
You'll notice that "Interviewer 10" only appears in "Area 1" but should appear in all areas.
If you could find a spare few minutes to run over it, I would be most truly grateful.
Many thanks
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/04/2008, 4:52 AM |
|
I will, sunday + bank holiday though.
Meanwhile, think about a way of making you id's unique.....
I will have to look at your implementation to see how you store the selected, to be able to give any usable answer.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
SteveHumby
Posts: 20
|
| Posted: 05/04/2008, 1:01 PM |
|
Thanks mate.
I guess I could add the SupervisorID and AreaID together to make the ID unique but it would be an ugly way of doing it, I would also need to strip off the AreaID before storing it as its only the SupervisorID that i'm interested in.
Anyway, thanks for giving me that idea, but I'm sure there must me a better way.
Have a good weekend
|
|
|
|
wkempees
|
| Posted: 05/05/2008, 6:27 AM |
|
Download done.
"SteveHumby" <SteveHumby@forum.codecharge> schreef in bericht
news:8481e16238cf10@news.codecharge.com...
> Thanks mate.
>
> I guess I could add the SupervisorID and AreaID together to make the ID
> unique
> but it would be an ugly way of doing it, I would also need to strip off
> the
> AreaID before storing it as its only the SupervisorID that i'm interested
> in.
>
> Anyway, thanks for giving me that idea, but I'm sure there must me a
> better
> way.
>
> Have a good weekend
>
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/05/2008, 8:37 AM |
|
OK First off: 2 problems.
1, no project in zip file
2, (mine) I didnot notice you are on .Net
But I think I solved your problem for you as follows:
The Litbox should run on your table tblInterviewerArea
IntAreaID is your unique key item
You have infact created the necessary many to many table here.
So your SQL should be altered, altered version will be in my next post.
Meanwhile, you might be able to do this yourself
HTH, Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/05/2008, 9:01 AM |
|
SELECT tblInterviewerArea.IntAreaID, tblInterviewers.Interviewer, tblArea.Area, tblInterviewers.IsSupervisor
FROM (tblInterviewerArea INNER JOIN tblInterviewers ON tblInterviewerArea.InterviewerID = tblInterviewers.InterviewerID) INNER JOIN tblArea ON tblInterviewerArea.AreaID = tblArea.AreaID
WHERE (((tblInterviewers.IsSupervisor)=True));
Is the query as MSAccess would accept it.
You will have to do the concatenate [Forenames] &' ' & [Surnames] yourselves as these seem not to be in my version of your MsAccess mdb.
Also in the concatenate I would include the Area name
You would also want to sort or group by, probably on InterviewerID, AreaID to have them stick together.
The CCS Visual Query Builder in TABLE mode would allow you to do exactly the same SQL but would rewrite it in a more standard way.
Solved?
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
SteveHumby
Posts: 20
|
| Posted: 05/05/2008, 9:29 AM |
|
Hi Walter
It does give me a unique ID but unfortunatly the value I need to store is the InterviewerID.
It gives me a thought though, I wonder if I build the ListBox using the AreaID as the primary ID and the InterviewerID, the do a DLookUp using the InterviewerID to get the Interviewer Name.
Then I can set the bound column and the text column to the Interviewer ID... Although thinking about it I don't think that will work either...
I have been experimenting with combining the InterviewerID and AreaID to produce a unique ID which works perfectly, unfortunately thats where another problem arises.
I test the length of the ID (which is the InterviewerID and AreaID combined) and the length of the AreaID, then do a quick sum to leave the length of the proper InterviewerID.
But, even though I get the correct values, it still doesnt work...
Does this make any sense?
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/05/2008, 11:24 AM |
|
You do not have a problem as far as I see it.
Store the tblInterviewerArea.IntAreaID instead of the InterviewerID.
Beacuse that table holds all the info you need.
If you want the SupervisorName details or whatever, you have the InterviewerID in tblInterviewerArea
You just need to adjust your Dlookup or whatever other method you may want to use.
You have created the many-to-many relationship, use it.......
The only other way I can see this to be made working is:
Create the Listbox as I describe, in the events store the obtained InterviewerID in the appropriate field.
That way your target field will hold te correct InterviewerID and looking up that name will be easy (Dlookup), only .... your listbox on subsequent edits of the record, will not position to the right supervisor
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/05/2008, 11:28 AM |
|
Afterthought:
If you do your original SQL, but make sure that where you do :
[Forename] & ' ' & [Surname] as Supervisor
You include something extra like
[Forename] & ' ' & [Surname] & '- ' & AreaID as Supervisor
That would make the uniqueness of the text part and might (I said might) solve your listbox problem.
{brilliance with no computer at hand to test it)
Based on the fact that a Listbox will only show unique values (ID,Text)
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
SteveHumby
Posts: 20
|
| Posted: 05/05/2008, 11:49 AM |
|
Quote wkempees:
Afterthought:
If you do your original SQL, but make sure that where you do :
[Forename] & ' ' & [Surname] as Supervisor
You include something extra like
[Forename] & ' ' & [Surname] & '- ' & AreaID as Supervisor
That would make the uniqueness of the text part and might (I said might) solve your listbox problem.
{brilliance with no computer at hand to test it)
Based on the fact that a Listbox will only show unique values (ID,Text)
Walter
Good Idea but unfortunately that doesn't work either.
I have tested the idea that you gave me of creating a Unique ID by adding together the SupervisorID and AreaID together then strip off the AreaID before storing it and it works really well.
I am trying to fit that into the full App now with a few errors unfortunately but I think we're on the right track.
Thanks for your help, I am most grateful.
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/05/2008, 1:20 PM |
|
OK 
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
DaveSause
Posts: 14
|
| Posted: 05/07/2008, 11:14 AM |
|
OK, excuse me butting in at this late date, but it sounds like rather than concatenating data to make a unique key you should just make a composite key. For example, Supervisor ID and Area ID could be set to index, duplicates ok. Then just click both Supervisor ID and Area ID to select them and this assign the key. Now the combination of Supervisor ID and Area ID has to be unique, but either alone can be repeated as many times as required. When you select in code charge, you can ask for just the Supervisor, or just the area, or both and return the correct number of records. If these are related tables for which you want to preserve referential integrity, it's a bit trick to create the relationship. You can only select one field at a time, right? Well just pick one, and Access will recognize the composite key and create the relationships as if they were individual keys; it will recognize the one-to-many and allow you to specify referential integrity, cascade update, etc.
If Access won't let you designate the keys as I mention, then the issue is that you don't have referential integrity. Research the data and make sure the records are unique. Let's say you realize that it's not only SupervisorID and Area ID but also something else, like Record Date. This means you need a three part key.
Everytime I have found myself concatenating and disassembling data into another field to locate records, it's proven to be a fundamental datatbase design flaw and trying to use an atomic key when a composite was needed.
Hope this helps.
Dave
_________________
Dave Sause |
|
|
|
SteveHumby
Posts: 20
|
| Posted: 05/07/2008, 3:10 PM |
|
Hi Dave
Thanks for the post.
I still have no idea why but I rebuilt the page completly and it now works fine - like I say, no idea why.
The database contained a composite key, this was never the problem. Again, I have no idea why but CodeCharge was compressing the amount of records displayed in the ListBox. After starting from scratch, using the same data, SQL or db query, it displayed exactly as required.
Anyway, thanks for your help.
Have fun...
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 05/08/2008, 6:09 AM |
|
Nice to know.
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
|
