I have a database with a 'Projects' table with 148 records. One of the fields is named ‘Current’ (a y/n field). I also have a table named 'Printing' with a lookup field named 'Project' based on the 'Projects' table. I have a data entry form set up based on 'Printing'. The combo box for the 'Project' field on the form is based on a query that finds only projects that are marked as 'current' in the 'Project' table. I've come up with a glitch though - if a record in ‘Printing’ has a project that was current at the time it was entered but subsequently has been marked as non-current, the ‘Project’ control on the form goes blank. The 'project' data remains correct in the ‘Printing’ table, but the form doesn't display it correctly. The form is used as a 'job log' so when a job is logged in, I want the ‘Project’ control pull-down to display only current projects but I also need the form to always display that information no matter what the status of a project. I’ve tried playing with the ‘limit to list’ property with no luck. I’m not a coder at all so please be gentle with me.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Combo box problem
- Thread starter LidaRose
- Start date
Hi, LidaRose!
I'm not sure I'm clear on what you need, but if I'm following your description correctly, the combobox would have to go blank when the form displays a project that isn't current.
You've told the combobox to only display records that are Y.
When the form has a record that is an N, the combobox won't have anything to display.
My recommendation would be to split your form into two: one for data entry - new project and one for data entry - existing project.
Then remove the Y requirement from the combobox on the Existing Project form.
New projects would have to have a Y, while existing projects would display all information.
Good luck!
Michelle Hakala
I'm not sure I'm clear on what you need, but if I'm following your description correctly, the combobox would have to go blank when the form displays a project that isn't current.
You've told the combobox to only display records that are Y.
When the form has a record that is an N, the combobox won't have anything to display.
My recommendation would be to split your form into two: one for data entry - new project and one for data entry - existing project.
Then remove the Y requirement from the combobox on the Existing Project form.
New projects would have to have a Y, while existing projects would display all information.
Good luck!
Michelle Hakala
TheAceMan1
Programmer
How are ya LidaRose . . .
See Ya! . . . . . .
Be sure to see FAQ219-2884:
Perhaps if you explain this [purple]ambiguity[/purple] in more detail?LidaRose said:
Be sure to see FAQ219-2884:
- Thread starter
- #4
Thanks to both of you - maybe I should try to explain what I need by coming at this from the ‘form’ point of view first. I’m duplicating a db and process already in place. It was originally programmed in Filemaker Pro (and works very well too) but we now have to switch to Access.
The form in question, ‘Printing Log’, is based on a table named ‘Printing’. It’s used to log in print job orders, track them throughout the process of filling them, and to log them out when completed. The form must be open and available at all times throughout the processing of the orders.
As orders are logged in, a ‘Project’ must be designated for each. (There is a field named ‘Project’ in the ‘Printing’ table that is related to the ‘Project ID’ field in the ‘Projects’ table.) The ‘Project’ combo box on the form is based on a query that looks up project names in a ‘Projects’ table. The only criteria in the query is to find projects that are marked as ‘Current’. The reason for this is that there are over 200 projects in the ‘Projects’ table – it's much cleaner to choose from the 50 or so that are current at any one time.
This all works fine until this situation arises:- for example – On 9/1, a print order, #10, is logged into ‘Printing Log’ for the ‘ABC Project’. (Because it is marked as ‘current’ in the ‘Projects’ table, ‘ABC Project’ shows up in the pull-down menu for the ‘project’ combo box on the ‘Printing Log’ form.) On 9/5, the #10 print order is complete and logged out of ‘Printing Log’. On 9/8, ‘ABC Project’ is marked as ‘Not Current’ in the ‘Projects’ table.
Now when I go to the ‘Printing Log’ form, the ‘Project’ combo box is blank for print order #10. However, when I go to the ‘Printing’ table, #10 still shows ‘ABC Project’ in the record. I need to always be able to see in the ‘Printing Log’ form what ‘project’ an order was printed for whether or not that project is current at that point in time.
I’ve played around a bit more with the form and ‘project’ shows in a text box based on the ‘Project’ field from the ‘Printing’ table. I don’t have that much room to play with on the ‘Printing Log’ form though. Short of a separate entry screen at the time of logging in jobs (which will meet with great resistance by the way) is there anything else that you can think of that would work?
The form in question, ‘Printing Log’, is based on a table named ‘Printing’. It’s used to log in print job orders, track them throughout the process of filling them, and to log them out when completed. The form must be open and available at all times throughout the processing of the orders.
As orders are logged in, a ‘Project’ must be designated for each. (There is a field named ‘Project’ in the ‘Printing’ table that is related to the ‘Project ID’ field in the ‘Projects’ table.) The ‘Project’ combo box on the form is based on a query that looks up project names in a ‘Projects’ table. The only criteria in the query is to find projects that are marked as ‘Current’. The reason for this is that there are over 200 projects in the ‘Projects’ table – it's much cleaner to choose from the 50 or so that are current at any one time.
This all works fine until this situation arises:- for example – On 9/1, a print order, #10, is logged into ‘Printing Log’ for the ‘ABC Project’. (Because it is marked as ‘current’ in the ‘Projects’ table, ‘ABC Project’ shows up in the pull-down menu for the ‘project’ combo box on the ‘Printing Log’ form.) On 9/5, the #10 print order is complete and logged out of ‘Printing Log’. On 9/8, ‘ABC Project’ is marked as ‘Not Current’ in the ‘Projects’ table.
Now when I go to the ‘Printing Log’ form, the ‘Project’ combo box is blank for print order #10. However, when I go to the ‘Printing’ table, #10 still shows ‘ABC Project’ in the record. I need to always be able to see in the ‘Printing Log’ form what ‘project’ an order was printed for whether or not that project is current at that point in time.
I’ve played around a bit more with the form and ‘project’ shows in a text box based on the ‘Project’ field from the ‘Printing’ table. I don’t have that much room to play with on the ‘Printing Log’ form though. Short of a separate entry screen at the time of logging in jobs (which will meet with great resistance by the way) is there anything else that you can think of that would work?
I'm sorry, but no. You can't have (that I know of) the combobox display both Y and N records when you've told it to only display Y ones.
Were it mine, I'd consider redesigning the form to eliminate the combobox totally.
My first stab at it would be... maybe using a popup user input form when they click whatever they click to get to this form that asks them New Project? If yes, go to form which has the combobox, if no, then to a different one with just a text box (no restrictions on Y or N).
If that didn't work, I'd brainstorm something else. Good luck!
Michelle Hakala
Were it mine, I'd consider redesigning the form to eliminate the combobox totally.
My first stab at it would be... maybe using a popup user input form when they click whatever they click to get to this form that asks them New Project? If yes, go to form which has the combobox, if no, then to a different one with just a text box (no restrictions on Y or N).
If that didn't work, I'd brainstorm something else. Good luck!
Michelle Hakala
TheAceMan1
Programmer
LidaRose . . .
You need a combobox, listbox, or subform who's recordsource is based on a query/sql linking the [blue]Projects table[/blue] with the [blue]Printing table[/blue] to show [purple]ProjectName & Order#[/purple]. Since the tables are [blue]linked via ProjectID[/blue] this should be no problem . . .
See Ya! . . . . . .
Be sure to see FAQ219-2884:
You need a combobox, listbox, or subform who's recordsource is based on a query/sql linking the [blue]Projects table[/blue] with the [blue]Printing table[/blue] to show [purple]ProjectName & Order#[/purple]. Since the tables are [blue]linked via ProjectID[/blue] this should be no problem . . .
Be sure to see FAQ219-2884:
- Status
Similar threads
- Locked
- Question