Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Update subform based on criteria/criterias.

Copper Contributor

Appreciate if anyone could help me in correcting the below mentioned code. 

There are a couple of requirements which I was not able to bring.  Thanks in advance.

 

1. The sub form shall be populated based on the criteria given. Eg, 

If the field Enquiry No is selected in the combo box all records pertaining to that particular enquiry shall be displayed in the sub form. The next combo box is Ref_No.. once ref_ No is selected it should display those records in the sub form where it matches with the Enquiry no and the Ref No.  Further it goes as based on the next selection in the combo boxes.

 

2. For certain combo boxes I would prefer to keep it blank case by case. As per the below mentioned VBA. Not able to keep that blank. (Intension is to show all the records in the subform which matches the first 2 criteria.


Private Sub Cmb_Enquiry_No_Change()
SF_masterpid.Requery
End Sub

Private Sub Cmb_Installation_Type_Change()
SF_masterpid.Requery
End Sub

Private Sub Cmb_PID_Inst_Type_Change()
SF_masterpid.Requery
End Sub

Private Sub Cmb_Ref_No_Change()
SF_masterpid.Requery
End Sub

Private Sub Cmb_Ref_Type_Change()
SF_masterpid.Requery
End Sub

 

Private Sub Form_Load()
Cmb_Enquiry_No.RowSourceType = "Table/Query"
Cmb_Enquiry_No.RowSource = "Select Distinct Enquiry_No from T_master_pid"
Cmb_Ref_No.RowSourceType = "Table/Query"
Cmb_Ref_No.RowSource = "Select Distinct Ref_No from T_master_pid"
Cmb_Ref_Type.RowSourceType = "Table/Query"
Cmb_Ref_Type.RowSource = "Select Distinct Ref_Type from T_master_pid"
Cmb_PID_Inst_Type.RowSourceType = "Table/Query"
Cmb_PID_Inst_Type.RowSource = "Select Distinct PID_Inst_Type from T_master_pid"
Cmb_Installation_Type.RowSourceType = "Table/Query"
Cmb_Installation_Type.RowSource = "Select Distinct Installation_Type from T_master_pid"

End Sub

0 Replies