Sub-form lookup

[rguy84]

I have been making a call log. Due to the nature of it we have multiple entries per person usually. So typing a person's contact details 5 times gets annoying.

I was thinking of having a button that brings up another form that has every name (first last) of everybody who is currently added. I could click their name, and another button. The button would populate the main form and close the small form.

Ths is somewhat easy to do in access,. but not sure how to start in excel.

 

[rguy84]

If you are just trying to get the initial list why not do it manually?

An initial list would have to include potentially all employees. So having everybody just-in-case would entail ~19,500 people - literally. Yeah we have our regular people, so right now I have abour 40 unique names in the two months the log was started.

It's probably going to be a one off anyway.

Yes - if i could have everbody that might contact us.

Here's the file I was working on, like I said I didn't really do much on it but perhaps it'll give you an idea of what I've been rambling about.

http://www.box.net/shared/furub2fls8u5ufurhd0f

Just double click on column B on the Log worksheet.

very nice.

 

[Norie]

I'm not sure I understand.

Why do you need to include all potential contacts/employees in the list?

If you really need to do that then you definitely need something other than Excel.

 

[rguy84]

I guess i can populate it with frequent people. Can I do a magical compare in the background at Workbook open?

if(count(log.lastname && log.firstname)>3){
 if(log.value != customer.value){
  add person to sheet for subform
 }
}

or would this be too much

 

[Norie]

This is roughly what I was thinking:

1 Create a list from the contacts in the current log.

2 User double clicks column B to add new entry to log.

3 Userform opens where they can:
a) select a contact from the dropdown
or b) enter a contact

4 If they selected from the list or what they enter is on the list the relevant columns
are populated from the data on the 'Contacts' worksheet and the userform closed.

5 If they enter a name not on the list the are offered the oppurtunity to add the
(new?) contact they have entered to the list.

6 If they select to add the new contact they fill in the rest of the userform, that
data is used to populate the relevant columns on the log worksheet and
the data is added to the list on the Contacts worksheet so that next time the
userform is opened the list includes the new contact.

So you start of with an initial list and each time an unknown contact is entered by
the user it's added to that list, building up a list of contacts.

 

[rguy84]

The form does a few things on submit, so I would want to mod step 4. Currently the form looks like:
--
team member [DD] Date [T]
Initiated by [DD] [button for sub form]
Customer info frame
last [T] Phone #: [T]
First [T] email: [T]
Division [DD]
summary [T]
Start Time [T] end [T]
[submit] [clear]
--
[T] - text field
[DD] Drop down.

#4- click button for sub, on sub have a list box from contacts worksheet. Choose one- hit ok, data filled into main form
#5 - add a tic box under email asking to add for future use vs another prompt
#6- agreed

 

[Norie]

You've had a form right from the start?

Your first post mentioned 'sub' form but I assumed you just meant a small form
allowing the user to select the contact.

I didn't realise you had a 'main' form already which was used to enter data into the
log worksheet.

 

[rguy84]

oh sorry yes, I have a form that launches on wb open, as well as a button on the worksheet that relaunches.

 

[Norie]

Any chance there's anything else you've not mentioned?

 

[rguy84]

Not that I know of... :P

 

[Norie]

Well all you need to do is add a button to open the contact 'sub' form and then direct the input to the main form instead of the worksheet.

Though I'm still unsure what you want to happen when an unknown contact is entered.

Do the fields in the main form just stay blank?

Or do you still want the user to enter the details for the new contact on the sub for, and transfer them to the main form.