Form with linked criteria - way to have not linked if null?

[Guest]

It worked!!!! Thank you so much! that was exactly what I wanted. I very much
appreciate your help - have a very Merry Christmas!

Heidi

Hi Heidi

Do you mean that if the DinnerID in cmbfkDinnerID has any corresponding
records in tblAttendancePresenter then you want to open the form
frmEntrepreneurAttendance with those records listed, but if there are no
matching records you want to open frmEntrepreneurAttendance whowing ALL the
records, whether they match the combo or not?

If so, then try this:

stDocName = "frmEntrepreneurAttendance"
stLinkCriteria = "[fkPresentDinnerID]=" & Me![cmbfkDinnerID]
If DCount( "*", "tblAttendancePresenter", stLinkCriteria ) = 0 Then
DoCmd.OpenForm stDocName
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

If that's not what you want, and the database is not too large, email me a
zipped copy at graham-at-mvps-dot-org. I wouldn't normally invite someone
to do this, but we seem to be going around in circles

Oh, and I will be going away on my holidays tomorrow and will be travelling,
so I might not get back online for more than 24 hours from now.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm sorry I'm not being so clear on this... If it makes it easier, i can
email you the db, just let me know...

Ok.....the main form is bound but it's not bound to the same table -
frmAttendance is bound to tblAttendance, frmEntrepreneurAttendance is
bound
to 2 joined tables, tblAttendancePresenter and tblEntrepreneur. Yes, the
frmAtt has the combobox w/just a list of dates from tblDinnerDates,
containing an ID (hidden in the combo) and a date.

Here is the detailed info on the sources:

frmAttendance w/source tblAttendance, fields below:
-pkAttendanceID
-fkDinnerID (to join w/the Dinner ID in the tblDinnerDates)
-Name (contains full name of investor/angel attendee)
-fkAngelID (ID to join with the ID in tblAngel containing investor info)
-fkSubID (ID to join with the ID in tblSub - holds any subs for the angel
investor)
Form contains combo box sourced to tblDinnerDates (explained above). This
form contains a few list boxes for attendance but then a command button to
add presenters to the attendance.

frmEntrepreneurAttendance w/source tblAttendancePresenter joined to
tblEntrepreneur (1st tbl field: fkEntrepreneurID joined to 2nd tbl
EntrepreneurID), only listing pertinent fields because there are too many
and
not many used:
-EntrepreneurID
-Company (company name, criteria: is not null)
-PresenterCount (user defined entry - how many presenters are attending
for
this company)
-fkPresentDinnerID ("gets" the ID of the dinner date when command button
on
this form is clicked - activates action qry)
Combo box on this form containing EntrepreneurID and Company from
tblEntrepreneur. When company is selected, the linked subform shows
specific
company information (linked on EntrepreneurID).

Do you need anything more?

Thanks again, I appreciate your time!!
Heidi

Hi Heidi

Inline...

Ok, w/the first one - changing the If statement, it returns an OnClick
error:
the Variable is not defined.

This is what I first suspected (that EntrepreneurID is not defined),
which
is why I asked if you had Option Explicit.

I cannot understand though, why
If Not IsNull(EntrepreneurID) Then
should compile OK, while
If Len(EntrepreneurID & "") > 0 Then
does not.

I feel I'm not getting all the information here.

As for the others - I'm not sure I know how I can do this.... The main
form
(frmAttendance) just has a command button that opens up the 2nd form
(frmEntrepreneurAttendance). How do I get to be "on" a record when it's
not
designated to choose a certain record? But then, maybe the first test
negates
the second?

So you're saying that the first form is unbound (has no recordset)? I
have
been labouring under the misapprehension that your first form has a
control
named EntrepreneurID (or at least a field in its RecordSource of that
name).
But it just has a combobox with a list of dates, right? Where do those
dates come from?

How about you give me a comprehensive description of your table
structures
and their relationships, and what is on the two forms - what is the
RecordSource and what is the ControlSource of the controls, and the
RowSource of any comboboxes.

With any luck we'll get this nailed by Christmas
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Here's what I'm thinking and tell me if I'm totally off because even if
I'm
right, I still don't know how to fix it......The frmAttendance has the
command button w/the code to pull records if some value in
EntrpreneurID
exists... but here's the thing - where in the code is it saying the
EntrepreneurID is in the frmEntrepreneurAttendance and not in
frmAttendance?
In other words, it may be a null value because it literally doesn't
exist
as
far as the code is concerned because I'm not pointing it to the other
form...
But if thta is true, how do i fix that? Or am I making this more
difficult
than it really is?

 

[Graham Mandeno]

Phew! Thank the Birthday Boy for that!!

And a very Merry Christmas to you too
--
Glad to have been of help.

Graham Mandeno [Access MVP]
Auckland, New Zealand

It worked!!!! Thank you so much! that was exactly what I wanted. I very
much
appreciate your help - have a very Merry Christmas!

Heidi

Hi Heidi

Do you mean that if the DinnerID in cmbfkDinnerID has any corresponding
records in tblAttendancePresenter then you want to open the form
frmEntrepreneurAttendance with those records listed, but if there are no
matching records you want to open frmEntrepreneurAttendance whowing ALL
the
records, whether they match the combo or not?

If so, then try this:

stDocName = "frmEntrepreneurAttendance"
stLinkCriteria = "[fkPresentDinnerID]=" & Me![cmbfkDinnerID]
If DCount( "*", "tblAttendancePresenter", stLinkCriteria ) = 0 Then
DoCmd.OpenForm stDocName
Else
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

If that's not what you want, and the database is not too large, email me
a
zipped copy at graham-at-mvps-dot-org. I wouldn't normally invite
someone
to do this, but we seem to be going around in circles

Oh, and I will be going away on my holidays tomorrow and will be
travelling,
so I might not get back online for more than 24 hours from now.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm sorry I'm not being so clear on this... If it makes it easier, i
can
email you the db, just let me know...

Ok.....the main form is bound but it's not bound to the same table -
frmAttendance is bound to tblAttendance, frmEntrepreneurAttendance is
bound
to 2 joined tables, tblAttendancePresenter and tblEntrepreneur. Yes,
the
frmAtt has the combobox w/just a list of dates from tblDinnerDates,
containing an ID (hidden in the combo) and a date.

Here is the detailed info on the sources:

frmAttendance w/source tblAttendance, fields below:
-pkAttendanceID
-fkDinnerID (to join w/the Dinner ID in the tblDinnerDates)
-Name (contains full name of investor/angel attendee)
-fkAngelID (ID to join with the ID in tblAngel containing investor
info)
-fkSubID (ID to join with the ID in tblSub - holds any subs for the
angel
investor)
Form contains combo box sourced to tblDinnerDates (explained above).
This
form contains a few list boxes for attendance but then a command button
to
add presenters to the attendance.

frmEntrepreneurAttendance w/source tblAttendancePresenter joined to
tblEntrepreneur (1st tbl field: fkEntrepreneurID joined to 2nd tbl
EntrepreneurID), only listing pertinent fields because there are too
many
and
not many used:
-EntrepreneurID
-Company (company name, criteria: is not null)
-PresenterCount (user defined entry - how many presenters are attending
for
this company)
-fkPresentDinnerID ("gets" the ID of the dinner date when command
button
on
this form is clicked - activates action qry)
Combo box on this form containing EntrepreneurID and Company from
tblEntrepreneur. When company is selected, the linked subform shows
specific
company information (linked on EntrepreneurID).

Do you need anything more?

Thanks again, I appreciate your time!!
Heidi




:

Hi Heidi

Inline...

Ok, w/the first one - changing the If statement, it returns an
OnClick
error:
the Variable is not defined.

This is what I first suspected (that EntrepreneurID is not defined),
which
is why I asked if you had Option Explicit.

I cannot understand though, why
If Not IsNull(EntrepreneurID) Then
should compile OK, while
If Len(EntrepreneurID & "") > 0 Then
does not.

I feel I'm not getting all the information here.

As for the others - I'm not sure I know how I can do this.... The
main
form
(frmAttendance) just has a command button that opens up the 2nd form
(frmEntrepreneurAttendance). How do I get to be "on" a record when
it's
not
designated to choose a certain record? But then, maybe the first
test
negates
the second?

So you're saying that the first form is unbound (has no recordset)?
I
have
been labouring under the misapprehension that your first form has a
control
named EntrepreneurID (or at least a field in its RecordSource of that
name).
But it just has a combobox with a list of dates, right? Where do
those
dates come from?

How about you give me a comprehensive description of your table
structures
and their relationships, and what is on the two forms - what is the
RecordSource and what is the ControlSource of the controls, and the
RowSource of any comboboxes.

With any luck we'll get this nailed by Christmas
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

Here's what I'm thinking and tell me if I'm totally off because even
if
I'm
right, I still don't know how to fix it......The frmAttendance has
the
command button w/the code to pull records if some value in
EntrpreneurID
exists... but here's the thing - where in the code is it saying the
EntrepreneurID is in the frmEntrepreneurAttendance and not in
frmAttendance?
In other words, it may be a null value because it literally doesn't
exist
as
far as the code is concerned because I'm not pointing it to the
other
form...
But if thta is true, how do i fix that? Or am I making this more
difficult
than it really is?