Sub records?

[mounty76]

HI All, thanks for all the help, much appreciated. Am making progress!!

Three things.....

1. Modify the Master/child links to fix the name of the child link to be just POID - I'm assuming you mean delete the relationship in the relationship window then remake that link? It was just POID in there so I think I'm barking up the wrong tree here?

2. Referential integrity is not enforced - The checkbox is not highlighted? But with the error message on there and default set to NIL will this kind of ensure the same thing?

3. Now when trying to add a record in the POdetail I get an error message as below. When I enter a description of a product it puts the PODetail ID as zero and then on the next line down it puts in the POID from the main form. I'm guessing this is because of an issue with 1. above....

Thanks for your help and patience!!

 

[mounty76]

Attached is sample DB of the issues I'm having now, it is almost working!

 

[mounty76]

1. No. I said to modify the links. Because of the extraneous table in the Recordsource, the child name is invalid.
2. There is no substitute for RI. Why would you NOT want to have the database engine enforce something that you would otherwise have to write code to enforce?
3. You did NOT remove the extraneous table. The master/child links are NOT set at all now. In the earlier version, the name of the child was wrong.

I can fix it for you but that will not help you to understand the problem. You will keep making the same mistake.

Hi Pat, thanks for this......I'm completely lost now......I'm happy to make the changes but if you can explain what I need to do then I'll be able to follow it through and hopefully better understand....thank you in advance

 

[mounty76]

Hi Pat, thanks for this......I'm completely lost now......I'm happy to make the changes but if you can explain what I need to do then I'll be able to follow it through and hopefully better understand....thank you in advance

Sorry I don't know what this means "extraneous table in the Recordsource"?

 

[mounty76]

HI Pat, thanks for this. It was getting very messy so deleted them and started again from scratch taking into account everything said. I now have it working properly, the FK is automatically done because the parent/child relationship is correctly setup, RI is enforced and all is good, thank you.

 

[mounty76]

Yes I removed this as well, all working good now, thanks again.

 

[mounty76]

You're welcome. Did you also remove the 0 as the default for the FK and add the required constraint?

On other thing....haha!!

I want to be able to 'clone' an order including the line items, obviously I'll have to make an append query but how would I do this and the PODetails FK recognise the new Primary Key for the new PO?

 

[mounty76]

 

[mounty76]

Hi Pat, thank you so much, will try this out over the next couple of days...most likely with some more questions! lol. Thanks again and I'll keep you posted how it goes

 

[mounty76]

Hi Pat,

Having some issues.......I don't get any error messages, but it also doesn't clone the record...here is the modified code:

FYI:

tblPO - The primary key is POID (autonumber)
tblDetail - The FK is called DetailID

So it should copy the mainform details into tblPO and give a new POID, then remember this new POID and paste it into DetailID for each line item in tblDetail thats in the subform....Think that's how it should work anyway


Private Sub Command48_Click()

On Error GoTo Err_Handler

'Purpose: Duplicate the main form record and related records in the subform.

Dim NewPOID As Long 'Primary key value of the new record.
Dim OldQuoteID As Long
Dim db As DAO.Database
Dim qd As DAO.QueryDef

'Save any edits first
If Me.Dirty Then
Me.Dirty = False
End

'Make sure there is a record to duplicate.
If Me.NewRecord Then
MsgBox "Select the record to duplicate."
Exit Sub
End If


'Duplicate the main record: add to form's clone.
OldQuoteID = Me.POID
With Me.RecordsetClone
.AddNew
!Quote = Null
!EDD = Null
!BudgetCode = Me.BudgetCode
!Currency = Me.Currency
!POCreatedBy = Me.POCreatedBy
!POTitle = Me.POTitle
!Supplier = Me.Supplier
!PODate = Date
!POSent = Null
!POApproved = Null
!POOrdered = Null
!POPartReceived = Null
!POReceived = Null
!POPaid = Null
!POClose = Null

.Update

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
NewPOID = !POID
'Display the new duplicate.
Me.Bookmark = .LastModified
End With

'' copy details
Set qd = db.QueryDefs!qryPODetail
qd.Parameters!DetailID = OldQuoteID
qd.Parameters!DetailID = NewQuoteID
qd.Execute dbSeeChanges


Me.tblPODetail.Requery
Set db = Nothing

Exit_Handler:
Exit Sub

Err_Handler:
Select Case Err.Number
Case 3021, 2501 ' update cancelled
Resume Exit_Handler
Case Else
MsgBox Err.Number & "--" & Err.Description
Resume Exit_Handler
End Select
End If
End Sub

 

[mounty76]

Thanks Pat, have made the changes as above and renamed the control to Clone_Click()

It clones the PO now and gives it a new POID but then I get an error code 91 and it doesn't copy the subform data, I have this code below but have tried a few different options but can't seem to figure it out

'Save the primary key value, to use as the foreign key for the related records.
.Bookmark = .LastModified
NewPOID = !POID
'Display the new duplicate.
Me.Bookmark = .LastModified
End With

'' copy details
Set qd = db.QueryDefs!qryPODetail
qd.Parameters!EnterOldPOID = OldPOID
qd.Parameters!EnterNewPOID = NewPOID
qd.Execute dbSeeChanges


I think the parameters should only be one which should be qd.Parameters!DetailID = NewPOID

But this doesn't work either. FYI I've no primary key on the PODetail table, only the FK

 

[mounty76]

Ah OK, thanks for this, here is the SQL from qryPODetail

SELECT tblPODetail.DetailID, tblPODetail.ItemDescription, tblPODetail.Quanity, tblPODetail.BudgetCode, tblPODetail.Currency, tblPODetail.Price, tblPODetail.VAT, tblPODetail.TotalPrice, tblPODetail.EDD, tblPODetail.Ordered, tblPODetail.Received
FROM tblPODetail;

 

[mounty76]

Sorry for delay....busy with the day job!! Thanks Pat, all working now, very much appreciated!!