Populate a list box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We use several cmdbuttons to open the same form frmCust to display records.
We open the form with the following:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCust"
stLinkCriteria = "[StatusID] = 1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Each cmdbutton has the stLinkCriteria = "[StatusID] = "?"" changed.
"?" = 1 for Active, 2 for Inactive, 3 for Deceased, etc.

Once the form is open we use a list box to go to specific records. The
problem is the list box is based on the original form and reads:

SELECT [qryCust].[Full name] FROM [qryCust];

How can we limit the list box to only display the records in the current
recordset of the form using VBA?

Thanks!
 
You could pass the filter string as the OpenArgs argument in the
DoCmd.OpenForm method (in addition to using it in the Filter argument), and
then change the SQL statement of the list box's Row Source using that
string.

So, for example, you'd change the OpenForm action to this:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , stLinkCriteria

Then, in the stDocName form's Load event, read the OpenArgs value and change
the SQL statement of the listbox:

Private Sub Form_Load()
Dim strSQL As String
strSQL = "SELECT [qryCust].[Full name] FROM [qryCust]"
If Len(Me.OpenArgs & "") > 0 Then
strSQL = strSQL & " WHERE " & Me.OpenArgs
End If
Me.ListBoxName.RowSource = strSQL
End Sub
 
Thank you very much Ken
Your suggestion worked perfect first time! Perhaps you could help me with 2
other things.
1) I posted the question you answered in total 4 times with different
question names. The reason was my browser did not display the "All Threads"
dropdown list, the default was "Answered Questions", so when I could not find
it I reposted it. Is there a way to delete my redundant questions, so as to
not waste others time?
2)How can I set the value of an unbound text box using the cmdbutton that
opens the form?

Thanks again
Doug

Ken Snell (MVP) said:
You could pass the filter string as the OpenArgs argument in the
DoCmd.OpenForm method (in addition to using it in the Filter argument), and
then change the SQL statement of the list box's Row Source using that
string.

So, for example, you'd change the OpenForm action to this:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , stLinkCriteria

Then, in the stDocName form's Load event, read the OpenArgs value and change
the SQL statement of the listbox:

Private Sub Form_Load()
Dim strSQL As String
strSQL = "SELECT [qryCust].[Full name] FROM [qryCust]"
If Len(Me.OpenArgs & "") > 0 Then
strSQL = strSQL & " WHERE " & Me.OpenArgs
End If
Me.ListBoxName.RowSource = strSQL
End Sub
--

Ken Snell
<MS ACCESS MVP>



Dheinze57 said:
We use several cmdbuttons to open the same form frmCust to display
records.
We open the form with the following:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCust"
stLinkCriteria = "[StatusID] = 1"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Each cmdbutton has the stLinkCriteria = "[StatusID] = "?"" changed.
"?" = 1 for Active, 2 for Inactive, 3 for Deceased, etc.

Once the form is open we use a list box to go to specific records. The
problem is the list box is based on the original form and reads:

SELECT [qryCust].[Full name] FROM [qryCust];

How can we limit the list box to only display the records in the current
recordset of the form using VBA?

Thanks!
Click to expand...
Click to expand...
 
I must admit that I have no experience with the web interface to these
newsgroups -- I use Outlook Express. So I don't know if you can delete a
post through that web interface after it's been posted.

Regarding your second question, it depends.... Is the form that you open
always opened only from the initial form? What type of value do you want to
display -- one based on a value from the calling form, or one based on some
other default value?

Provide more details about what you want to do.
--

Ken Snell
<MS ACCESS MVP>



Dheinze57 said:
Thank you very much Ken
Your suggestion worked perfect first time! Perhaps you could help me with
2
other things.
1) I posted the question you answered in total 4 times with different
question names. The reason was my browser did not display the "All
Threads"
dropdown list, the default was "Answered Questions", so when I could not
find
it I reposted it. Is there a way to delete my redundant questions, so as
to
not waste others time?
2)How can I set the value of an unbound text box using the cmdbutton that
opens the form?

Thanks again
Doug

Ken Snell (MVP) said:
You could pass the filter string as the OpenArgs argument in the
DoCmd.OpenForm method (in addition to using it in the Filter argument),
and
then change the SQL statement of the list box's Row Source using that
string.

So, for example, you'd change the OpenForm action to this:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , ,
stLinkCriteria

Then, in the stDocName form's Load event, read the OpenArgs value and
change
the SQL statement of the listbox:

Private Sub Form_Load()
Dim strSQL As String
strSQL = "SELECT [qryCust].[Full name] FROM [qryCust]"
If Len(Me.OpenArgs & "") > 0 Then
strSQL = strSQL & " WHERE " & Me.OpenArgs
End If
Me.ListBoxName.RowSource = strSQL
End Sub
Click to expand...
Click to expand...


< snipped >
 
This is the same frmCust opened with the cmdButtons previously discussed.
I wish to just put the string "Active" or "Inactive" or "Deceased" in the
cmdButton code and have it displayed on the frmCust in an unbound text box.
I could populate the unbound text box based on the [StatusID] field,
however, the stLinkCriteria will occasionally filter on an additional field
like [OutOfState] in which case I would put "Active - Out of State" or
"Active - Local" to display on the filtered form.
I hope this is clear. Thank you for your time.

Doug

Ken Snell (MVP) said:
I must admit that I have no experience with the web interface to these
newsgroups -- I use Outlook Express. So I don't know if you can delete a
post through that web interface after it's been posted.

Regarding your second question, it depends.... Is the form that you open
always opened only from the initial form? What type of value do you want to
display -- one based on a value from the calling form, or one based on some
other default value?

Provide more details about what you want to do.
--

Ken Snell
<MS ACCESS MVP>



Dheinze57 said:
Thank you very much Ken
Your suggestion worked perfect first time! Perhaps you could help me with
2
other things.
1) I posted the question you answered in total 4 times with different
question names. The reason was my browser did not display the "All
Threads"
dropdown list, the default was "Answered Questions", so when I could not
find
it I reposted it. Is there a way to delete my redundant questions, so as
to
not waste others time?
2)How can I set the value of an unbound text box using the cmdbutton that
opens the form?

Thanks again
Doug

Ken Snell (MVP) said:
You could pass the filter string as the OpenArgs argument in the
DoCmd.OpenForm method (in addition to using it in the Filter argument),
and
then change the SQL statement of the list box's Row Source using that
string.

So, for example, you'd change the OpenForm action to this:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , ,
stLinkCriteria

Then, in the stDocName form's Load event, read the OpenArgs value and
change
the SQL statement of the listbox:

Private Sub Form_Load()
Dim strSQL As String
strSQL = "SELECT [qryCust].[Full name] FROM [qryCust]"
If Len(Me.OpenArgs & "") > 0 Then
strSQL = strSQL & " WHERE " & Me.OpenArgs
End If
Me.ListBoxName.RowSource = strSQL
End Sub
Click to expand...
Click to expand...


< snipped >
Click to expand...
 
OK - What I do in these situations is use the OpenArgs to pass a
concatenated string of values, where the string might look like this:

FormName|ValueForTextbox

where I'm using the pipe ( | ) character as the delimiter.

So, using the same code setup that we discussed, this would be an example of
your command button's code:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCust"
stLinkCriteria = "[StatusID] = 1"
DoCmd.OpenForm stDocName, , , stLinkCriteria, , , _
stLinkCriteria & "|" & "Active"


Then, in the called form:

Private Sub Form_Load()
Dim strSQL As String
Dim varOpenArgs As Variant
strSQL = "SELECT [qryCust].[Full name] FROM [qryCust]"
If Len(Me.OpenArgs & "") > 0 Then
varOpenArgs = Split(Me.OpenArgs)
strSQL = strSQL & " WHERE " & varOpenArgs(0)
Me.TextboxName.Value = varOpenArgs(1)
End If
Me.ListBoxName.RowSource = strSQL
End Sub

--

Ken Snell
<MS ACCESS MVP>


Dheinze57 said:
This is the same frmCust opened with the cmdButtons previously discussed.
I wish to just put the string "Active" or "Inactive" or "Deceased" in the
cmdButton code and have it displayed on the frmCust in an unbound text
box.
I could populate the unbound text box based on the [StatusID] field,
however, the stLinkCriteria will occasionally filter on an additional
field
like [OutOfState] in which case I would put "Active - Out of State" or
"Active - Local" to display on the filtered form.
I hope this is clear. Thank you for your time.

Doug

Ken Snell (MVP) said:
I must admit that I have no experience with the web interface to these
newsgroups -- I use Outlook Express. So I don't know if you can delete a
post through that web interface after it's been posted.

Regarding your second question, it depends.... Is the form that you open
always opened only from the initial form? What type of value do you want
to
display -- one based on a value from the calling form, or one based on
some
other default value?

Provide more details about what you want to do.
--

Ken Snell
<MS ACCESS MVP>



Dheinze57 said:
Thank you very much Ken
Your suggestion worked perfect first time! Perhaps you could help me
with
2
other things.
1) I posted the question you answered in total 4 times with different
question names. The reason was my browser did not display the "All
Threads"
dropdown list, the default was "Answered Questions", so when I could
not
find
it I reposted it. Is there a way to delete my redundant questions, so
as
to
not waste others time?
2)How can I set the value of an unbound text box using the cmdbutton
that
opens the form?

Thanks again
Doug

:

You could pass the filter string as the OpenArgs argument in the
DoCmd.OpenForm method (in addition to using it in the Filter
argument),
and
then change the SQL statement of the list box's Row Source using that
string.

So, for example, you'd change the OpenForm action to this:
DoCmd.OpenForm stDocName, , , stLinkCriteria, , ,
stLinkCriteria

Then, in the stDocName form's Load event, read the OpenArgs value and
change
the SQL statement of the listbox:

Private Sub Form_Load()
Dim strSQL As String
strSQL = "SELECT [qryCust].[Full name] FROM [qryCust]"
If Len(Me.OpenArgs & "") > 0 Then
strSQL = strSQL & " WHERE " & Me.OpenArgs
End If
Me.ListBoxName.RowSource = strSQL
End Sub
Click to expand...


< snipped >
Click to expand...
Click to expand...
 
Back
Top