Please Help Quick! Continuing Problem passing data from one form to another

[Chrstine Pearce]

I've been struggling with this problem all week, so if there's anyone who can respond quickly I'd be much obliged as I’m about ready to shed a tear or two over this!

I have set up the following. (Note I've included ONLY the field names relevant to my problem.)

A table called CAR with a field called RequestorID*.

A table called Employees with the fields EmployeeID*,
LastName, FirstName, Phone, Email, Mobile

* The RequestorID and EmployeeID are linked.

A query called EmployeesQuery that gets data from the Employees table:

EmployeeID (primary key)
mName: [LastName] & ", " & [FirstName]
Phone
Email
Mobile

A form called frmCreateCAR with the following fields, in addition to other bound
fields from the CAR table.

Name (unbound)
Phone (unbound)
Email (unbound)
Mobile (unbound)

Okay, so here's my problem. When users open the frmCreateCAR form, I have a button they can click to open a form called called frmEmployees, which has all the information from the EmployeesQuery

When the user finds the name they want, they double-click the Name field. The EmployeeID is stored and frmEmployees closes.

The user is returned to frmCreateCAR where the Name, Phone, Email & Mobile values for selected EmployeeID are populated in their respective fields

It sounds like this should be SO easy, but I can't for the life of me figure out how to do it. (Another user a week ago suggested a combo box could do this easily, but it isn’t what I want nor need in the grander scheme of things.) Any help speedily delivered will be so very, very, very appreciated!

Christine

 

[Mile-O]

A table called CAR with a field called RequestorID*.

A table called Employees with the fields EmployeeID*,
LastName, FirstName, Phone, Email, Mobile

* The RequestorID and EmployeeID are linked.

The RequestorID and EmployeeID are linked? In what way? A one-to-one?

What are the fields in CAR? Surely you would have the EmployeeID from Employees as a field in CAR.

 

[Chrstine Pearce]

Thanks for responding so quickly, Mile-o-Phile.

The link is one to many.

In the CAR table, there is a field called RequestorID. The link between the Employees table and CAR table is between EmployeeID and RequestorID. (This is because there will be more employees listed in the CAR table for different things.)

Christine

 

[Mile-O]

Re: Re: Please Help Quick! Continuing Problem passing data from one form to another

In the CAR table, there is a field called RequestorID. The link between the Employees table and CAR table is between EmployeeID and RequestorID. (This is because there will be more employees listed in the CAR table for different things.)

I'm sorry but I don't know what you mean by this - maybe it's just the lack of detail.

Is RequestorID the primary key of the CAR table? As I've asked already, what other fields are in this table?

When you say this: "The link between the Employees table and CAR table is between EmployeeID and RequestorID." then it sounds like you may have a design - or a conceptual - problem.

This: "This is because there will be more employees listed in the CAR table for different things." confirms my thinking.

 

[Chrstine Pearce]

Re: Re: Please Help Quick! Continuing Problem passing data from one form to another

Hi again.

The primary key in the CAR table is CARNumber. Other fields in the CAR table include:

CARDate
SourceCode
ReasonCode
UrgencyCode
ProblemDescription
Product
Customer
Process
Manager (employee name)
AssignedTo (employee name)
DateAssigned
ProblemResolution
ResolutionCode

I decided to link EmployeeID in the Employees table to the RequestorID in the CAR table because the Access Help file said you don't need to link fields with similar names and because, as you can see from the CAR field list, there will be other employee names in each record. Perhaps that doesn't make a difference, but I don't know and can't test it because I can't figure out how to get the data from the Employee's table/query/from into the CAR table/form.

I hope this makes sence.

 

[Mile-O]

Re: Re: Re: Please Help Quick! Continuing Problem passing data from one form to another

CARDate
SourceCode
ReasonCode
UrgencyCode
ProblemDescription
Product
Customer
Process
Manager (employee name)
AssignedTo (employee name)
DateAssigned
ProblemResolution
ResolutionCode

Ok, I'm guessing CAR is an acronym for some sort of complaint or problem reporting.

So, let's look at the fields in that.

CarNumber: - primary key (autonumber????)

Source/Reason/Urgency/Resolution ? I'm guessing there is a list to choose from so these should have a table each of their own.

Problem description - no probs there

Product - a foreign key to a products table
Customer - a foreign key to a products table

Process - ???

Manager - ?
AssignedTo - ?

With these two, is the manager the manager of the person in AssignedTo? If so, you don't need the Manager field in this table - it is not dependant upon the CARNumber.


And still no sign of that RequestorID in the CARTable...

 

[Chrstine Pearce]

Thank you for being patient with me! I will try to answer each question as you posted them.

Ok, I'm guessing CAR is an acronym for some sort of complaint or problem reporting.
---Yes, Corrective Action System.

CarNumber: - primary key (autonumber????)
---Yes, it is an autonumber

Source/Reason/Urgency/Resolution ? I'm guessing there is a list to choose from so these should have a table each of their own.
---Yes, each of these is in its own table.
Source is the user found out about the problem: from the customer, audit, etc.
Reason is undefined at the minute.
Urgency is high, medium, low options
Resolution is a variety of codes describing what was done to correct the problem

Product - a foreign key to a products table
--Yes

Customer - a foreign key to a products table
--Yes

Process - ???
--Process is a combo box with values from the Process table. It gives the user options to choose where the problem lies, i.e. in Engineering, Human Resources, Quality, Purchasing, Marketing, etc.

Manager - ?
--Manager is the name (employee name) of the manager responsible for the process.

AssignedTo - ?
--This field is where the Manager will insert the name of the employee he assigns to fix the problem.

And still no sign of that RequestorID in the CARTable...
--The RequestorID is in the CAR table.

Would it help if I sent this stuff to you? Would you mind?

Christine

 

[Mile-O]

If you can post a copy of the database (A97) leaving only the necessary stuff. Take out any confidential information and put a tiny bit of dummy data in (about three records). Zip it up and post it here.

 

[Chrstine Pearce]

Oh NOooo, Mile-o-Phile... I am running Access 2002! I don't think it is possible to save it in older versions! Am I sunk?

Christine ... now panicking

 

[Mile-O]

Oh NOooo, Mile-o-Phile... I am running Access 2002! I don't think it is possible to save it in older versions! Am I sunk?

Access 2002 - Tools -> Convert to Access 97 format

 

[Chrstine Pearce]

Me again. Attached is my file, prepared per your instructions. I can't thank you enough for taking the time to help me!

Regards,
Christine

 

[Pat Hartman]

The easiest way to show "lookup" fields is by basing your form on a query that joins the two tables. So create a query that joins the employee table with the CAR table on requestorID to EmployeeID. If you also want to show the manager's name and the assigned to emplyee's name, you'll need to add the employee table twice more to the query. Join the second instance to the ManagerID and the third instance to the AssignedToID. You can now select information for all these people from the employee table and display it along with the CAR info. As soon as the relevant field in the CAR table is filled in, the related fields will auto-populate. So, when you enter an employeeID into the RequestorID, the RequestorName, etc will populate.

Since you are selecting the same columns as many as three times from the employee table, you'll need to use alias names to avoid confusion. So for example, in the lastName field for the Requestor in the query grid put:

RequestorLastName:LastName

for the Manager:

ManagerLastName:LastName

for the AssignedTo:

AssignedToLastName:LastName

This way each occurance of the LastName field will have a unique name and you (and the form) won't get confused.

 

[Chrstine Pearce]

Mile-O-Phile out there? RE: Passing data from one form to another

Thanks for that, Pat. I wouldn't have known to do that and will come in very useful for other routines I'll have to write in the neare future. However, it doesn't really address the current problem, which is one Mile-O-Phile is working on for me when I sent him my file last Thursday. (At least I'm hoping he's still looking at it - since you wrote I can't "reply" to him to find out!) Many thanks again.

Christine

 

[Mile-O]

Here... - next time, go with Pat's