Basic Question on combo boxes

[kjy]

Say you have a database set-up. You have combo boxes setup which drop down choices. Now the user looks through the choices and doesn't see his option, so he types in his option. How do i accept that option and add it to the list of drop down choices?

 

[thornmastr]

Use the not in list event to add the selection to the underlying source, then requery the combo box control to see the new addition on your form. Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com

 

[raymondo]

Open the properties list of the combo with the form in design mode
Under Events, select the Not In List event and use the code generator to start yourself off.
Note that Access creates a sub with two parameters. The first is the new data you have entered (as a string) and the second is used internally by Access to know what to do.
Here's an example
Sub CBox_NotInList(NewData As String, Response As Integer)

Dim intMsgBox As Integer
Dim strData As String

strData = Trim$(NewData)
intMsgBox = MsgBox("""" + strData + """" + " does not appear in the database. Do you want to add it now?", vbQuestion + vbYesNo)
If intMsgBox = vbYes Then
DoCmd.OpenForm FormName:="FormName", DataMode:=acFormAdd, WindowMode:=acDialog, OpenArgs:=strData
'You can put a check in here if you want to be absolutely sure that the new data has been added with Response = acDataErrContinue if it hasn't been added (for example a DLookup)
Response = acDataErrAdded
End If
Response = acDataErrDisplay
End Sub
A couple of things to note:
I have assumed that the list comes from a table somewhere, hence my DoCmd.OpenForm line. I open a form called FormName to allow the user to input the new data - note that the form is opened in "dialog" mode. This ensures that the sub will stop executing immediately so that new data can be entered before anything else happens.
Response =
acDataErrAdded means that all went okay and access will automatically requery the source
acDataErrContinue means no supplementary error messages wil be displayed even if no new data was actually added (perhaps the user cancelled)
acDataErrDisplay displays access error message (only in the event there is one of course)
Hope this is what you're after
Raymondo

 

[kjy]

OK, i'm just learning how to use this software...so i'm playing around with thornmastr's suggestions and while creating my macro, I ask the computer to perform the following commands: CopyObject, Save, Requery, and Close. When I try and place a new item into my guide box, I get the following error: This operation isn't available while there is a module in break mode. Reset the executing code and try the operation again. Thanks for your help... you guys have been most helpful....sorry if i sound too much like a neophyte.

 

[thornmastr]

You may well be a neophyte but you can certainly ask a well constructed question. I tend to try and answer questions by concepts rather than by specific coding examples unless the code clearly explains the process.

Since you seem to be rather curious about how the process of development integrates into a functioning product, let me give you a strong suggestion. Macros solve very limited problems, do not allow for error checking, and the more complex they become the harder they are to debug. You have enough grasp of what you want to do to begin moving away from macros and more toward using Visual Basic. Try writing functions to do what your macros do.

There are a lot of people willing to help you develop good coding techniques, but some, like me, shy away from information, advise, or even hints about macros.
Robert Berman
Data Base consultant
Vulcan Software Services
thornmastr@yahoo.com