Asset Database problem

[Mabuya]

Form Problem because of relations ?

I made a database to register assets of the IT department, I've made the relationships to be one to many and so that 1 User can contain more Hardware...

Now I've made a form which shows the hardware device and in a subform the user, I want to make it possible to change the user by searching the user on username using a combo box, which doenst work.
I also cannot make new entries to the database trough that form, it states that i need a related record in the table users when i skip from the main hardware form...

I think I need to adjust my relations to make it possible, I recall using somekind of 'linking table' back in my schooldays which could hold the solution.

Can anyone please help?

thx

 

[jzwp22]

I'm not real clear on your structure. Does it look something like this:

tblUsers
-pkUserID primary key, autonumber
-txtUserName

tblAssets
-pkAssetID primary key, autonumber
-txtAssetName


tblUserAssets
-pkUserAssetID primary key, autonumber
-fkUserID foreign key to tblUsers
-fkAssetID foreign key to tblAssets


When doing this in a form, the main form would be bound to tblUser and the subform would be based on tblUserAssets. I would use a combo box on that form that is tied to tblAssets.

Hopefully this helps.

 

[Mabuya]

Hello and thanks for the response


My table structure at the moment is:

tblUsers
ID - autonumber, primary key, 1 to Assets-Users ID
Username - text
etc
etc

tblAssets
ID - autonumber, primary key
Users ID, number, foreign key to tblUsers
Assetname - text
etc
etc

----

What I wanted to acomplish is the ability to change the user on a piece of hardware without having to delete the hardware and readding it. I tried to do this by basing my table on the tblAsset, putting the tblUser in a subform and then making the option to swap the user in the subform, which i can't seem to get done.

Currently I'm trying to make a combobox based on a Query that gets the ID, Username, First Name and Last name from tblUsers and saves the ID to the User ID in the table Asset, that should work (in my theory) but I also want to swap the user actively in the subform when it works.

I'm not an access guru (tho an access course is in the planning) so most I learn is from trial and error and trough google

Any help is wildly apreciated..

 

[neileg]

You've got your form and subform the wrong way round for this to work. The subfor should be the many side data and the main form is the one side.

In your case I suspect you need to ditch the subform and use a combo box to select your user.

 

[Simon_MT]

Or your create a dialog form that first allows you to request the User and then open the Asset form based on that information.

Simon

 

[Mabuya]

For now it's fixed, I say for now because I'll keep looking for a better solution

@neileg
I wanted to maintain the one to many relationship on the Users - Assets table because 1 user can contain more assets and not the other way around.

I've added a Combobox with as the control source a Query that selects the ID, Username, Lastname and First name fields from table users with as bound collum the ID, I made it save the ID value to the User ID foreign key field in the Asset table, now it's possible to change the user trough the Combobox, for some reason without any tricks the subform changes actively with the changes I make in the combobox, I've disabled edits in the subform so it can still be in the form for viewing more details.

For hardware that is added to the database without a user I made a dummy record.

It's not the perfect solution but for now it will do, the manager likes to see some results by now .

Thanx all

 

[neileg]

@neileg
I wanted to maintain the one to many relationship on the Users - Assets table because 1 user can contain more assets and not the other way around.

I didn't say the relationship was the wrong way round, I said your form/subform was the wrong way round. Your relationship is correct. Your user data should be in the form and the asset data in the subform. If you make the subform continuous, you'll have a list of the assets linked to the user. If you make it single form, you can scroll through the assets linked to the user.

If you want the form to be about the asset and then select the related user, then you can't have the user data in the subform because of referential integrity demanding a user record first. That's why I suggested a combo box. You could turn off referential integrity. Normally you try to prevent 'orphaned' records on the many side of the relationship. There are occasions, however, where you want to allow this. If you really wany to enter assets with no owner, then turn it off.