Visible Property

B

Ben

Hi all!
I have a question with regards to what I am doing.
In the form I have separate drop down list boxes for each option. They have
respective "SELECT" row sources. The way I am doing it is that, I hide the
rest of the options and just show the main option by using the VISIBLE
property setting to FALSE or TRUE.
Below are the options and their respective Row Source" SQL:

- By Customer Name
"SELECT Customers.[Last Name], Customers.[First Name], Customers.City,
Customers.State, Customers.[Customer Number]
FROM Customers WHERE ((Not (Customers.[Last Name]) Is Null)) OR ((Not
(Customers.[First Name]) Is Null))
ORDER BY Customers.[Last Name], Customers.[First Name];"

- By Company
"SELECT Customers.Company, Customers.[Last Name], Customers.[First Name],
Customers.City, Customers.State, Customers.[Customer Number] FROM Customers
WHERE ((Not (Customers.Company) Is Null)) ORDER BY Customers.Company,
Customers.[Last Name], Customers.[First Name];"

- By Customer Number
"SELECT Customers.[Customer Number], Customers.[Last Name], Customers.[First
Name], Customers.Company, Customers.City, Customers.State FROM Customers
ORDER BY Customers.[Customer Number], Customers.[Last Name],
Customers.[First Name];"

- By Phone
"SELECT Phone AS [Telephone], [Last Name] & ", " & [First Name] AS Name,
Company, [Customer Number] FROM Customers WHERE ((Not (Phone) Is Null))
UNION SELECT [Other Phone] AS [Telephone], [Last Name] & ", " & [First
Name] AS Name, Company, [Customer Number] FROM Customers WHERE ((Not ([Other
Phone]) Is Null)) UNION SELECT Fax AS [Telephone], [Last Name] & ", " &
[First Name] AS Name, Company, [Customer Number] FROM Customers WHERE ((Not
(Fax) Is Null)) ORDER BY [Telephone], Name, Company, [Customer Number];"

Below is the script for the option selection:
Select Case Me.[Type Of Search]
Case 1
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Customer Name to Search For
"

[By Search Type CustNum].Visible = False
[By Search Type Company].Visible = False
[By Search Type].Visible = False
[By Search Type CustName].Visible = True
[By Search Type CustName].SetFocus

Case 2
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Company to Search For "

[By Search Type CustNum].Visible = False
[By Search Type].Visible = False
[By Search Type CustName].Visible = False
[By Search Type Company].Visible = True
[By Search Type Company].SetFocus

Case 3
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Customer Number to Search For
"

[By Search Type].Visible = False
[By Search Type CustName].Visible = False
[By Search Type Company].Visible = False
[By Search Type CustNum].Visible = True
[By Search Type CustNum].SetFocus

Case 4
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Phone Number to Search For
"

[By Search Type CustName].Visible = False
[By Search Type Company].Visible = False
[By Search Type CustNum].Visible = False
[By Search Type].Visible = True
[By Search Type].SetFocus

End Select

"By Phone" is the default option. The problem that I am encountering is
that when I click a different option (e.g. By Company) and then go back to
"By Phone" option, the drop down list box does not have any records at all.

What am I doing wrong?

Thanks for the help in advance!
 
J

james.igoe

Are you requerying your comboboxes?

me.combobox = null
me.combobox.requery

On chaging your option, you'd need to requery the combobox.
 
B

Ben

No. Cause the only option that is not working is the "By Phone". And I do
not want to requery it also because I do not need to have the most recent
copy of the records. Also, the rest of the options are working fine even if
I select anyone of them.
Could it be the "SELECT" statement because I am using a UNION?
 
B

Ben

Please disregard my thread. I fixed the problem.
Thanks.

Ben said:
Hi all!
I have a question with regards to what I am doing.
In the form I have separate drop down list boxes for each option. They
have respective "SELECT" row sources. The way I am doing it is that, I
hide the rest of the options and just show the main option by using the
VISIBLE property setting to FALSE or TRUE.
Below are the options and their respective Row Source" SQL:

- By Customer Name
"SELECT Customers.[Last Name], Customers.[First Name], Customers.City,
Customers.State, Customers.[Customer Number]
FROM Customers WHERE ((Not (Customers.[Last Name]) Is Null)) OR ((Not
(Customers.[First Name]) Is Null))
ORDER BY Customers.[Last Name], Customers.[First Name];"

- By Company
"SELECT Customers.Company, Customers.[Last Name], Customers.[First Name],
Customers.City, Customers.State, Customers.[Customer Number] FROM
Customers WHERE ((Not (Customers.Company) Is Null)) ORDER BY
Customers.Company, Customers.[Last Name], Customers.[First Name];"

- By Customer Number
"SELECT Customers.[Customer Number], Customers.[Last Name],
Customers.[First Name], Customers.Company, Customers.City, Customers.State
FROM Customers ORDER BY Customers.[Customer Number], Customers.[Last
Name], Customers.[First Name];"

- By Phone
"SELECT Phone AS [Telephone], [Last Name] & ", " & [First Name] AS Name,
Company, [Customer Number] FROM Customers WHERE ((Not (Phone) Is Null))
UNION SELECT [Other Phone] AS [Telephone], [Last Name] & ", " & [First
Name] AS Name, Company, [Customer Number] FROM Customers WHERE ((Not
([Other Phone]) Is Null)) UNION SELECT Fax AS [Telephone], [Last Name] &
", " & [First Name] AS Name, Company, [Customer Number] FROM Customers
WHERE ((Not (Fax) Is Null)) ORDER BY [Telephone], Name, Company, [Customer
Number];"

Below is the script for the option selection:
Select Case Me.[Type Of Search]
Case 1
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Customer Name to Search For "

[By Search Type CustNum].Visible = False
[By Search Type Company].Visible = False
[By Search Type].Visible = False
[By Search Type CustName].Visible = True
[By Search Type CustName].SetFocus

Case 2
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Company to Search For "

[By Search Type CustNum].Visible = False
[By Search Type].Visible = False
[By Search Type CustName].Visible = False
[By Search Type Company].Visible = True
[By Search Type Company].SetFocus

Case 3
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Customer Number to Search For "

[By Search Type].Visible = False
[By Search Type CustName].Visible = False
[By Search Type Company].Visible = False
[By Search Type CustNum].Visible = True
[By Search Type CustNum].SetFocus

Case 4
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Phone Number to Search For "

[By Search Type CustName].Visible = False
[By Search Type Company].Visible = False
[By Search Type CustNum].Visible = False
[By Search Type].Visible = True
[By Search Type].SetFocus

End Select

"By Phone" is the default option. The problem that I am encountering is
that when I click a different option (e.g. By Company) and then go back to
"By Phone" option, the drop down list box does not have any records at
all.

What am I doing wrong?

Thanks for the help in advance!
Click to expand...
 
D

Dirk Goldgar

Ben said:
Hi all!
I have a question with regards to what I am doing.
In the form I have separate drop down list boxes for each option.
They have respective "SELECT" row sources. The way I am doing it is
that, I hide the rest of the options and just show the main option by
using the VISIBLE property setting to FALSE or TRUE.
Below are the options and their respective Row Source" SQL:

- By Customer Name
"SELECT Customers.[Last Name], Customers.[First Name], Customers.City,
Customers.State, Customers.[Customer Number]
FROM Customers WHERE ((Not (Customers.[Last Name]) Is Null)) OR ((Not
(Customers.[First Name]) Is Null))
ORDER BY Customers.[Last Name], Customers.[First Name];"

- By Company
"SELECT Customers.Company, Customers.[Last Name], Customers.[First
Name], Customers.City, Customers.State, Customers.[Customer Number]
FROM Customers WHERE ((Not (Customers.Company) Is Null)) ORDER BY
Customers.Company, Customers.[Last Name], Customers.[First Name];"

- By Customer Number
"SELECT Customers.[Customer Number], Customers.[Last Name],
Customers.[First Name], Customers.Company, Customers.City,
Customers.State FROM Customers ORDER BY Customers.[Customer Number],
Customers.[Last Name], Customers.[First Name];"

- By Phone
"SELECT Phone AS [Telephone], [Last Name] & ", " & [First Name] AS
Name, Company, [Customer Number] FROM Customers WHERE ((Not (Phone)
Is Null)) UNION SELECT [Other Phone] AS [Telephone], [Last Name] &
", " & [First Name] AS Name, Company, [Customer Number] FROM
Customers WHERE ((Not ([Other Phone]) Is Null)) UNION SELECT Fax AS
[Telephone], [Last Name] & ", " & [First Name] AS Name, Company,
[Customer Number] FROM Customers WHERE ((Not (Fax) Is Null)) ORDER BY
[Telephone], Name, Company, [Customer Number];"

Below is the script for the option selection:
Select Case Me.[Type Of Search]
Case 1
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Customer Name to Search
For "

[By Search Type CustNum].Visible = False
[By Search Type Company].Visible = False
[By Search Type].Visible = False
[By Search Type CustName].Visible = True
[By Search Type CustName].SetFocus

Case 2
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Company to Search For
"

[By Search Type CustNum].Visible = False
[By Search Type].Visible = False
[By Search Type CustName].Visible = False
[By Search Type Company].Visible = True
[By Search Type Company].SetFocus

Case 3
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Customer Number to Search
For "

[By Search Type].Visible = False
[By Search Type CustName].Visible = False
[By Search Type Company].Visible = False
[By Search Type CustNum].Visible = True
[By Search Type CustNum].SetFocus

Case 4
[By Search Type].RowSource = ""
[Search Text].Caption = "Select the Phone Number to Search For
"

[By Search Type CustName].Visible = False
[By Search Type Company].Visible = False
[By Search Type CustNum].Visible = False
[By Search Type].Visible = True
[By Search Type].SetFocus

End Select

"By Phone" is the default option. The problem that I am encountering
is that when I click a different option (e.g. By Company) and then go
back to "By Phone" option, the drop down list box does not have any
records at all.

What am I doing wrong?

Thanks for the help in advance!
Click to expand...

When Me.[Type Of Search] is 4, you're making the combo box [By Search
Type] visible, so I have toi assume that's the combo you want to use
when the user will search by phone. However, every one of your option's
Case blocks begins with the statement
[By Search Type].RowSource = ""
Click to expand...

which will clear the rowsource of the combo box so that its list is
empty. I'm not sure why you're doing that, but I don't think its what
you want.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using DoCmd.FindRecord 2
local / SQL conflict 1
Sorting 0
SQL "IF" Question 5
UNION Query Statement Bug/Problem 5
Form to search for Customer 2
Help with this Search button 3
Update Records in Current Table and another Table 5

Top