I have a form with a combo box that is driven from a table. I have all the code necessary in the "Not in List" event to take the contents of the text that was typed into the box and automatically add it to the table that is the source for the combo box. What is the command that will refresh the combo box so that I don't have to close the form and open it again? I've tried requery for both the form and the combo box and neither seems to work.
Not in List
- Thread starter grnzbra
- Start date
I found the solution and am posting it in case someone else needs it (or I forget and come back in a month or two)
The NotInList event has two arguments with it
NewData as string and
Response as integer.
The new data is the text that was entered in the box.
The response can be one of three things.
acDataErrDisplay (default) doesn't allow the change
acDataErrContinue is used after displaying a custom message box, typically used to tell the user that the entry is not in the list and allow user to decide if he wants to add it. If he selects NO, the response will be set to acDataErrContinue.
acDataErrAdded is used in the same manner as acDataErrContinue except it is that it is used when the user selects YES.
The NotInList event has two arguments with it
NewData as string and
Response as integer.
The new data is the text that was entered in the box.
The response can be one of three things.
acDataErrDisplay (default) doesn't allow the change
acDataErrContinue is used after displaying a custom message box, typically used to tell the user that the entry is not in the list and allow user to decide if he wants to add it. If he selects NO, the response will be set to acDataErrContinue.
acDataErrAdded is used in the same manner as acDataErrContinue except it is that it is used when the user selects YES.
Keep in mind that if you pass "acDataErrAdded", it will automatically requery the recordsource and select the new data. Therefore, if a user clicks Yes but somehow backed out or messed with the data, Access will think that there wasn't a new data added, and will display standard error message saying to select within the list, even though the new entry is there (but doesn't match with NewData).
I found the solution and am posting it in case someone else needs it (or I forget and come back in a month or two)
The NotInList event has two arguments with it
NewData as string and
Response as integer.
The new data is the text that was entered in the box.
The response can be one of three things.
acDataErrDisplay (default) doesn't allow the change
acDataErrContinue is used after displaying a custom message box, typically used to tell the user that the entry is not in the list and allow user to decide if he wants to add it. If he selects NO, the response will be set to acDataErrContinue.
acDataErrAdded is used in the same manner as acDataErrContinue except it is that it is used when the user selects YES.
So it would look something like this:
Private Sub Combo12_NotInList(NewData As String, Response As Integer)
If MsgBox("'" & NewData & "' is not in the list. Do you wish to add it?", vbYesNo) = vbYes Then
DoCmd.OpenForm "TableNames", acNormal, , , , , NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
The OnOpen event of the TableNames form (Used for adding new table names to a list) goes to a new record, checks if OpenArgs is null. If it is, it continues, if it isn't, its value is added to the TableName field and the form is closed
The NotInList event has two arguments with it
NewData as string and
Response as integer.
The new data is the text that was entered in the box.
The response can be one of three things.
acDataErrDisplay (default) doesn't allow the change
acDataErrContinue is used after displaying a custom message box, typically used to tell the user that the entry is not in the list and allow user to decide if he wants to add it. If he selects NO, the response will be set to acDataErrContinue.
acDataErrAdded is used in the same manner as acDataErrContinue except it is that it is used when the user selects YES.
So it would look something like this:
Private Sub Combo12_NotInList(NewData As String, Response As Integer)
If MsgBox("'" & NewData & "' is not in the list. Do you wish to add it?", vbYesNo) = vbYes Then
DoCmd.OpenForm "TableNames", acNormal, , , , , NewData
Response = acDataErrAdded
Else
Response = acDataErrContinue
End If
End Sub
The OnOpen event of the TableNames form (Used for adding new table names to a list) goes to a new record, checks if OpenArgs is null. If it is, it continues, if it isn't, its value is added to the TableName field and the form is closed
Hmmm. If you're opening a form to input new data, you would want to open it in dialog mode:
(Don't remember how many commas acDialog needs to follow. Need to check it yourself).
This way, the code will pause execution until the form is closed then proceed to add new data. Otherwise, it won't work.
Why you said it work, I'm not understanding.
Code:
DoCmd.OpenForm "TableNames", acNormal, , , , acDialog , NewDataThis way, the code will pause execution until the form is closed then proceed to add new data. Otherwise, it won't work.
Why you said it work, I'm not understanding.
Good catch. Perhaps it worked for me because the only thing on the form being opened is the TableName field and it opens and closes before the code manages to continue. I'll have to add the acDialog to it just to make sure. Perhaps if the form took longer to load it would bomb the way I have it.
Thank you!
What you posted here didn't quite work for me, but it led me down the right path, and with some experimentation, I finally succeeded. Now when I enter a location that's not in the list, my entry form pops up as a little window, I enter data [new location name and details], save and close, and the new data is immediately shown.
Brilliant.
Now all I have to figure out is how to get the form to pull the location I tried to enter so that people won't have to enter it twice.
What you posted here didn't quite work for me, but it led me down the right path, and with some experimentation, I finally succeeded. Now when I enter a location that's not in the list, my entry form pops up as a little window, I enter data [new location name and details], save and close, and the new data is immediately shown.
Brilliant.
Now all I have to figure out is how to get the form to pull the location I tried to enter so that people won't have to enter it twice.
- Local time
- Today, 20:40
- Joined
- Sep 12, 2006
- Messages
- 16,072
i thought i saw a similar posting elsewhere - the problem is that the code falls through before the popup box closes - i dont really like dialog boxes and try to avoid them, so i generally do
DoCmd.OpenForm stDocName
while isopen(stdocname)
do events
wend
cbobox.requery
DoCmd.OpenForm stDocName
while isopen(stdocname)
do events
wend
cbobox.requery
