Updating several tables at once from a form based on a query

[RCurtin]

Hi,
I've spend ages reviewing the relationships between my tables and changing my query but cannot get this to work. I've read alot of the posts on the subject also but trying to apply the suggestions to my problem hasn't worked.

It is a database of engineering drawings. A drawing can have one or more revisions. I have written code to import the drawing and revision info into the datebase so when the drawings are actually received I need to be able to add the additional data in tblReceivedDrawings such as the tracking number, date received etc.
These are my tables:

tblDrawingRegister
DrawingNum (PK)
DrawingName

tblDrawingRevisions
DrawingNum (PK)
Rev(PK)
LatestIssueDate
ElectronicCopy
OrderNum
DrawingStatus

tblReceivedDrawings
DrawingID (PK) auto-num
DrawingNum
DrawingRev
DateReceived
ActionCode
TrackingNum

I have created a query called qSelDrawingsAndRevisions which combines tblDrawingRegister and tblDrawingRevisions.

The form is based on a query called qSelIncomingDrawings. This query joins qSelDrawingsAndRevisions and tblReceivedDrawings. It shows all the records from qSelDrawingsAndRevisions and related records from tblReceivedDrawings and is joined on the drawingNum and Rev.

Adding the extra info e.g. tracking num, date received etc about drawings that are in tblDrawingsRegister works fine. However if I want to add a completly new drawing I get an error message:
"The Microsoft Jet database engine cannot find a record in the table 'tblDrawingsRegister' with key matching field(s) 'qSelDrawingsAndRevisions.DrawingNum'

How can I get it to update the drawingNum in tblDrawingRevisions and tblDrawingsRegister at the same time?
Is it an issue with my query or relationships?
Or do I need to write an OnUpdate procedure for the DrawingNum textbox so that it created a new record in the DrawingsRegister table to correspond with the one in the DrawingsRevisions table?
I have a feeling that this might be overcomplicating it and that I just need to change the query?

Am very confused about how to approach this and would really appreciate a few pointers...

 

[llkhoutx]

"The Microsoft Jet database engine cannot find a record in the table 'tblDrawingsRegister' with key matching field(s) 'qSelDrawingsAndRevisions.DrawingNum'

You have a compound key in tblDrawingRevisions. DrawingNUm shouldn't be a PK in that table. That field should be related to tblDrawingRegister.DrawingNum with cascading updates and deletes. tblDrawingRevisions.Rev(PK) should be an autonumber.

I would use a form frmDrawingRegister with subform, sfrmDrawingRevisions (one link), with subsubform ssfrmReceivedDrawings (two links). They'll all tumble together with correct "Link Master and Link Child" properties.

 

[RCurtin]

Thanks very much for your reply llkhoutx.

Just to explain why I have a compound primary key in the tblDrawingRevisions. Both the DrawingNum and Rev are primary keys because both of those together makes each record unique. I import the drawing numbers and revision numbers from a spreadsheet and I want to make sure that dublicates are not added e.g. a drawing with the same revision is not added twice.

I don't think that I could have an Autonumber for the revision because there could be multiple drawings with a revision of 1 or 2 etc.

At the moment tblDrawingRegister and tblDrawingRevisions are related through DrawingNum. (Also tblDrawingRegister and tblReceivedDrawings are related through DrawingNum.)

Maybe I should have a field called DrawingID in tblRevisions as well as tblReceivedDrawings? Maybe this would simplify the relationships?
But that still won't let me add a new drawing and have Access add the new drawingNum to both tblDrawingRegister and tblDrawingRevisions.

I did try this before using subforms but then I found out that you could have a form based on a query so I changed it. One reason was that the relationship between tblDrawingRevisions and tblReceivedDrawings is a one-to-one relationship. Also I had subforms within subforms on my form and I thought that using a query was a much neater way to do it. But maybe subforms is the way to go? Or is writing code to create the new record in tblDrawingRegister a better idea?

 

[RCurtin]

Screenshots of relationships

Hi,
Thought it might make it easier if I uploaded a screen shop of the relationships and the query that my form is based on.

:

"The Microsoft Jet database engine cannot find a record in the table 'tblDrawingsRegister' with key matching field(s) 'qSelDrawingsAndRevisions.DrawingNum'

I just read that the error that I'm getting means that I have a foreign key which does not exist as a Primary key (or the foreign key is Null). But as you can see from the attachments DrawingNum is related in both tables. What am I missing here? I just feel that this should work without writing code to insert a record into one of the tables.

I would really appreciate any tips here..

 

[neileg]

You can have drawing number and revision number as a compund index set to No Duplicates, and use an autonumber as a PK.

 

[RCurtin]

Requery when form is based on a query that is based on another query!

Thanks very much for that Neil. I've changed that to a compound index and tested to make sure it will not add dublicates. That works perfectly. I've added a new primary key to tblDrawingRevisions:
DrawingID (autonumber primary key)
Its much better now because I'm not repeating DrawingNum and Rev in the 2 tables.

I still have a problem though but I understand why. Usually if I go to add the received drawing info there is an existing record in tblDrawingRegister and I can just go ahead and add the dateReceived, trackingNum etc to tblReceivedDrawings.

However if I want to add this information for a drawing that does not yet exist I get an error obviously. I've written the code in the change event of the DrawingName textbox so that when a new drawing number and title are added this is added to tblDrawingRegister.

sqlDrawingsRegister = "SELECT * FROM tblDrawingsRegister"
  rsAddDrawing.Open sqlDrawingsRegister, connection, adOpenForwardOnly, adLockOptimistic, adCmdText
    
        With rsAddDrawing
            .AddNew
            !DrawingNum = Me.txtDrawingNum
            !DrawingName = Me.txtDrawingName
            .Update
        End With
    
    rsAddDrawing.Close
    MsgBox "new drawing added.", vbInformation
    Me.Requery

That works - I checked that the drawing was in tblDrawingRegister. (I also added the necessary info to tblDrawingRevisions) However I still get an error saying that there is no matching record for DrawingNum in tblDrawingRegister.

I think that this may be because the query that the form is based on is itself based on a query:
The form is based on a query called IncomingDrawings.
IncomingDrawings joins qSelDrawingsAndRevisions (tblDrawingRegister + tblDrawingRevisions) and tblReceivedDrawings.

So the new records is added to tblDrawingRegister and tblDrawingRevisions but how do I get the form to requery qSelDrawingsAndRevisions? That is what I think the problem is but maybe I'm not on the right track with that??

 

[neileg]

It would be easier to use a form/subform setup. If there is no record in the subform, you are presented with a blank record which you can complete. The subform wizard will ensure the linking fields are handled correctly.

 

[RCurtin]

I really would prefer not to use subforms because I'll need lots of them if I do it that way.

The master form would have to be the based on tblDrawingRegister. That would have the tblDrawingRevisions which in turn would have the tblReceivedDrawings subform. Then I'll need other subforms for tblInTransmittals (each drawing can have more than one transmittal) and another for the tblShippingDetails.

Is it possible to do it any other way??

 

[neileg]

If you used a series of tabs on your form, you can manage the number of subforms you need in a friendly and aesthetic way.

 

[yeahyeahyeah]

Hi Neil,

I am having almost the exact same issue as our friend Mr. Curtain but I guess I'm still too much of a newbie to figure out how to apply his solution to my problem. I'm really at a deadend here so I'm hoping you can help.

I'm getting the exact same error message. I have a form that is sourced by a query called "qryUIRInitialInput" (UIR=Unusual Incident Report. This is the form in which the Initial Report is entered).

It appears--like above--the issue is probably related to referential integrity
problems between the two tables that feed "qryUIRInitialInput" ("Table 1" and "Arc Programs") Both tables contain the field "program" which is in both
tables and is the primary key in "Arc Programs". The two tables are
joined by the 'program' field with a one (Arc Programs) to many(Table
1) relationship. In the relationship control box the "enforce referential
integrity" is checked.


I'm attaching images of the relationships and the relationship control box to illustrate.

This issue has me at a total dead end with a very big and lengthy project so any help you can provide would be tremendously appreciated.

Many, many thanks,
David

 

[neileg]

David, you've not really given enough info to tell exactly what is happening here.

However, I don't see why you need a query to join these two tables. The form can be based on the data in Table1 alone, and use a combo box for the ArcPrograms data.

I don't use cascade update only cascade delete. Try turning this off and see what happens.

While I'm here, it is better to exclude spaces from your object names, ArcPrograms instead of Arc Programs. It is even better to adopt a naming convention that identifies the object type, tblArcPrograms.

 

[RCurtin]

Thanks for all the help - I've just changed my form to use tabs and subforms - it works well and looks better now!

What I did in the end was set up a main form based on my query qSelDrawingsAndRevisions. I put a tab control on the form and on the first page of the tab I put a subform frmDrawingRevisions. The linking field is DrawingNum - the subform setup makes sure this is updated in both tblDrawingRegister and in tblDrawingRevisions.

On the second tab page I put a subform based on tblReceivedDrawings. This links with the DrawingID field of the master form (SelDrawingsAndRevisions) This was why I used this query as the source of my main form rather than tblDrawingRevisions (which does not contain DrawingID )

This is how I understand this:
I had a form based on a query that has the same field in 2 different tables. This was fine when I was just viewing records and making changes that would cause the table on the many side of the relationship to be updated. But if I wanted to be able to update the table on the one side of the relationship it is not really possible to do this and it is better to use subforms.

Is this correct?

Hope this helps someone else. Thanks again for your help llkhoutx and Neil

 

[yeahyeahyeah]

Hello Neil,

Well even thouigh I did not do a very good job of describing the problem, I used the feedback you offered and it worked! I eliminated the underlying query in favor of working with the table and combo boxes.

Thanks so much.