Trying to populate data using a Macro

  • Thread starter Thread starter TheDrescher
  • Start date Start date
T

TheDrescher

On my main form, I have two controls (ManagerSelect and TenureBox) that
populate conditionally based on the value selected for a third control
(EmpName). I've written code using the DLookup function to select the
appropriate data:
=DLookUp("[Manager]","[EMP]","ID=" &[EmpName])
=DLookUp("[AdjServiceDate]","[EMP]","ID=" &[EmpName])

To allow the fields in the control source to populate correctly, I can't
store this code in the control source box (where I've tested it and it
works). I decided to enter the code in a macro and set it as the On Got
Focus Event for the controls in question. When I do this, I get an error
that says:

The object doesn't contain the Automation object '[[the data that should
populate]]'.
You tried to run a Visual Basic procedure to set a property or method for an
object. HOwever, the component doesn't make the property or method available
for Automation operations.

How would I correct this? Thanks!
 
It seems like you (and Access) are working a lot harder than you need to...

Depending on how your tables are set up (provide a description if you want
more specific suggestions), you could join your EmployeeName table to the
Manager table in a query and use that query to feed your form.

By the way, based on what you've shown in your DLookUp() expressions, you
are using [EmpName] (?Employee's Name) as an ID field. If this is accurate,
be prepared for some surprising results. What happens when you have two
employees with the same name?

Also, if [EmpName] is a field that holds your employee's full name, how do
you generate a list of employees, sorted alphabetically BY LAST NAME?!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Jeff,

Right now, things like the employee full name, manager, tenure date,
employee ID# and other information are stored in a linked table (EMP) that is
controlled by our attendance department. I do not have access to edit this
table, but it is the first one updated when there are any manager changes
(which happens frequently).

In my DLookup expressions I'm using the control EmpName as the ID. I
basically wanted it to say "based on what the user selects for the employee
name, automatically populate their manager and tenure date for that person
from the EMP table."

So basically, the Full Name, Manager, and AdjServiceDate are all fields in
the EMP table and I was using the control EmpName from the main form as an
identifier. This was working until I realized that the DLookup was in the
control source for the Manager and Tenure Date controls and I wanted this
information to save in the records table (the control source for the rest of
the inputs on the main form)

I have a very "excel" mindset going into this because that's the program
I've been using most for the past few years, this new business in access kind
of got thrown at me last minute and I'm trying to work with what skill I have.

Jeff Boyce said:
It seems like you (and Access) are working a lot harder than you need to...

Depending on how your tables are set up (provide a description if you want
more specific suggestions), you could join your EmployeeName table to the
Manager table in a query and use that query to feed your form.

By the way, based on what you've shown in your DLookUp() expressions, you
are using [EmpName] (?Employee's Name) as an ID field. If this is accurate,
be prepared for some surprising results. What happens when you have two
employees with the same name?

Also, if [EmpName] is a field that holds your employee's full name, how do
you generate a list of employees, sorted alphabetically BY LAST NAME?!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

TheDrescher said:
On my main form, I have two controls (ManagerSelect and TenureBox) that
populate conditionally based on the value selected for a third control
(EmpName). I've written code using the DLookup function to select the
appropriate data:
=DLookUp("[Manager]","[EMP]","ID=" &[EmpName])
=DLookUp("[AdjServiceDate]","[EMP]","ID=" &[EmpName])

To allow the fields in the control source to populate correctly, I can't
store this code in the control source box (where I've tested it and it
works). I decided to enter the code in a macro and set it as the On Got
Focus Event for the controls in question. When I do this, I get an error
that says:

The object doesn't contain the Automation object '[[the data that should
populate]]'.
You tried to run a Visual Basic procedure to set a property or method for
an
object. HOwever, the component doesn't make the property or method
available
for Automation operations.

How would I correct this? Thanks!


.
 
OK, lets see if I understand...

You have a single table with the facts you are trying to see.

Here's an approach that will let you see the Manager, etc, for a selected
employee:

* create a query against the table. Include the fields you'll want to
see, sort on, or select on.
* create a form based on the query. Add controls for those fields you
want to see (e.g., Manager)
* add an unbound combobox in the header. Base that combobox on a query
that gets the ID field and the [EmpName] field from your table.
* modify your (first) query -- in the Selection Criterion under the ID
field (your table should have an ID field that is NOT the employee name),
put something like (untested, use your form's name):

Forms!YourFormName!YourComboboxControlName

* Now, back to the form. Open it in design view and add an event
procedure for the combobox's AfterUpdate event. That procedure only needs:

Me.Requery

Here's how it works:

* you open the form.
* there's nothing in the combobox, so the query that feeds the form looks
up the employee with an ID = ...!!! nothing (i.e., none of the employeeds)
* you select an employee from the combobox
* the AfterUpdate event fires, causing the form to requery its source (the
query)
* this time the query finds an ID for the selected employee in the
combobox, so it "gets" that record
* the form loads up the record for that employee

.... and because you included the Manager, etc. on the form, you see the
selected employee's Manager, etc.

Is this what you were trying to accomplish?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.



TheDrescher said:
Jeff,

Right now, things like the employee full name, manager, tenure date,
employee ID# and other information are stored in a linked table (EMP) that
is
controlled by our attendance department. I do not have access to edit
this
table, but it is the first one updated when there are any manager changes
(which happens frequently).

In my DLookup expressions I'm using the control EmpName as the ID. I
basically wanted it to say "based on what the user selects for the
employee
name, automatically populate their manager and tenure date for that person
from the EMP table."

So basically, the Full Name, Manager, and AdjServiceDate are all fields
in
the EMP table and I was using the control EmpName from the main form as an
identifier. This was working until I realized that the DLookup was in the
control source for the Manager and Tenure Date controls and I wanted this
information to save in the records table (the control source for the rest
of
the inputs on the main form)

I have a very "excel" mindset going into this because that's the program
I've been using most for the past few years, this new business in access
kind
of got thrown at me last minute and I'm trying to work with what skill I
have.

Jeff Boyce said:
It seems like you (and Access) are working a lot harder than you need
to...

Depending on how your tables are set up (provide a description if you
want
more specific suggestions), you could join your EmployeeName table to the
Manager table in a query and use that query to feed your form.

By the way, based on what you've shown in your DLookUp() expressions, you
are using [EmpName] (?Employee's Name) as an ID field. If this is
accurate,
be prepared for some surprising results. What happens when you have two
employees with the same name?

Also, if [EmpName] is a field that holds your employee's full name, how
do
you generate a list of employees, sorted alphabetically BY LAST NAME?!

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

TheDrescher said:
On my main form, I have two controls (ManagerSelect and TenureBox) that
populate conditionally based on the value selected for a third control
(EmpName). I've written code using the DLookup function to select the
appropriate data:
=DLookUp("[Manager]","[EMP]","ID=" &[EmpName])
=DLookUp("[AdjServiceDate]","[EMP]","ID=" &[EmpName])

To allow the fields in the control source to populate correctly, I
can't
store this code in the control source box (where I've tested it and it
works). I decided to enter the code in a macro and set it as the On
Got
Focus Event for the controls in question. When I do this, I get an
error
that says:

The object doesn't contain the Automation object '[[the data that
should
populate]]'.
You tried to run a Visual Basic procedure to set a property or method
for
an
object. HOwever, the component doesn't make the property or method
available
for Automation operations.

How would I correct this? Thanks!


.
 
Back
Top