mgrygorczuk
New member
- Local time
- Today, 11:46
- Joined
- Feb 25, 2008
- Messages
- 6
Hi everyone, I've just registered but I read this forum often as it answers the most questions I got; but for this problem I couldn't find an answer.
It's simple, and there are solutions for it in the forum, but they don't work in my case.
I've got a problem with combo box.
It's working fine when the SQL query is as follows:
SELECT DISTINCT ProjectsQuery.PrID, ProjectsQuery.PrNo, ProjectsQuery.PrCode, ProjectsQuery.PrName FROM ProjectsQuery;
Explanation:
Its a document control database, in which items in archive room are recorded. An item is a boxs with documentation for a specific project, so there is projects table and items table.
ProjectsQuery gets records from ProjectsTable, pr. number, code and name. Every record in projects table has unique PrID. Relation are in 3rd normal form.
Combo box is bounded to PrID, 4 colums are displayed, first one with 0 length as it is a key value. Combo box is located on a form that is used for adding new items; one item has only one project, but there can be many items for the same project. So, when I add new item, I key in the project number into a combo box, and it works fine. PrID is not autonumber, I use Dmax+1 in default value. It is a Number data type.
The problem: when I add new project, it gets unique prID, and in combo box its always on the bottom of the list. Which isn't good because it is project's number that I want records to be sorted by. So, I tried to add ascending sorting in the query, in field PrNo (project number). It seems to work fine, records are sorted by projects number; even the latest additions are on the correct place; but when I try to write the project number into field I get an error message:"The text you entered isnt an item on the list; select an item from the list, or enter text that matches one of the listed items" , even if i put the proper, existing in a list entry. For example: when I expand the combo box I see value 10 amongst the others, plus 3 other columns with project details; I can select proj. no 10 with mouse and it works ok, but when I try to key in "10" I get an error.
Before I added the sorting I was able to key in values and it worked well; why cant I do it now?
Well, there was a problem before adding the sorting: when I tried to key in a project number that I added using the form in database, I got the same error message; the project appeared on the expanded combo box, I was able to select it with mouse, but when I tried to key in the number I got the error message. I imported the data into a table from excel sheet; new additions made with forms doesnt seem to work - why?
It happened to me before few times, and I dont know why it acts like that, but Access seems to treat differently the data that I add to table with a form, especially if I modify the form at some point, and the data that I import from excel sheet. I cant see the difference, they are all store in same table - whats wrong?
I searched the forum for answers, as you can see I added DISTINCT after SELECT, but it doesnt work in my case; I want the combo box to display all records from projects table sorted alphabetically, AND to be able to key in project number so I dont have to use the mouse. First part is working, projects are sorted, but I cant key in the values I need. Also, newly added records seem to act differently then imported ones.
Any help would be greatly appreciated; I do a lot of data entry with this form and its annoying to use mouse to get to the end of the list for some project entries.
It's simple, and there are solutions for it in the forum, but they don't work in my case.
I've got a problem with combo box.
It's working fine when the SQL query is as follows:
SELECT DISTINCT ProjectsQuery.PrID, ProjectsQuery.PrNo, ProjectsQuery.PrCode, ProjectsQuery.PrName FROM ProjectsQuery;
Explanation:
Its a document control database, in which items in archive room are recorded. An item is a boxs with documentation for a specific project, so there is projects table and items table.
ProjectsQuery gets records from ProjectsTable, pr. number, code and name. Every record in projects table has unique PrID. Relation are in 3rd normal form.
Combo box is bounded to PrID, 4 colums are displayed, first one with 0 length as it is a key value. Combo box is located on a form that is used for adding new items; one item has only one project, but there can be many items for the same project. So, when I add new item, I key in the project number into a combo box, and it works fine. PrID is not autonumber, I use Dmax+1 in default value. It is a Number data type.
The problem: when I add new project, it gets unique prID, and in combo box its always on the bottom of the list. Which isn't good because it is project's number that I want records to be sorted by. So, I tried to add ascending sorting in the query, in field PrNo (project number). It seems to work fine, records are sorted by projects number; even the latest additions are on the correct place; but when I try to write the project number into field I get an error message:"The text you entered isnt an item on the list; select an item from the list, or enter text that matches one of the listed items" , even if i put the proper, existing in a list entry. For example: when I expand the combo box I see value 10 amongst the others, plus 3 other columns with project details; I can select proj. no 10 with mouse and it works ok, but when I try to key in "10" I get an error.
Before I added the sorting I was able to key in values and it worked well; why cant I do it now?
Well, there was a problem before adding the sorting: when I tried to key in a project number that I added using the form in database, I got the same error message; the project appeared on the expanded combo box, I was able to select it with mouse, but when I tried to key in the number I got the error message. I imported the data into a table from excel sheet; new additions made with forms doesnt seem to work - why?
It happened to me before few times, and I dont know why it acts like that, but Access seems to treat differently the data that I add to table with a form, especially if I modify the form at some point, and the data that I import from excel sheet. I cant see the difference, they are all store in same table - whats wrong?
I searched the forum for answers, as you can see I added DISTINCT after SELECT, but it doesnt work in my case; I want the combo box to display all records from projects table sorted alphabetically, AND to be able to key in project number so I dont have to use the mouse. First part is working, projects are sorted, but I cant key in the values I need. Also, newly added records seem to act differently then imported ones.
Any help would be greatly appreciated; I do a lot of data entry with this form and its annoying to use mouse to get to the end of the list for some project entries.
Last edited:
