Access Help

[samcarter1022]

Hello,

I was wondering if someone here would know if this is possible and if so how would I go about it.

I'm working on a basic database for asset tracking. I have a form that is connected to the asset tracking table, it can display the table entries, save changes, add new entries and search the entries.

What I would like to do is be able to add notes using text boxes and have the information entered there show in a notes section on the form. So basically new information is entered into the notes text boxes and added to the already existing notes that will be displayed on the form. I'm trying to not lose the integrity of historical data, just add new information.



Any help or guidence would be greatly appreciated.

Thanks,

Kerry.

[jzwp11]

Welcome to the forum!

If you can have many notes relative to the asset over time, that describes a one-to-many relationship which is best handled with a separate but related table. Assuming that you have a primary key field in your asset table, you would make a join to the notes table via that field


tblAssetNotes
-pkAssetNotesID primary key, autonumber
-fkAssetID foreign key to your asset table's primary key field
-dteNote (date of the note)
-memoNote (memo or text field to hold the text of the note)

In the relationship window you would create a one-to-many relationship between your main asset table and tblAssetNotes.

With respect to your form, you would create a new form based on tblAssetNotes and then drag that into your current form. The notes form would be a subform in your main form.

[samcarter1022]

Hi jzwp11,

Thank you

okay I'll give that a try, I'm a novice at this so it might take me some time, but I'll let you know how it goes

Kerry.

[jzwp11]

OK, let us know how it works out or if you have any additional questions.

[samcarter1022]

I will thanks

[samcarter1022]

Welcome to the forum!

If you can have many notes relative to the asset over time, that describes a one-to-many relationship which is best handled with a separate but related table. Assuming that you have a primary key field in your asset table, you would make a join to the notes table via that field


tblAssetNotes
-pkAssetNotesID primary key, autonumber
-fkAssetID foreign key to your asset table's primary key field
-dteNote (date of the note)
-memoNote (memo or text field to hold the text of the note)

In the relationship window you would create a one-to-many relationship between your main asset table and tblAssetNotes.

With respect to your form, you would create a new form based on tblAssetNotes and then drag that into your current form. The notes form would be a subform in your main form.

Hi jzwp11,

okay I created the notes table and created the relationship to the asset tracking table. I created a form for the asset notes and added it to the asset tracking form. Now when I try to run the asset tracking form I get an error



I thought at first maybe I hadn't set something, but I can't figure out what the problem is.

Kerry.

[jzwp11]

What is the data type of the primary key field in the asset table? Is it also an autonumber field? Is the datat type of the fkAssetID field in the notes must match the data type of the key field in the asset table. As a note the autonumber data type is equivalent to a long number field.

If you right click the frame of the subform (while in design view of the main form) you should be able to look at the properties which should show the relationship between the main form and the subform (I think it is designated as parent and child)

[samcarter1022]

that worked thanks

now I have to figure out how to link the tables properly. It looks like I have two seperate tables that aren't talking to each other

[jzwp11]

I have attached a database that shows the relationships and a form (frmAssets) that might help you out.

[samcarter1022]

Thank you, I'll take a look at it and try it out on mine The funny thing is every time I look at the database to get something working I think of something else to try to make it better, then I have new problems

[jzwp11]

...get something working I think of something else to try to make it better, then I have new problems

That's true of a lot of stuff, not just databases!

[samcarter1022]

true enough