Combo Box with 'ALL' problem

[Gina Whipp]

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

 

[Douglas J. Steele]

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;

 

[Gina Whipp]

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

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

(no private e-mails, please)

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 Whipp]

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

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

(no private e-mails, please)

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 Schapel]

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

 

[Douglas J. Steele]

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

(no private e-mails, please)

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

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

(no private e-mails, please)

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 Whipp]

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]

Completely blank

--
Gina Whipp

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

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

(no private e-mails, please)

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

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

(no private e-mails, please)


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 Schapel]

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

 

[Gina Whipp]

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]

Doug,

Thanks for your help but Steve actually solved my problem!

--
Gina Whipp

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

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

(no private e-mails, please)

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

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

(no private e-mails, please)


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 Whipp]

I figured that out when I actually READ the error message!