combo box autofill

[Carl]

I have a form with a name box and one with the email. What i am trying to do
is as you type in the name it will go ahead and fill in the name as you type
and the letters change and as this happens it also looks up the e-mail
address and shows it in the 2nd combo box and changes as the name changes. If
the name is not in the table it will automaticly save it to the table and
updat the table so the next time you look for it it will show up in the name
combo box with the save button i have on the form can anyone help.

Thank you
Carl

 

[Jeff Boyce]

Carl

A combobox is used to select from a list of available items. Why would your
second control be a combobox if you would be "filling" it with a
pre-existing email address?

And if the email address already exists, why would you be storing it again?

You can set a combobox in Access to autocomplete, which sounds a little like
what you are describing as you type in the name.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[June7 via AccessMonster.com]

Your name box must be a combobox. Then you have the properties AutoExpand,
RowSource, RowSourceType, AllowValueListEdits, ListItemsEditForm and event
OnNotInList. I haven't used the AllowValueListEdits nor ListItemsEditForm
properties. I use OnNotInList event to control what happens if item not in
RowSource.
Ex:

Private Sub cbxStateNum_NotInList(NewData As String, Response As Integer)
If MsgBox("State Number not in database. Add new project record?",
vbOKCancel + vbQuestion, "NoRecord") = vbOK Then
Me.cbxStateNum.LimitToList = False
DoCmd.GoToRecord , , acNewRec
Me.cbxStateNum.LimitToList = True
Me!StateNum = NewData
Me.cbxName.SetFocus
Me.cbxName.Requery
Me.tbxStateNum.Enabled = False
End If
Response = acDataErrContinue
Me.cbxStateNum = Null
Me.lbxProjects.Requery
End Sub

If the combobox is bound to the table field and you are on a new record, the
entered name will be saved straight into the new record.
Then can use the AfterUpdate event to do something with the entered name such
as retrieve and display related information.

 

[June7 via AccessMonster.com]

Amendent to previous post. The email box can be a textbox. Use the
AfterUpdate event of the name combobox to run code that will lookup up the
email address and then set the value of the textbox. Code can be a DLookUp
function or a recordset Select query. Since you don't need to save the email
address again, could even put the DLookUp expression in the textbox
ControlSource.

 

[June7 via AccessMonster.com]

PS: if you put DLookUp in the ControlSource, would not need the AfterUpdate
event.

Amendent to previous post. The email box can be a textbox. Use the
AfterUpdate event of the name combobox to run code that will lookup up the
email address and then set the value of the textbox. Code can be a DLookUp
function or a recordset Select query. Since you don't need to save the email
address again, could even put the DLookUp expression in the textbox
ControlSource.

I have a form with a name box and one with the email. What i am trying to do
is as you type in the name it will go ahead and fill in the name as you type

[quoted text clipped - 6 lines]

Thank you
Carl

 

[Carl]

I have a table with the names and e-mail address and 2 other with a check box
for a yes/no from the form i have a combo box that drops down with names and
it will auto put in the names as you type that works fine but i can not get
the 2nd combo box the auto fill with the address as you type in the name as
you type and it will not fill it in even after you get the hole name. So what
i am trying to do is get it to automaticly put in the address as you type in
the name or pick it from the dropdown list and if the name is not on the list
it will add it to the table.
carl

 

[Ron2006]

IF the email address is in the same table or a linked table with the
name then


create a query that includes the names AND the email address sorted by
name

Use this query as the basis for the name combo and have fields set to
2 bound to 1 and lenghts to show or not show both fields.

In afterupdate event of combo place code:

me.emailtxtfieldname = me.namecomboname.column(1)

This saves you from using a lookup that is a slower function.
The ## for the column is a 0 based number. First column would be 0,
second column is 1, etc.

Ron