Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Launching Macro based upon a result from a query?
- Thread starter CMoriarty
- Start date
CMoriarty,
how about this from access help:
Make a macro run under specific conditions
In the Macro window, click Conditions on the toolbar.
In the Condition column, type a conditional expression in the row where you want to set the condition.
Notes
In a Microsoft Access database (.mdb), click the Build button to use the Expression Builder. The Expression Builder is not available from a Microsoft Access project.
You can't use an SQL expression in a macro Condition column.
In the Action column, enter the action you want Microsoft Access to perform when the condition is true.
When you run the macro, Microsoft Access evaluates the first conditional expression. If the condition is true, Microsoft Access carries out the action in that row and all of the immediately following actions that are preceded by an ellipsis (...) in the Condition column. For example, the following macro carries out the MsgBox and StopMacro actions only when there is no value in the SupplierID field (when the value is Null).
Microsoft Access then runs any additional actions in the macro that have a blank Condition column until it reaches another expression, a macro name, or the end of the macro.
If the condition is false, Microsoft Access ignores the action and any immediately following actions that are preceded by an ellipsis in the Condition column and moves to the next action row that contains another condition or a blank Condition column.
or, if you are using vba you could store the data in a varaible then based on that variable if it evaluates to a certain piece of date then run macrox if another run macroy.
hth
regards,
longhair
how about this from access help:
Make a macro run under specific conditions
In the Macro window, click Conditions on the toolbar.
In the Condition column, type a conditional expression in the row where you want to set the condition.
Notes
In a Microsoft Access database (.mdb), click the Build button to use the Expression Builder. The Expression Builder is not available from a Microsoft Access project.
You can't use an SQL expression in a macro Condition column.
In the Action column, enter the action you want Microsoft Access to perform when the condition is true.
When you run the macro, Microsoft Access evaluates the first conditional expression. If the condition is true, Microsoft Access carries out the action in that row and all of the immediately following actions that are preceded by an ellipsis (...) in the Condition column. For example, the following macro carries out the MsgBox and StopMacro actions only when there is no value in the SupplierID field (when the value is Null).
Microsoft Access then runs any additional actions in the macro that have a blank Condition column until it reaches another expression, a macro name, or the end of the macro.
If the condition is false, Microsoft Access ignores the action and any immediately following actions that are preceded by an ellipsis in the Condition column and moves to the next action row that contains another condition or a blank Condition column.
or, if you are using vba you could store the data in a varaible then based on that variable if it evaluates to a certain piece of date then run macrox if another run macroy.
hth
regards,
longhair
- Thread starter
- #3
Longhair
I've tried your suggestion, but without success. I want the macro to launch when:
[Table1].[Field1]=[Table2].[Field2]
Everytime I enter this information into the conditions column of the macro, the following message occurs
Microsoft Office Access can not find the name 'Table1' you entered into the expression.
You may have specified a control that wasn't on the current object without specifying ther correct form or report context.
To refer to a control on another form or report, precede the control name with the name of a collection, usually either forms or Reports, and the name of the form or report to chich the control belongs.
For example, Forms![products]![Units In Stock]
What am I doing wrong?
I've tried your suggestion, but without success. I want the macro to launch when:
[Table1].[Field1]=[Table2].[Field2]
Everytime I enter this information into the conditions column of the macro, the following message occurs
Microsoft Office Access can not find the name 'Table1' you entered into the expression.
You may have specified a control that wasn't on the current object without specifying ther correct form or report context.
To refer to a control on another form or report, precede the control name with the name of a collection, usually either forms or Reports, and the name of the form or report to chich the control belongs.
For example, Forms![products]![Units In Stock]
What am I doing wrong?
- Thread starter
- #5
is 'table1' the name of the table? Yes
'fieldx' the name of the field? Yes
The actual informaton is the following
Tabel1 Name: Security1
Field1name: EmpID
Tabel2 Name: Security2
Field1name: ID
The condition I wrote was:
[Security1].[EmpID]=[Security2].[ID]
I'm still getting the error that I described in my earlier post.
'fieldx' the name of the field? Yes
The actual informaton is the following
Tabel1 Name: Security1
Field1name: EmpID
Tabel2 Name: Security2
Field1name: ID
The condition I wrote was:
[Security1].[EmpID]=[Security2].[ID]
I'm still getting the error that I described in my earlier post.
- Thread starter
- #7
- Thread starter
- #9
Before I get started, I should explain a little about me/ I'm not a VB programmer, so I do not know how to write code... I’m just dangerous enough to create interactive forms with macros running as a result of what the user does on the forms…..
Basically, I'm creating a security (user ID/Password) portion to the database I am creating. When the user opens the access database, they're prompted by a form attached to Security1 table. The user must fill in their ID and password, which populates the Security1 table with the information they've entered.
In a second table (Security2), a secure copy of user id and password are stored. If the user entered in their user id and password in Security1 matches the information stored in Security2, I want the macro open the next appropriate forms. If the user id and password are incorrect, I want security1 to refresh and prompt the user to re-enter their information.
I hope I’m trying to explain this correctly.
BTW, do to the environment we are operating in, I do not want to use Access’ canned security package.
Thanks for all your help so far!
Basically, I'm creating a security (user ID/Password) portion to the database I am creating. When the user opens the access database, they're prompted by a form attached to Security1 table. The user must fill in their ID and password, which populates the Security1 table with the information they've entered.
In a second table (Security2), a secure copy of user id and password are stored. If the user entered in their user id and password in Security1 matches the information stored in Security2, I want the macro open the next appropriate forms. If the user id and password are incorrect, I want security1 to refresh and prompt the user to re-enter their information.
I hope I’m trying to explain this correctly.
BTW, do to the environment we are operating in, I do not want to use Access’ canned security package.
Thanks for all your help so far!
- Thread starter
- #10
It sounds as though you don't need the the table "Security1", you can refer to the values on the form directly.
Also, a condition for running a macro needs a single value that can be checked for true or false. A query returns a table, not a value. Look at the functions dlookup() or dcount() - those return single values that can be checked.
The syntax for referring to a value on a form is something like forms("formname").fieldname, where fieldname is the name of the field on the form (look at its properties)
Also, a condition for running a macro needs a single value that can be checked for true or false. A query returns a table, not a value. Look at the functions dlookup() or dcount() - those return single values that can be checked.
The syntax for referring to a value on a form is something like forms("formname").fieldname, where fieldname is the name of the field on the form (look at its properties)
CMoriarty,
now i understand a bit better. i would suggest a different approach, though. have them enter the data on the form, then click un update button. use the data from the form to search Security2. if it's valid then write to Security1.
do a search for 'validating input'.
hth
regards,
longhair
now i understand a bit better. i would suggest a different approach, though. have them enter the data on the form, then click un update button. use the data from the form to search Security2. if it's valid then write to Security1.
do a search for 'validating input'.
hth
regards,
longhair
- Thread starter
- #13
CMoriarty,
user enters data (user id & password) into your form. then you use the data from the form ie SecurityForm1.EmdID and .Password to do the look up of table Security2.ID and .Password. if there is a match then write to table Security1 (if you need to - if you don't need it again, then don't write it) and aloow the user to proceed. if there is no match, then allow x number of tries before they have to start over. what i meant by do a search was...click the search button up on top of the webpage by the thread id, search for 'validating input', login, password, etc. search this forum and the access vba one. you should be able to find some good previous examples of how others have implemented login's for access.
hth
regards,
longhair
user enters data (user id & password) into your form. then you use the data from the form ie SecurityForm1.EmdID and .Password to do the look up of table Security2.ID and .Password. if there is a match then write to table Security1 (if you need to - if you don't need it again, then don't write it) and aloow the user to proceed. if there is no match, then allow x number of tries before they have to start over. what i meant by do a search was...click the search button up on top of the webpage by the thread id, search for 'validating input', login, password, etc. search this forum and the access vba one. you should be able to find some good previous examples of how others have implemented login's for access.
hth
regards,
longhair
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question