Automatic Field Entry

[Mike]

Im starting in on a new database and i need to have the "Street" field
automatically enter in the name of the street from a list of streets in a
separate table.
Once the user starts inputting the first letter of the street name i want to
have all suggestions for all streets that begin with that letter show in a
pull down window or something similar. If 2 or more letters are typed the
list will shorten etc.
Can access do something like this?

 

[nanne]

create a input box on the form with the wizard based on the table "streets"

suc6

Nnne

 

[Mike]

create a input box on the form with the wizard based on the table

"streets"

Well that alsmost works like i want but it only jumps to the corresponding
street name if it matches the first letter you type.
If mulitple streets have the same first letter it goes to the first in the
list and if you type the same letter again it goes to the next and so on. I
guess itll work but i was hoping for something a little closer to what i had
described.

 

[Mike]

Well it works great when using the combo box instead of the list box.
Now im wondering how i can have it allow street names that aren't in the
table and at the same time prompt the user to either add it to the Streets
table or not.

 

[John Vinson]

Well it works great when using the combo box instead of the list box.
Now im wondering how i can have it allow street names that aren't in the
table and at the same time prompt the user to either add it to the Streets
table or not.

Yes; set the Combo's "Limit to List" property to Yes, and put the code
from the link below into its Not In List event:

http://www.mvps.org/access/forms/frm0015.htm

John W. Vinson[MVP]

 

[Mike]

John,
Great site but just a little short on directions. Im no seasoned Access guru
but i did figure out how to enter the code in the appropriate place.
I changed the first line where it says cbxAEName to the name of my table but
i suspect there are some other things that need changing to make it work
with my database.
Im getting an error whether i click yes or no at the prompt.
Could you maybe shed some light on this.

Thanks
Mike

 

[Mike]

Actually i entered the Combo Box name in place of the cbxAEName and i then
changed a line a ways below that to the name of my table.
The other line was Set rs = db.OpenRecordset("Streets", dbOpenDynaset) where
Streets was tblAE.

 

[John Vinson]

Actually i entered the Combo Box name in place of the cbxAEName and i then
changed a line a ways below that to the name of my table.
The other line was Set rs = db.OpenRecordset("Streets", dbOpenDynaset) where
Streets was tblAE.

Please copy and paste your code; indicate the name of the Form, the
Name properties of the street field and of the combo box, and the name
of the table.

John W. Vinson[MVP]

 

[Mike]

Please copy and paste your code; indicate the name of the Form, the
Name properties of the street field and of the combo box, and the name
of the table.

John W. Vinson[MVP]

John,
I messed with this to much i suspect and now even after starting from
scratch i cant get the Yes No prompt back. I just get a cant execute code in
design mode error.
Anyways here's the code as i have it now which i believe is exactly as i had
it when i got the Yes No prompt.
Form Name is "Contacts" Combo Box name is "Combo34" Name Property for the
street field is the same as the Combo Box "Combo34"
and the name of the Table (the one that contains the street names) is
"Streets"
I hate to even ask this now but now that im using a combo box for the Street
name and for the 2 zip codes, they don't appear in a Report im generating
from this form. Would you happen to know if that's an easy fix?

Thanks for your help it is much appreciated
Mike

The Code:
Private Sub Combo34_NotInList(NewData As String, Response As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = "'" & NewData & "' is not an available AE Name " & vbCrLf &
vbCrLf
strMsg = strMsg & "Do you want to associate the new Name to the current
DLSAF?"
strMsg = strMsg & vbCrLf & vbCrLf & "Click Yes to link or No to re-type
it."

If MsgBox(strMsg, vbQuestion + vbYesNo, "Add new name?") = vbNo Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblAE", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!AEName = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub