ListBox RowSourceType and RowSource

[dave]

I populate in 2 listbox properties the RowSourceType and RowSource with no
problem and I can get the list boxes to do what I want. I populate them with
a query below. My problem is that when I close out of Access and reopen the
form I get an error message saying the I might have not set the rowSourceType
to a incorrect value. I have tried to blank out the RowSourceType and
RowSource properties on form close and form load but I quess I am not doing
it correctly. Everytime I am opening the form it gives me an error and can
not figure out how to blank out the properties. Can someone please give me
example code of how to set these fields to NOTHING (or default values) when
initialized.

String1 = Me.End_Item
string2 = "Select DISTINCT [END ITEM AND CONTRACT].[Contract]FROM [END ITEM
AND CONTRACT]where End_Item ="
string3 = string2 + " " + "'" + String1 + "'"
Forms!testform!Contract.RowSourceType = "Table/Query"
Forms!testform!Contract.RowSource = string3

Thanks in Advance,

dave

 

[OfficeDev18 via AccessMonster.com]

Hi, Dave,

First of all, lose Distinct; it's not necessary when not JOINing another
table. If you mean GROUP BY, say so. Also, if the row source is designed to
return only one possible value, why have a combo box? You can do the same
thing in a textbox by putting =SELECT.... etc. in the textbox's control
source property.

By the way, is your code a direct copy-and-paste from the Row Source property
in your form? If so, there are no spaces between some of the words, which
will create problems.

What about the second combobox? What is its row source?

There's no reason to use string1=Me.EndItem. You can use Me.EndItem right in
the concatenation. The way I would code this is as follows:

Forms!testform!Contract.RowSourceType = "Table/Query"
Forms!testform!Contract.RowSource = "SELECT [END ITEM AND CONTRACT].[Contract]
FROM [END ITEM
AND CONTRACT] WHERE End_Item = '" & Me.End_Item & "' GROUP BY Contract;"

The usage of three concatenated strings is confusing to me, maybe to Access
too.

Also, there is no reason on earth to blank out the row source type or row
source when closing the form.

Also, you're using double negatives, as in "... the I might have not set the
rowSourceType to a incorrect value", but I imagine that's only a typo.

After all is said and done, I'm confused also. Maybe we can enlighten each
other!

Sam

I populate in 2 listbox properties the RowSourceType and RowSource with no
problem and I can get the list boxes to do what I want. I populate them with
a query below. My problem is that when I close out of Access and reopen the
form I get an error message saying the I might have not set the rowSourceType
to a incorrect value. I have tried to blank out the RowSourceType and
RowSource properties on form close and form load but I quess I am not doing
it correctly. Everytime I am opening the form it gives me an error and can
not figure out how to blank out the properties. Can someone please give me
example code of how to set these fields to NOTHING (or default values) when
initialized.

String1 = Me.End_Item
string2 = "Select DISTINCT [END ITEM AND CONTRACT].[Contract]FROM [END ITEM
AND CONTRACT]where End_Item ="
string3 = string2 + " " + "'" + String1 + "'"
Forms!testform!Contract.RowSourceType = "Table/Query"
Forms!testform!Contract.RowSource = string3

Thanks in Advance,

dave

 

[Douglas J. Steele]

The DISTINCT certainly might be necessary, Sam. Perhaps the table contains
many different contracts in it, and Dave is trying to get a list of the
distinct contracts in the table. I think it's better to use DISTINCT than
GROUP BY if that's the case.

I agree that the missing spaces around the key words FROM and WHERE are
probable causes for errors.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi, Dave,

First of all, lose Distinct; it's not necessary when not JOINing another
table. If you mean GROUP BY, say so. Also, if the row source is designed
to
return only one possible value, why have a combo box? You can do the same
thing in a textbox by putting =SELECT.... etc. in the textbox's control
source property.

By the way, is your code a direct copy-and-paste from the Row Source
property
in your form? If so, there are no spaces between some of the words, which
will create problems.

What about the second combobox? What is its row source?

There's no reason to use string1=Me.EndItem. You can use Me.EndItem right
in
the concatenation. The way I would code this is as follows:

Forms!testform!Contract.RowSourceType = "Table/Query"
Forms!testform!Contract.RowSource = "SELECT [END ITEM AND
CONTRACT].[Contract]
FROM [END ITEM
AND CONTRACT] WHERE End_Item = '" & Me.End_Item & "' GROUP BY Contract;"

The usage of three concatenated strings is confusing to me, maybe to
Access
too.

Also, there is no reason on earth to blank out the row source type or row
source when closing the form.

Also, you're using double negatives, as in "... the I might have not set
the
rowSourceType to a incorrect value", but I imagine that's only a typo.

After all is said and done, I'm confused also. Maybe we can enlighten each
other!

Sam

I populate in 2 listbox properties the RowSourceType and RowSource with no
problem and I can get the list boxes to do what I want. I populate them
with
a query below. My problem is that when I close out of Access and reopen
the
form I get an error message saying the I might have not set the
rowSourceType
to a incorrect value. I have tried to blank out the RowSourceType and
RowSource properties on form close and form load but I quess I am not
doing
it correctly. Everytime I am opening the form it gives me an error and
can
not figure out how to blank out the properties. Can someone please give
me
example code of how to set these fields to NOTHING (or default values)
when
initialized.

String1 = Me.End_Item
string2 = "Select DISTINCT [END ITEM AND CONTRACT].[Contract]FROM [END
ITEM
AND CONTRACT]where End_Item ="
string3 = string2 + " " + "'" + String1 + "'"
Forms!testform!Contract.RowSourceType = "Table/Query"
Forms!testform!Contract.RowSource = string3

Thanks in Advance,

dave

 

[Dave]

Sam thanks for the feedback. Here is the senario. My list boxes can have up
to 100 hundred records in them even with the distinct. ListBox1- Contracts,
ListBox2- End_Items. I have 2 listboxes that basically feed each other. I
can have one contract that can have many end_items (The 2nd list box) or I
can have one End_Item with many contracts(The 1st List box) The user
determines what they want to sort on either Contract, End_Item or None.
Example- If contract is selected the contract listbox (The 1st List box) is
populated with all contracts. When a user selects a proper contract Access
takes the value (ON_CLICK) and puts in the proper information needed to
populate the other list box End_Item (The second list box).
If users selects End_Item it is just works backwards, it will populate
Contracts. I need to make sure that every user has these options I want to
make sure all users start with blank listboxes hence why I was wanted to
blank out the row source type and row source.

If there is a better way I am open to suggestions.


Example for Populating End Items.
Forms!testform!End_Item.RowSourceType = "Table/Query"
Forms!testform!End_Item.RowSource = SELECT [END ITEM AND
CONTRACT].[Contract] FROM [END ITEM AND CONTRACT] WHERE End_Item = '" &
Me.End_Item & "' GROUP BY Contract;"

Hi, Dave,

First of all, lose Distinct; it's not necessary when not JOINing another
table. If you mean GROUP BY, say so. Also, if the row source is designed to
return only one possible value, why have a combo box? You can do the same
thing in a textbox by putting =SELECT.... etc. in the textbox's control
source property.

By the way, is your code a direct copy-and-paste from the Row Source property
in your form? If so, there are no spaces between some of the words, which
will create problems.

What about the second combobox? What is its row source?

There's no reason to use string1=Me.EndItem. You can use Me.EndItem right in
the concatenation. The way I would code this is as follows:

Forms!testform!Contract.RowSourceType = "Table/Query"
Forms!testform!Contract.RowSource = "SELECT [END ITEM AND CONTRACT].[Contract]
FROM [END ITEM
AND CONTRACT] WHERE End_Item = '" & Me.End_Item & "' GROUP BY Contract;"

The usage of three concatenated strings is confusing to me, maybe to Access
too.

Also, there is no reason on earth to blank out the row source type or row
source when closing the form.

Also, you're using double negatives, as in "... the I might have not set the
rowSourceType to a incorrect value", but I imagine that's only a typo.

After all is said and done, I'm confused also. Maybe we can enlighten each
other!

Sam

I populate in 2 listbox properties the RowSourceType and RowSource with no
problem and I can get the list boxes to do what I want. I populate them with
a query below. My problem is that when I close out of Access and reopen the
form I get an error message saying the I might have not set the rowSourceType
to a incorrect value. I have tried to blank out the RowSourceType and
RowSource properties on form close and form load but I quess I am not doing
it correctly. Everytime I am opening the form it gives me an error and can
not figure out how to blank out the properties. Can someone please give me
example code of how to set these fields to NOTHING (or default values) when
initialized.

String1 = Me.End_Item
string2 = "Select DISTINCT [END ITEM AND CONTRACT].[Contract]FROM [END ITEM
AND CONTRACT]where End_Item ="
string3 = string2 + " " + "'" + String1 + "'"
Forms!testform!Contract.RowSourceType = "Table/Query"
Forms!testform!Contract.RowSource = string3

Thanks in Advance,

dave

 

[david epsom dot com dot au]

I get an error message saying

"the I might have not set the rowSourceType to a incorrect value"

Help us help you dave: copy out the exact message and post
it when you want help.

RowSource properties on form close

Unless you SAVE the form, it doesn't matter what you
put in form close. Open the form in design mode and
reset the properties.

Forms!testform!Contract.RowSourceType = "Table/Query"

Don't reset the RowSourceType unless you are changing
it. Just leave it at the correct value.

and form load

String1 = Me.End_Item

If you try to set that before you have an End_Item, it
is not going to work.

Open the form in design mode and reset the property.
When you open the form, only reset the property after
checking that your values are good.

(david)

I populate in 2 listbox properties the RowSourceType and RowSource with no
problem and I can get the list boxes to do what I want. I populate them
with
a query below. My problem is that when I close out of Access and reopen
the
form I get an error message saying the I might have not set the
rowSourceType
to a incorrect value. I have tried to blank out the RowSourceType and
RowSource properties on form close and form load but I quess I am not
doing
it correctly. Everytime I am opening the form it gives me an error and
can
not figure out how to blank out the properties. Can someone please give
me
example code of how to set these fields to NOTHING (or default values)
when
initialized.

String1 = Me.End_Item
string2 = "Select DISTINCT [END ITEM AND CONTRACT].[Contract]FROM [END
ITEM
AND CONTRACT]where End_Item ="
string3 = string2 + " " + "'" + String1 + "'"
Forms!testform!Contract.RowSourceType = "Table/Query"
Forms!testform!Contract.RowSource = string3

Thanks in Advance,

dave

 

[OfficeDev18 via AccessMonster.com]

Good morning, Dave,

I admit to being a purist, and maybe a bit of a perfectionist. I prefer to
take the initiative rather than to confuse Access into doing something
unexpected. The way I would do this is to have a second form that contains
only an option group. You would give the user a choice between sorting either
by

* Contract or
* End Item (of course the * here is only to depict the checkbox).

Now you are fore-armed with what you need. Say the user selects Contract. In
this form's (the preliminary form) DoCmd.OpenForm statement to open the main
form, you would put the word "Contract" in the OpenArgs position (see the
OpenForm method for details).

In the main form's OnLoad event, you would put code to capture the OpenArgs
argument (OA = Me.OpenArgs, where OA is declared as a GLOBAL variable (type
string) in the form's Declarations section). By the way, notice I don't use
the Forms!FormName!....... form. The keyword Me is a shortcut meaning the
current object, in this case the form.

In the form's OnCurrent event, put a simple If statement:

If OA = "Contract" Then
Me.Contract.RowSource = "SELECT [END ITEM AND CONTRACT].[Contract] FROM
[END ITEM AND CONTRACT] GROUP BY Contract ORDER BY Contract;"
(notice no WHERE clause)
Me.Contract.Enabled = True
Me.End_Item.Enabled = False
Me.Contract.SetFocus
Me.Contract.Requery
Else 'OA = "End_Item"
Me.End_Item.RowSource = "SELECT [END ITEM AND CONTRACT].[End_Item] FROM
[END ITEM AND CONTRACT] GROUP BY End_Item ORDER BY End_Item;"
Me.End_Item.Enabled = True
Me.Contract.Enabled = False
Me.End_Item.SetFocus
Me.End_Item.Requery
End If

(As David Epsom says, set the Row Source property for both comboboxes to
"Table/Query" in their respective property sheets and leave them alone; they
never change.)

Now the user is forced to first pick a contract. In the contract combobox's
AfterUpdate event, put the following code:

If OA = "Contract" Then
Me.End_Item.RowSource = "SELECT [END ITEM AND CONTRACT].[End_Item] FROM
[END ITEM AND CONTRACT] WHERE Contract = '" & Me.Contract & "' GROUP BY
[End_Item] ORDER BY End_Item;"
Me.End_Item.Enabled = True
Me.End_Item.SetFocus
Me.Contract.Enabled = False
Me.End_Item.Requery
Exit Sub
End If

Note the Exit Sub above. This code is in addition to and BEFORE any
processing code you had in the combobox's AfterUpdate event before, as at
this time you're not ready to process - you don't have both Contract and
End_Item yet. After you pick the End_Item is the time to process.

Now the user must choose an End Item, as the contract combobox has been
disabled. When you close the form you don't need to reset anything; the next
time you open the form you'll again encounter the option group first which
will segue into the main form.

In the End_Item combobox's AfterUpdate event, put the following code:

If OA = "End_Item" Then
Me.End_Item.RowSource = "SELECT [END ITEM AND CONTRACT].Contract FROM
[END ITEM AND CONTRACT] WHERE End_Item = '" & Me.End_Item & "' GROUP BY
Contract ORDER BY Contract;"
Me.Contract.Enabled = True
Me.Contract.SetFocus
Me.End_Item.Enabled = False
Me.Contract.Requery
Exit Sub
End If

Again, this code is in addition to and BEFORE any other code you may have in
this event.

Like I hinted before, this is a bit to break off and chew. But if you want to
know exactly what Access is gonna do you need to be as explicit as possible.

For any foreign terms, like option group, etc., you can find entries in the
help file. I suggest you read them thoroughly, especially if they give you
problems initially.

Hope this was helpful,

Sam

Sam thanks for the feedback. Here is the senario. My list boxes can have up
to 100 hundred records in them even with the distinct. ListBox1- Contracts,
ListBox2- End_Items. I have 2 listboxes that basically feed each other. I
can have one contract that can have many end_items (The 2nd list box) or I
can have one End_Item with many contracts(The 1st List box) The user
determines what they want to sort on either Contract, End_Item or None.
Example- If contract is selected the contract listbox (The 1st List box) is
populated with all contracts. When a user selects a proper contract Access
takes the value (ON_CLICK) and puts in the proper information needed to
populate the other list box End_Item (The second list box).
If users selects End_Item it is just works backwards, it will populate
Contracts. I need to make sure that every user has these options I want to
make sure all users start with blank listboxes hence why I was wanted to
blank out the row source type and row source.

If there is a better way I am open to suggestions.

Example for Populating End Items.
Forms!testform!End_Item.RowSourceType = "Table/Query"
Forms!testform!End_Item.RowSource = SELECT [END ITEM AND
CONTRACT].[Contract] FROM [END ITEM AND CONTRACT] WHERE End_Item = '" &
Me.End_Item & "' GROUP BY Contract;"

Hi, Dave,

[quoted text clipped - 53 lines]