Open subform of subform with specific record (1 Viewer)

I am a little above to be a beginner in MS access and VBA. I am in middle of project in building a MS Access to handle Customer Relation Management include quoting and invoicing. However, the dilemma that I am facing is, I am unable to script a VBA Code to open Sub-form of a Sub-form with a specific record.

I have a form consists of:

1. Main Form (frm_AllCompany) : Company Information with PK: CompanyID

2. Company Locations Sub-form (frmCompanyLocation): Location Information with PK: LocationID

a. Contact Sub-form of Company Locations Sub-form (sfrmContact): Contact Information with PK: ContactID

Forms!frm_AllCompany![frmCompanyLocation]![sfrmContact]

I have a Datasheet form listed all the contacts names. What I am trying to do is to click on the contact name to open the main form for editing the information.

My trouble is that I am unable to script the right VBA Code to open the main form by selecting record from Contact Sub-form by clicking the name from the datasheet form

Forms!frm_AllCompany![frmCompanyLocation]![sfrmContact]

Any help with a draft of the VBA Script I will be appreciated

I have a form consists of:

1. Main Form (frm_AllCompany) : Company Information with PK: CompanyID
2. Company Locations Sub-form (frmCompanyLocation): Location Information with PK: LocationID
a. Contact Sub-form of Company Locations Sub-form (sfrmContact): Contact Information with PK: ContactID

Forms!frm_AllCompany![frmCompanyLocation]![sfrmContact]

I have a Datasheet form listed all the contacts names. What I am trying to do is to click on the contact name to open the main form for editing the information.

Click to expand...

If I understand correctly you select a contact from one form and want to open the main form to that company that has that contact. Based on what you show a contact can only be related to 1 location, and 1 location can only be related to 1 company.

If that is the case create a query joining company, location, and contact. Call it qryCompLocCon

Now that I think about it, your sub sub form is likely in single form view so you would not only have to open to the correct company, but move the sub sub form to correct contact. You need to then pass the contactID in openArgs (if opening the form acdialog) or move the sub sub form to the contact if not opening dialog. I will demo the latter.

You may find this article useful for understanding subform syntax: http://access.mvps.org/access/forms/frm0031.htm

Here is my VBA Script , it is just working to open the right company. But it opens the first record for the Company Location and first record of the Location Contact. How can I make this script open the main form with specific record of the Location and the Location contact.

Please I need help


Private Sub txtUserName_Click()

1001 Dim GetCompany As String
1002 Dim GetCompanyID As Long
1003 Dim GetLocationID As Long
1004 Dim GetUser As String
1005 Dim GetUserID As Long


1006 GetUser = DLookup("[ContactName]", "tbl_Contacts", "[ContactName] = " & "'" & Forms!frm_ActiveCustomerDS!txtUserName & "'")
1007 GetUserID = DLookup("[ContactID]", "tbl_Contacts", "[ContactName] = " & "'" & GetUser & "'")
1008 GetLocationID = DLookup("[LocationID]", "tbl_Contacts", "[ContactName] = " & "'" & GetUser & "'")
1009 GetCompanyID = DLookup("[CompanyID]", "tbl_CompanyLocation", "[LocationID] = " & GetLocationID)
1010 GetCompany = DLookup("[Company]", "tbl_Companys", "[CompanyID]= " & GetCompanyID)

1011 DoCmd.OpenForm "frm_AllCompany", acNormal

1012 DoCmd.Echo False
1013 Forms![frm_AllCompany].Filter = ""
1014 Forms![frm_AllCompany].FilterOn = False
1015 DoCmd.ApplyFilter , "[CompanyID] = " & GetCompanyID
1016 Forms![frm_AllCompany].FilterOn = True
1017 DoCmd.Echo True


1018 Forms![frm_AllCompany]!cboSelectCompany.Value = GetCompanyID
1019 If Forms![frm_AllCompany]!cboSelectCompany = GetCompanyID Then

1020 Dim strSQL As String
1021 Dim strSQL1 As String
1022 Dim strSQL2 As String

1023 strSQL = "SELECT tbl_Companys.* FROM tbl_Companys " & vbCrLf & _
"WHERE tbl_Companys.CompanyID= " & GetCompanyID & ";"

1024 Forms![frm_AllCompany].Form.RecordSource = strSQL



1025 Forms!frm_AllCompany![frmCompanyLocation].txtCompanyLocationID.Value = GetLocationID
1026 Forms!frm_AllCompany![frmCompanyLocation].txtCompanyLocationID.Refresh

1027 DoCmd.Echo False
1028 Forms![frm_AllCompany].Filter = ""
1029 Forms![frm_AllCompany].FilterOn = False
1030 DoCmd.ApplyFilter , "[LocationID] = " & GetLocationID
1031 Forms![frm_AllCompany].FilterOn = True
1032 DoCmd.Echo True


1031 strSQL1 = "SELECT tbl_CompanyLocation.* FROM tbl_CompanyLocation " & vbCrLf & _
"WHERE tbl_CompanyLocation.LocationID= " & GetLocationID & ";"

1032 If Forms!frm_AllCompany![frmCompanyLocation].txtCompanyLocationID.Value <> GetLocationID Then
Forms!frm_AllCompany![frmCompanyLocation].Form.RecordSource = strSQL1


1033 Forms!frm_AllCompany![frmCompanyLocation]![sfrmContact].ContactID.Value = " & GetUserID"
1034 Forms!frm_AllCompany![frmCompanyLocation]![sfrmContact].ContactID.Refresh

1035 DoCmd.Echo False
1036 Forms![frm_AllCompany].Filter = ""
1037 Forms![frm_AllCompany].FilterOn = False
1038 DoCmd.ApplyFilter , "[ContactID] = " & GetUserID
1039 Forms![frm_AllCompany].FilterOn = True
1040 DoCmd.Echo True


1041 strSQL2 = "SELECT tbl_Contacts.* FROM tbl_Contacts " & vbCrLf & _
"WHERE tbl_Contacts.ContactID= " & GetUserID & ";"

1042 If Forms!frm_AllCompany![frmCompanyLocation]![sfrmContact].ContactID.Value <> GetUserID Then
1043 Forms!frm_AllCompany![frmCompanyLocation]![sfrmContact].Form.RecordSource = strSQL2

1044 Call Fresh

1045 End If
1046 End If
1047 End If


End Sub