Gurus,

Here's my problem...

I have a combo box with pulls the Brokers territory via the query below,
works perfectly... EXCEPT

SELECT btTerritoryStateID FROM tblBrokerTerritory WHERE
(((tblBrokerTerritory.btBrokerID)=[Forms]![frmLister]![txtBrokerID])) UNION
Select "ALL" as Bogus FROM tblBrokerTerritory;

If the Broker has no Listing in that territory the whole form goes blank and
you have to close the form and reopen it. I have tried requerying and
undoing but the form still goes blank. I have tried this on the
Not_In_List, Before_Update and After_Update event. Please note, I do want
to show all their territories because I want to give a message that says "No
Listing in this State/Country".

Any ideas?

Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

The problem, as you doubtlessly know, is that txtBrokerID will be null when
there's no data.

Try:

SELECT btTerritoryStateID
FROM tblBrokerTerritory WHERE
btBrokerID = [Forms]![frmLister]![txtBrokerID]
OR [Forms]![frmLister]![txtBrokerID] IS NULL
UNION
Select "ALL" as Bogus FROM tblBrokerTerritory;


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Gina Whipp" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Gurus,
>
> Here's my problem...
>
> I have a combo box with pulls the Brokers territory via the query below,
> works perfectly... EXCEPT
>
> SELECT btTerritoryStateID FROM tblBrokerTerritory WHERE
> (((tblBrokerTerritory.btBrokerID)=[Forms]![frmLister]![txtBrokerID]))
> UNION Select "ALL" as Bogus FROM tblBrokerTerritory;
>
> If the Broker has no Listing in that territory the whole form goes blank
> and you have to close the form and reopen it. I have tried requerying and
> undoing but the form still goes blank. I have tried this on the
> Not_In_List, Before_Update and After_Update event. Please note, I do want
> to show all their territories because I want to give a message that says
> "No Listing in this State/Country".
>
> Any ideas?
>
> Thanks,
> Gina Whipp
>
> "I feel I have been denied critical, need to know, information!" - Tremors
> II
>

Yes, I know that but I was still HOPING that it didn't matter. Sorry to say
your solution did not work either.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:(E-Mail Removed)...
> The problem, as you doubtlessly know, is that txtBrokerID will be null
> when there's no data.
>
> Try:
>
> SELECT btTerritoryStateID
> FROM tblBrokerTerritory WHERE
> btBrokerID = [Forms]![frmLister]![txtBrokerID]
> OR [Forms]![frmLister]![txtBrokerID] IS NULL
> UNION
> Select "ALL" as Bogus FROM tblBrokerTerritory;
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Gina Whipp" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Gurus,
>>
>> Here's my problem...
>>
>> I have a combo box with pulls the Brokers territory via the query below,
>> works perfectly... EXCEPT
>>
>> SELECT btTerritoryStateID FROM tblBrokerTerritory WHERE
>> (((tblBrokerTerritory.btBrokerID)=[Forms]![frmLister]![txtBrokerID]))
>> UNION Select "ALL" as Bogus FROM tblBrokerTerritory;
>>
>> If the Broker has no Listing in that territory the whole form goes blank
>> and you have to close the form and reopen it. I have tried requerying
>> and undoing but the form still goes blank. I have tried this on the
>> Not_In_List, Before_Update and After_Update event. Please note, I do
>> want to show all their territories because I want to give a message that
>> says "No Listing in this State/Country".
>>
>> Any ideas?
>>
>> Thanks,
>> Gina Whipp
>>
>> "I feel I have been denied critical, need to know, information!" -
>> Tremors II
>>
>
>

Okay tried the below...

Private Sub cboState_BeforeUpdate(Cancel As Integer)
If DLookup("llStateID", "qryLister", "btBrokerID = txtBrokerID") <>
Me.cboState Then
DoCmd.CancelEvent
Me.Undo
MsgBox "No Listings in this State/Province", vbInformation,
"State/Province"
End If
End Sub

But now it won't look up anything. My thought was if I could see if the
State was in the table then okay find the Listings but if it's not in the
table show the message. But I get the message all the time because it's not
really searching the entire table. I may need more coffee... any ideas out
there???

Thanks,
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:(E-Mail Removed)...
> The problem, as you doubtlessly know, is that txtBrokerID will be null
> when there's no data.
>
> Try:
>
> SELECT btTerritoryStateID
> FROM tblBrokerTerritory WHERE
> btBrokerID = [Forms]![frmLister]![txtBrokerID]
> OR [Forms]![frmLister]![txtBrokerID] IS NULL
> UNION
> Select "ALL" as Bogus FROM tblBrokerTerritory;
>
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Gina Whipp" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> Gurus,
>>
>> Here's my problem...
>>
>> I have a combo box with pulls the Brokers territory via the query below,
>> works perfectly... EXCEPT
>>
>> SELECT btTerritoryStateID FROM tblBrokerTerritory WHERE
>> (((tblBrokerTerritory.btBrokerID)=[Forms]![frmLister]![txtBrokerID]))
>> UNION Select "ALL" as Bogus FROM tblBrokerTerritory;
>>
>> If the Broker has no Listing in that territory the whole form goes blank
>> and you have to close the form and reopen it. I have tried requerying
>> and undoing but the form still goes blank. I have tried this on the
>> Not_In_List, Before_Update and After_Update event. Please note, I do
>> want to show all their territories because I want to give a message that
>> says "No Listing in this State/Country".
>>
>> Any ideas?
>>
>> Thanks,
>> Gina Whipp
>>
>> "I feel I have been denied critical, need to know, information!" -
>> Tremors II
>>
>
>

Gina,

The DLookup function is not applicable to your purpose here. It only
applies to the first record in the table/query you are looking at.

Not 100% sure I understand what you want, but I think this may be closer
to the mark:
If DCount("*", "qryLister", "btBrokerID = " & Me.txtBrokerID & " And
llStateID = " & Me.cboState) = 0 Then

--
Steve Schapel, Microsoft Access MVP

Gina Whipp wrote:
> Okay tried the below...
>
> Private Sub cboState_BeforeUpdate(Cancel As Integer)
> If DLookup("llStateID", "qryLister", "btBrokerID = txtBrokerID") <>
> Me.cboState Then
> DoCmd.CancelEvent
> Me.Undo
> MsgBox "No Listings in this State/Province", vbInformation,
> "State/Province"
> End If
> End Sub
>
> But now it won't look up anything. My thought was if I could see if the
> State was in the table then okay find the Listings but if it's not in the
> table show the message. But I get the message all the time because it's not
> really searching the entire table. I may need more coffee... any ideas out
> there???

Really? It worked in a test I did, but your setup must be different than
what I cobbled together.

What actually shows on frmLister: nothing, or a blank row allowing input?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Gina Whipp" <(E-Mail Removed)> wrote in message
news:O4SYL%(E-Mail Removed)...
> Yes, I know that but I was still HOPING that it didn't matter. Sorry to
> say your solution did not work either.
>
> --
> Gina Whipp
>
> "I feel I have been denied critical, need to know, information!" - Tremors
> II
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
> news:(E-Mail Removed)...
>> The problem, as you doubtlessly know, is that txtBrokerID will be null
>> when there's no data.
>>
>> Try:
>>
>> SELECT btTerritoryStateID
>> FROM tblBrokerTerritory WHERE
>> btBrokerID = [Forms]![frmLister]![txtBrokerID]
>> OR [Forms]![frmLister]![txtBrokerID] IS NULL
>> UNION
>> Select "ALL" as Bogus FROM tblBrokerTerritory;
>>
>>
>> --
>> Doug Steele, Microsoft Access MVP
>> http://I.Am/DougSteele
>> (no private e-mails, please)
>>
>>
>> "Gina Whipp" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> Gurus,
>>>
>>> Here's my problem...
>>>
>>> I have a combo box with pulls the Brokers territory via the query below,
>>> works perfectly... EXCEPT
>>>
>>> SELECT btTerritoryStateID FROM tblBrokerTerritory WHERE
>>> (((tblBrokerTerritory.btBrokerID)=[Forms]![frmLister]![txtBrokerID]))
>>> UNION Select "ALL" as Bogus FROM tblBrokerTerritory;
>>>
>>> If the Broker has no Listing in that territory the whole form goes blank
>>> and you have to close the form and reopen it. I have tried requerying
>>> and undoing but the form still goes blank. I have tried this on the
>>> Not_In_List, Before_Update and After_Update event. Please note, I do
>>> want to show all their territories because I want to give a message that
>>> says "No Listing in this State/Country".
>>>
>>> Any ideas?
>>>
>>> Thanks,
>>> Gina Whipp
>>>
>>> "I feel I have been denied critical, need to know, information!" -
>>> Tremors II
>>>
>>
>>
>
>

Steve,

I kinda figured that but I'm getting so desperate.... You are on the right
track, it even got me to thinking but I still can't get it to work.

What I am trying to do is... I am a Broker and I have Listings in NY and GA
but my territory includes FL. If I select FL from the cboState combo box I
want a message to display that says "No Listings....". What is happening
now is the form goes blank because it has no Listings to show. Note, I am
using a query that allows me to show 'ALL' in the combo box because when I
first open the form it shows me all my Listings until I select a State.

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"Steve Schapel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Gina,
>
> The DLookup function is not applicable to your purpose here. It only
> applies to the first record in the table/query you are looking at.
>
> Not 100% sure I understand what you want, but I think this may be closer
> to the mark:
> If DCount("*", "qryLister", "btBrokerID = " & Me.txtBrokerID & " And
> llStateID = " & Me.cboState) = 0 Then
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Gina Whipp wrote:
>> Okay tried the below...
>>
>> Private Sub cboState_BeforeUpdate(Cancel As Integer)
>> If DLookup("llStateID", "qryLister", "btBrokerID = txtBrokerID") <>
>> Me.cboState Then
>> DoCmd.CancelEvent
>> Me.Undo
>> MsgBox "No Listings in this State/Province", vbInformation,
>> "State/Province"
>> End If
>> End Sub
>>
>> But now it won't look up anything. My thought was if I could see if the
>> State was in the table then okay find the Listings but if it's not in the
>> table show the message. But I get the message all the time because it's
>> not really searching the entire table. I may need more coffee... any
>> ideas out there???

Completely blank

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
news:(E-Mail Removed)...
> Really? It worked in a test I did, but your setup must be different than
> what I cobbled together.
>
> What actually shows on frmLister: nothing, or a blank row allowing input?
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Gina Whipp" <(E-Mail Removed)> wrote in message
> news:O4SYL%(E-Mail Removed)...
>> Yes, I know that but I was still HOPING that it didn't matter. Sorry to
>> say your solution did not work either.
>>
>> --
>> Gina Whipp
>>
>> "I feel I have been denied critical, need to know, information!" -
>> Tremors II
>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
>> news:(E-Mail Removed)...
>>> The problem, as you doubtlessly know, is that txtBrokerID will be null
>>> when there's no data.
>>>
>>> Try:
>>>
>>> SELECT btTerritoryStateID
>>> FROM tblBrokerTerritory WHERE
>>> btBrokerID = [Forms]![frmLister]![txtBrokerID]
>>> OR [Forms]![frmLister]![txtBrokerID] IS NULL
>>> UNION
>>> Select "ALL" as Bogus FROM tblBrokerTerritory;
>>>
>>>
>>> --
>>> Doug Steele, Microsoft Access MVP
>>> http://I.Am/DougSteele
>>> (no private e-mails, please)
>>>
>>>
>>> "Gina Whipp" <(E-Mail Removed)> wrote in message
>>> news:(E-Mail Removed)...
>>>> Gurus,
>>>>
>>>> Here's my problem...
>>>>
>>>> I have a combo box with pulls the Brokers territory via the query
>>>> below, works perfectly... EXCEPT
>>>>
>>>> SELECT btTerritoryStateID FROM tblBrokerTerritory WHERE
>>>> (((tblBrokerTerritory.btBrokerID)=[Forms]![frmLister]![txtBrokerID]))
>>>> UNION Select "ALL" as Bogus FROM tblBrokerTerritory;
>>>>
>>>> If the Broker has no Listing in that territory the whole form goes
>>>> blank and you have to close the form and reopen it. I have tried
>>>> requerying and undoing but the form still goes blank. I have tried
>>>> this on the Not_In_List, Before_Update and After_Update event. Please
>>>> note, I do want to show all their territories because I want to give a
>>>> message that says "No Listing in this State/Country".
>>>>
>>>> Any ideas?
>>>>
>>>> Thanks,
>>>> Gina Whipp
>>>>
>>>> "I feel I have been denied critical, need to know, information!" -
>>>> Tremors II
>>>>
>>>
>>>
>>
>>
>
>

Gina,

If llStateID is a text data type field, and the cboState combobox is
selecting items such as FL which is text, then a modification to the
function I showed before will be necessary:
If DCount("*", "qryLister", "btBrokerID = " & Me.txtBrokerID & " And
llStateID = '" & Me.cboState & "'") = 0 Then

--
Steve Schapel, Microsoft Access MVP

Gina Whipp wrote:
> Steve,
>
> I kinda figured that but I'm getting so desperate.... You are on the right
> track, it even got me to thinking but I still can't get it to work.
>
> What I am trying to do is... I am a Broker and I have Listings in NY and GA
> but my territory includes FL. If I select FL from the cboState combo box I
> want a message to display that says "No Listings....". What is happening
> now is the form goes blank because it has no Listings to show. Note, I am
> using a query that allows me to show 'ALL' in the combo box because when I
> first open the form it shows me all my Listings until I select a State.
>

Steve,

Wasn't your solution, it was a syntax error, State is text. I believe you
have solved it. HUGH THANKS! Don't know why I never even tried DCount!

--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II
"Steve Schapel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Gina,
>
> The DLookup function is not applicable to your purpose here. It only
> applies to the first record in the table/query you are looking at.
>
> Not 100% sure I understand what you want, but I think this may be closer
> to the mark:
> If DCount("*", "qryLister", "btBrokerID = " & Me.txtBrokerID & " And
> llStateID = " & Me.cboState) = 0 Then
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Gina Whipp wrote:
>> Okay tried the below...
>>
>> Private Sub cboState_BeforeUpdate(Cancel As Integer)
>> If DLookup("llStateID", "qryLister", "btBrokerID = txtBrokerID") <>
>> Me.cboState Then
>> DoCmd.CancelEvent
>> Me.Undo
>> MsgBox "No Listings in this State/Province", vbInformation,
>> "State/Province"
>> End If
>> End Sub
>>
>> But now it won't look up anything. My thought was if I could see if the
>> State was in the table then okay find the Listings but if it's not in the
>> table show the message. But I get the message all the time because it's
>> not really searching the entire table. I may need more coffee... any
>> ideas out there???