Problem getting a form to open to a related record.

[firecop1]

I have a button on a form called "MainForm" that when clicked I want to open
another form which should show all "Rental" records relating to the location
on the "MainForm"

In my MainTable which is what the MainForm" is populated by I have a field
called ListingID (which is also my keyfield and as such is an AutoNumber).

In my "Rental" table I have a field called "PremID" which is a number field
that I want to use to tie the records into the MainTable.
(I will be using the same field name in other tables to link them to the
MainTable as well).

I am confused as to how and where to setup whatever needs to be done to open
the Rental form filtered to the record (or if there are no records at a new
record). I tried when creating the button using the macro to choose form
operation then open form then chose my Rental form then "open the form and
find specific data to display" but when it gets to the select fields that
contain matching data I can chhose a field from the Rental table but nothing
appears under the main table (no fields whatsoever).

Perhaps I need another field in my main table to use as a linking field?

Any help would surely be appreciated here and as always thanks in advance
for your help.

Andrew J. Brofman
Chief Fire Marshal
Lake Grove, NY

 

[jahoobob via AccessMonster.com]

You will probably be best served if you show your Rental records in a subform
(Continuous) on the MainForm (Single.) When you place the subform in the
MainForm, select the subform objects Properties and then select "Link Child
Fields" then select the ellipsis (...) at the end of the blank. You will be
shown a form from which you can select ListingID as the parent and PremID as
the child.
Once this is done, whatever records you enter in the subform will be related
to the record on the MainForm and whatever record you view on the Main will
show all the rental records.

 

[firecop1]

I thought about doing this but I already have a continuous subform on the
main form that lits all activities for the location in the specified
records. As I mainly work off a laptop, I dont have the real estate to see
another subform and the rental data only applies to a select number of
locations so it is better suited out of the way. Finally I am going to have
a number of other forms as the databasegrows that will be linked in the same
fashion.

Now if a subform can be hidden until a button is pressed than I guess that
could work but again, real estate is an issue.

 

[jahoobob via AccessMonster.com]

Okay. Make your new form a popup so that you can leave your MainForm open
and the new one will show up in front of it. Place this code in the On Click
event of the button to open the new form:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "nameofRentalsform"
stLinkCriteria = "[PremID]=" & Me![ListingID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

I thought about doing this but I already have a continuous subform on the
main form that lits all activities for the location in the specified
records. As I mainly work off a laptop, I dont have the real estate to see
another subform and the rental data only applies to a select number of
locations so it is better suited out of the way. Finally I am going to have
a number of other forms as the databasegrows that will be linked in the same
fashion.

Now if a subform can be hidden until a button is pressed than I guess that
could work but again, real estate is an issue.

You will probably be best served if you show your Rental records in a
subform

[quoted text clipped - 46 lines]

 

[firecop1]

Worked like a charm......
Short and sweet-no where near as difficult as I would have expected it to
be.

Thanks again.

Andy

Okay. Make your new form a popup so that you can leave your MainForm open
and the new one will show up in front of it. Place this code in the On
Click
event of the button to open the new form:
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "nameofRentalsform"
stLinkCriteria = "[PremID]=" & Me![ListingID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

I thought about doing this but I already have a continuous subform on the
main form that lits all activities for the location in the specified
records. As I mainly work off a laptop, I dont have the real estate to see
another subform and the rental data only applies to a select number of
locations so it is better suited out of the way. Finally I am going to
have
a number of other forms as the databasegrows that will be linked in the
same
fashion.

Now if a subform can be hidden until a button is pressed than I guess that
could work but again, real estate is an issue.

You will probably be best served if you show your Rental records in a
subform

[quoted text clipped - 46 lines]

Chief Fire Marshal
Lake Grove, NY

 

[missinglinq via AccessMonster.com]

Hey, Chief!

Glad you got your problem solved while I was away. Just a couple of thoughts,
since I know you're new at this, and you mentioned having a problem with
"real estate!"

I, too, work primarily in apps that use up a lot of real estate, and have a
couple of basic strategies I use. The first involves the use of tabbed pages.
Tabbed pages have the advantage of allowing essentially unlimited numbers of
controls without undue crowding, and at the same time allow information to be
grouped logically. One tab, for instance, could have demographic and contact
information for a manufacturing facility, another information on the location
of emergency equipment, standpipes, etc, and another yet a record of
inspections of the facility. And all of these tabbed pages are actually one
form, with the controls on them referenced as if they were all on one "screen.
" Or you can place subforms on some of the pages. If you ever try to go this
route and have problems, let us know. They're quite handy, and you only have
to know a few things to make them work for you.

The second strategy I use involves magic! Which is to say making things
appear and disappear! You could actually layer several subforms on top of
each other, and by controlling the .Visible property, make only one of them
show at a time, thus saving real estate!

Linq
(e-mail address removed)

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[firecop1]

You know, I have seen the Tabs used in Filemaker but did not know that it
could easily be done in Access. That would definately solve my real estate
problems and keep things simple. I just have to figure out where and how to
set it up.

Are there any examples freely floating around that you know of ?
Or is there a tab wizard in Access 2007?

AS far as the layering of subforms-that sounds WAY out of my league but
again, would keep things to "one" form which I like.

 

[missinglinq via AccessMonster.com]

Hey, Chief!
It's really a breeze, just one or two "gotta know" things. I posted a very
short how-to on another forum and they included it in their tutorials section.
You might want to have a look at some of the other stuff there, too! Scroll
down and look for "Using Tabbed Pages on a Form."

http://www.thescripts.com/forum/thread632608.html

Have a great (and safe) 4th!

Linq

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000

Message posted via AccessMonster.com

 

[firecop1]

I am reading through it now and I like the concept....

I appreciate all of your help you are truly an Access Master LOL...

Safe holiday to you as well..I am sure I will be busy!!

Andy