Matching Records and iterate through sub forms in access
I have a main form MForm which has a subform A and that subform A has another subform subform B inside.
"MForm" contains IDs based on the dates. (main record set. Distinct dates)
The subform A (Linked by "ID" and "DateR" to the main form) is the one where I have to enter countries in four different columns namely AR, IR, SR, ER. These fields are number fields and needs to be updated manually by the user.
the subform B which is under the above mentioned subform A (Linked by date and country) has a queried data from which i have numbers for AR_Count, IR_Count, SR_Count and ER_Count respectively. (Queried by "DateR" and "Country" from an excel query)
Now the purpose is i have an additional field in Subform A which is a field list and has two values "Matched" and "Unmatched".
What i want to accomplish is when the numbers for AR,IR,SR and ER inputted by user in Subform A are equal to the numbers in AR_Count,IR_Count,SR_Count and ER_Count respectively in Subform B then the value for the field list should automatically change to "Matched". Now i need that to be for all records in that subform for that entry.
Secondly, when all the records in the subform have matched in status field then the mainform "MForm" has a field called Status as well. with values "Active" and "Closed" . I want that once all the fields have values "Matched" in Subform A then in the MForm status should change to "closed" else it stays active.
I have included a picture to show how my form looks like . The user must select a date and fill the Subform A. Subform B has values pulled from a query.
Now i want a VBA Code and i want to ask how i can achieve these functions in my form. additionally where i can use that. I tried using after update or before update handlers for status fields with no luck.
FORM LAYOUT
vba validation ms-access access matching
edited Nov 21 '18 at 15:13
Roman Pokrovskij
4,30284979
asked Nov 21 '18 at 10:31
XenaD AuxXenaD Aux
11
add a comment |
I have a main form MForm which has a subform A and that subform A has another subform subform B inside.
"MForm" contains IDs based on the dates. (main record set. Distinct dates)
The subform A (Linked by "ID" and "DateR" to the main form) is the one where I have to enter countries in four different columns namely AR, IR, SR, ER. These fields are number fields and needs to be updated manually by the user.
the subform B which is under the above mentioned subform A (Linked by date and country) has a queried data from which i have numbers for AR_Count, IR_Count, SR_Count and ER_Count respectively. (Queried by "DateR" and "Country" from an excel query)
Now the purpose is i have an additional field in Subform A which is a field list and has two values "Matched" and "Unmatched".
What i want to accomplish is when the numbers for AR,IR,SR and ER inputted by user in Subform A are equal to the numbers in AR_Count,IR_Count,SR_Count and ER_Count respectively in Subform B then the value for the field list should automatically change to "Matched". Now i need that to be for all records in that subform for that entry.
Secondly, when all the records in the subform have matched in status field then the mainform "MForm" has a field called Status as well. with values "Active" and "Closed" . I want that once all the fields have values "Matched" in Subform A then in the MForm status should change to "closed" else it stays active.
I have included a picture to show how my form looks like . The user must select a date and fill the Subform A. Subform B has values pulled from a query.
Now i want a VBA Code and i want to ask how i can achieve these functions in my form. additionally where i can use that. I tried using after update or before update handlers for status fields with no luck.
FORM LAYOUT
vba validation ms-access access matching
edited Nov 21 '18 at 15:13
Roman Pokrovskij
4,30284979
asked Nov 21 '18 at 10:31
XenaD AuxXenaD Aux
11
2
Please post the code you already have, and point out what exactly isn't working. Is your code not being called? Is it malfunctioning? Stack Overflow is not a code-writing service, but people here can help you to spot problems.
– Roland Weber
Nov 21 '18 at 12:01
its not about the code itself but i want to ask as to how i can achieve the above explained result. How can i see to it that i can apply validations in such a way with two sub forms
– XenaD Aux
Nov 22 '18 at 14:29
DLookup("[CounType]", "iL/iW Summary", ("[Country] Like '" & Me.Country & "'") And ("[DateReceived] Like '" & Me.Date_Received & "'") And ("[Type] Like '" & "AR" & "'"))
– XenaD Aux
Nov 26 '18 at 15:56
The above VBA code is one which i want to place in the AR_Afterupdate field in my current form. where i want to lookup the value of CounType in the table iL/iW Summary. I want the current input country to match with the one in iL/iW Summary table and the date received to match as well. i tried this with error: data type mismatch.
– XenaD Aux
Nov 26 '18 at 16:03
I checked Country is string in both and datereceived as date in both the form and the table. I am trying to use this lookup to validate against the value that users fill in the form.
– XenaD Aux
Nov 26 '18 at 16:03
add a comment |
I have a main form MForm which has a subform A and that subform A has another subform subform B inside.
"MForm" contains IDs based on the dates. (main record set. Distinct dates)
The subform A (Linked by "ID" and "DateR" to the main form) is the one where I have to enter countries in four different columns namely AR, IR, SR, ER. These fields are number fields and needs to be updated manually by the user.
the subform B which is under the above mentioned subform A (Linked by date and country) has a queried data from which i have numbers for AR_Count, IR_Count, SR_Count and ER_Count respectively. (Queried by "DateR" and "Country" from an excel query)
Now the purpose is i have an additional field in Subform A which is a field list and has two values "Matched" and "Unmatched".
What i want to accomplish is when the numbers for AR,IR,SR and ER inputted by user in Subform A are equal to the numbers in AR_Count,IR_Count,SR_Count and ER_Count respectively in Subform B then the value for the field list should automatically change to "Matched". Now i need that to be for all records in that subform for that entry.
Secondly, when all the records in the subform have matched in status field then the mainform "MForm" has a field called Status as well. with values "Active" and "Closed" . I want that once all the fields have values "Matched" in Subform A then in the MForm status should change to "closed" else it stays active.
I have included a picture to show how my form looks like . The user must select a date and fill the Subform A. Subform B has values pulled from a query.
Now i want a VBA Code and i want to ask how i can achieve these functions in my form. additionally where i can use that. I tried using after update or before update handlers for status fields with no luck.
FORM LAYOUT
vba validation ms-access access matching
edited Nov 21 '18 at 15:13
Roman Pokrovskij
4,30284979
asked Nov 21 '18 at 10:31
XenaD AuxXenaD Aux
11
I have a main form MForm which has a subform A and that subform A has another subform subform B inside.
"MForm" contains IDs based on the dates. (main record set. Distinct dates)
The subform A (Linked by "ID" and "DateR" to the main form) is the one where I have to enter countries in four different columns namely AR, IR, SR, ER. These fields are number fields and needs to be updated manually by the user.
the subform B which is under the above mentioned subform A (Linked by date and country) has a queried data from which i have numbers for AR_Count, IR_Count, SR_Count and ER_Count respectively. (Queried by "DateR" and "Country" from an excel query)
Now the purpose is i have an additional field in Subform A which is a field list and has two values "Matched" and "Unmatched".
What i want to accomplish is when the numbers for AR,IR,SR and ER inputted by user in Subform A are equal to the numbers in AR_Count,IR_Count,SR_Count and ER_Count respectively in Subform B then the value for the field list should automatically change to "Matched". Now i need that to be for all records in that subform for that entry.
Secondly, when all the records in the subform have matched in status field then the mainform "MForm" has a field called Status as well. with values "Active" and "Closed" . I want that once all the fields have values "Matched" in Subform A then in the MForm status should change to "closed" else it stays active.
I have included a picture to show how my form looks like . The user must select a date and fill the Subform A. Subform B has values pulled from a query.
Now i want a VBA Code and i want to ask how i can achieve these functions in my form. additionally where i can use that. I tried using after update or before update handlers for status fields with no luck.
FORM LAYOUT
vba validation ms-access access matching
vba validation ms-access access matching
edited Nov 21 '18 at 15:13
Roman Pokrovskij
4,30284979
asked Nov 21 '18 at 10:31
XenaD AuxXenaD Aux
11
edited Nov 21 '18 at 15:13
Roman Pokrovskij
4,30284979
asked Nov 21 '18 at 10:31
XenaD AuxXenaD Aux
11
edited Nov 21 '18 at 15:13
Roman Pokrovskij
4,30284979
edited Nov 21 '18 at 15:13
Roman Pokrovskij
4,30284979
edited Nov 21 '18 at 15:13
Roman Pokrovskij
4,30284979
4,30284979
asked Nov 21 '18 at 10:31
XenaD AuxXenaD Aux
11
asked Nov 21 '18 at 10:31
XenaD AuxXenaD Aux
11
asked Nov 21 '18 at 10:31
XenaD AuxXenaD Aux
11
11
2
Please post the code you already have, and point out what exactly isn't working. Is your code not being called? Is it malfunctioning? Stack Overflow is not a code-writing service, but people here can help you to spot problems.
– Roland Weber
Nov 21 '18 at 12:01
its not about the code itself but i want to ask as to how i can achieve the above explained result. How can i see to it that i can apply validations in such a way with two sub forms
– XenaD Aux
Nov 22 '18 at 14:29
DLookup("[CounType]", "iL/iW Summary", ("[Country] Like '" & Me.Country & "'") And ("[DateReceived] Like '" & Me.Date_Received & "'") And ("[Type] Like '" & "AR" & "'"))
– XenaD Aux
Nov 26 '18 at 15:56
The above VBA code is one which i want to place in the AR_Afterupdate field in my current form. where i want to lookup the value of CounType in the table iL/iW Summary. I want the current input country to match with the one in iL/iW Summary table and the date received to match as well. i tried this with error: data type mismatch.
– XenaD Aux
Nov 26 '18 at 16:03
I checked Country is string in both and datereceived as date in both the form and the table. I am trying to use this lookup to validate against the value that users fill in the form.
– XenaD Aux
Nov 26 '18 at 16:03
add a comment |
2
Please post the code you already have, and point out what exactly isn't working. Is your code not being called? Is it malfunctioning? Stack Overflow is not a code-writing service, but people here can help you to spot problems.
– Roland Weber
Nov 21 '18 at 12:01
its not about the code itself but i want to ask as to how i can achieve the above explained result. How can i see to it that i can apply validations in such a way with two sub forms
– XenaD Aux
Nov 22 '18 at 14:29
DLookup("[CounType]", "iL/iW Summary", ("[Country] Like '" & Me.Country & "'") And ("[DateReceived] Like '" & Me.Date_Received & "'") And ("[Type] Like '" & "AR" & "'"))
– XenaD Aux
Nov 26 '18 at 15:56
The above VBA code is one which i want to place in the AR_Afterupdate field in my current form. where i want to lookup the value of CounType in the table iL/iW Summary. I want the current input country to match with the one in iL/iW Summary table and the date received to match as well. i tried this with error: data type mismatch.
– XenaD Aux
Nov 26 '18 at 16:03
I checked Country is string in both and datereceived as date in both the form and the table. I am trying to use this lookup to validate against the value that users fill in the form.
– XenaD Aux
Nov 26 '18 at 16:03
2
2
Please post the code you already have, and point out what exactly isn't working. Is your code not being called? Is it malfunctioning? Stack Overflow is not a code-writing service, but people here can help you to spot problems.
– Roland Weber
Nov 21 '18 at 12:01
Please post the code you already have, and point out what exactly isn't working. Is your code not being called? Is it malfunctioning? Stack Overflow is not a code-writing service, but people here can help you to spot problems.
– Roland Weber
Nov 21 '18 at 12:01
its not about the code itself but i want to ask as to how i can achieve the above explained result. How can i see to it that i can apply validations in such a way with two sub forms
– XenaD Aux
Nov 22 '18 at 14:29
its not about the code itself but i want to ask as to how i can achieve the above explained result. How can i see to it that i can apply validations in such a way with two sub forms
– XenaD Aux
Nov 22 '18 at 14:29
DLookup("[CounType]", "iL/iW Summary", ("[Country] Like '" & Me.Country & "'") And ("[DateReceived] Like '" & Me.Date_Received & "'") And ("[Type] Like '" & "AR" & "'"))
– XenaD Aux
Nov 26 '18 at 15:56
DLookup("[CounType]", "iL/iW Summary", ("[Country] Like '" & Me.Country & "'") And ("[DateReceived] Like '" & Me.Date_Received & "'") And ("[Type] Like '" & "AR" & "'"))
– XenaD Aux
Nov 26 '18 at 15:56
The above VBA code is one which i want to place in the AR_Afterupdate field in my current form. where i want to lookup the value of CounType in the table iL/iW Summary. I want the current input country to match with the one in iL/iW Summary table and the date received to match as well. i tried this with error: data type mismatch.
– XenaD Aux
Nov 26 '18 at 16:03
The above VBA code is one which i want to place in the AR_Afterupdate field in my current form. where i want to lookup the value of CounType in the table iL/iW Summary. I want the current input country to match with the one in iL/iW Summary table and the date received to match as well. i tried this with error: data type mismatch.
– XenaD Aux
Nov 26 '18 at 16:03
I checked Country is string in both and datereceived as date in both the form and the table. I am trying to use this lookup to validate against the value that users fill in the form.
– XenaD Aux
Nov 26 '18 at 16:03
I checked Country is string in both and datereceived as date in both the form and the table. I am trying to use this lookup to validate against the value that users fill in the form.
– XenaD Aux
Nov 26 '18 at 16:03
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53410108%2fmatching-records-and-iterate-through-sub-forms-in-access%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53410108%2fmatching-records-and-iterate-through-sub-forms-in-access%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Please post the code you already have, and point out what exactly isn't working. Is your code not being called? Is it malfunctioning? Stack Overflow is not a code-writing service, but people here can help you to spot problems.
– Roland Weber
Nov 21 '18 at 12:01
its not about the code itself but i want to ask as to how i can achieve the above explained result. How can i see to it that i can apply validations in such a way with two sub forms
– XenaD Aux
Nov 22 '18 at 14:29
DLookup("[CounType]", "iL/iW Summary", ("[Country] Like '" & Me.Country & "'") And ("[DateReceived] Like '" & Me.Date_Received & "'") And ("[Type] Like '" & "AR" & "'"))
– XenaD Aux
Nov 26 '18 at 15:56
The above VBA code is one which i want to place in the AR_Afterupdate field in my current form. where i want to lookup the value of CounType in the table iL/iW Summary. I want the current input country to match with the one in iL/iW Summary table and the date received to match as well. i tried this with error: data type mismatch.
– XenaD Aux
Nov 26 '18 at 16:03
I checked Country is string in both and datereceived as date in both the form and the table. I am trying to use this lookup to validate against the value that users fill in the form.
– XenaD Aux
Nov 26 '18 at 16:03