If statement problem

  • Thread starter Thread starter Knowlton
  • Start date Start date
K

Knowlton

I have a check box on a form to relate married members to
each other. The visible property of the spouse name
control(combo box) and it's label are set to no. I put
code in the AfterUpdate event of the check box to make
the name control visible and populate it with the list
of members. When I try it, I get a syntax error on the
rowsource query. Also the set visible lines do not
appear to be working. Here is the code I have. Can
someone tell me what my problem is?
TIA,
Knowlton
 
You didn't show your code?

|I have a check box on a form to relate married members to
| each other. The visible property of the spouse name
| control(combo box) and it's label are set to no. I put
| code in the AfterUpdate event of the check box to make
| the name control visible and populate it with the list
| of members. When I try it, I get a syntax error on the
| rowsource query. Also the set visible lines do not
| appear to be working. Here is the code I have. Can
| someone tell me what my problem is?
| TIA,
| Knowlton
 
Actually I thought maybe you were clairvoyant. We'll try
again.
Private Sub SpouseIsMember_AfterUpdate()
If Me.SpouseIsMember = Yes Then 'Check box to
relate to another member
Me.SpouseName_Label.Visible = True
Me.SpouseName.Visible = True
Me.SpouseName.RowSource = _
SELECT "FirstName, " & "LastName" _
FROM tblMembers _
WHERE "FirstName, " & "LastName" IS NOT NULL
End If
End Sub
Thanks again,
Knowlton
 
You need to build a full string that is a valid SQL statement to use as the
RowSource:

Me.SpouseName.RowSource = _
"SELECT FirstName, LastName " & _
"FROM tblMembers " & _
"WHERE FirstName Is Not Null And LastName Is Not Null;"
 
Thanks for your reply. I copied your code into mine but
it returns no records. The "If" statement is working
because a cover over the drop down arrow is working as it
should but nothing is showing in the combo box. I copied
the SQL into a blank window(deleting quotes, etc) opened
the query and it displayed the list of names. I can't
figure out why it isn't working in the Sub. Here is what
I presently have:
Private Sub SpouseIsMember_AfterUpdate()
If Me.SpouseIsMember = Yes Then 'Check box to
relate to another member
Me.SpouseNameCover.Visible = False 'rectangle to
cover drop down arrow
Me.SpouseName.RowSource = _
"SELECT FirstName, LastName " & _
"FROM tblMembers " & _
"WHERE FirstName Is Not Null And LastName Is
Not Null;"
Else
Me.SpouseNameCover.Visible = True
End If

Me.SpouseName.Requery 'Tried this, wasn't sure if I
needed it or not
End Sub

Thanks for your help!!!
Knowlton
 
What are the values for these properties of the combo box:

Bound Column
Column Count
Column Widths
List Rows
List Width
 
Bound Column = 1
Column Count = 2
Column Widths = 1",1"
List Rows = 8
List Width = auto

The drop down box opens the same width as the control
with a scroll bar. I am only concerned with the first
name, however I need to be sure it is Joe Smith, not Joe
Wilson. How do I get the display to show the full width
without a scroll bar?

Thanks Again,
Knowlton
 
I've noticed the label cover is working backwards. It's
visible property is set to yes so that the drop down
arrow is not visible on a new form. According to the
code, as I understand it, when the SpouseIsMember is
checked, the SpouseNameCover.visible should be false
thereby showing the drop down arrow. If I check
SpouseIsMember, the drop down arrow remains invisible.
If I then uncheck SpouseIsMember, the drop down arrow
becomes visible.
I've tried using True instead of Yes but the results are
the same.
Any ideas?
Thanks,
Knowlton
 
To show full width without a horizontal scroll bar, set the List Width
property to the width you want (which should be about .1 or .2 inch longer
than the total width of your visible columns):

List Width = 2.2"


As for the IF part of your AfterUpdate code, use True, not Yes, as the test
for SpouseIsMember, assuming that SpouseIsMember is checkbox control. Yes is
not a valid value for boolean fields in VBA code; True (actual value is -1)
and False (actual value is 0) are valid values to use.

Do you have a Default Value set for the checkbox control? If not, set it to
No in the form's design view; that way, it won't have a Null value initially
for a new record, which can play havoc with code at times. Tell me more
about the SpouseIsMember control -- what type of control is it, is it bound
to a field, etc.?
--

Ken Snell
<MS ACCESS MVP>
 
The SpouseIsMember control is a check box with the
control source set to the SpouseIsMember field in the
Members table. I set the default to "No" and changed
the "Yes" to "True" in the code and now the cover is
working correctly. However, I am still not getting any
data in the SpouseName combo box.
Thanks,
Knowlton
 
If you're not seeing rows in the combo box, then your query is not working
for you.

Create a new query in design view. Select no tables. Change to SQL view.
Paste the SQL statement below into the query:
SELECT FirstName, LastName
FROM tblMembers
WHERE FirstName Is Not Null And LastName Is Not Null;

Change to datasheet view. Do you see any records? If not, then your query is
not what you need to use.

Tell us more about the table's fields and their data types and the data that
are in them.
 
I have succeeded in getting the query results into the
combo box. Thanks so much for your help!
Knowlton
 
Back
Top