Developer Forum

Goos evening:

i posted a question on this group about the same subject but maybe i didn't
explained myself very clear.

the problem is:

i have three comboboxes named Cmb1 and Cmb2 and Cmb3. I want to populate a
listbox with the result of the values filled in the 3 comboboxes.

When i load the form the listbox is populated with all the records that i
have in my source query

After this, If i fill a value in Cmb1 or Cmb2 or Cmb3 or in all of them , i
want that the listbox "refresh" and presents the records that fullfill the
values in the comboxes....

I think this is possible but i don't know how to do it. i thought in SQL
string to select the records, but sincerely i don't know how to do it...

I think this situation is much more complex than i thought ( a simple Form
and subform) and now i am very desperate to end my program.


Any help will be welcome

Thanks to all
-
Adriano Santos

How do you plan on using the results of the combo boxes? Are there fields in
the Row Source of your list box that should be filtered based on the combo
boxes? How about providing some information about your combo box row sources
and the list box row source.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.yqcomputer.com/

Good evening

Efectively, the records on the list box should be the records that are
filtered by the three comboboxes.

Fields in Combobox:
CodSector <=> Cmb1
CentroAnalise <=> Cmb2
Funcionario <=> Cmb3

DataDados <=> Between Datetime Picker1 and DateTimePicker2


Cmb1 Row Source => QrySectores
Cmb2 Row Source => QryCentrosAnalise
Cmb3 Row Source => QryColaboradores

DataDados RowSource => QryDados_PorData

Those are the fields that after being filled must filter the records and
populate the listbox


I expect to answer your questions and help you to help me.

Thanks in advance.

Any other questions, please just let me know

Thanks a lot

--
Adriano Santos



"Duane Hookom" escreveu:

f I understand correctly, I would write a function or sub to create the SQL
for the Row Source of your list box. Assuming CodSector and CentroAnalise are
text and Funcionario is numeric and your list box name is DataDados. Your
code would be in your form's module and could be called from the after update
event of your criteria controls.

Function BuildListRowSource()
Dim strSQL as String
Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.Cmb1) Then
strWhere = strWhere & " And [CodSector]=""" & _
Me.Cmb1 & """ "
End If
If Not IsNull(Me.Cmb2) Then
strWhere = strWhere & " And [CentroAnalise]=""" & _
Me.Cmb2 & """ "
End If
If Not IsNull(Me.Cmb3) Then
strWhere = strWhere & " And [Funcionario]=" & _
Me.Cmb3
End If
strSQL = "SELECT * FROM QryDados_PorData WHERE " & strWhere
Me.DataDados.RowSource = strSQL
End Function
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


"Jazz57" wrote:

ood morning

there are 2 things i didn't quit understood and 1 that i might explain not
so good:

1. Could it be a function or sub ? I have some problems with the functions.

2. My list box's name is List101 although i can name it LstDataDados.
DataDados is a field from my table where i have 2 DateTime Picker where i
put the StartDate and the FinalDate to filter the records to populate the
ListBox.

For instances, i can filter my records from Cmb1 , cmb1 cmb3 and between
StartDate and FinalDate.


Hope to clarify the situation. I know that the date fields have some trouble
to filter them. I have another post where i am dealing with ("Boolean Nulls")

Nevertheless, thanks for all the help and support




--
Adriano Santos



"Duane Hookom" escreveu:

ood morning and once again:

on My listbox's Row Source is a SQL statement that populates the all list.
This listbox has been built with an wizard.

Just to help your thoughts.

This might be understood as something like that.

" When i open the form i show all the records. After i fill the forms and
the dattime picker, the listbox show me only the records that match the combo
boxes and datetime picker values"

Sorry for didn't mentioned on the previous reply

Best regards and thanks a lot again

--
Adriano Santos



"Duane Hookom" escreveu:

nce again, understanding your kindly reply:

When do you say "could be called from the after event of your criteria
controls", do you mean that i have to put this code on every combo i have ?

Thanks again for your help


--
Adriano Santos



"Duane Hookom" escreveu:

i Duane:

i tried the code you mentioned as a SUB and didn't worked. So, i think is
better to put everything right to you, so you can be inside the problem.

Please consider this as the resume and problem itself

I have a form with 3 comboboxes, 2 datetime Picker, 6 chk boxes and i listbos

Function: to filter records from a query file and display them on a listbox

CmbSector
Row Source => Select TSectores.CodSector from TSectores;
CmbCentroAnalise
Row Source => Select TCentrosAnalise.CodCentroAnalise From TCentrosAnalise;
CmbFuncionario
Row Source => Select TColaboradores.CodColaborador From TColaboradores;

The 2 DateTime Picker are reserved for the StartDate and Final Date

The 6 Check Boxes are for the fields that i want to view displayed on the
listbox
If they are not checked, all the fields are viewed, otherwise only one field
can be checked.
There is a set of 8 fields that are common and then if i check one check box
i see plus that particular field.

List Box LstInqueritos
RowSource => Select * From QPorSector_Datas (in this query exists all the
fields i need)

According to your 2nd reply you mentioned that "could be called from the
after update event of your criteria controls".


Q1: Do i have to use a function ? If yes how do i do it ?

Q2: Do i have to repeat the function or sub in every control (combobox and
DateTime Picker) i have?

Q3: The listbox Row Source should be empty ( I guess ) otherwise she will
populate allways with the same information , right ?


Maybe i should do this before, but i thought that there will be no such
problems that i am dealing now.

Please accept my apologies and will wait for your help and support on this
matter

Many Thanks...

Maybe this issue should be shown to all community in order they don't do the
same mistakes i did and they don't ocupy the time of people that wants to
help us.

Once again, thanks for your help
--
Adriano Santos



"Duane Hookom" escreveu:

ou will need code (or a macro) to update the list box after you have changed
your control values.

What are the data types of CodSector, CodCentroAnalise, and CodColaborador?

Are the fields in LstInqueritos the same names ie: CodSector,
CodCentroAnalise, and CodColaborador?

What are the names of the two date controls? What is the name of the date
field in your LstInqueritos list box Row Source?

What are the names of the check box controls? Are they actually members of
an option group so that only one can be selected? Are you suggesting the
number of columns/fields in LstInqueritos will be determined by which check
box is selected? Or, are the check boxes used to filter the records?
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


"Jazz57" wrote:

hanks for your prompty reply.

I will answer according to your questions:

CodSector text (string)
CodCentroAnalise text (string)
CodColaborador integer

------------------------

On ListBox they have the following names
ListBox
CodSector CodSector
CodCentroAnalise CodigoCentroAnalise
CodColaborador Funcionario

------------------------
Date Controls name in LstInqueritos

DTPDataInicial DataDados
DTPDataFinal DataDados

------------------------
Name of Check Boxes (they are not part of a option Group)
Yes. If none of checkboxes is selected, all the fields are viewed, otherwise
only
a few are viewed according to the checkbox is selected.
They are not used to filter the records, just to view the fields.

Names

ChkPerformance
ChkActividade
ChkOcupacao
ChkIntProd
ChkIntNProd
ChkIntTotal

--------------------------------------------------------------------------------------

i think i answered all your questions.

If by mistake i forgot something, please just let me know


Thanks again for your patient, help and support


------------------------
--
Adriano Santos



"Duane Hookom" escreveu:

ry code like:
Function BuildListRowSource()
Dim strSQL as String
Dim strWhere as String
strWhere = "1 = 1 "
If Not IsNull(Me.CmbSector) Then
strWhere = strWhere & " And [CodSector]=""" & _
Me.CmbSector & """ "
End If
If Not IsNull(Me.CmbCentroAnalise) Then
strWhere = strWhere & " And [CodigoCentroAnalise]=""" & _
Me.CmbCentroAnalise & """ "
End If
If Not IsNull(Me.CmbFuncionario ) Then
strWhere = strWhere & " And [Funcionario]=" & _
Me.CmbFuncionario
End If
If not IsNull(Me.DTPDataInicial) Then
strWhere = strWhere & " And [DataDados] >=#" & _
Me.DTPDataInicial & "# "
End If
If not IsNull(Me.DTPDataFinal) Then
strWhere = strWhere & " And [DataDados] <=#" & _
Me.DTPDataFinal & "# "
End If
strSQL = "SELECT * FROM QryDados_PorData WHERE " & strWhere
debug.Print strSQL
Me.LstInqueritos.RowSource = strSQL
End Function

You then need to update the After Update property of each of the significant
controls to
After Update: =BuildListRowSource()
You can select all significant controls and set their properties at once.

If you really think you need to change the columns displayed, you will need
to add code that changes the "SELECT *..." to "SELECT FieldA, FieldB,
FieldC,..." as well as change the List boxes Number of columns and column
widths properties.

If you try this and it doesn't work. Press Ctrl+G to find out the value of
strSQL. Post this back to use along with your complete code.


--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


"Jazz57" wrote:

ood Afternoon:

After i wrote the code you sent, (Thanks very much for it) i had some
problems.

1 - When i uptaded the CmbSector (1st Combo) all the records from the
listbox disapeared. It is suposed to stay one.

2 - On DateTime Picker i haven't the afterupate property.

3 - the Format of date is a timestamp format (date time) and as you can see
on debug from SQL . It could be one of the problems.

----------------------------------------------
My code:

Function BuildListRowSource()

Dim strSQL As String
Dim strwhere As String

strwhere = "1 = 1 "

If Not IsNull(Me.CmbSector) Then
strwhere = strwhere & " and [CodSector]=""" & _
Me.CmbSector & """ "
End If

If Not IsNull(Me.CmbCentroAnalise) Then
strwhere = strwhere & " and [CodigoCentroAnalise] = """ & _
Me.CmbCentroAnalise & """ "
End If

If Not IsNull(Me.CmbFuncionario) Then
strwhere = strwhere & " and [Funcionario] = " & _
Me.CmbFuncionario
End If

If Not IsNull(DTPDataInicial) Then
strwhere = strwhere & " and [DataDados] >=#" & _
Me.DTPDataInicial & "# "
End If

If Not IsNull(DTPDataFinal) Then
strwhere = strwhere & " and [DataDados] <=#" & _
Me.DTPDataFinal & "# "
End If

strSQL = "DELECT * FROM QryPorSector_Dados WHERE " & strwhere
Debug.Print strSQL
Me.LstInqueritos.RowSource = strSQL


End Function

On design view, i changed the after update property of the 3 comboboxes to
=[BuildListRowSource]

i didn't do it to the DTPDataInicial as well to DTPDataFinal

--------------------------------------
Debug Print

DELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="COLADOS" and
[DataDados] >=#30-11-2007 14:47:24# and [DataDados] <=#11-12-2007 14:38:28#
DELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="COLADOS" and
[DataDados] >=#30-11-2007 14:47:24# and [DataDados] <=#11-12-2007 14:38:28#
DELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="COLADOS" and
[DataDados] >=#30-11-2007 14:47:24# and [DataDados] <=#11-12-2007 14:38:28#
DELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="COLADOS" and
[CodigoCentroAnalise] = "CAM 2500" and [DataDados] >=#30-11-2007 14:47:24#
and [DataDados] <=#11-12-2007 14:38:28#
DELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="COLADOS" and
[CodigoCentroAnalise] = "CAM 2500" and [DataDados] >=#30-11-2007 14:47:24#
and [DataDados] <=#11-12-2007 14:38:28#
DELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="COLADOS" and
[CodigoCentroAnalise] = "CAM 2500" and [DataDados] >=#30-11-2007 14:47:24#
and [DataDados] <=#11-12-2007 14:38:28#

-------------------------------------------------

For the purpose of viewing the fields, i am going to do the following:

if ChkPerformance or ChkActividade..... = "No" then
SELECT * FROM QryPorSector_Dados WHERE ....

endif

if ChkPerformance = "Yes" then
SELECT CodSector, CodCentroAnalise, ... , Performance FROM
QryPorSector_Dados WHERE ....
endif

--------------------------------------------------------------------------------------

i hope that i have replyed to all the

ou can change your code to use the proper date formatting with the following
code. Also, I'm not sure about your language but I think this:
strSQL = "DELECT * FROM QryPorSector_Dados WHERE " & strwhere
should be:
strSQL = "SELECT * FROM QryPorSector_Dados WHERE " & strwhere

Function BuildListRowSource()

Dim strSQL As String
Dim strwhere As String

strwhere = "1 = 1 "

If Not IsNull(Me.CmbSector) Then
strwhere = strwhere & " and [CodSector]=""" & _
Me.CmbSector & """ "
End If

If Not IsNull(Me.CmbCentroAnalise) Then
strwhere = strwhere & " and [CodigoCentroAnalise] = """ & _
Me.CmbCentroAnalise & """ "
End If

If Not IsNull(Me.CmbFuncionario) Then
strwhere = strwhere & " and [Funcionario] = " & _
Me.CmbFuncionario
End If

If Not IsNull(DTPDataInicial) Then
strwhere = strwhere & " and [DataDados] >=#" & _
Format(Me.DTPDataInicial, "mm\/dd\/yyyy") & "# "
End If

If Not IsNull(DTPDataFinal) Then
strwhere = strwhere & " and [DataDados] <=#" & _
Format(Me.DTPDataFinal, "mm\/dd\/yyyy") & "# "
End If

strSQL = "DELECT * FROM QryPorSector_Dados WHERE " & strwhere
Debug.Print strSQL
Me.LstInqueritos.RowSource = strSQL


End Function

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


"Jazz57" wrote:

i again

My mistake with DELETE x SELECT. it's OK now

But nevertheless, the records still don't appear. And i know they might
because i have at least 1 record that match the selection....
------------------------------------------
Debug.Print SQL

What do you think about my aproach to view fields? you mentioned something
about ListBoxes Number and Column Width Properties...

that information must be coded, right?

Thanks again for your help
--
Adriano Santos



"Duane Hookom" escreveu:

gain i forgot to mention one thing:

In order to appear something when i open the Form, the row source of my
listbox is with and instruction like that:

SELECT QPorSector_Datas.CodSector, QPorSector_Datas.CodigoCentroAnalise,
QPorSector_Datas.DataDados, QPorSector_Datas.Funcionario,
QPorSector_Datas.NomeBreve, QPorSector_Datas.TempoPresenca,
QPorSector_Datas.TempoPadrao, QPorSector_Datas.TempoTrabalhado,
QPorSector_Datas.IntProdutivas, QPorSector_Datas.IntNaoProdutivas,
QPorSector_Datas.Performance, QPorSector_Datas.Actividade,
QPorSector_Datas.TaxaOcupacao FROM QPorSector_Datas ORDER BY [CodSector],
[CodigoCentroAnalise], [DataDados], [Funcionario];

Question: should it be there or the Row Source must be empty?

Please forgive me for not mentioned it.

---------------------------------------------------------
Debug.Print SQL (forgot previous answer)

SELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="TRANSFERES"
and [CodigoCentroAnalise] = "TRANSFERES" and [DataDados] >=#11/30/2007# and
[DataDados] <=#12/11/2007#
SELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="TRANSFERES"
and [CodigoCentroAnalise] = "TRANSFERES" and [Funcionario] = 337 and
[DataDados] >=#11/30/2007# and [DataDados] <=#12/11/2007#
SELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="TRANSFERES"
and [CodigoCentroAnalise] = "TRANSFERES" and [Funcionario] = 337 and
[DataDados] >=#11/30/2007# and [DataDados] <=#12/11/2007#
SELECT * FROM QryPorSector_Dados WHERE 1 = 1 and [CodSector]="TRANSFERES"
and [CodigoCentroAnalise] = "TRANSFERES" and [Funcionario] = 337 and
[DataDados] >=#11/30/2007# and [DataDados] <=#12/11/2007#
SELECT * FROM QPorSector_Dados WHERE 1 = 1 and [CodSector]="TRANSFERES"
and [DataDados] >=#11/30/2007# and [DataDados] <=#12/11/2007#
SELECT * FROM QPorSector_Dados WHERE 1 = 1 and [CodSector]="TRANSFERES"
and [DataDados] >=#11/30/2007# and [DataDados] <=#12/11/2007#
SELECT * FROM QPorSector_Dados WHERE 1 = 1 and [CodSector]="TRANSFERES"
and [DataDados] >=#11/30/2007# and [DataDados] <=#12/11/2007#
SELECT * FROM QPorSector_Dados WHERE 1 = 1 and [CodSector]="TRANSFERES"
and [CodigoCentroAnalise] = "TRANSFERES" and [DataDados] >=#11/30/2007# and
[DataDados] <=#12/11/2007#
SELECT * FROM QPorSector_Dados WHERE 1 = 1 and [CodSector]="TRANSFERES"
and [CodigoCentroAnalise] = "TRANSFERES" and [DataDados] >=#11/30/2007# and
[DataDados] <=#12/11/2007#
SELECT * FROM QPorSector_Dados WHERE 1 = 1 and [CodSector]="TRANSFERES"
and [CodigoCentroAnalise] = "TRANSFERES" and [DataDados] >=#11/30/2007# and
[DataDados] <=#12/11/2007#
SELECT * FROM QPorSector_Dados WHERE 1 = 1 and [CodSector]="TRANSFERES"
and [CodigoCentroAnalise] = "TRANSFERES" and [Funcionario] = 337 and
[DataDados] >=#11/30/2007# and [DataDados] <=#12/11/2007#
SELECT * FROM QPorSector_Dados WHERE 1 = 1 and [CodSector]="TRANSFERES"
and [CodigoCentroAnalise] = "TRANSFERES" and [Funcionario] = 337 and
[DataDados] >=#11/30/2007# and [DataDados] <=#12/11/2007#
SELECT * FROM QPorSector_Dados WHERE 1 = 1 and [CodSector]="TRANSFERES"
and [CodigoCentroAnalise] = "TRANSFERES" and [Funcionario] = 337 and
[DataDados] >=#11/30/2007# and [DataDados] <=#12/11/2007#

---

Thanks again


--
Adriano Santos



"Jazz57" escreveu: