Table Update issue

[cstacy]

Try this approach

Table Quotes

QuoteNumber(PK)
Customer

Table Revisions

RevisionID(PK)
QuoteNumber(FK)
RevisionNumber (For this quote)

You should be able to link these tables using a join which shows all Quotes and any revisions that belong to a quote. I imagine you may have initial quotes with no revisions.


Base your form on a query that shows the linked data.

Hope this helps

Thank you Rabbie, but I can't help to think I am doing something very wrong with my relationships.

I created a new database with just the feilds mentioned. On the Table Quotes I created QuoteNumber as a number type field, and customer as text. I then created a new table called Table Revisions. Field Revisions ID as Autonumber, and Field QuoteNumber as type number. I left RevisionNumber as text.

Under relationships I joined both QuoteNumber from Table Quotes, and Field Quotenumber from Table Revisions Enforcing Integrity, and cascade both update and delete. Under Join Type I chose Option 3 which was Include ALL records from 'Table Revisions' and only those records from 'Table Quotes' where the joined fields are equal.

I created a Query using all fields from both tables and created a form based on this query. When I use QuoteNumber and Customer from Table Quotes, and RevisionNumber from Table Revisions I am unable to add new Revisions Still. I get the error that doing this will create duplicate enties in the primary key, index or relationship.

I will love you all forever if you can teach this old dog how to do this.

 

[Rabbie]

I still think you have your relationships the wrong way round. IMO you should select all Quotes and the revisions(if any) that belong to them. This will allow you to add revisions to a Quote even if it doesn't have any at present.

 

[cstacy]

At first I thought this was going to be be pretty easy for me to do, but this one issue has really got me stuck.

I've attached a very basic database based on just the five fields mentioned above in two tables. The Form I am using only has three fields on it. Two from the first table and one from the second.

I can create the first record fine, but when I go to create another Quote number 1 with a different revision I still come up with an error. To me I have exhausted all the relationship possibilities.

I'm pretty sure once I can get my head around this basic database I can apply the same method to my larger one as this seems to be the main issue I'm having.

Sorry to be a pain, but I really need to get this working both for work, but more importantly for my own sanity.

Thank you again to everyone who has helped.

 

[cstacy]

Hi everyone. I've still been trying to figure out this issue I have and have come across another way to do it.

Since I can't seem to get the relationships to work like I want I am looking at keeping one table. Now I have been looking at ways to summarise this information on a form and stumbled across ideas about "Filter on Selection".

I believe this would allow me to do what I want, but I can't figure out the coding for it. Anyone know how to do it?

I would also need to filter multiple entries in the combo box.

So I would have a form with a subform. I would like to have a combi box on the form that will determine which information will display in the subform. Also I heard their is a way to filter multiple entries from the combi box?

Again any help would be great.

Thank you again.