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

[Heidi]

I have a main form that records attendance with a drop down of dates
available. When I go thru and enter all the regular attendees, i have a
button that will open up a form with available presenters for me to enter as
attending that day (as a presenter). It consists of a drop down that shows
all the company names of the possible companies in my table (that are not
null) and when you choose a presenter, it displays their contact info below
with an option to choose how many will be in attendance for that company. I
then click a button that runs an append qry to add them to attendance.

My problem is that I can only get it to work one of two ways - either i have
a button that opens the presenter form with no linked criteria (all records)
but then it won't show me who is currently set to attend for that date
already. OR i have a button that has a linked criteria on the date on the
main form and a matching date in one of the fields of the table that the
presenters form has. Only problem with this is that if there are no companies
set to present for that date, it won't allow me to choose any more either -
it will display the companies in the drop down but when i click on one, no
information shows up below in their contact info because there record source
is null.

Is there any ways to add some sort of defining criteria that if there are
currently no records associated with this date, dont link the criteria - show
all records. If there is a record(s) for the associated date, pull that date
and allow me to add more if i choose.....


Thanks!

 

[Graham Mandeno]

Hi Heidi

Use a string variable to set up your WhereCondition argument for the
OpenForm (if you used the wizard for your button code, you will have one
already called stLinkCriteria).

Now, check first to see if the combo box is null before setting the value in
the string:

If Not IsNull(cboCompany) Then
stLinkCriteria = "CompanyID=" & cboCompany
' the Else stLinkCriteria = "" is implied
End If
DoCmd.OpenForm ...

 

[Heidi]

Graham,

Sorry it took so long to try this but things have been a little
nuts....Quick question on this - w/the first part where you check if
cbocompnay is not null.... I want to check if the date is null though don't
I? If there is no date that matches in the field in the company form, then
don't pull anything specific, otherwise, only pull the company that has a
date that matches the main form...

Thanks,
Heidi

Hi Heidi

Use a string variable to set up your WhereCondition argument for the
OpenForm (if you used the wizard for your button code, you will have one
already called stLinkCriteria).

Now, check first to see if the combo box is null before setting the value in
the string:

If Not IsNull(cboCompany) Then
stLinkCriteria = "CompanyID=" & cboCompany
' the Else stLinkCriteria = "" is implied
End If
DoCmd.OpenForm ...

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a main form that records attendance with a drop down of dates
available. When I go thru and enter all the regular attendees, i have a
button that will open up a form with available presenters for me to enter
as
attending that day (as a presenter). It consists of a drop down that shows
all the company names of the possible companies in my table (that are not
null) and when you choose a presenter, it displays their contact info
below
with an option to choose how many will be in attendance for that company.
I
then click a button that runs an append qry to add them to attendance.

My problem is that I can only get it to work one of two ways - either i
have
a button that opens the presenter form with no linked criteria (all
records)
but then it won't show me who is currently set to attend for that date
already. OR i have a button that has a linked criteria on the date on the
main form and a matching date in one of the fields of the table that the
presenters form has. Only problem with this is that if there are no
companies
set to present for that date, it won't allow me to choose any more
either -
it will display the companies in the drop down but when i click on one, no
information shows up below in their contact info because there record
source
is null.

Is there any ways to add some sort of defining criteria that if there are
currently no records associated with this date, dont link the criteria -
show
all records. If there is a record(s) for the associated date, pull that
date
and allow me to add more if i choose.....


Thanks!

 

[Graham Mandeno]

Hi Heidi

Sorry - I read your post too quickly and thought you were selecting on the
Company combo.

Perhaps it would help if you posted the code you are currently using to open
the form.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Sorry it took so long to try this but things have been a little
nuts....Quick question on this - w/the first part where you check if
cbocompnay is not null.... I want to check if the date is null though
don't
I? If there is no date that matches in the field in the company form, then
don't pull anything specific, otherwise, only pull the company that has a
date that matches the main form...

Thanks,
Heidi

Hi Heidi

Use a string variable to set up your WhereCondition argument for the
OpenForm (if you used the wizard for your button code, you will have one
already called stLinkCriteria).

Now, check first to see if the combo box is null before setting the value
in
the string:

If Not IsNull(cboCompany) Then
stLinkCriteria = "CompanyID=" & cboCompany
' the Else stLinkCriteria = "" is implied
End If
DoCmd.OpenForm ...

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a main form that records attendance with a drop down of dates
available. When I go thru and enter all the regular attendees, i have a
button that will open up a form with available presenters for me to
enter
as
attending that day (as a presenter). It consists of a drop down that
shows
all the company names of the possible companies in my table (that are
not
null) and when you choose a presenter, it displays their contact info
below
with an option to choose how many will be in attendance for that
company.
I
then click a button that runs an append qry to add them to attendance.

My problem is that I can only get it to work one of two ways - either i
have
a button that opens the presenter form with no linked criteria (all
records)
but then it won't show me who is currently set to attend for that date
already. OR i have a button that has a linked criteria on the date on
the
main form and a matching date in one of the fields of the table that
the
presenters form has. Only problem with this is that if there are no
companies
set to present for that date, it won't allow me to choose any more
either -
it will display the companies in the drop down but when i click on one,
no
information shows up below in their contact info because there record
source
is null.

Is there any ways to add some sort of defining criteria that if there
are
currently no records associated with this date, dont link the
criteria -
show
all records. If there is a record(s) for the associated date, pull that
date
and allow me to add more if i choose.....


Thanks!

 

[Heidi]

Ok - i hope this is not too confusing......

Here is the code from just using a wizard to link the date on the main form
to the date (if there is any) in the source data in the company's form:
____________
Private Sub openEntAttendance_Click()
On Error GoTo Err_openEntAttendance_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEntrepreneurAttendance"

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

Exit_openEntAttendance_Click:
Exit Sub

Err_openEntAttendance_Click:
MsgBox Err.Description
Resume Exit_openEntAttendance_Click

End Sub
____________________________

So like i said, if there is no date in any record matching that date, it
pulls a null value and i can't do anything with it. The combo in the Company
form will hold names because it gets it's values straight from the table
rather than the source data as usual, but when it wont find any records to
match (because they're null) it does nothing. I can do a straight Open Form
with no linked criteria but then i can't tell which companies are slated for
that date - it shows them all. The user could then be adding the same company
multiple times at that rate, not realizing they're already added.

Thanks for your help - let me know!!
Heidi

Hi Heidi

Sorry - I read your post too quickly and thought you were selecting on the
Company combo.

Perhaps it would help if you posted the code you are currently using to open
the form.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Sorry it took so long to try this but things have been a little
nuts....Quick question on this - w/the first part where you check if
cbocompnay is not null.... I want to check if the date is null though
don't
I? If there is no date that matches in the field in the company form, then
don't pull anything specific, otherwise, only pull the company that has a
date that matches the main form...

Thanks,
Heidi

Hi Heidi

Use a string variable to set up your WhereCondition argument for the
OpenForm (if you used the wizard for your button code, you will have one
already called stLinkCriteria).

Now, check first to see if the combo box is null before setting the value
in
the string:

If Not IsNull(cboCompany) Then
stLinkCriteria = "CompanyID=" & cboCompany
' the Else stLinkCriteria = "" is implied
End If
DoCmd.OpenForm ...

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a main form that records attendance with a drop down of dates
available. When I go thru and enter all the regular attendees, i have a
button that will open up a form with available presenters for me to
enter
as
attending that day (as a presenter). It consists of a drop down that
shows
all the company names of the possible companies in my table (that are
not
null) and when you choose a presenter, it displays their contact info
below
with an option to choose how many will be in attendance for that
company.
I
then click a button that runs an append qry to add them to attendance.

My problem is that I can only get it to work one of two ways - either i
have
a button that opens the presenter form with no linked criteria (all
records)
but then it won't show me who is currently set to attend for that date
already. OR i have a button that has a linked criteria on the date on
the
main form and a matching date in one of the fields of the table that
the
presenters form has. Only problem with this is that if there are no
companies
set to present for that date, it won't allow me to choose any more
either -
it will display the companies in the drop down but when i click on one,
no
information shows up below in their contact info because there record
source
is null.

Is there any ways to add some sort of defining criteria that if there
are
currently no records associated with this date, dont link the
criteria -
show
all records. If there is a record(s) for the associated date, pull that
date
and allow me to add more if i choose.....


Thanks!

 

[Graham Mandeno]

Well, it looks like all you need is to check if Combo16 is null:

If Not IsNull(Combo16) Then
stLinkCriteria = "[fkPresentDinnerID]=" & Me![Combo16]
End If

BTW, you should rename Combo16 to something more meaningful - say,
cboSelectDate.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Ok - i hope this is not too confusing......

Here is the code from just using a wizard to link the date on the main
form
to the date (if there is any) in the source data in the company's form:
____________
Private Sub openEntAttendance_Click()
On Error GoTo Err_openEntAttendance_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEntrepreneurAttendance"

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

Exit_openEntAttendance_Click:
Exit Sub

Err_openEntAttendance_Click:
MsgBox Err.Description
Resume Exit_openEntAttendance_Click

End Sub
____________________________

So like i said, if there is no date in any record matching that date, it
pulls a null value and i can't do anything with it. The combo in the
Company
form will hold names because it gets it's values straight from the table
rather than the source data as usual, but when it wont find any records to
match (because they're null) it does nothing. I can do a straight Open
Form
with no linked criteria but then i can't tell which companies are slated
for
that date - it shows them all. The user could then be adding the same
company
multiple times at that rate, not realizing they're already added.

Thanks for your help - let me know!!
Heidi

Hi Heidi

Sorry - I read your post too quickly and thought you were selecting on
the
Company combo.

Perhaps it would help if you posted the code you are currently using to
open
the form.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Sorry it took so long to try this but things have been a little
nuts....Quick question on this - w/the first part where you check if
cbocompnay is not null.... I want to check if the date is null though
don't
I? If there is no date that matches in the field in the company form,
then
don't pull anything specific, otherwise, only pull the company that has
a
date that matches the main form...

Thanks,
Heidi

:

Hi Heidi

Use a string variable to set up your WhereCondition argument for the
OpenForm (if you used the wizard for your button code, you will have
one
already called stLinkCriteria).

Now, check first to see if the combo box is null before setting the
value
in
the string:

If Not IsNull(cboCompany) Then
stLinkCriteria = "CompanyID=" & cboCompany
' the Else stLinkCriteria = "" is implied
End If
DoCmd.OpenForm ...

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a main form that records attendance with a drop down of dates
available. When I go thru and enter all the regular attendees, i
have a
button that will open up a form with available presenters for me to
enter
as
attending that day (as a presenter). It consists of a drop down that
shows
all the company names of the possible companies in my table (that
are
not
null) and when you choose a presenter, it displays their contact
info
below
with an option to choose how many will be in attendance for that
company.
I
then click a button that runs an append qry to add them to
attendance.

My problem is that I can only get it to work one of two ways -
either i
have
a button that opens the presenter form with no linked criteria (all
records)
but then it won't show me who is currently set to attend for that
date
already. OR i have a button that has a linked criteria on the date
on
the
main form and a matching date in one of the fields of the table that
the
presenters form has. Only problem with this is that if there are no
companies
set to present for that date, it won't allow me to choose any more
either -
it will display the companies in the drop down but when i click on
one,
no
information shows up below in their contact info because there
record
source
is null.

Is there any ways to add some sort of defining criteria that if
there
are
currently no records associated with this date, dont link the
criteria -
show
all records. If there is a record(s) for the associated date, pull
that
date
and allow me to add more if i choose.....


Thanks!

 

[Heidi]

Actually it's in the Company's form that I'm trying to check if null - that's
why it's so confusing... The combo will never be null, so that's not my
issue, it's if the combo has an id and when I open the
frmEntrepreneurAttendance form, that date field in the source code is either
null or matches the id in the combo. Does that make sense? I'm sorry if I'm
making this really confusing.... If there is no id/date that matches the
combo, it want it to show me all records in the frmEntrepreneurAttendance.

Well, it looks like all you need is to check if Combo16 is null:

If Not IsNull(Combo16) Then
stLinkCriteria = "[fkPresentDinnerID]=" & Me![Combo16]
End If

BTW, you should rename Combo16 to something more meaningful - say,
cboSelectDate.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Ok - i hope this is not too confusing......

Here is the code from just using a wizard to link the date on the main
form
to the date (if there is any) in the source data in the company's form:
____________
Private Sub openEntAttendance_Click()
On Error GoTo Err_openEntAttendance_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmEntrepreneurAttendance"

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

Exit_openEntAttendance_Click:
Exit Sub

Err_openEntAttendance_Click:
MsgBox Err.Description
Resume Exit_openEntAttendance_Click

End Sub
____________________________

So like i said, if there is no date in any record matching that date, it
pulls a null value and i can't do anything with it. The combo in the
Company
form will hold names because it gets it's values straight from the table
rather than the source data as usual, but when it wont find any records to
match (because they're null) it does nothing. I can do a straight Open
Form
with no linked criteria but then i can't tell which companies are slated
for
that date - it shows them all. The user could then be adding the same
company
multiple times at that rate, not realizing they're already added.

Thanks for your help - let me know!!
Heidi

Hi Heidi

Sorry - I read your post too quickly and thought you were selecting on
the
Company combo.

Perhaps it would help if you posted the code you are currently using to
open
the form.
--
Graham Mandeno [Access MVP]
Auckland, New Zealand

Graham,

Sorry it took so long to try this but things have been a little
nuts....Quick question on this - w/the first part where you check if
cbocompnay is not null.... I want to check if the date is null though
don't
I? If there is no date that matches in the field in the company form,
then
don't pull anything specific, otherwise, only pull the company that has
a
date that matches the main form...

Thanks,
Heidi

:

Hi Heidi

Use a string variable to set up your WhereCondition argument for the
OpenForm (if you used the wizard for your button code, you will have
one
already called stLinkCriteria).

Now, check first to see if the combo box is null before setting the
value
in
the string:

If Not IsNull(cboCompany) Then
stLinkCriteria = "CompanyID=" & cboCompany
' the Else stLinkCriteria = "" is implied
End If
DoCmd.OpenForm ...

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a main form that records attendance with a drop down of dates
available. When I go thru and enter all the regular attendees, i
have a
button that will open up a form with available presenters for me to
enter
as
attending that day (as a presenter). It consists of a drop down that
shows
all the company names of the possible companies in my table (that
are
not
null) and when you choose a presenter, it displays their contact
info
below
with an option to choose how many will be in attendance for that
company.
I
then click a button that runs an append qry to add them to
attendance.

My problem is that I can only get it to work one of two ways -
either i
have
a button that opens the presenter form with no linked criteria (all
records)
but then it won't show me who is currently set to attend for that
date
already. OR i have a button that has a linked criteria on the date
on
the
main form and a matching date in one of the fields of the table that
the
presenters form has. Only problem with this is that if there are no
companies
set to present for that date, it won't allow me to choose any more
either -
it will display the companies in the drop down but when i click on
one,
no
information shows up below in their contact info because there
record
source
is null.

Is there any ways to add some sort of defining criteria that if
there
are
currently no records associated with this date, dont link the
criteria -
show
all records. If there is a record(s) for the associated date, pull
that
date
and allow me to add more if i choose.....


Thanks!

 

[Graham Mandeno]

Then you should use the expression:

If Not IsNull([control that contains the company ID]) Then
stLinkCriteria = ...


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Actually it's in the Company's form that I'm trying to check if null -
that's
why it's so confusing... The combo will never be null, so that's not my
issue, it's if the combo has an id and when I open the
frmEntrepreneurAttendance form, that date field in the source code is
either
null or matches the id in the combo. Does that make sense? I'm sorry if
I'm
making this really confusing.... If there is no id/date that matches the
combo, it want it to show me all records in the frmEntrepreneurAttendance.

Well, it looks like all you need is to check if Combo16 is null:

If Not IsNull(Combo16) Then
stLinkCriteria = "[fkPresentDinnerID]=" & Me![Combo16]
End If

BTW, you should rename Combo16 to something more meaningful - say,
cboSelectDate.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Heidi]

I have tried that before but I get an error message (tried it again to be
sure):
"Microsoft Office Access can't find the field "|" referred to in your
expression"

Do I need some sort of syntax to designate that the control is located in
the other form? I don't know the syntax for that so I havne't been able to
test it... this is what i have in my code with what you suggested:

stDocName = "frmEntrepreneurAttendance"

If Not IsNull([EntrepreneurID]) Then
stLinkCriteria = "[fkPresentDinnerID]=" & Me![cmbfkDinnerID]
End If

DoCmd.OpenForm stDocName, , , stLinkCriteria


Thanks!

Then you should use the expression:

If Not IsNull([control that contains the company ID]) Then
stLinkCriteria = ...


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Actually it's in the Company's form that I'm trying to check if null -
that's
why it's so confusing... The combo will never be null, so that's not my
issue, it's if the combo has an id and when I open the
frmEntrepreneurAttendance form, that date field in the source code is
either
null or matches the id in the combo. Does that make sense? I'm sorry if
I'm
making this really confusing.... If there is no id/date that matches the
combo, it want it to show me all records in the frmEntrepreneurAttendance.

Well, it looks like all you need is to check if Combo16 is null:

If Not IsNull(Combo16) Then
stLinkCriteria = "[fkPresentDinnerID]=" & Me![Combo16]
End If

BTW, you should rename Combo16 to something more meaningful - say,
cboSelectDate.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Graham Mandeno]

Heidi, some questions for you to answer:

1. Is "EntrepreneurID" the name of a control on the same form as your
"openEntAttendance" command button?

2. Does your code compile without errors? (Click Debug > Compile <project
name>)

3. Which line of code is throwing the erroe? (To find out, comment out the
"On Error Goto" line)

4. Does the code behave correctly if [EntrepreneurID] is NOT null (the
situation you say was working OK before)?

--
Soon...

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have tried that before but I get an error message (tried it again to be
sure):
"Microsoft Office Access can't find the field "|" referred to in your
expression"

Do I need some sort of syntax to designate that the control is located in
the other form? I don't know the syntax for that so I havne't been able to
test it... this is what i have in my code with what you suggested:

stDocName = "frmEntrepreneurAttendance"

If Not IsNull([EntrepreneurID]) Then
stLinkCriteria = "[fkPresentDinnerID]=" & Me![cmbfkDinnerID]
End If

DoCmd.OpenForm stDocName, , , stLinkCriteria


Thanks!

Then you should use the expression:

If Not IsNull([control that contains the company ID]) Then
stLinkCriteria = ...


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Actually it's in the Company's form that I'm trying to check if null -
that's
why it's so confusing... The combo will never be null, so that's not my
issue, it's if the combo has an id and when I open the
frmEntrepreneurAttendance form, that date field in the source code is
either
null or matches the id in the combo. Does that make sense? I'm sorry if
I'm
making this really confusing.... If there is no id/date that matches
the
combo, it want it to show me all records in the
frmEntrepreneurAttendance.



:

Well, it looks like all you need is to check if Combo16 is null:

If Not IsNull(Combo16) Then
stLinkCriteria = "[fkPresentDinnerID]=" & Me![Combo16]
End If

BTW, you should rename Combo16 to something more meaningful - say,
cboSelectDate.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

 

[Heidi]

1. EntrepreneurID is the ID field in the table that is the control source for
the form itself. Pretty much everything in the form works off of that - the
drop down (ID and company name), The link fields between what the drop down
holds and the subform holding the details of the company, etc.

2. Yes, no errors on compile

3. the debugger stops on "If Not IsNull([EntrepreneurID]) Then", however it
still gives me the same error message when I comment out the OnError...

4. No, it still errors out. If i comment out the If statement, it works
fine, opening up the one company that matches (is not null).

Thanksa bunch!

Heidi, some questions for you to answer:

1. Is "EntrepreneurID" the name of a control on the same form as your
"openEntAttendance" command button?

2. Does your code compile without errors? (Click Debug > Compile <project
name>)

3. Which line of code is throwing the erroe? (To find out, comment out the
"On Error Goto" line)

4. Does the code behave correctly if [EntrepreneurID] is NOT null (the
situation you say was working OK before)?

--
Soon...

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have tried that before but I get an error message (tried it again to be
sure):
"Microsoft Office Access can't find the field "|" referred to in your
expression"

Do I need some sort of syntax to designate that the control is located in
the other form? I don't know the syntax for that so I havne't been able to
test it... this is what i have in my code with what you suggested:

stDocName = "frmEntrepreneurAttendance"

If Not IsNull([EntrepreneurID]) Then
stLinkCriteria = "[fkPresentDinnerID]=" & Me![cmbfkDinnerID]
End If

DoCmd.OpenForm stDocName, , , stLinkCriteria


Thanks!

Then you should use the expression:

If Not IsNull([control that contains the company ID]) Then
stLinkCriteria = ...


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Actually it's in the Company's form that I'm trying to check if null -
that's
why it's so confusing... The combo will never be null, so that's not my
issue, it's if the combo has an id and when I open the
frmEntrepreneurAttendance form, that date field in the source code is
either
null or matches the id in the combo. Does that make sense? I'm sorry if
I'm
making this really confusing.... If there is no id/date that matches
the
combo, it want it to show me all records in the
frmEntrepreneurAttendance.



:

Well, it looks like all you need is to check if Combo16 is null:

If Not IsNull(Combo16) Then
stLinkCriteria = "[fkPresentDinnerID]=" & Me![Combo16]
End If

BTW, you should rename Combo16 to something more meaningful - say,
cboSelectDate.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand