Subform cmdButton to Linked Form (?)

[tbl]

In this situation, I could have made nested subforms, but
that doesn't seem as clean to me as a "standard"
form/subform pair with a linked (popup) form fired from a
command button on the subform.

I really don't have the skills I should have to do this, so
I did this:

I created the form subform, and popup form. Then, to the
subform, I added the command button as the last item in each
row (continuous form; this isn't exactly the cleanest
approach either, but I'm working with limited neurons,
here).

Assuming that the built-in form wizard would be a safer bet
than my own conjurings, I used the wizard to make a pair of
dummy, linked forms, to use as code samples. Then I copied
all of their code to my real forms, and substituted the
proper (I think) object names to finish off the task.

These are intended to be data-entry/edit forms, so when I
click on the button in the subform to load and display the
popup, I'd like to be able to add new records in the popup,
*or* be able to view/edit existing, related links.

After two days of "try this", I'm pretty well out of the fun
zone.

It seems that the filter just isn't working: if the current
subform record doesn't already have an associated popup
record, when I click the button on that row, the popup
displays whatever the first record is in that table, even
tho the Id's don't match. Immediately switching to design
view (popup), the properties show no filter info. I've
studied the filter code 'till I'm no longer able to remember
my favorite swear words, but can't seem to get lucky.

If anyone more lucid would care to chip in, it just might
save me from a visit to the luny bin.

 

[Steve Schapel]

Tbl,

Well, whereas your general description is good, you haven't really given
us much to go on as regards the specifics. So I am shooting in the dark
here. But in my experience, the most common scenario would be:
- the subform has a field that uniquely identifies each record,
probably the primary key field from the subform's underlying table.
- the record source of the "popup" form includes a field related to
the subform's ID field.
- the skeleton of the code behind the command button on the subform
will look something like this...

If DCount("*","PopupTable","[ID]=" & Me.ID) > 0 Then
DoCmd.OpenForm "Popup", , , "[ID]=" & Me.ID
Else
MsgBox "No matching records"
End If

If you want to enter new records via the "popup" form, you will need to
provide for the foreign key value. This will either be a direct data
entry requirement, or else a Default Value setting (I guess this would
assume the popup form is always opened via the other form), or code on
the Before Insert event.

Does that help?

 

[Wayne-I-M]

Hi

Not really sure I understand the problem but I "think"?? you are trying to
open a popup form related to a selection from a nested subform. If I am wrong
please let me know.

Try this on your subform

Private Sub Something_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "PopUpFormName"
stLinkCriteria = "[LinkedFieldOnPopup]=" & Me![LinkedFieldOnSubForm]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub


Something_Click() could be an AfterUpdate or whatever - just used for example

 

[tbl]

Thanks for the reply, Wayne.

My replies inline...

Hi

Not really sure I understand the problem but I "think"?? you are trying to
open a popup form related to a selection from a nested subform. If I am wrong
please let me know.

Very close... Opening a popup related to a record selection
on a subform (the word "nested" probably doesn't apply
here--just a form and subform situation).

Try this on your subform

Private Sub Something_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "PopUpFormName"
stLinkCriteria = "[LinkedFieldOnPopup]=" & Me![LinkedFieldOnSubForm]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

That's the technique I've been trying, but I must be using
an improper syntax for the stLinkCriteria line.

My popup opens fine, but the Id from the subform isn't
flowing in--it just puts the Id from the first subform
record, no matter what the real Id should be.

Something_Click() could be an AfterUpdate or whatever - just used for example

I've been triggering from a cmdButton that repeats on each
row in the subform.

I'm assuming that once I get this working, it'll only be
good for one entry on the popup. I'll have to put some code
behind the popup to make it grab the current subform record
Id if I'm adding a second (or more) record to the popup and
want to keep them related to the subform.

 

[tbl]

Thanks, Steve.

My replies inline...

Tbl,

Well, whereas your general description is good, you haven't really given
us much to go on as regards the specifics. So I am shooting in the dark
here. But in my experience, the most common scenario would be:
- the subform has a field that uniquely identifies each record,
probably the primary key field from the subform's underlying table.
- the record source of the "popup" form includes a field related to
the subform's ID field.
- the skeleton of the code behind the command button on the subform
will look something like this...

If DCount("*","PopupTable","[ID]=" & Me.ID) > 0 Then
DoCmd.OpenForm "Popup", , , "[ID]=" & Me.ID
Else
MsgBox "No matching records"
End If

That's a little different approach to what I've been trying
(see my reply to Wayne), but certainly looks interesting.

If you want to enter new records via the "popup" form, you will need to
provide for the foreign key value. This will either be a direct data
entry requirement, or else a Default Value setting (I guess this would
assume the popup form is always opened via the other form), or code on
the Before Insert event.

Does that help?

Yes, I think it does.

 

[tbl]

Very close... Opening a popup related to a record selection
on a subform (the word "nested" probably doesn't apply
here--just a form and subform situation).

I should have mentioned that the linking field names in the
tables are both "IvDetId", and the controls on the forms are
both named "txtIvDetId".

 

[Steve Schapel]

Tbl,

I'm assuming that once I get this working, it'll only be
good for one entry on the popup. I'll have to put some code
behind the popup to make it grab the current subform record
Id if I'm adding a second (or more) record to the popup and
want to keep them related to the subform.

Nope. That's not right. The apporoach you are using is based on the
assumption that the Record Source of the "popup" already includes at
least one record with a ID matching the ID on the launching subform. If
it in fact already includes more than one matching record, it will take
you to the first of them, and presumably you will be able to scroll
through them on the popup. If the popup form's Record Source does *not*
already contain a matching record, then what you are doing won't work.
If you are hoping that somehow what you are doing will allow the entry
of a new record in the popup form automatically with the ID matching the
subform's ID, then no, as I said in my initial reply, this will not happen.

 

[tbl]

Tbl,


Nope. That's not right. The apporoach you are using is based on the
assumption that the Record Source of the "popup" already includes at
least one record with a ID matching the ID on the launching subform. If
it in fact already includes more than one matching record, it will take
you to the first of them, and presumably you will be able to scroll
through them on the popup. If the popup form's Record Source does *not*
already contain a matching record, then what you are doing won't work.
If you are hoping that somehow what you are doing will allow the entry
of a new record in the popup form automatically with the ID matching the
subform's ID, then no, as I said in my initial reply, this will not happen.

I guess where I got off-track was in thinking that linked
forms would function much the same as form/subform, where
it's rather seemless when switching between viewing/editing
existing subform records and creating new subform records.

That's the functionality I was after with my linked forms.

 

[Steve Schapel]

Tbl,

The functionality you are talking about is handled by Access via the
Link Master Fields and Link Child Fields properties of a subform.

A separate form, opened in its own right, has no such
connection/relationship with any other form that happens to be open at
the same time. Can't be done.

Therefore, you have to explicitly manage all data entered via a separate
form. In your example, if you want the ID field of a new record on your
"popup" form to be entered with the value of the ID field of the calling
form's current record, you can use code to enter it. Probably the
Before Insert event of the popup form would be the most appropriate
place for this.

 

[tbl]

Tbl,

The functionality you are talking about is handled by Access via the
Link Master Fields and Link Child Fields properties of a subform.

A separate form, opened in its own right, has no such
connection/relationship with any other form that happens to be open at
the same time. Can't be done.

Therefore, you have to explicitly manage all data entered via a separate
form. In your example, if you want the ID field of a new record on your
"popup" form to be entered with the value of the ID field of the calling
form's current record, you can use code to enter it. Probably the
Before Insert event of the popup form would be the most appropriate
place for this.

Thanks, Steve. I'm slowly gaining traction, and I now have
some working forms (with your help).

 

[Steve Schapel]

Very good, Tbl. Best wishes for the rest of your project.