Another linking/Join Question

[Rick]

I Have a database that has the following fields:
Entity (Dropdwnbx with 3 selections)
Department Number
Department Name
Department Head
Job Position
Position Qualifier

Here is my delimma, PLEASE HELP!!
I would like the user who may be updating the record to select one of the 3 entities from the "Entity" dropdownbx.

Then based on the "Entity" Have the associated "Department numbers populate the "Department Number" combo box.

Now the user will select a Department number from the available list, and have the "Department Name" Field , "Department Head" field, and "Job position" field automatically populate.

I could really use help with this one guys, I am in a time crunch but do not have the knowledge to put it all together. Again, The part I am struggling with is the "How to Autopopulate the other fields based on the user selecting the department number.

 

[WayneRyan]

Rick,

In the AfterUpdate event for your combobox put
the following:

Me.DepartmentCombo.Rowsource =
"Select * " & _
"From Departments " & _
"Where Entity = '" & Me.EntityCombobox & "'"

Me.DepartmentCombo.Requery
Me.DepartmentCombo.SetFocus

Then in the AfterUpdate for the Department
take on the next one.

hth,
Wayne

 

[Rick]

Thanks Wayne

Do you think maybe I could Zip up what I have and have you take a look at it? I appreciate the quick feedback,This is driving me crazy!!

 

[WayneRyan]

Rick,

Would love to but ...

It will be quarantined by corporate e-mail and I'm outta here
for the day.

What part is giving you the problem?

On the properties box for your entity combo (right-click and
select properties) go to the event tab. Select AfterUpdate,
Select EventProcedure, then on the right margin click on the
three dots. That will bring up the code window.

Then you can enter the example code, substituting your
field and table names. Although, if this is all new to you
then you are in for a battle.

Keep me posted.
Wayne

 

[sambo]

I'll take a crack if you want to Zip it and post it.

 

[Rick]

Thanks Guys

Here is a copy of what i have so far. Please let me know what you think and if what i need to accomplish can be done. I am struggling to learn this so please be patient and use little words : ) Thanks again

 

[sambo]

Hey Rick,

I cleaned it up just a tad for you. I hope you don't mind.
Check out the new table structure. Notice now, that there are 1 to Many Relationships set up. This is called Normalization. There is definitely more to be done, but this is a good start.

Pay close attention to tblJob. You need to take your table (tblDept), and add the Job Related Records to the newly created tblJob.

Also, when you base a form's RecordSource On a ComboBox Field Value, you should not have that combo box as part of the RecordSource. It should be unbound. Take a look at the new structure of the form also. The combo boxes that hold the parameters for your form are now in the Form Header Section. The actual Jobs that a user will be sifting through are set in the Detail Section.

Next, take a look at the revisions made to your VBA Code Window behind your form. It is important to insert Me.Requery after updating the value in the parameter Field. In your case this field is the DeptNumber Field.

Finally, take a look at the newly created recordsource used in the form. You can see that it is no longer a table, but rather, a query based on 3 tables. This will allow you to use information from 3 tables in your form (much more powerful).

Let me know if you need more help...

 

[Rick]

Sambo, I hate to ask but, Could you guide me further with this project? I will work on what is there so far but i would appreciate your help with this project. Thanks in advance.

 

[sambo]

No problem..
Did you get the fields of tblJob figured out. You will want to take the majority of your fields from your original table (tblDept) and put them into tblJob. This will allow you to have a Normalized structure as opposed to the <i>flat</i> structure you originally had.

Also, you need to define whether this form will be used to Add Data, Edit Data, or simply View Data, or even a combination of all 3. This will determine how involved you can get with your recordsource.

 

[Rick]

Sambo, I will work on moving the fields into tbljob. The main form will be used for data entry,viewing, and editing the records. Thanks

 

[Rick]

Sambo, I am almost where i need to be with this project. I can't seem to get the relationships exactly right. I cannot get the "DepartmentHead" to show up as i am scrolling through the job positions(??) Can you please take a look at what I have so far and give me some ideas? I need this D/B done this week and i am stuck. Thanks I am attaching the latest D/B

 

[sambo]

Rick..
What have you done to the beautiful structure we had begun to create?? It breaks my heart to see you structuring it the way you have chosen. I see you have totally extinguished the primary key from each table. Not to mention you added a bazillion comment fields when this could have easily been done by building a simple child comment table and linking it to tblJobs (which by the way seems to have vanished).

Oh well, no use crying over spilt milk. You are getting the #Name argument in a number of fields because you have left them bound to table fields that no longer exits. You will need to delete these controls from your form, or add the fields to tblEmployee.

The reason there is nothing showing up in your Department Head field is fairly simple. There is simply no data in your Department Head Field of your tblEmployee. And since you base your forms RecordSource on tblEmployee, instead of on a query involving both tables (as was suggested) the form field is simply looking to tblEmployee and seeing nothing in the field. Thus, it displays nothing. Remember, computers are stupid, they only do what you tell them to do. Nothing more nothing less, every single time.

As for your relationships. Oy Veh... A left join on two non-primary key fields??? That won't get you anywhere.

Your one saving grace.. Your form seems to be doing what you want it to do. If that is good enough for you, then you can go ahead and populate that DepartmentHead field in tblEmployees and be done. Else, you could once again go back to the drawing board w/ Normalization.

Good Luck..

 

[Rick]

Sambo, I still have the D/B we started, I am in a bit of a crunch here trying to get this thing done. I am trying to learn as I go but sometimes the situation will not permit that. I tried to create a Query with both tables but I could not add to the recordset(??) Can you please tell me how to go about creating the query where I could add data to the form? Thanks Sambo and sorry I frustrated you, I am doing to myself too. Rick

 

[sambo]

No Problemo Mi Amigo..
As long as you are willing to learn you can always improve.

Go to design view of your form.
Select the form object.
In the properties window, go to the data tab and select the record source option.
Click the elipsus (...)
It will ask you if you want to make a query of your table.
Click OK (Yes).
When the query design IDE pops up, add both tables to the top of the IDE.
Now drag all of the fields you want for your form into the design grid below the tables.

I'm really not sure if your table structure will work for adding data if you use a multi table query. You will just have to trial and error it.

I will say this. You really, really, really need to re-insert Primary Keys (Autonumber format) into both tables. Then you need to add a foreign key to your child table. I will also say this. Righ now you have it set up so that tblEmployee is your parent table. This is incorrect. tblDepartment should be your parent table. This will allow you to drop many employees down from any 1 department. This is called one to many, and it is exactly how departments relate to employees. 1 Department to Many Employees.

As I said before, it seems to work (kind of), but it could be a lot better. And in order to enter data, a re-structure is a must.

Good Luck..

 

[Rick]

Thanks Sambo, Think we can give it the ol' college try again??
I will not touch any of your corrections.......Promise : )

 

[sambo]

Rick..

tblDEPTHEAD and tblEMPLOYEE are now obsolete. You had a bunch of employees in tblEMPLOYEE that were not associated with any DepartmentNumber in tblDEPTHEAD, consequently you lost a lot of data. Now you're down to something like 475 total employees instead of 900+.

The important thing is that you table structure is now set up. The form is linked to a 2 table query.

Check the relationships. Now, an employee cannot exist without first linking that employee to a Department. There was no need to keep the Entity and Home Department fields in tblEmp, since this data comes from tblDept. This is called Normalizing.

Your whole category Yes/No scheme X 22 is still horrible (no offense). But the form now works. The boxes that show up empty on the form are not linked to anything because there are no phone fields in either table. You could add phone fields if you wanted.

Good Luck..

 

[Rick]

Sambo, Can I ask you one more question? Yes this is what "The Boss" wants to see but, Can you tell me how I can set it up to be able to enter the department number and have that departments recordset populate? This is to save a Department head from having to scroll through all other departments records to come to their records. Maybe a Form with one field item called "Department Number" When the user types in their Department number then it would open the main form with just that departments record(??) What do you think? is there a more efficiant way? Thanks

 

[sambo]

Rick,
I changed the RecordSource to Have the criteria DeptNumber = Forms!MainForm!DeptNumber.
And then it was necessary to Requery the Form in the event that DeptNumber had been updated.

 

[sambo]

Is everything peachy??

 

[Rick]

Sambo, What can i say, It's what he wanted to see. I of course see updating issues but will deal with them as they come. I can't thank you enough for your time and effort along with you patients. I guess I need to read up on Normilzation as well as Bound and unbound Fields. I thought I had tried linking the two tables the way you did but, i see you had some fields that were unbound, I need to look into that more. Thanks again for everything. Rick