Display a specific record by clicking an open form button-not work

[Tammy]

Hello -

This problem may be due to the relationship type. I have a property form
(frmProperties) with all the general property info, and the record source =
tblProperties.

There is another form called frmBuildings - record source = tblBuildings.

There is a relationship established between tblProperties and tblBuildings.
It is a one-to-many relationship: one property can have many buildings
associated with it.

The two tables are linked on the field called strPropertyNum.

What I'd like to do on the property form is have a button that takes a user
directly to the building form, and display the buildings for that property.
It doesn't necessarily have to filter out the records, just bring me to the
first record (or, building) for that property.

I am using the Command Wizard to create a button to open a form and chose to
open the form and find specific data to display. I matched the fields on
strPropertyNum.

The button takes me to the form, but does not go directly to the matching
property number. It just brings me to the first record on the form.

I tried a macro, as well, but the macro opens the building form with the
form filtered to a new record - even though the Where Condition was set to:
[strPropertyNum]=[Forms]![frmBuilding]![strPropertyNum]

Can this not be done for a one-to-many relationship?

Without VBA, is there a way to click a button that brings a user to a form
with the records related to the specific property ? Can I bring the user to
the first record (building number) associated with the property, or to a
filtered set of buldings based on the property number? I can use the button
either way.

Thanks for any suggestions - if possible, no VBA solutions since I am
unfamiliar with that window. (But I take direction well, if that is the only
solution.)
Thanks!

 

[Jeanette Cunningham]

Tammy,
usually it is possible to do this if you build both forms at the same time
using the forms wizard.
The forms wizard presents a screen with 3 options, (after you have chosen
the tables, fields).
Choose the one that says something about finding a record based on a value
in a combo or similar.


Jeanette Cunningham -- Melbourne Victoria Australia

 

[banem2]

Hello -

This problem may be due to the relationship type. I have a property form
(frmProperties) with all the general property info, and the record source =
tblProperties.

There is another form called frmBuildings - record source = tblBuildings..

There is a relationship established between tblProperties and tblBuildings..
It is a one-to-many relationship: one property can have many buildings
associated with it.

The two tables are linked on the field called strPropertyNum.

What I'd like to do on the property form is have a button that takes a user
directly to the building form, and display the buildings for that property..
It doesn't necessarily have to filter out the records, just bring me to the
first record (or, building) for that property.

I am using the Command Wizard to create a button to open a form and chose to
open the form and find specific data to display. I matched the fields on
strPropertyNum.

The button takes me to the form, but does not go directly to the matching
property number. It just brings me to the first record on the form.

I tried a macro, as well, but the macro opens the building form with the
form filtered to a new record - even though the Where Condition was set to:
[strPropertyNum]=[Forms]![frmBuilding]![strPropertyNum]

Can this not be done for a one-to-many relationship?

Without VBA, is there a way to click a button that brings a user to a form
with the records related to the specific property ? Can I bring the user to
the first record (building number) associated with the property, or to a
filtered set of buldings based on the property number? I can use the button
either way.

Thanks for any suggestions - if possible, no VBA solutions since I am
unfamiliar with that window. (But I take direction well, if that is the only
solution.)  
Thanks!

Actually its easy with VBA. Create new button and close Wizard. Right
click button, click Properties and find event OnClick (Event tab).
Choose [Event Procedure] there and click "..." button. Put this line
of code there:

DoCmd.OpenForm "frmBuildings", , , _
"[strPropertyNum] = " & _
strPropertyNum

Make sure the form "frmBuildings" has not set DataEntry = Yes,
otherwise it will always open in mode for adding new data.

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[Tammy]

Hi Branislav,

Thanks so much for responding to my post. I added the code you sent, but am
getting a parameter dialog box and have to enter the property number. After I
enter a property number, I am taken to a filtered list of that property's
buildings. Is it possible to be taken to the buildings for that property
automatically, instead of having to answer a prompt?

Thanks again for your help!
Tammy

Hello -

This problem may be due to the relationship type. I have a property form
(frmProperties) with all the general property info, and the record source =
tblProperties.

There is another form called frmBuildings - record source = tblBuildings..

There is a relationship established between tblProperties and tblBuildings..
It is a one-to-many relationship: one property can have many buildings
associated with it.

The two tables are linked on the field called strPropertyNum.

What I'd like to do on the property form is have a button that takes a user
directly to the building form, and display the buildings for that property..
It doesn't necessarily have to filter out the records, just bring me to the
first record (or, building) for that property.

I am using the Command Wizard to create a button to open a form and chose to
open the form and find specific data to display. I matched the fields on
strPropertyNum.

The button takes me to the form, but does not go directly to the matching
property number. It just brings me to the first record on the form.

I tried a macro, as well, but the macro opens the building form with the
form filtered to a new record - even though the Where Condition was set to:
[strPropertyNum]=[Forms]![frmBuilding]![strPropertyNum]

Can this not be done for a one-to-many relationship?

Without VBA, is there a way to click a button that brings a user to a form
with the records related to the specific property ? Can I bring the user to
the first record (building number) associated with the property, or to a
filtered set of buldings based on the property number? I can use the button
either way.

Thanks for any suggestions - if possible, no VBA solutions since I am
unfamiliar with that window. (But I take direction well, if that is the only
solution.)
Thanks!

Actually its easy with VBA. Create new button and close Wizard. Right
click button, click Properties and find event OnClick (Event tab).
Choose [Event Procedure] there and click "..." button. Put this line
of code there:

DoCmd.OpenForm "frmBuildings", , , _
"[strPropertyNum] = " & _
strPropertyNum

Make sure the form "frmBuildings" has not set DataEntry = Yes,
otherwise it will always open in mode for adding new data.

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[Tammy]

Hi Jeanette,

Thank you for taking the time to answer my post. The forms are already
created, however, and a lot of work went into them. So, starting from scratch
would be a little too time consuming. Thanks again, though, and I will try
your suggestion on future forms. - Tammy

Tammy,
usually it is possible to do this if you build both forms at the same time
using the forms wizard.
The forms wizard presents a screen with 3 options, (after you have chosen
the tables, fields).
Choose the one that says something about finding a record based on a value
in a combo or similar.


Jeanette Cunningham -- Melbourne Victoria Australia

Hello -

This problem may be due to the relationship type. I have a property form
(frmProperties) with all the general property info, and the record source
=
tblProperties.

There is another form called frmBuildings - record source = tblBuildings.

There is a relationship established between tblProperties and
tblBuildings.
It is a one-to-many relationship: one property can have many buildings
associated with it.

The two tables are linked on the field called strPropertyNum.

What I'd like to do on the property form is have a button that takes a
user
directly to the building form, and display the buildings for that
property.
It doesn't necessarily have to filter out the records, just bring me to
the
first record (or, building) for that property.

I am using the Command Wizard to create a button to open a form and chose
to
open the form and find specific data to display. I matched the fields on
strPropertyNum.

The button takes me to the form, but does not go directly to the matching
property number. It just brings me to the first record on the form.

I tried a macro, as well, but the macro opens the building form with the
form filtered to a new record - even though the Where Condition was set
to:
[strPropertyNum]=[Forms]![frmBuilding]![strPropertyNum]

Can this not be done for a one-to-many relationship?

Without VBA, is there a way to click a button that brings a user to a form
with the records related to the specific property ? Can I bring the user
to
the first record (building number) associated with the property, or to a
filtered set of buldings based on the property number? I can use the
button
either way.

Thanks for any suggestions - if possible, no VBA solutions since I am
unfamiliar with that window. (But I take direction well, if that is the
only
solution.)
Thanks!

 

[Jeanette Cunningham]

Tammy,
using access' forms wizard to create the setup will give you the code you
want and you get to see how it can be done.
You just create basic forms using the access wizard - no need to do much
work on the 2 forms - they are just made to learn from.
It takes just 5 minutes to do this.
If you can get it working this way, you can just copy and paste the code to
your 'proper' forms.


Jeanette Cunningham -- Melbourne Victoria Australia

Hi Branislav,

Thanks so much for responding to my post. I added the code you sent, but
am
getting a parameter dialog box and have to enter the property number.
After I
enter a property number, I am taken to a filtered list of that property's
buildings. Is it possible to be taken to the buildings for that property
automatically, instead of having to answer a prompt?

Thanks again for your help!
Tammy

Hello -

This problem may be due to the relationship type. I have a property
form
(frmProperties) with all the general property info, and the record
source =
tblProperties.

There is another form called frmBuildings - record source =
tblBuildings..

There is a relationship established between tblProperties and
tblBuildings..
It is a one-to-many relationship: one property can have many buildings
associated with it.

The two tables are linked on the field called strPropertyNum.

What I'd like to do on the property form is have a button that takes a
user
directly to the building form, and display the buildings for that
property..
It doesn't necessarily have to filter out the records, just bring me to
the
first record (or, building) for that property.

I am using the Command Wizard to create a button to open a form and
chose to
open the form and find specific data to display. I matched the fields
on
strPropertyNum.

The button takes me to the form, but does not go directly to the
matching
property number. It just brings me to the first record on the form.

I tried a macro, as well, but the macro opens the building form with
the
form filtered to a new record - even though the Where Condition was set
to:
[strPropertyNum]=[Forms]![frmBuilding]![strPropertyNum]

Can this not be done for a one-to-many relationship?

Without VBA, is there a way to click a button that brings a user to a
form
with the records related to the specific property ? Can I bring the
user to
the first record (building number) associated with the property, or to
a
filtered set of buldings based on the property number? I can use the
button
either way.

Thanks for any suggestions - if possible, no VBA solutions since I am
unfamiliar with that window. (But I take direction well, if that is the
only
solution.)
Thanks!

Actually its easy with VBA. Create new button and close Wizard. Right
click button, click Properties and find event OnClick (Event tab).
Choose [Event Procedure] there and click "..." button. Put this line
of code there:

DoCmd.OpenForm "frmBuildings", , , _
"[strPropertyNum] = " & _
strPropertyNum

Make sure the form "frmBuildings" has not set DataEntry = Yes,
otherwise it will always open in mode for adding new data.

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[banem2]

Hi Branislav,

Thanks so much for responding to my post. I added the code you sent, but am
getting a parameter dialog box and have to enter the property number. After I
enter a property number, I am taken to a filtered list of that property's
buildings. Is it possible to be taken to the buildings for that property
automatically, instead of having to answer a prompt?

Thanks again for your help!
Tammy

Hello -
This problem may be due to the relationship type. I have a property form
(frmProperties) with all the general property info, and the record source =
tblProperties.
There is another form called frmBuildings - record source = tblBuildings..
There is a relationship established between tblProperties and tblBuildings..
It is a one-to-many relationship: one property can have many buildings
associated with it.
The two tables are linked on the field called strPropertyNum.
What I'd like to do on the property form is have a button that takes auser
directly to the building form, and display the buildings for that property..
It doesn't necessarily have to filter out the records, just bring me to the
first record (or, building) for that property.
I am using the Command Wizard to create a button to open a form and chose to
open the form and find specific data to display. I matched the fields on
strPropertyNum.
The button takes me to the form, but does not go directly to the matching
property number. It just brings me to the first record on the form.
I tried a macro, as well, but the macro opens the building form with the
form filtered to a new record - even though the Where Condition was set to:
[strPropertyNum]=[Forms]![frmBuilding]![strPropertyNum]
Can this not be done for a one-to-many relationship?
Without VBA, is there a way to click a button that brings a user to a form
with the records related to the specific property ? Can I bring the user to
the first record (building number) associated with the property, or toa
filtered set of buldings based on the property number? I can use the button
either way.
Thanks for any suggestions - if possible, no VBA solutions since I am
unfamiliar with that window. (But I take direction well, if that is the only
solution.)  
Thanks!

Actually its easy with VBA. Create new button and close Wizard. Right
click button, click Properties and find event OnClick (Event tab).
Choose [Event Procedure] there and click "..." button. Put this line
of code there:

DoCmd.OpenForm "frmBuildings", , ,  _
  "[strPropertyNum] = " & _
  strPropertyNum

Make sure the form "frmBuildings" has not set DataEntry = Yes,
otherwise it will always open in mode for adding new data.

Regards,
Branislav Mihaljev
Microsoft Access MVP

In:

DoCmd.OpenForm "frmBuildings", , , _
"[strPropertyNum] = " & _
strPropertyNum

replace "strPropertyNum" with the name of field on your form which
holds Property Number.

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[Tammy]

Hi Branislav,

I had a second pair of eyes take a look at this when someone passed by my
desk to make sure I was seeing my fieldnames correctly. My fieldnames were
okay.

I showed him the code in the Event Procedure, and turns out he was somewhat
familiar with this kind of stuff. With a little help from the HELP section,
he changed the code slightly. Here is what finally worked for me:

Private Sub cmdSeePropertyBuildings_Click()
DoCmd.OpenForm "frmBuilding", , , _
"[strPropertyNum] = '" & _
[strPropertyNum] & "'"
End Sub

Thank so much for getting me so far along, I really appreciate all your help
with this.

Almost Friday! Have a great weekend!

Hi Branislav,

Thanks so much for responding to my post. I added the code you sent, but am
getting a parameter dialog box and have to enter the property number. After I
enter a property number, I am taken to a filtered list of that property's
buildings. Is it possible to be taken to the buildings for that property
automatically, instead of having to answer a prompt?

Thanks again for your help!
Tammy

Hello -

This problem may be due to the relationship type. I have a property form
(frmProperties) with all the general property info, and the record source =
tblProperties.

There is another form called frmBuildings - record source = tblBuildings..

There is a relationship established between tblProperties and tblBuildings..
It is a one-to-many relationship: one property can have many buildings
associated with it.

The two tables are linked on the field called strPropertyNum.

What I'd like to do on the property form is have a button that takes a user
directly to the building form, and display the buildings for that property..
It doesn't necessarily have to filter out the records, just bring me to the
first record (or, building) for that property.

I am using the Command Wizard to create a button to open a form and chose to
open the form and find specific data to display. I matched the fields on
strPropertyNum.

The button takes me to the form, but does not go directly to the matching
property number. It just brings me to the first record on the form.

I tried a macro, as well, but the macro opens the building form with the
form filtered to a new record - even though the Where Condition was set to:
[strPropertyNum]=[Forms]![frmBuilding]![strPropertyNum]

Can this not be done for a one-to-many relationship?

Without VBA, is there a way to click a button that brings a user to a form
with the records related to the specific property ? Can I bring the user to
the first record (building number) associated with the property, or to a
filtered set of buldings based on the property number? I can use the button
either way.

Thanks for any suggestions - if possible, no VBA solutions since I am
unfamiliar with that window. (But I take direction well, if that is the only
solution.)
Thanks!

Actually its easy with VBA. Create new button and close Wizard. Right
click button, click Properties and find event OnClick (Event tab).
Choose [Event Procedure] there and click "..." button. Put this line
of code there:

DoCmd.OpenForm "frmBuildings", , , _
"[strPropertyNum] = " & _
strPropertyNum

Make sure the form "frmBuildings" has not set DataEntry = Yes,
otherwise it will always open in mode for adding new data.

Regards,
Branislav Mihaljev
Microsoft Access MVP

In:

DoCmd.OpenForm "frmBuildings", , , _
"[strPropertyNum] = " & _
strPropertyNum

replace "strPropertyNum" with the name of field on your form which
holds Property Number.

Regards,
Branislav Mihaljev
Microsoft Access MVP

 

[banem2]

Hi Branislav,

I had a second pair of eyes take a look at this when someone passed by my
desk to make sure I was seeing my fieldnames correctly. My fieldnames were
okay.

I showed him the code in the Event Procedure, and turns out he was somewhat
familiar with this kind of stuff. With a little help from the HELP section,
he changed the code slightly. Here is what finally worked for me:

Private Sub cmdSeePropertyBuildings_Click()
DoCmd.OpenForm "frmBuilding", , , _
  "[strPropertyNum] = '" & _
  [strPropertyNum] & "'"
End Sub

Thank so much for getting me so far along, I really appreciate all your help
with this.

Almost Friday! Have a  great weekend!

Hi Branislav,
Thanks so much for responding to my post. I added the code you sent, but am
getting a parameter dialog box and have to enter the property number. After I
enter a property number, I am taken to a filtered list of that property's
buildings. Is it possible to be taken to the buildings for that property
automatically, instead of having to answer a prompt?
Thanks again for your help!
Tammy
:
Hello -
This problem may be due to the relationship type. I have a property form
(frmProperties) with all the general property info, and the recordsource =
tblProperties.
There is another form called frmBuildings - record source = tblBuildings..
There is a relationship established between tblProperties and tblBuildings..
It is a one-to-many relationship: one property can have many buildings
associated with it.
The two tables are linked on the field called strPropertyNum.
What I'd like to do on the property form is have a button that takes a user
directly to the building form, and display the buildings for that property..
It doesn't necessarily have to filter out the records, just bring me to the
first record (or, building) for that property.
I am using the Command Wizard to create a button to open a form and chose to
open the form and find specific data to display. I matched the fields on
strPropertyNum.
The button takes me to the form, but does not go directly to the matching
property number. It just brings me to the first record on the form..
I tried a macro, as well, but the macro opens the building form with the
form filtered to a new record - even though the Where Condition was set to:
[strPropertyNum]=[Forms]![frmBuilding]![strPropertyNum]
Can this not be done for a one-to-many relationship?
Without VBA, is there a way to click a button that brings a user to a form
with the records related to the specific property ? Can I bring the user to
the first record (building number) associated with the property, or to a
filtered set of buldings based on the property number? I can use the button
either way.
Thanks for any suggestions - if possible, no VBA solutions since Iam
unfamiliar with that window. (But I take direction well, if that is the only
solution.)  
Thanks!
Actually its easy with VBA. Create new button and close Wizard. Right
click button, click Properties and find event OnClick (Event tab).
Choose [Event Procedure] there and click "..." button. Put this line
of code there:
DoCmd.OpenForm "frmBuildings", , ,  _
  "[strPropertyNum] = " & _
  strPropertyNum
Make sure the form "frmBuildings" has not set DataEntry = Yes,
otherwise it will always open in mode for adding new data.
Regards,
Branislav Mihaljev
Microsoft Access MVP

In:

DoCmd.OpenForm "frmBuildings", , ,  _
  "[strPropertyNum] = " & _
  strPropertyNum

replace "strPropertyNum" with the name of field on your form which
holds Property Number.

Regards,
Branislav Mihaljev
Microsoft Access MVP

I thought you are dealing with strPropertyNum (Num = Number) as
number, although "str" means "string". Usually tables are related by
ID fields which are in most cases numbers.

Regards,
Branislav Mihaljev
Microsoft Access MVP