2 Questions on Default Values and Multiple Field Lookup

  • Thread starter Thread starter Robert T
  • Start date Start date
R

Robert T

I’m building a Travel Expense database for my co-workers who travel to
various companies to do audits. This will not be a shared database, each
employee will have their own copy of the application that stores only their
travel expenses.

It’s obviously a One to Many application with tblTravel as the Parent, and
tblExpenses as the child table. Tracking expenses was the easy part, however,
I’m having a hard time designing two nifty little features for the Form. I’m
asking the question here because I assume it will require some coding on the
Form.

First question:
Each employee will have a one record table called tblTraveler that stores
their Lastname, Firstname, MI [if they have one], Office Name, Phone Number,
and Email address. When the traveler creates a new record, I would like the
form to automatically enter the traveler’s Lastname, Firstname, MI, Office
Name, Phone Number, and Email address from tblTraveler in READ ONLY format.

Second question:
There will be a combo box on the form so they can look up the name of the
company they’re auditing from tblCompanies. However, once they select the
company, I would like to automatically add the Company Address, City, State,
Zip, and Phone Number onto the form in READ ONLY format.

Thanks,
Robert
 
Robert,
On your Travel form, you should be using a combo box to enter an
EmployeeID, as well as a CompanyID.
Since you've captured the EmployeeID and the CompanyID, there's no need
to capture all the other information about those two values... just display
them (as you asked for)
On my Website below, I have a 97 and 2003 sample file called Combo
Populate Multiple Fields.
It shows how to "display" ancillary information... related to a combo
selected value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
Al:

I know your technically correct about the EmployeeID, however, they will be
the only person using the application so I don't think that's necessary in
this case.

As for both the Employee and Company info, it isn't a matter of capturing
only the ID numbers on the form, it's about adding a nice little touch so
they can see all of the pertinent info on the form. I realize for data
tracking we only need the Employee and Company ID numbers, but I wanted to do
something extra.

Most of these people are not technically proficient so having their name,
office, phone, number, and email adderss automatically added to the form
makes this application more appealing to them.

In terms of the company look up, I realize we normally capture only the
CompanyID number with a combo box, but it would also be a nice little extra
touch to display all of the info about the company on the form, as opposed to
only the Company Name.

What do you think?

'm gong to check out your link on comast.

I truly appreciate your response and offer to help.

Thanks,
Robert



Al Campagna said:
Robert,
On your Travel form, you should be using a combo box to enter an
EmployeeID, as well as a CompanyID.
Since you've captured the EmployeeID and the CompanyID, there's no need
to capture all the other information about those two values... just display
them (as you asked for)
On my Website below, I have a 97 and 2003 sample file called Combo
Populate Multiple Fields.
It shows how to "display" ancillary information... related to a combo
selected value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Robert T said:
I'm building a Travel Expense database for my co-workers who travel to
various companies to do audits. This will not be a shared database, each
employee will have their own copy of the application that stores only
their
travel expenses.

It's obviously a One to Many application with tblTravel as the Parent, and
tblExpenses as the child table. Tracking expenses was the easy part,
however,
I'm having a hard time designing two nifty little features for the Form.
I'm
asking the question here because I assume it will require some coding on
the
Form.

First question:
Each employee will have a one record table called tblTraveler that stores
their Lastname, Firstname, MI [if they have one], Office Name, Phone
Number,
and Email address. When the traveler creates a new record, I would like
the
form to automatically enter the traveler's Lastname, Firstname, MI, Office
Name, Phone Number, and Email address from tblTraveler in READ ONLY
format.

Second question:
There will be a combo box on the form so they can look up the name of the
company they're auditing from tblCompanies. However, once they select the
company, I would like to automatically add the Company Address, City,
State,
Zip, and Phone Number onto the form in READ ONLY format.

Thanks,
Robert
Click to expand...
Click to expand...
 
Al:

I checked out your combo box lookup for the ancilliary information and that
looks very cool for the Company Info on our form. Thanks!

Do you suggest I do the same thing to display the Employee Info? However, I
would much rather eliminate the need for a combo box and automatically
display the employee's info on the form. I don't think we don't need a combo
box because there's only one record in tblEmployees.

Any thoughts?

Robert

Al Campagna said:
Robert,
On your Travel form, you should be using a combo box to enter an
EmployeeID, as well as a CompanyID.
Since you've captured the EmployeeID and the CompanyID, there's no need
to capture all the other information about those two values... just display
them (as you asked for)
On my Website below, I have a 97 and 2003 sample file called Combo
Populate Multiple Fields.
It shows how to "display" ancillary information... related to a combo
selected value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Robert T said:
I'm building a Travel Expense database for my co-workers who travel to
various companies to do audits. This will not be a shared database, each
employee will have their own copy of the application that stores only
their
travel expenses.

It's obviously a One to Many application with tblTravel as the Parent, and
tblExpenses as the child table. Tracking expenses was the easy part,
however,
I'm having a hard time designing two nifty little features for the Form.
I'm
asking the question here because I assume it will require some coding on
the
Form.

First question:
Each employee will have a one record table called tblTraveler that stores
their Lastname, Firstname, MI [if they have one], Office Name, Phone
Number,
and Email address. When the traveler creates a new record, I would like
the
form to automatically enter the traveler's Lastname, Firstname, MI, Office
Name, Phone Number, and Email address from tblTraveler in READ ONLY
format.

Second question:
There will be a combo box on the form so they can look up the name of the
company they're auditing from tblCompanies. However, once they select the
company, I would like to automatically add the Company Address, City,
State,
Zip, and Phone Number onto the form in READ ONLY format.

Thanks,
Robert
Click to expand...
Click to expand...
 
Robert,
It seems as though you feel that I was not in favor of displaying the
ancillary Employee or Company details.
Not so... it's perfectly OK to do.

I'll assume that the EmpID is "hard-wired" into each user's form.

Since the EmpID is always static, a subform based on the EmpDetails
table, would work well to "display" the Employee ancillary data values from
tblTraveler, linked to the main form via the EmpID.

Same with the cboCompanyID combo. A subform based on your CompanyDetails
table, linked to the main form by CompanyID, would always display the
ancillary company values. You would just Requery the Company sub on the
AfterUpdate event of cboCompanyID.
You'd only have to lock the two subforms...

OR...
You could use the subform for employee data, and my Combo Populates
Other Fields method for the company data. But since you want quite a few
fields "displayed", I'd opt for the Emp subform and a Company subform.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Robert T said:
Al:

I know your technically correct about the EmployeeID, however, they will
be
the only person using the application so I don't think that's necessary in
this case.

As for both the Employee and Company info, it isn't a matter of capturing
only the ID numbers on the form, it's about adding a nice little touch so
they can see all of the pertinent info on the form. I realize for data
tracking we only need the Employee and Company ID numbers, but I wanted to
do
something extra.

Most of these people are not technically proficient so having their name,
office, phone, number, and email adderss automatically added to the form
makes this application more appealing to them.

In terms of the company look up, I realize we normally capture only the
CompanyID number with a combo box, but it would also be a nice little
extra
touch to display all of the info about the company on the form, as opposed
to
only the Company Name.

What do you think?

'm gong to check out your link on comast.

I truly appreciate your response and offer to help.

Thanks,
Robert



Al Campagna said:
Robert,
On your Travel form, you should be using a combo box to enter an
EmployeeID, as well as a CompanyID.
Since you've captured the EmployeeID and the CompanyID, there's no
need
to capture all the other information about those two values... just
display
them (as you asked for)
On my Website below, I have a 97 and 2003 sample file called Combo
Populate Multiple Fields.
It shows how to "display" ancillary information... related to a combo
selected value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Robert T said:
I'm building a Travel Expense database for my co-workers who travel to
various companies to do audits. This will not be a shared database,
each
employee will have their own copy of the application that stores only
their
travel expenses.

It's obviously a One to Many application with tblTravel as the Parent,
and
tblExpenses as the child table. Tracking expenses was the easy part,
however,
I'm having a hard time designing two nifty little features for the
Form.
I'm
asking the question here because I assume it will require some coding
on
the
Form.

First question:
Each employee will have a one record table called tblTraveler that
stores
their Lastname, Firstname, MI [if they have one], Office Name, Phone
Number,
and Email address. When the traveler creates a new record, I would like
the
form to automatically enter the traveler's Lastname, Firstname, MI,
Office
Name, Phone Number, and Email address from tblTraveler in READ ONLY
format.

Second question:
There will be a combo box on the form so they can look up the name of
the
company they're auditing from tblCompanies. However, once they select
the
company, I would like to automatically add the Company Address, City,
State,
Zip, and Phone Number onto the form in READ ONLY format.

Thanks,
Robert
Click to expand...
Click to expand...
Click to expand...
 
Robert,
I responded to your other 9:23AM post, without having seen this one.
Please see that post... with that fact in mind.
Think we're on the way to a workable solution.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Robert T said:
Al:

I checked out your combo box lookup for the ancilliary information and
that
looks very cool for the Company Info on our form. Thanks!

Do you suggest I do the same thing to display the Employee Info? However,
I
would much rather eliminate the need for a combo box and automatically
display the employee's info on the form. I don't think we don't need a
combo
box because there's only one record in tblEmployees.

Any thoughts?

Robert

Al Campagna said:
Robert,
On your Travel form, you should be using a combo box to enter an
EmployeeID, as well as a CompanyID.
Since you've captured the EmployeeID and the CompanyID, there's no
need
to capture all the other information about those two values... just
display
them (as you asked for)
On my Website below, I have a 97 and 2003 sample file called Combo
Populate Multiple Fields.
It shows how to "display" ancillary information... related to a combo
selected value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

Robert T said:
I'm building a Travel Expense database for my co-workers who travel to
various companies to do audits. This will not be a shared database,
each
employee will have their own copy of the application that stores only
their
travel expenses.

It's obviously a One to Many application with tblTravel as the Parent,
and
tblExpenses as the child table. Tracking expenses was the easy part,
however,
I'm having a hard time designing two nifty little features for the
Form.
I'm
asking the question here because I assume it will require some coding
on
the
Form.

First question:
Each employee will have a one record table called tblTraveler that
stores
their Lastname, Firstname, MI [if they have one], Office Name, Phone
Number,
and Email address. When the traveler creates a new record, I would like
the
form to automatically enter the traveler's Lastname, Firstname, MI,
Office
Name, Phone Number, and Email address from tblTraveler in READ ONLY
format.

Second question:
There will be a combo box on the form so they can look up the name of
the
company they're auditing from tblCompanies. However, once they select
the
company, I would like to automatically add the Company Address, City,
State,
Zip, and Phone Number onto the form in READ ONLY format.

Thanks,
Robert
Click to expand...
Click to expand...
Click to expand...
 
Al:

As for displaying the Employee info, the solution was easier than I thought.
I simply created a new form for Employee info [remember, there's only one
record because just one person is using the database]. I then placed that new
Employee form on the main form as a sub form. It works great.

And with your combo box tip, they'll have the ability to look up a company
and display all of the Company info on the form.

With your help, I think we have a solution. However, after posting
yesterday's thread, I decided to go with my first Tabbed Form design so I'm
going to post a new question today.

Thanks for your help,
Robert

Al Campagna said:
Robert,
I responded to your other 9:23AM post, without having seen this one.
Please see that post... with that fact in mind.
Think we're on the way to a workable solution.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Robert T said:
Al:

I checked out your combo box lookup for the ancilliary information and
that
looks very cool for the Company Info on our form. Thanks!

Do you suggest I do the same thing to display the Employee Info? However,
I
would much rather eliminate the need for a combo box and automatically
display the employee's info on the form. I don't think we don't need a
combo
box because there's only one record in tblEmployees.

Any thoughts?

Robert

Al Campagna said:
Robert,
On your Travel form, you should be using a combo box to enter an
EmployeeID, as well as a CompanyID.
Since you've captured the EmployeeID and the CompanyID, there's no
need
to capture all the other information about those two values... just
display
them (as you asked for)
On my Website below, I have a 97 and 2003 sample file called Combo
Populate Multiple Fields.
It shows how to "display" ancillary information... related to a combo
selected value.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."

I'm building a Travel Expense database for my co-workers who travel to
various companies to do audits. This will not be a shared database,
each
employee will have their own copy of the application that stores only
their
travel expenses.

It's obviously a One to Many application with tblTravel as the Parent,
and
tblExpenses as the child table. Tracking expenses was the easy part,
however,
I'm having a hard time designing two nifty little features for the
Form.
I'm
asking the question here because I assume it will require some coding
on
the
Form.

First question:
Each employee will have a one record table called tblTraveler that
stores
their Lastname, Firstname, MI [if they have one], Office Name, Phone
Number,
and Email address. When the traveler creates a new record, I would like
the
form to automatically enter the traveler's Lastname, Firstname, MI,
Office
Name, Phone Number, and Email address from tblTraveler in READ ONLY
format.

Second question:
There will be a combo box on the form so they can look up the name of
the
company they're auditing from tblCompanies. However, once they select
the
company, I would like to automatically add the Company Address, City,
State,
Zip, and Phone Number onto the form in READ ONLY format.

Thanks,
Robert
Click to expand...
Click to expand...
Click to expand...
 
Back
Top