Creating a record with a foreign key inferred from other fields

[phil210293]

Hello Access Forums,



Firstly, I am very new to Access, SQL and databases so please be patient with me!

I am trying to create a database that tracks inspections of assets. So far, I have created tables for contacts, sites, assets, an inspection log, asset specific defects, and a joining table for the two (one inspection can identify multiple defects, just as one defect can be identified on multiple inspections), and forms to add new assets, sites, & contacts.

When I come to the form for a new inspection, my goal is:

- to use a combobox to select a site
- to use a second combobox to choose an asset on that site by its reference number (the reference number is only unique to each site, so may be repeated if a different site uses the same numbering system and thus is not used as a key; I have an autonumber field to give unique keys to the assets but these will be meaningless to the end user so I want to choose by site reference)
- from these two boxes, a unique asset may be identified and an inspection logged

I have been searching and googling for days trying to get this to work but I have been getting errors; currently it says "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship". Previously, I have had a message stating "The current field must match the join key in the table that serves as the 'one' side of one-to-many relationship", and at one point managed to make the form work and create inspection records but upon checking the tables, it was creating an asset in the asset table.

I need help resolving this as I have completely hit a wall with it. If you require any more information, please let me know and I will try and supply it.

Cheers,

Phil

[ranman256]

I would use a form that shows all assets in a continuous form.
2 unbound combos: cboSite, cboAsset.
afterupdate event would filter the form...

Sub cboAsset_afterupdate()
Me.filter= "[asset]='" & cboAsset & "'"
me.filterOn= true
end sub

then user can click EDIT or NEW button,
to open the record in a single record form to edit.

[phil210293]

I'll give this a try, my goal is really to have a single "New Inspection" form where the site, date, inspection team, etc. are populated from the previous entry, with the list of defects from the last inspection of that asset brought forwards to be added to or removed, as there can be a lot of assets on a site all inspected at once, so having a separate form that I have to choose the site/asset and then log the inspections from a pop-up or the like would be a bit less expeditious, but if it works it will be fine for now. I can see in my head more or less exactly how I need it to work but my inexperience with Access is holding me back, it's very frustrating!

[Micron]

You seem to be describing more than one issue. On one hand it seems like you need a subform (one site/many defects) to which you add records (deleting records isn't always a good idea). On the other hand, your issue is that you need cascading combos but the site one needs to have DISTINCT or DISTINCT ROW predicate in its rowsource. Typically, when using combos to search or filter by, you don't bind them to fields as ranman suggests. I don't know if that's the reason for your key violation error as you haven't said much about that. Then there is the "last record" thing. If your recordset is not ordered by some sort of date identifier, there is really no such thing as "last" in a table. However, if you want to carry on with defect record editing for an inspection, the form/subform design should eliminate the need for that?

[phil210293]

On one hand it seems like you need a subform (one site/many defects) to which you add records (deleting records isn't always a good idea)

I don't delete anything as I need to maintain a history of defects on an asset; I meant remove them in the sense that the defect will not be associated with the new inspection using the join table, sorry if I explained that poorly.

I don't know if that's the reason for your key violation error as you haven't said much about that

I don't know much about it, only the error message that I get given. If you tell me what you need to know, I will try and give it to you but as I mentioned I am new to Access so I am feeling my way round in the dark a bit here.

Then there is the "last record" thing. If your recordset is not ordered by some sort of date identifier, there is really no such thing as "last" in a table

The inspections have a date associated with them; I have used this to create a query that collects all the extant defects, I was planning on using this somehow? At the minute I am just trying to get a new inspection record successfully added to the table without any errors or accidental creation of new assets, one problem at a time

[Micron]

If you are having a hard time adding a record and getting a key violation error then you must be doing something like copying parts of a record to simplify data entry, but that process is also trying to create a new record with the primary key as one of the fields. You just leave the PK out of the copy because you can't duplicate it. If that doesn't shed any light on your issue, I suggest you copy, compact and and then upload a db sample to make this quicker.

[phil210293]

I think I've fixed it; I started the form again from fresh, I'm using an unbound combobox with the site names as the row source to filter a combobox bound to the asset ID, with the assetID column width 0 to hide it and then a second column with the reference displayed. Seems to work so on to the next problem I have no idea how to solve

Thanks for your help ranman256 & Micron