Re: Multiple Column Combo box, NotInList event

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: eric (ericnospammers_at_noway.org)
Date: 12/08/04 

Date: Wed, 08 Dec 2004 00:14:01 GMT

Thanks for the tip Graham,
I will try out making a form that will handle it, but I have about 10 other
fields with the same notinlist coding already being used and I would like to
stay consistent.

The thing about the form is it must be easy to use and fast as it is now.
This is the only problem that exists. Isn't there a simple way to add some
lines to ask for the corresponding value instead?

I'm absolutely not knocking what you're saying, I'm just saying I'd like to
stay consistent. Now if I've messed up all the others with that same code
then someone tell me so I can go redo it all.

Eric

"Graham Mandeno" <Graham.Mandeno@nomail.please> wrote in message
news:%2316btvJ3EHA.3380@TK2MSFTNGP09.phx.gbl...
> Hi E
>
> It is common to need to add a record with information that is not enitrely
> supplied by what the user has typed into the combobox (NewData). For
> example, you might be adding a new customer record with other required
> fields such as phone number or address.
>
> To capture all this new information, it's best to use a form. The
technique
> I use is to open a form modally and pass the NewData value in OpenArgs.
> Here is a simple example using your code:
> If MessValue = vbYes Then
> DoCmd.OpenForm "frmAddDDLTaskType", DataMode:=acFormAdd, _
> WindowMode:=acDialog, OpenArgs:=NewData
> Response = acDataErrAdded
> Else
> Response = acDataErrContinue
> End If
>
> The Form_Load procedure in the opened form simply takes the passed value
and
> pops it into the required field:
> Me!Task = Me.OpenArgs
>
> You may find you need to deal with occurrences like the user cancelling
the
> add operation from the form, or changing the text that was passed from
> NewData (thus making the content of the combo invalid once again) but this
> should get you most of the way.
> --
> Good Luck!
>
> Graham Mandeno [Access MVP]
> Auckland, New Zealand
>
>
> "hhhh" <hhhh@www.org> wrote in message
> news:uPntd.3140$0b3.933@fe2.texas.rr.com...
> > Hello all,
> > I have a combo box on a form with 2 columns. When a new value is entered
I
> > am using the NotInList event to handle it. My problem is I have a second
> > column in the Combo Box that represents the full description of the
first
> > Column and I haven't been able to update that field with the new full
> > description. The second column comes from the same table the first
> > does(tblDDLTaskTypes).
> >
> > I had started to program in an Input box to accept the new description
> > value
> > but am getting object errors among others. I am not that experienced
with
> > vba and can't get the syntax correct. I have inherited this db and am
not
> > sure which way to go.
> >
> > Here is the code that accepts the new value. I have not been able to
> > figure
> > out how to get the second field entered with an InputBox.
> >
> > Private Sub Task_NotInList(NewData As String, Response As Integer)
> > Dim strTask As String
> > Dim MessValue As Integer
> > Dim Msg As String
> >
> > 'Exit this sub if the combo box is cleared of values
> > If NewData = "" Then Exit Sub
> > 'Message for the New Data message box
> > Msg = "'" & NewData & "' is not currently in the list of Task Types." &
> > vbCr
> > & vbCr
> > Msg = Msg & "Do you want to add it?"
> > 'Here is the message box that pops up making reference to the field
> > MessValue = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Task Type...")
> > If MessValue = vbYes Then
> > 'here is where the table and field are referenced for the list
> > strTask = "Insert Into tblDDLTaskTypes ([Task]) values ('" &
NewData
> > &
> > "')"
> > 'This is where the new value is added to that field to the first
> > column
> > CurrentDb.Execute strTask, dbFailOnError
> > Response = acDataErrAdded
> > Else
> > Response = acDataErrContinue
> > End If
> > End Sub
> >
> > Thanks, I haven't worked with double column combo/notinlist situations
> > before,
> > Many TIA,
> > E.
> >
> >
>
>

Relevant Pages

  • Re: How do I find duplicate rows, add quantity field & retain one
    ... Dim xRng As Range ... Dim ws1 As Worksheet, ws2 As Worksheet ... Dim hold As Variant, NewData() As Variant ... '' copy xrng into hold variant variable, now hold is variant array. ...
    (microsoft.public.excel.programming)
  • Re: "/decompile /recompile"
    ... Response As Integer) ... 'Undo the combo-box before updating control value to "NewData" ... Dim ctl As Control ... Set rstShipVia = MyDB.OpenRecordset ...
    (comp.databases.ms-access)
  • Re: How do I find duplicate rows, add quantity field & retain one
    ... "Rick Hansen" wrote: ... Dim xRng As Range ... Dim ws1 As Worksheet, ws2 As Worksheet ... Dim hold As Variant, NewData() As Variant ...
    (microsoft.public.excel.programming)
  • "/decompile /recompile"
    ... Response As Integer) ... 'Undo the combo-box before updating control value to "NewData" ... Dim ctl As Control ... Set rstShipVia = MyDB.OpenRecordset ...
    (comp.databases.ms-access)
  • Re: How do I find duplicate rows, add quantity field & retain one
    ... Dim xRng As Range ... Dim ws1 As Worksheet, ws2 As Worksheet ... Dim hold As Variant, NewData() As Variant ... '' copy xrng into hold variant variable, now hold is variant array. ...
    (microsoft.public.excel.programming)