Combobox NotInList problem

[die_laffin via AccessMonster.com]

Hi

I have a combobox on a form that lists email addresses that I use for
emailing my reports

What I am trying to do is have the ability to add another email address if
its not in the list

The combobox is unbound & recordsource is tblEmailAddresses. This table just
has 3 fields, EmailAddress, FirstName & Surname

At present when NotInList fires & it tells me that its not in the list &
opens my form to let me enter the details.
Problem is I cannot work out how to pass the already entered email address to
the new form so that I dont have to retype the email address.

Any help would be appreciated. Its been a long say & I'm feeling a bit thick!

Thanks
die_laffin

 

[Graham Mandeno]

The OpenForm method has an argument called OpenArgs. You can use this to
pass data to the form that is being opened.

In your NotInList procedure, modify your OpenForm line as follows:

DoCmd.OpenForm "Your form name", DataMode:=acAdd _
WindowMode:=acDialog, OpenArgs:=NewData

This will pass the NewData value to the form.

Now, your form needs to do something with that data when it loads, so in
your Form_Load event procedure:

If Me.DataEntry then
Me.EmailAddress.DefaultValue = """" & Me.OpenArgs & """"
End If

This will set the default value for EmailAddress to the value passed in
OpenArgs.

 

[Klatuu]

I find using the After Update event works better than the Not In List event.
It allows you to make the selected record the current record or add a new
record if the entered value is not in the table. The user can also start
over if it was a typing error. I just think I have more control, and the
code is a little tighter. Here is an example from one of my forms:

Note, there is some additional code in here to deal with populating a
subform and, the combo is not the bound control, so I populate it as well.

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
On Error GoTo cboActivity_AfterUpdate_Error

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.txtMactivity = Me.cboActivity
Me.frmSubAttributeTable!txtMactivity = Me.cboActivity
Else
Me.cboActivity = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
Me.cboActivity = Null
Call SetNavButtons(Me)
Me.txtDescription.SetFocus
End If
cboActivity_AfterUpdate_Exit:

On Error Resume Next
rst.Close
Exit Sub

cboActivity_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboActivity_AfterUpdate of VBA Document
Form_frmAttributetable"
GoTo cboActivity_AfterUpdate_Exit

End Sub

 

[die_laffin via AccessMonster.com]

Thanks for the responses guys!

Problem was mainly me being to tired, but the problem is now sorted.

Thanks again

die_laffin