Updating a subform records based on a combo box selection in the Main form with VBA code. Not OK.

[NKB]

Here is a copy of my DB.
PurchasesDB.zip

[NKB]

Andy49,
I have zipped the DB file and it is attached. Please look at it and tell me where I went wrong!

[NKB]

Here is a copy of my DB file.
PurchasesDB.zip

[NKB]

I have been trying to upload and attach my DB file to this reply. I hope this works now!

[NKB]

a copy of my zipped DB file

[NKB]

Try zipping your file nkb then upload it

a copy of my zipped DB file:

PurchasesDB.zip

[NKB]

Try zipping your file nkb then upload it

I have been trying to reply with the zipped DB file but unsuccessful! Maybe at a later time

[NKB]

I apologize. I have been attaching my zipped DB file for a few times mistakenly. I was not seeing my reply with the attachment. Finally, I saw it at the last page. Once again I apologize for this. I am learning from my mistakes.

[Mayer]

Hello
try sql = "SELECT tblPurchasesDetails.* FROM tblPurchasesDetails WHERE (((tblPurchasesDetails.PurchaseID)=[Forms]![frmPurchasesReview]![cboPurchaseID]));"

[andy49]

PurchasesDBv11.zip

Let me know if this is OK

[Mayer]

just add a blue line.
Because your sub form is linked with main form by [PurchaseID], so the record in main form should move according to cboPurchaseID value

Private Sub cboPurchaseID_AfterUpdate() Dim strSQL As String Me.Recordset.FindFirst "[PurchaseID] = " & Me!cboPurchaseID strSQL = "SELECT tblPurchasesDetails.*, tblShoppingMaterials.Desc, tblShops.ShopName" strSQL = strSQL & " FROM tblShops INNER JOIN (tblShoppingMaterials INNER JOIN tblPurchasesDetails ON tblShoppingMaterials.ItemID = tblPurchasesDetails.ItemID) ON tblShops.ShopID = tblPurchasesDetails.ShopID" strSQL = strSQL & " WHERE (((tblPurchasesDetails.PurchaseID)=" & Me.cboPurchaseID & "));" Debug.Print "strSQL =" & strSQL Me!sbfPurchasesDetailsSubFormView.Form.RecordSourc e = strSQL ' "qryLineItems" Me.sbfPurchasesDetailsSubFormView.Form.Requery End Sub

[NKB]

Mayer,

Thank you very much. It finally worked!. Now, please explain to me why this blue line made the difference and solved the problem. I do not understand the logic behind it. I put the blue line in the code and it worked. Then I removed it for testing and it did not work. Magic!!.

Andy49 thank you for the help.

[NKB]

Mayer,

For testing purposes, I removed the following two entries in the subform property sheet and left them blank, and the cbo and subform worked fine. No change. Why? What is going on? How is this working in Access.
The two entries are:
Link Master Fields: (empty)
Link Child Fields: (empty)

[andy49]

I'm not experienced enough to say whether these are the reasons NKB but my two observations from your database are that your main form wasn't bound to a table and that you had no relationships set up so the wizard wasn't able to set anything up successfully.

[NKB]

Mayer,

In addition, I removed the requery line: Me.sbfPurchasesDetailsSubFormView.Form.Requery
and it is working OK. All I need now is an explanation for this to make sense to me!! I hope you can help.