Ok, I probably have a goofy title for this thread, but this is what I want to do. I have some fields that have a lookup to another table. For example, tblTransactions has a field name AcccountID (long) that has a lookup to tblCOA, COAID, to select the account. What I would like to do is have an item in the Account list, let's call it "Add Account" (which would be the first item on the list, COAID would be zero) and the user can select this and then code to open frmCOA can be brought up to add a new account to this list.
Your approach of having a dummy item for user to select and trigger opening form to add new record is probably doable but would not involve NotInList event. It would probably mean code in combobox BeforeUpdate event to validate input. And building the combobox RowSource to show this dummy item would probably involve a UNION query, unless it is actually a record in lookup table.
Your approach of having a dummy item for user to select and trigger opening form to add new record is probably doable but would not involve NotInList event. It would probably mean code in combobox BeforeUpdate event to validate input. And building the combobox RowSource to show this dummy item would probably involve a UNION query, unless it is actually a record in lookup table.
I guess the main reason I've rarely if ever used NotInList is because generally I don't want to encourage people adding stuff to dropdowns willy-nilly. Generally speaking, the type of data element that exists in drop downs is a very serious matter, and something management or some specialized worker has authority over as the downstream impacts are many. The act of 'adding' something that would effect a change to a dropdown is kept separate, on a separate tab/screen that only some people have access to. NotInList makes it 'feel' more like a process where anybody can type anything they want and add it. Just one man's opinion
The notion of creating a closed domain of values for a combo box coupled with an open-ended way to circumvent that closed domain, strikes me as being a good way to drive yourself crazy keeping up with data clean up and maintenance resulting from people taking advantage of the ability to add their own values at will.
In a highly controlled environment, it might even be the case that overriding the list of accounts should be restricted to admin users who have the authority to do so.
In a more practical case, it might be better to have an established procedure that allows new accounts to be added, but not through the same method used when adding transactions against existing accounts. I think your method of adding a dummy account to trigger that workflow is doable. On the other hand, I also think a command button next to the combo box that runs the procedure to add a new account might be more trouble-free.
I would definitely not use the NotInList approach for this, either.
The code in a command button would open a form as a popup to add the new account to the accounts table.
Or, if your screen real estate is highly limited, you could get away with including the "add new" option in a union query with the accounts.
That might look like this:
Code:
SELECT 0 as AccountID, " <Add New>" AS Account
FROM tblYourTableNameGoesHere UNION SELECT AccountID, Account FROM tblYourTableNameGoesHere Order BY Account
In the AfterUpdate event of the combo box, check the value of the selected AccountID; if it's 0 open the form to add new Accounts as a modal. You will need to requery the combo box after the Popup form closes so that the newly added account is in the rowsource.
If an existing AccountID was selected, do nothing. It's cool.
There may be additional factors to account for, although that's the general approach you might try.
The notion of creating a closed domain of values for a combo box coupled with an open-ended way to circumvent that closed domain, strikes me as being a good way to drive yourself crazy keeping up with data clean up and maintenance resulting from people taking advantage of the ability to add their own values at will.
In a highly controlled environment, it might even be the case that overriding the list of accounts should be restricted to admin users who have the authority to do so.
In a more practical case, it might be better to have an established procedure that allows new accounts to be added, but not through the same method used when adding transactions against existing accounts. I think your method of adding a dummy account to trigger that workflow is doable. On the other hand, I also think a command button next to the combo box that runs the procedure to add a new account might be more trouble-free.
I would definitely not use the NotInList approach for this, either.
The code in a command button would open a form as a popup to add the new account to the accounts table.
Or, if your screen real estate is highly limited, you could get away with including the "add new" option in a union query with the accounts.
That might look like this:
Code:
SELECT 0 as AccountID, " <Add New>" AS Account
FROM tblYourTableNameGoesHere UNION SELECT AccountID, Account FROM tblYourTableNameGoesHere Order BY Account
In the AfterUpdate event of the combo box, check the value of the selected AccountID; if it's 0 open the form to add new Accounts as a modal. You will need to requery the combo box after the Popup form closes so that the newly added account is in the rowsource.
If an existing AccountID was selected, do nothing. It's cool.
There may be additional factors to account for, although that's the general approach you might try.
If you set the RowSourceType of a ComboBox or ListBox to "Value List", you can run the AddItem() method to add a row to the list, so you can loop through any enumerable list, and fill the control programmatically that way. If you have multiple columns, delimit column data with a semi-colon. You can also construct a complete semi-colon delimited string, and set the RowSource property to that string.
In a highly controlled environment, it might even be the case that overriding the list of accounts should be restricted to admin users who have the authority to do so.
For my biggest Navy project, that was exactly what I did. The "Not In List" code triggered for all users - but what it ACTUALLY did was make an entry in a log file that wasn't visible to my users. That way I could ask the user what they were trying to do. The combo's .RowSource was never changed that way. There was a convoluted path to actually add new entries. Basically, EVERY table had a single-table, single-record form that you could use to edit the contents of a combo or listbox .RowSource table. You NEVER actually used NotInList.
There is a difference between what type of data is populating the list. For example, if you are working on an Order Entry application, you add new customers all the time. BUT, the process to add a new customer isn't just to add a new customer name. There's lots of other pieces of data that are also required when adding a new account. There is unlikely to be programming code that applies to specific customers though. However, If the combo is a list of departments, there is probably no coding logic involved that does different things for different departments so, using the notinlist event probably won't be a problem as long as your users are not sloppy. Then we get to things like Customer Type. Here, you might want only programmers to add new codes because the processing logic might require different processing or different data requirements for different types of customers.
The notion of creating a closed domain of values for a combo box coupled with an open-ended way to circumvent that closed domain, strikes me as being a good way to drive yourself crazy keeping up with data clean up and maintenance resulting from people taking advantage of the ability to add their own values at will.
In a highly controlled environment, it might even be the case that overriding the list of accounts should be restricted to admin users who have the authority to do so.
In a more practical case, it might be better to have an established procedure that allows new accounts to be added, but not through the same method used when adding transactions against existing accounts. I think your method of adding a dummy account to trigger that workflow is doable. On the other hand, I also think a command button next to the combo box that runs the procedure to add a new account might be more trouble-free.
I would definitely not use the NotInList approach for this, either.
The code in a command button would open a form as a popup to add the new account to the accounts table.
Or, if your screen real estate is highly limited, you could get away with including the "add new" option in a union query with the accounts.
That might look like this:
Code:
SELECT 0 as AccountID, " <Add New>" AS Account
FROM tblYourTableNameGoesHere UNION SELECT AccountID, Account FROM tblYourTableNameGoesHere Order BY Account
In the AfterUpdate event of the combo box, check the value of the selected AccountID; if it's 0 open the form to add new Accounts as a modal. You will need to requery the combo box after the Popup form closes so that the newly added account is in the rowsource.
If an existing AccountID was selected, do nothing. It's cool.
There may be additional factors to account for, although that's the general approach you might try.
