Just not getting it??

[Stranger]

My current setup is:

tblhardware.hardwareid 1 to many tblemployeehardware.hardwareid
tblemployees.employeeid 1 to many tblemployeehardware.employeeid

tblsites.siteid 1 to many tblsitelocation.siteid
tbllocationdepartment.locationdepartmentid 1 to many tblsitelocation.locationdepartmentid
tblsitelocation.sitelocationid 1 to many tblemployees.sitelocationid

I hope that is clear.

Now, what I have tried is creating the main form from the employees table
Created the subform using tblhardware.servicetag, tblsites.site, tbllocationdepartment.locationdepartment

I made servicetab, site, locationdepartment a combo box

I can see all hardware assigned to each employee as I cycle throuhg.

When I try to change an assigned hardware by picking the service tag from the list, it does not let me. I believe it thingks I am trying to add the service tag to the table a second time which is not allowed. No duplicate service tags.

How do I do this the correct way?

I want to be able to change an employees assigned hardware which may or may not involve also changing the site and location.

I also need to be able to assign an employee to a new piece of hardware. I think that covers what I have left to do.

thanks for any help.

Jack

 

[John Vinson]

My current setup is:

tblhardware.hardwareid 1 to many tblemployeehardware.hardwareid
tblemployees.employeeid 1 to many tblemployeehardware.employeeid

tblsites.siteid 1 to many tblsitelocation.siteid
tbllocationdepartment.locationdepartmentid 1 to many tblsitelocation.locationdepartmentid
tblsitelocation.sitelocationid 1 to many tblemployees.sitelocationid

Is there any connection between Hardware and Sites?

I hope that is clear.

Now, what I have tried is creating the main form from the employees table

That's good...

Created the subform using tblhardware.servicetag, tblsites.site, tbllocationdepartment.locationdepartment

But that's NOT.

You want to base the Subform on tblemployeehardware, using EmployeeID
as the master/child link field. On this Subform you can have a combo
box bound to tblemployeehardware.hardwareid, using tblhardware as its
RowSource.

I made servicetab, site, locationdepartment a combo box

I can see all hardware assigned to each employee as I cycle throuhg.

I'm not sure how, since your form does not include the table
(tblemployeehardware) which contains the information about what
hardware is assigned to which employee!

When I try to change an assigned hardware by picking the service tag from the list, it does not let me. I believe it thingks I am trying to add the service tag to the table a second time which is not allowed. No duplicate service tags.

Exactly. If you base the Subform on tblhardware, then any attempt to
enter data on the subform will - for that very reason - be an attempt
to edit the hardware table.

How do I do this the correct way?

I want to be able to change an employees assigned hardware which may or may not involve also changing the site and location.

To do so you would add or delete a record to tblemployeehardware, or
change the HardwareID in that table. The Subform will let you do this.

I do not understand how the site and location relate to the hardware.
Is the site a property of the Employee? of the Hardware? of both?


John W. Vinson[MVP]
(no longer chatting for now)

 

[Stranger]

Hi.

The only connection between hardare and site is in the tblemployeehardware.
EmployeeID, HardwareID, SiteLocationID.

How the site and location relate to the hardware.
Is the site a property of the Employee? of the Hardware? of both?

This is where I had some confusion. Hardware would have a site/location but
not necassarily be assigned to an employee. What I did was put Not Assigned
in the employee first and last name fields.

You want to base the Subform on tblemployeehardware, using EmployeeID
as the master/child link field. On this Subform you can have a combo
box bound to tblemployeehardware.hardwareid, using tblhardware as its
RowSource.

This is the other part I was confused on. I had also tried making the forms
uisng hte link tables but that was just showing the numbers in the boxes. I
was using hte wizard so manybe that is where the problem is.

The hardwareid is the auto number field in tblhardware. I would like to be
able to assign hardware to an employee by using hte service tag/serial
number. Since that is unique.

Thanks for your help.

 

[John Vinson]

Hi.

The only connection between hardare and site is in the tblemployeehardware.
EmployeeID, HardwareID, SiteLocationID.


This is where I had some confusion. Hardware would have a site/location but
not necassarily be assigned to an employee. What I did was put Not Assigned
in the employee first and last name fields.

ok... so if you want to see that hardware you'ld have to bring up the
mainform record for Mr. Not Assigned? Is that reasonable in the
workflow?

This is the other part I was confused on. I had also tried making the forms
uisng hte link tables but that was just showing the numbers in the boxes. I
was using hte wizard so manybe that is where the problem is.

I suspect you are yet another victim of the abominable Lookup Wizard.
The link table DOES contain ID numbers. That's ABSOLUTELY CORRECT.

What you need to do on the Subform is use a Combo Box bound to the
link table ID fields. The combo box's Row Source would be tblHardware,
or tblLocations, as appropriate; its bound column would be the numeric
ID; but the first visible field of the combo box would be the hardware
serial number, or the location name, or whatever is meaningful to the
user.

The hardwareid is the auto number field in tblhardware. I would like to be
able to assign hardware to an employee by using hte service tag/serial
number. Since that is unique.

If the serial number is unique, stable, and reasonably short, you
might want to consider using that field as the Primary Key of the
hardware table. It is *NOT* necessary to have an Autonumber as a
primary key, though Access might give you that impression! This will
require some changes to your table structure (for example you'll need
to put a serial number field of the same size and datatype in your
tblemployeehardware in place of the long integer HardwareID) but may
simplify things, since the tables will now show the actual serial
number without needing a lookup.

John W. Vinson[MVP]
(no longer chatting for now)

 

[Stranger]

I've been thinking about this again. Maybe the site and location should be
tied to the hardware and then the employee to the hardware. Would that make
more since?

 

[John Vinson]

I've been thinking about this again. Maybe the site and location should be
tied to the hardware and then the employee to the hardware. Would that make
more since?

It very well might... but that's a business rule decision, not a
database design decision. Since I don't have any idea even what kind
of "hardware" you're dealing with, I simply don't know.

John W. Vinson[MVP]
(no longer chatting for now)

 

[Stranger]

Hi!! I think I got it!!!

thank you for all of your help.

 

[Stranger]

I have one more question. How would I go about removing an employee from an
assigned hardware?

 

[John Vinson]

I have one more question. How would I go about removing an employee from an
assigned hardware?

Delete the record in the linking table.

John W. Vinson[MVP]
(no longer chatting for now)