Hi, Matt.
The RowSource property is an SQL query statement that selects columns from
one or more tables, optionally with selection and sorting criteria. Its
syntax is:
SELECT {tablename.field1name, tablename.field2name, …} FROM tablename
WHERE tablename.fieldname = somevalue
ORDER BY sortfield
Consult your favorite Access reference for a more complete discussion of SQL
statements. You can also learn about it by creating queries in Query Design
View, and then switching to SQL (View, SQL) and seeing how the query looks.
Note, however, that there are a few subtle differences between how SQL is
implemented in Access and mainframes.
Substitute the name of your “lookup table†for “YourTableâ€, the name of your
primary key field for “YourPKâ€, and the name of your text field for
“YourDescriptionâ€.
So, if your table is named, say, Category, with fields ID and CategoryDesc,
and the value of ID for the “Sale†record is 1, the default RowSource would
be:
SELECT Category.ID, Category.CategoryDesc FROM Category
WHERE Category.ID <> 1
Your textbox' AfterUpdate event procedure would then be, assuming your combo
box is named cboCategory and your textbox is named txtMyTextbox,
If IsNull(Me![txtMyTextBox]) Then
Me![cboCategory].RowSource = "SELECT Category.ID, Category.CategoryDesc
FROM Category WHERE Category.ID <> 1"
Else
Me![cboCategory].RowSource = "SELECT Category.ID, Category.CategoryDesc
FROM Category
End If
Me![cboCategory].Requery
Also, since a primary key is used to index the table, and join to other
related tables, it is much faster to use a numeric primary key.
Hope that helps.
Sprinks
Matt said:
Whew, that's a lot to soak in. I am getting my values in the combo box from
a table, but I don't have a numeric primary key. I just have the values
("opportunity""Sale""Loss") in the first column, and then I have that as the
primary key. I tried what you told me below, even adding a column in my
table to include a numberic primary key, but it didn't work. Although, I
know it's because I'm a little confused. Below where you have
"Yourtable.YourPK" or ."YourDescription", what is "YourPK" &
"YourDescription"? Would I type column(0) and column(1) in place of those?
Sprinks said:
Hi, Matt.
By default, set the Row Source of the combo box to only the second and third
options, and then change and requery it if the user has entered a value in
the textbox AfterUpdate event.
If you are using text values for the combo box' Row Source, the default Row
Source property would be:
"Opportunity";"Loss"
If you're getting the values from a table, presumably you have a numeric
primary key and a text field, such as:
1 Sale
2 Opportunity
3 Loss
If your combo box is Bound to the first column, with its ColumnWidth set to
0", thus *displaying* the meaningful text, the default Row Source would be:
SELECT YourTable.YourPK, YourTable.YourDescription FROM YourTable
WHERE YourTable.YourPK <> 1
The AfterUpdate event procedure of the textbox would change the RowSource as
appropriate:
If IsNull([YourTextbox]) Then
Me.YourComboBox.RowSource = "SELECT YourTable.YourPK,
YourTable.YourDescription FROM YourTable
WHERE YourTable.YourPK <> 1"
Else
"SELECT YourTable.YourPK, YourTable.YourDescription FROM YourTable"
End If
or, if you are using text values:
If IsNull([YourTextBox]) Then
Me.YourCombo.RowSource = "Opportunity";"Loss"
Else
Me.YourCombo.RowSource = "Sale";"Opportunity";"Loss"
End If
Hope that helps.
Sprinks
:
I've built a form so my employees can enter in their daily sales they make.
I have a combo box [ComboA] that lists "Sale, Opportunity, Loss." I have
another text box for them to enter in order numbers [textA]. What I would
like to do is if [textA] is null, then they CANNOT choose "Sale" in [ComboA].
If [textA] does have a value, then they can choose whatever they want. Can
anybody help?
