understanding macros

  • Thread starter Thread starter ace
  • Start date Start date
A

ace

Could someone explain in plain english the following macro, copied from the
Microsoft project management database!

OpenForm Employee Details, Form, ,="[ID]=" & [ID], ,Dialog
SetTempVar CurrentID, [ID]
Requery
SearchForRecord , , First, ="[ID]=" & [TempVars]![CurrentID]

What is this macro doing? I have looked at the online macro explanations in
Access but it does not give good examples there.

Thanks
AC Erdal
 
ACE,

This does not appear to be a well constructed macro. My guess is that is is
supposed to open the Employee Details form at a particular record,
identified by the value of the ID field in the form that the macro is
activated from. But in fact it doesn't really make sense.

Maybe you could scrap this macro, and instead start from scratch according
to what you actually want to achieve.
 
ACE,

This does not appear to be a well constructed macro. My guess is that is is
supposed to open the Employee Details form at a particular record,
identified by the value of the ID field in the form that the macro is
activated from. But in fact it doesn't really make sense.

Maybe you could scrap this macro, and instead start from scratch according
to what you actually want to achieve.
 
Steve,
Thanks for your response.

Here is the part that I am having problem. How can I get a field info from a
table that I have foreign key? So, here is an example;

tblResourceType
1 Seller

tblTransaction (this table contains all transaction information)
ID-Transaction
ID-Seller
ID-Properties
Transaction Status

tblSeller
ID-Seller
Seller Name
Sale Date

tblProperties
ID-Properties
Property Address

Lets I have the "Property Address" and "ID-Properties" from tblProperties
table;
1- How can I get/display the "Transaction Status" field for that particular
property?
2- How can I get/display the "Seller Name" from tblSeller?
3- How can I get/display a list of sellers that matche "Sales Date" period?
So in case of 1/2 one item being returned, and in case of 3 multiple items.

I appreciate any help in coding this.

Thanks,
AC



Steve Schapel said:
ACE,

This does not appear to be a well constructed macro. My guess is that is is
supposed to open the Employee Details form at a particular record,
identified by the value of the ID field in the form that the macro is
activated from. But in fact it doesn't really make sense.

Maybe you could scrap this macro, and instead start from scratch according
to what you actually want to achieve.

--
Steve Schapel, Microsoft Access MVP


ace said:
Could someone explain in plain english the following macro, copied from
the
Microsoft project management database!

OpenForm Employee Details, Form, ,="[ID]=" & [ID], ,Dialog
SetTempVar CurrentID, [ID]
Requery
SearchForRecord , , First, ="[ID]=" & [TempVars]![CurrentID]

What is this macro doing? I have looked at the online macro explanations
in
Access but it does not give good examples there.

Thanks
AC Erdal
Click to expand...
Click to expand...
 
Steve,
Thanks for your response.

Here is the part that I am having problem. How can I get a field info from a
table that I have foreign key? So, here is an example;

tblResourceType
1 Seller

tblTransaction (this table contains all transaction information)
ID-Transaction
ID-Seller
ID-Properties
Transaction Status

tblSeller
ID-Seller
Seller Name
Sale Date

tblProperties
ID-Properties
Property Address

Lets I have the "Property Address" and "ID-Properties" from tblProperties
table;
1- How can I get/display the "Transaction Status" field for that particular
property?
2- How can I get/display the "Seller Name" from tblSeller?
3- How can I get/display a list of sellers that matche "Sales Date" period?
So in case of 1/2 one item being returned, and in case of 3 multiple items.

I appreciate any help in coding this.

Thanks,
AC



Steve Schapel said:
ACE,

This does not appear to be a well constructed macro. My guess is that is is
supposed to open the Employee Details form at a particular record,
identified by the value of the ID field in the form that the macro is
activated from. But in fact it doesn't really make sense.

Maybe you could scrap this macro, and instead start from scratch according
to what you actually want to achieve.

--
Steve Schapel, Microsoft Access MVP


ace said:
Could someone explain in plain english the following macro, copied from
the
Microsoft project management database!

OpenForm Employee Details, Form, ,="[ID]=" & [ID], ,Dialog
SetTempVar CurrentID, [ID]
Requery
SearchForRecord , , First, ="[ID]=" & [TempVars]![CurrentID]

What is this macro doing? I have looked at the online macro explanations
in
Access but it does not give good examples there.

Thanks
AC Erdal
Click to expand...
Click to expand...
 
AC,

It is difficult to see how this relates to your original question.

Retrieving the data you are looking for would normally be done via a Query.
It is not related to a macro.

For example, for #1, you would make a query, which includes the
tblProperties table, plus the tblTransactions table, appropriately Joined on
the ID-Properties field. And then use the applicable Criteria (probably in
the ID-Properties field) to return those transactions associated with this
property.

In the case of Forms, then it is common to use a form (continuous view in
most cases) based on the table on the "many" side of a one-to-many
relationship, and put this as a subform on the form that is based on the
"one" side table. So, in terms of your example, you might have a form based
on the tblProperties table, and on this you place a subform based on the
tblTransactions table. Setting the Link Master Fields and Lick Child Fields
properties of the subform to ID-Properties will ensure that the subform only
shows the transactions related to the main form's current property.

By the way, as an aside, it is not a good idea to use a - as part of the
name of a field.
 
AC,

It is difficult to see how this relates to your original question.

Retrieving the data you are looking for would normally be done via a Query.
It is not related to a macro.

For example, for #1, you would make a query, which includes the
tblProperties table, plus the tblTransactions table, appropriately Joined on
the ID-Properties field. And then use the applicable Criteria (probably in
the ID-Properties field) to return those transactions associated with this
property.

In the case of Forms, then it is common to use a form (continuous view in
most cases) based on the table on the "many" side of a one-to-many
relationship, and put this as a subform on the form that is based on the
"one" side table. So, in terms of your example, you might have a form based
on the tblProperties table, and on this you place a subform based on the
tblTransactions table. Setting the Link Master Fields and Lick Child Fields
properties of the subform to ID-Properties will ensure that the subform only
shows the transactions related to the main form's current property.

By the way, as an aside, it is not a good idea to use a - as part of the
name of a field.
 
Steve,
The relation is I believe this is what the code from Microsoft Project
database is trying to do! Basically, accessing employee data based on "ID"
field.

You wrote:
"Setting the Link Master Fields and Lick Child Fields
properties of the subform to ID-Properties will ensure that the subform only
shows the transactions related to the main form's current property."

What do you mean by Master field and Child field? Can you explain how do you
do the linking!

Thanks,
AC
 
Steve,
The relation is I believe this is what the code from Microsoft Project
database is trying to do! Basically, accessing employee data based on "ID"
field.

You wrote:
"Setting the Link Master Fields and Lick Child Fields
properties of the subform to ID-Properties will ensure that the subform only
shows the transactions related to the main form's current property."

What do you mean by Master field and Child field? Can you explain how do you
do the linking!

Thanks,
AC
 
AC,

In your example, there is a one-to-many relationship between tblProperties
and tblTransaction. That is, based on the structure of your tables, for
each Property, there can be more than one associated Transaction. Right?

Now, this can be modelled at the Form level by a Transaction subform on a
Property form.

Then, if you look at the Properties (not to be confused with the properties
in your table!) of the subform, you will see it has a Link Master Fields
property and a Link Child Fields property. And in this property sheet for
the subform, if you enter the name of the field that is the basis of the
relationship between the main form's reocrd source and the subform record
source, which in this example is the ID-Properties field, then the subform
will automatically manage its data according to the relationship with the
main form records.

Not sure if that makes it any clearer... get back if you still don't
understand.
 
AC,

In your example, there is a one-to-many relationship between tblProperties
and tblTransaction. That is, based on the structure of your tables, for
each Property, there can be more than one associated Transaction. Right?

Now, this can be modelled at the Form level by a Transaction subform on a
Property form.

Then, if you look at the Properties (not to be confused with the properties
in your table!) of the subform, you will see it has a Link Master Fields
property and a Link Child Fields property. And in this property sheet for
the subform, if you enter the name of the field that is the basis of the
relationship between the main form's reocrd source and the subform record
source, which in this example is the ID-Properties field, then the subform
will automatically manage its data according to the relationship with the
main form records.

Not sure if that makes it any clearer... get back if you still don't
understand.
 
Steve,
I did post a reply to you but I do not see it in the system! So I am
reposting again.

With your solution, seems like I can display those fields using subform. How
about if I want to compare one of the fields like "seller name" with another
field that the user might enter. How do I do that? See the original database
setup below;

tblResourceType
1 Seller

tblTransaction (this table contains all transaction information)
ID-Transaction
ID-Seller
ID-Properties
Transaction Status

tblSeller
ID-Seller
Seller Name
Sale Date

tblProperties
ID-Properties
Property Address

Lets I have the "Property Address" and "ID-Properties" from tblProperties
table;

How can I get the "Seller Name" from tblSeller, for comparison?

Thanks,
AC Erdal

______________________________
 
Steve,
I did post a reply to you but I do not see it in the system! So I am
reposting again.

With your solution, seems like I can display those fields using subform. How
about if I want to compare one of the fields like "seller name" with another
field that the user might enter. How do I do that? See the original database
setup below;

tblResourceType
1 Seller

tblTransaction (this table contains all transaction information)
ID-Transaction
ID-Seller
ID-Properties
Transaction Status

tblSeller
ID-Seller
Seller Name
Sale Date

tblProperties
ID-Properties
Property Address

Lets I have the "Property Address" and "ID-Properties" from tblProperties
table;

How can I get the "Seller Name" from tblSeller, for comparison?

Thanks,
AC Erdal

______________________________
 
Back
Top