Button to open sub-form and follow main

[MikeF]

On Form1, need a button that will open Form2, link Form2's formID field to
Form1's formID field, and follow Form1 as the records are scrolled.

Form2 being much like a sub-form, only not embedded into Form1 - just pops
up and "follows" when necessary.

A simple code routine would be sincerely appreciated.

Thank you,
- Mike

 

[Jack Leach]

This can probably be done, but I don't think it would be an easy task.
There's a number of things to keep in mind.

You could probably set this up by setting the recordsource for form2 on the
OnCurrent event of form 1. So every time a record is changed in form 1, form
2's recordsource would be updated and linked to the current ID from form 1.

In order to do this right though, you should have some verification in there
that form 2 is open before you go trying to change anything on it, make sure
that if form 1 is closed, form 2 is closed as well, etc. This is all stuff
that's obviously handled internally from access, but when the code is all
done you might see some slow performance from doing it all through vba.

Also, when adding new records to form 2 you will have to manually set the
fields that link the two, rather than relying on access to do it for you as
in a normal 1 to many form/subform relationship.

Are you sure that there's no way to accomplish this using a subform, perhaps
on a different tab that only is visible when the user clicks the button?

--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[MikeF]

Jack,

Perhaps you're reading more into this than is intended.

Form1 is merely a summary form.
Form 2 contains the details.

So if Form1 is actually frmCompany - which has a Company name; and Form2 is
actually frmCompanyDetail, which has the company's address/phone/city/etc.

That way I don't have to store all the details in an already-crowded summary
form, but have easy access to them - via a button - if necessary.

There's never the same data in frmCompanyDetails that would need to be
changed in frmCompany.
At least not the ID field.

Is this clearer, or is it what you thought in the first place, and is not an
easy task?

- Mike

 

[Jack Leach]

Is this clearer, or is it what you thought in the first place, and is not an

easy task?

All of the above <g>


That is a bit clearer, and its along the same lines as what I had thought,
and I still don't think it would be a lot of fun, for the same reasons as my
previous post.

The "standard" way to do this would be to open the detail form in Dialog
mode, from either a button or a double click in the summary field(s), let the
user edit the details, and then they close the form and move on. This keeps
things fairly straightfoward.

I don't really see any reason why you couldn't do it the way that you'd like
to, but it will be a lot more work. I've never done it before, and there may
be a few snags I'm not thinking of at the moment, but it seems feasible.

Here's a little quick-start quide:

In the button to open the form, pass the currently selected record's ID as
an openarg (the last argument in the DoCmd.OpenForm method).

Then, in the Open event of the detail form, read the OpenArg (Me.OpenArgs)
to get the ID that you need to link to, and filter the form to show that
record(s).

In the Current event of the main form, you'll want to change the
recordsource of the detail form and requery it. Something along the lines of:

Forms![DetailFormName].Recordsource = _
"SELECT * FROM <table> WHERE [ID] = " & Me.ID
Forms![DetailsFormName].Requery

I'm not positive of that syntax, for example use only

Before trying to change the recordsource, we should make sure that the
detail form is open. I have an IsFormOpen() function buried away somewhere I
can dig up if need be.

Also, it would be prudent to close the detail form if it is opened when the
summary form is opened. This is easily enough accomplished using the
DoCmd.Close method (after making sure that the form is indeed open).


That covers the basics of what I had in mind....


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[MikeF]

Ok, thanx.
Let me rethink.

You see, the summary form contains lots of info.
I wanted to place a couple dozen buttons, so that numerous detail forms -
linked to the main id field - would pop up when required.

OR -- I could open them independently and edit them when required.

Pretty much a master [parent] form with lots of relevant detail [child]
forms, all linked to the main ID field.

- Mike

 

[MikeF]

Jack,
Thought I'd run this by you, slightly different issue.

Re just opening a detail form at the same record as the summary form [forget
the "following" part for now] ...

The following code works on other forms where the ID field is a number.

But my CompanyID field are text -- ie a text abbreviation of the company,
with numbers/etc all separated by a dash/minus sign "-" [without the
quotes] .
Example xxxx09-001-Houston.

When the button is clicked, an error comes up that says the database engine
cannot find xxxx09 as a valid field or expression.

Before I get much further along --- is there an easy remedy for this so I
can keep my CompanyID fields the same naming convention?

Regards,
- Mike

Private Sub btnCompanyDetailsForm_Click()

On Error GoTo Err_btnCompanyDetailsForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompanyDetails"

stLinkCriteria = "[CompanyID]=" & Me![CompanyID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnCompanyDetailsForm_Click:
Exit Sub

Err_btnCompanyDetailsForm_Click:
MsgBox Err.Description
Resume Exit_btnVenuesForm_Click


End Sub

Is this clearer, or is it what you thought in the first place, and is not an
easy task?

All of the above <g>


That is a bit clearer, and its along the same lines as what I had thought,
and I still don't think it would be a lot of fun, for the same reasons as my
previous post.

The "standard" way to do this would be to open the detail form in Dialog
mode, from either a button or a double click in the summary field(s), let the
user edit the details, and then they close the form and move on. This keeps
things fairly straightfoward.

I don't really see any reason why you couldn't do it the way that you'd like
to, but it will be a lot more work. I've never done it before, and there may
be a few snags I'm not thinking of at the moment, but it seems feasible.

Here's a little quick-start quide:

In the button to open the form, pass the currently selected record's ID as
an openarg (the last argument in the DoCmd.OpenForm method).

Then, in the Open event of the detail form, read the OpenArg (Me.OpenArgs)
to get the ID that you need to link to, and filter the form to show that
record(s).

In the Current event of the main form, you'll want to change the
recordsource of the detail form and requery it. Something along the lines of:

Forms![DetailFormName].Recordsource = _
"SELECT * FROM <table> WHERE [ID] = " & Me.ID
Forms![DetailsFormName].Requery

I'm not positive of that syntax, for example use only

Before trying to change the recordsource, we should make sure that the
detail form is open. I have an IsFormOpen() function buried away somewhere I
can dig up if need be.

Also, it would be prudent to close the detail form if it is opened when the
summary form is opened. This is easily enough accomplished using the
DoCmd.Close method (after making sure that the form is indeed open).


That covers the basics of what I had in mind....


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Jack,

Perhaps you're reading more into this than is intended.

Form1 is merely a summary form.
Form 2 contains the details.

So if Form1 is actually frmCompany - which has a Company name; and Form2 is
actually frmCompanyDetail, which has the company's address/phone/city/etc.

That way I don't have to store all the details in an already-crowded summary
form, but have easy access to them - via a button - if necessary.

There's never the same data in frmCompanyDetails that would need to be
changed in frmCompany.
At least not the ID field.

Is this clearer, or is it what you thought in the first place, and is not an
easy task?

- Mike

 

[Jack Leach]

stLinkCriteria = "[CompanyID]=" & Me![CompanyID]

Add in quotes to the link surround the value you pass so that it gets read
as a string rather than a number:

stLinkCriteria = "[CompanyID] = """ & Me![CompanyID] & """"

or

stLinkCriteria = "[CompanyID] = '" & Me![CompanyID] & "'"




Regardless of this fix, you may want to consider changing your ID to an
autonumber field anyway. It takes access much much longer to process a
string comparison than a long integer comparison (strings are the second
largest datatype in vba).

If you want a specific format that your users can navigate by
(1234-567-CMPNY) you can use this as a seperate field and index it. Usually
a field of that type of format is a contentation of other fields (1234 is a
field that has specific meaning, 567 a separate field, and CMPNY a third).
Then you use the complete format as a calculated field (and un-calculate it
to search for records when the user inputs the format).

Anyway, it's a somewhat disputed subject, some people always use
autonumbers, others don't unless they absolutely have to. That should help
out whichever way you go with it though.


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[MikeF]

Thanx.
I've been thinking about numbering the ID fields, but they come from Excel,
where for a long time they've made sense to numerous people.

Am already pushing the structured sense of Access on people that don't want
it.
That might put them over the edge!
.... Pretty sure you've been there ...

- Mike

stLinkCriteria = "[CompanyID]=" & Me![CompanyID]

Add in quotes to the link surround the value you pass so that it gets read
as a string rather than a number:

stLinkCriteria = "[CompanyID] = """ & Me![CompanyID] & """"

or

stLinkCriteria = "[CompanyID] = '" & Me![CompanyID] & "'"




Regardless of this fix, you may want to consider changing your ID to an
autonumber field anyway. It takes access much much longer to process a
string comparison than a long integer comparison (strings are the second
largest datatype in vba).

If you want a specific format that your users can navigate by
(1234-567-CMPNY) you can use this as a seperate field and index it. Usually
a field of that type of format is a contentation of other fields (1234 is a
field that has specific meaning, 567 a separate field, and CMPNY a third).
Then you use the complete format as a calculated field (and un-calculate it
to search for records when the user inputs the format).

Anyway, it's a somewhat disputed subject, some people always use
autonumbers, others don't unless they absolutely have to. That should help
out whichever way you go with it though.


--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

Jack,
Thought I'd run this by you, slightly different issue.

Re just opening a detail form at the same record as the summary form [forget
the "following" part for now] ...

The following code works on other forms where the ID field is a number.

But my CompanyID field are text -- ie a text abbreviation of the company,
with numbers/etc all separated by a dash/minus sign "-" [without the
quotes] .
Example xxxx09-001-Houston.

When the button is clicked, an error comes up that says the database engine
cannot find xxxx09 as a valid field or expression.

Before I get much further along --- is there an easy remedy for this so I
can keep my CompanyID fields the same naming convention?

Regards,
- Mike

Private Sub btnCompanyDetailsForm_Click()

On Error GoTo Err_btnCompanyDetailsForm_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCompanyDetails"

stLinkCriteria = "[CompanyID]=" & Me![CompanyID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_btnCompanyDetailsForm_Click:
Exit Sub

Err_btnCompanyDetailsForm_Click:
MsgBox Err.Description
Resume Exit_btnVenuesForm_Click


End Sub