can't auto fill

[chrisrach3]

I AM STUMPED.



I have a field (Prospect Name) in a form (Listing Leads) that uses a combo box to select a name from "Contacts Query". The Contacts Query has 6 fields: FirstName, LastName, BusinessPhone, MobilePhone, EmailAddress, and Expr. Expr is "Expr: [FirstName] & " " & [LastName]". And it probably has about 1,100 records.

So the Prospect Name field/combo box works GREAT, but I'd also like it to auto populate the rest of the fields in the Listing Leads form. Under "AfterUpdate", the code reads as follows:

Email = DLookup("EmailAddress", "Contacts Query", "Expr")

This is obviously only 1 of 6, but you get the point. And this brings up my 1st contact's email address, which is great. But as you can probably tell by the code, it brings up his address regardless of the name I type in the Prospect Name field. (I only point this out to let you know that it works up to this point.) So when I include the following code:

Email = DLookup("EmailAddress", "Contacts Query", "Expr=" & Prospect Name)

a dialog box opens that reads: "Compile error: Expected: list separator or)".

Here's what the entire code looks like:


Option Compare Database

Private Sub Form_Open(Cancel As Integer)

End Sub

Private Sub Prospect_Name_AfterUpdate()

Email = DLookup("EmailAddress", "Contacts Query", "Expr=" & Prospect Name)


End Sub


And here's the error message that comes up after I type someone's name in the Prospect Name field: "Compile Error: Syntax Error." And in the VBA, it highlights the following: Private Sub Prospect_Name_AfterUpdate()

Any ideas?

[pbaldy]

This would be a lot more efficient:

http://www.baldyweb.com/Autofill.htm

[chrisrach3]

Thank you for the feedback. What I'm doing with these is adding listing prospects, so I am essentially pasting this information into new records. I tried adding the following expression (?) into the control source field: =Property Name.Column(2) and nothing happened.

It appears that I AM trying to save the information as an AfterUpdate function. At the end of your last post, what was the "Me" in your expression?

[pbaldy]

"Me" is a shortcut that can be used in code to refer to the object (form in this case) that the code is in. By the way, a source of your original problem is the inadvisable space in the field name. I'd change it, but you can work around it by bracketing it:

[Property Name]

[chrisrach3]

Thanks for the advice. I'll try that out today.

[chrisrach3]

Ugh. So frustrating. So I tried it and no luck whatsoever. I have the following box that pops up: Run-time error '3075': Syntax error (missing operator) in query expression 'expr=John Smith'. I've changed all of my field names to contain no spaces. When I debug, it highlights the following line:

Email = DLookup("EmailAddress", "ContactsQuery", "expr=" & ProspectName)

Am I doing something wrong? It's probably impossible to tell without actually taking a look at my computer. I also tried the idea from your website, and it translates to the following:

[Listing Leads].Email = ContactsQuery.ProspectName.Column(2)

After I run that, the popup reads: Run-time error '424': Object required. Ugh.

[pbaldy]

This does not look like proper syntax:


[Listing Leads].Email = ContactsQuery.ProspectName.Column(2)

You should refer to the controls on the form containing the values.

Me.TextboxName = Me.ComboName.Column(x)

[Rasool Sarang]

Hi. Maybe this is useful: http://wp.me/pPUCs-1F