Access Drill Down from a form
I have a form (frm_Consolidated) that I would like to drill down to another form(frm_details) based on the following four frm_Consolidated fields:
1. Source
2. Contributes to IT MW
3. OpDivs… this is a concatenated field : OpDiv+Year_Identified
4. Risk Rating
The linked field is “Remaining Open” in the frm_Consolidated.
The related fields in the “frm_details” are
1. Source
2. Contributing_MW
3. OpDiv
4. Year_Identified
5. Risk_Rating
The name of the form to open from the drill down is frm_details.
Can you provide code to perform this routine? A sample DB is attached.
I've tried using the code below but not records are found:
If DCount("*", "tbl_Consolidated_Import",
MsgBox "No related record found", vbExclamation, "DSPC"
Exit Sub
Else
'open form
DoCmd.OpenForm "frm_details", , , "[Source]='" & Me.Source And [Risk Rating] = " & Me.Risk_Rating & And [Contributes to IT MW] = " & Me.Contributes_to_IT_MW & " '"
End If
Data_Call---Copy.accdb
1. Source
2. Contributes to IT MW
3. OpDivs… this is a concatenated field : OpDiv+Year_Identified
4. Risk Rating
The linked field is “Remaining Open” in the frm_Consolidated.
The related fields in the “frm_details” are
1. Source
2. Contributing_MW
3. OpDiv
4. Year_Identified
5. Risk_Rating
The name of the form to open from the drill down is frm_details.
Can you provide code to perform this routine? A sample DB is attached.
I've tried using the code below but not records are found:
If DCount("*", "tbl_Consolidated_Import",
MsgBox "No related record found", vbExclamation, "DSPC"
Exit Sub
Else
'open form
DoCmd.OpenForm "frm_details", , , "[Source]='" & Me.Source And [Risk Rating] = " & Me.Risk_Rating & And [Contributes to IT MW] = " & Me.Contributes_to_IT_MW & " '"
End If
Data_Call---Copy.accdb
Last Comment
ASKER
I get a type mismatch in the DCount statement
If DCount("*", "tbl_Consolidated_Import",
DoCmd.OpenForm "frm_details", , , "[Source]='" & Me.Source And [Risk Rating] = " & Me.Risk_Rating & And [Contributes to IT MW] = " & Me.Contributes_to_IT_MW & " '"
If DCount("*", "tbl_Consolidated_Import",
DoCmd.OpenForm "frm_details", , , "[Source]='" & Me.Source And [Risk Rating] = " & Me.Risk_Rating & And [Contributes to IT MW] = " & Me.Contributes_to_IT_MW & " '"
ASKER
Please note - all fields are text
here is the correct syntax
If DCount("*", "tbl_Consolidated_Import",
MsgBox "No related record found", vbExclamation, "DSPC"
Exit Sub
Else
DoCmd.OpenForm "frm_details", , , "[Source]='" & Me.Source & "' And [Risk_Rating] = '" & Me.Risk_Rating & "' And [Contributes to IT MW] = '" & Me.Contributes_to_IT_MW & "'"
End If
the other problem is you are using names of the field that is not in the table "tbl_Consolidated_Import"
like [Risk Rating] which should be Risk_Rating
and [Contributes to IT MW] which should be [Contributing_MW]
so try using this
If DCount("*", "tbl_Consolidated_Import",
MsgBox "No related record found", vbExclamation, "DSPC"
Exit Sub
Else
DoCmd.OpenForm "frm_details", , , "[Source]='" & Me.Source & "' And [Risk_Rating] = '" & Me.Risk_Rating & "' And [Contributing_MW] = '" & Me.Contributes_to_IT_MW & "'"
End If
If DCount("*", "tbl_Consolidated_Import",
MsgBox "No related record found", vbExclamation, "DSPC"
Exit Sub
Else
DoCmd.OpenForm "frm_details", , , "[Source]='" & Me.Source & "' And [Risk_Rating] = '" & Me.Risk_Rating & "' And [Contributes to IT MW] = '" & Me.Contributes_to_IT_MW & "'"
End If
the other problem is you are using names of the field that is not in the table "tbl_Consolidated_Import"
like [Risk Rating] which should be Risk_Rating
and [Contributes to IT MW] which should be [Contributing_MW]
so try using this
If DCount("*", "tbl_Consolidated_Import",
MsgBox "No related record found", vbExclamation, "DSPC"
Exit Sub
Else
DoCmd.OpenForm "frm_details", , , "[Source]='" & Me.Source & "' And [Risk_Rating] = '" & Me.Risk_Rating & "' And [Contributing_MW] = '" & Me.Contributes_to_IT_MW & "'"
End If
ASKER
Worked good however I added [Status] = Open and I get a syntax error....
If DCount("*", "tbl_Consolidated_Import",
MsgBox "No related record found", vbExclamation, "DSPC"
Exit Sub
Else
DoCmd.OpenForm "frm_details", , , "[Source]='" & Me.Source & "' And [Risk_Rating] = '" & Me.Risk_Rating & "' And [Status] = 'Open' & " 'And [Contributing_MW] = '" & Me.Contributes_to_IT_MW & "'"
If DCount("*", "tbl_Consolidated_Import",
MsgBox "No related record found", vbExclamation, "DSPC"
Exit Sub
Else
DoCmd.OpenForm "frm_details", , , "[Source]='" & Me.Source & "' And [Risk_Rating] = '" & Me.Risk_Rating & "' And [Status] = 'Open' & " 'And [Contributing_MW] = '" & Me.Contributes_to_IT_MW & "'"
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
If "Source" is a Text field, then you need a closing single quote after that, and you also need to be sure your spacing is correct on all of the AND concats.
You also seem to have a trailing double quote at the end.
If DCount("*", "tbl_Consolidated_Import",
Assuming Risk Rating and Contributes to IT MW are numeric or boolean fields, that should work.