Forms, Tables and Memory Variable Help

Chrstine Pearce

Registered User.
Local time
Today, 23:11
Joined
Oct 20, 2003
Messages
12
Hi there. I'm a frustrated FoxPro coder tried to come to grips with Access -- I don't understand it at all! Please help!

In a form called frmCreateCAR, a user wants to automatically complete contact details:
Name
Phone
Email
Mobile

Do do this, the user clicks a buton named EmployeeBtn which successfully opens a form called frmEmployees.

frmEmployees lists the fields from the employees table:
LastName
FirstName
Phone
Email
Mobile

Here's where I'm having big problems and this is what I want to happen: When the user finds the name they want, they double-click the LastName field. The fields are stored to memory (which have been declared as public in frmCreateCAR):

mName =LTrim$([LastName])+", "+LTrim$([FirstName])
Phone, Email and Mobile are also stored as mPhone,
mEmail. etc.

The Employees form then closes automatically and returns to the frmCreateCAR form where the fields Name, Phone, Email and Mobile are duly updated with the memory variables.

It sounds like this should be SO easy, but I can't for the life of me figure out how to do it. Any help will be greatly appreciated.

Cheers, Christine
 
There's a much easier way to do this.

In your form frmCreateCAR, create a combo box populated by a Query linked to your Employees table (which I'll refer to as NameField). The combo box will contain five columns:

1) the employee ID number (or whatever your unique ID field is)
2) [LastName] & ", " & [FirstName]
3) Phone
4) Email
5) Mobile

Specify in the combo box properties that there are five columns, but give columns 1, 3, 4 and 5 a width of zero.

Now you've got two choices. Either:

a) specify the Control source for each of your details field like so: for the Phone field, Control Source is "=Forms!frmCreateCAR!NameField.Column(2)". Email and Mobile would take the same formulas, but as columns 3 and 4, respectively.
b) alternatively, as an On Exit event for the Name field, you could specify in code:
Me.Phone = me.NameField.Column(2)
Me.Email = me.NameField.Column(3)
Me.Mobile = me.NameField.Column(4)

Option (a) is better if your details fields are unbound, meant to be looked up rather than stored. Option (b) is better if you're storing the details with the record, for any reason.
 
Thanks for your quick response, AlienRay!

Your suggestion is good, but there is actually alot more info available to the user than I included in my plea for help, which would make the combo box quite large. In addition, with over 100 employees the box would be really long, and in the current frmEmployees form I have included alphabetic navigation buttons. Plus, I'd like the user to have the ability to change their details, if isn't really suitable for a combo box without alot of "not in list" coding bother.

If you can suggest another alternative using the original spec I sent, I'd be eternally grateful!

Cheers,
Christine
 

Users who are viewing this thread

Back
Top Bottom