When a user enters data in a field that is not in a list. I want to open a dialog window from the not_in_list event so they can select to add new record, update the existing recoprd, or cancel the update. It should return a value That I can uses to call a update or add function. Just not sure if VBA supports opening a dialog to return a value. Or how I would do it.
Opening a Dailog box from VB Code
- Thread starter wjburke2
- Start date
Hey, if I've understood correctly what it is you want to do then that's quite easily doable. You're using a combo box right? So, say for example you have a combo box with its row source set to a specific table (I'm going to use the example of adding Titles (i.e. Mr, Mrs, Ms. etc.).
So your combo box displays titles, but you as the user type in "Dr." - which is not in the table. To have a message box open up providing options to either add the title to the table or to not (in which case the user will have to select a title from the drop-down list), in the combo box's Not In List event place the following code:
'Place this code in the combo box's Not In List event
Dim strNew As String
Dim intMsg as Integer
Set strNew = NewData
intMsg = (strNew & " is not an approved title. Would you like to add it?", vbQuestion+vbYesNo, "Add new title"
Select Case intMsg
Case vbYes
DoCmd.OpenForm "FormToAddTitles", WindowMode:=acDialog, OpenArgs:=strNew
Response = acDataErrAdded
Case vbNo
Me.cmbTitle = ""
Me.cmbTitle.Dropdown
Response = acDataErrContinue
End Select
Response = acDataErrContinue
'Place this code in the form that you use to add the new record (title in this case)
'Put it in the On Load event
If Not IsNull(Me.OpenArgs) Then
Me.txtTitle = Me.OpenArgs
End If
Hope this helps.
So your combo box displays titles, but you as the user type in "Dr." - which is not in the table. To have a message box open up providing options to either add the title to the table or to not (in which case the user will have to select a title from the drop-down list), in the combo box's Not In List event place the following code:
'Place this code in the combo box's Not In List event
Dim strNew As String
Dim intMsg as Integer
Set strNew = NewData
intMsg = (strNew & " is not an approved title. Would you like to add it?", vbQuestion+vbYesNo, "Add new title"
Select Case intMsg
Case vbYes
DoCmd.OpenForm "FormToAddTitles", WindowMode:=acDialog, OpenArgs:=strNew
Response = acDataErrAdded
Case vbNo
Me.cmbTitle = ""
Me.cmbTitle.Dropdown
Response = acDataErrContinue
End Select
Response = acDataErrContinue
'Place this code in the form that you use to add the new record (title in this case)
'Put it in the On Load event
If Not IsNull(Me.OpenArgs) Then
Me.txtTitle = Me.OpenArgs
End If
Hope this helps.
Your on the right track (but)
I have some code to add a record if the value is not in the list. The problem is somthmes the user would like to change the value not add a new record. I would like to give them that option through a form where they tell me what they want to do. I will then decide if I should use a update function or add function or cancel/undo their change. I am thinking this could get rid of those buttons and give the user Kind of like this:
Private Sub Combo14_NotInList
Display UpdOption screen
If answer = Update
do update function
elseif anwser = Add
do Add function
else
.undo
endif
end Sub
I have some code to add a record if the value is not in the list. The problem is somthmes the user would like to change the value not add a new record. I would like to give them that option through a form where they tell me what they want to do. I will then decide if I should use a update function or add function or cancel/undo their change. I am thinking this could get rid of those buttons and give the user Kind of like this:
Private Sub Combo14_NotInList
Display UpdOption screen
If answer = Update
do update function
elseif anwser = Add
do Add function
else
.undo
endif
end Sub
Last edited:
I'm not 100% sure I follow what exactly you want to do. From what I've understood, users want to change the value that appears in the combo box? I'm not sure how you want to achieve that but one method could be to have a "Change" button or something, which would open the form bound to the combo box's record source and filter it to display the value currently displayed by the combo box? E.g. code for the On Click event of the button could be:
DoCmd.OpenForm, "FormToChangeValue",WhereCondition:="[Field1] = '" & Me.[Field2] & "'",WindowMode:=acDialog
Replace [Field1] for the field in the dialog form, and [Field2] with the combo box that you're updating?
Sorry if I'm once again posting a useless solution! Just trying to help
.
DoCmd.OpenForm, "FormToChangeValue",WhereCondition:="[Field1] = '" & Me.[Field2] & "'",WindowMode:=acDialog
Replace [Field1] for the field in the dialog form, and [Field2] with the combo box that you're updating?
Sorry if I'm once again posting a useless solution! Just trying to help
.Actually you gave me a vital clue
The Docmd.OpenForm may do just what I want. I just have to figure out how to pass the option back to the calling form. Global varaible, or somthing like that. Then, the if statment will takecare of the rest. I have a function to Add and one Udate the categories table. I just need to decide what to do and then requery conrtol to display the updated records. Should work??? LOL. I am pretty new. So somtimes it takes a minute to research and figure how to apply the answers I get. It's all trial and error for me.
BTW
intMsg = (strNew & " is not an approved title. Would you like to add it?", vbQuestion+vbYesNo, "Add new title"
Causes a comopile error because of the comma after the question mark quotes. Not sure why.
(like to add it?", vbQuestion+vbYesNo)
The Docmd.OpenForm may do just what I want. I just have to figure out how to pass the option back to the calling form. Global varaible, or somthing like that. Then, the if statment will takecare of the rest. I have a function to Add and one Udate the categories table. I just need to decide what to do and then requery conrtol to display the updated records. Should work??? LOL. I am pretty new. So somtimes it takes a minute to research and figure how to apply the answers I get. It's all trial and error for me.
BTW
intMsg = (strNew & " is not an approved title. Would you like to add it?", vbQuestion+vbYesNo, "Add new title"
Causes a comopile error because of the comma after the question mark quotes. Not sure why.
(like to add it?", vbQuestion+vbYesNo)
Oops! Sorry yeah the parantheses is a typo here (didn't copy and paste just typed it up here). There shouldn't be a "(" before strNew.
If you want the combo box to be updated after you amend the record in the dialog form you could try adding something like: Me![NameOfComboBox].Requery
You could add that at the end of your code, (after the dialog box is opened, as the form being opened in dialog box will cause the code to halt until it is closed, therefore if any changes are made, the form is closed and then the combo box is requeried, displaying the most up-to-date data?
Just an idea. If I've got it wrong again please let me know! Hope this helps.
If you want the combo box to be updated after you amend the record in the dialog form you could try adding something like: Me![NameOfComboBox].Requery
You could add that at the end of your code, (after the dialog box is opened, as the form being opened in dialog box will cause the code to halt until it is closed, therefore if any changes are made, the form is closed and then the combo box is requeried, displaying the most up-to-date data?
Just an idea. If I've got it wrong again please let me know! Hope this helps.
Similar threads
