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
|
|
0
|
|
|
|
Reply
|
 Utf
|
12/8/2007 6:07:01 PM |
|
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.access.hookom.net/UCP/Default.htm
"Jazz57" wrote:
> 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
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/8/2007 8:23:00 PM
|
|
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:
> 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.access.hookom.net/UCP/Default.htm
>
>
> "Jazz57" wrote:
>
> > 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
> >
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/9/2007 8:43:01 PM
|
|
If 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:
> 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:
>
> > 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.access.hookom.net/UCP/Default.htm
> >
> >
> > "Jazz57" wrote:
> >
> > > 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
> > >
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/9/2007 9:19:01 PM
|
|
Good 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:
> If 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:
>
> > 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:
> >
> > > 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.access.hookom.net/UCP/Default.htm
> > >
> > >
> > > "Jazz57" wrote:
> > >
> > > > 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
> > > >
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/10/2007 9:13:00 AM
|
|
Good 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:
> If 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:
>
> > 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:
> >
> > > 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.access.hookom.net/UCP/Default.htm
> > >
> > >
> > > "Jazz57" wrote:
> > >
> > > > 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
> > > >
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/10/2007 9:31:00 AM
|
|
Once 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:
> If 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:
>
> > 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:
> >
> > > 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.access.hookom.net/UCP/Default.htm
> > >
> > >
> > > "Jazz57" wrote:
> > >
> > > > 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
> > > >
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/10/2007 9:40:00 AM
|
|
Hi 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:
> If 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:
>
> > 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:
> >
> > > 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.access.hookom.net/UCP/Default.htm
> > >
> > >
> > > "Jazz57" wrote:
> > >
> > > > 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
> > > >
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/10/2007 11:56:00 AM
|
|
You 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:
> Hi 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:
>
> > If 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:
> >
> > > 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:
> > >
> > > > 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.access.hookom.net/UCP/Default.htm
> > > >
> > > >
> > > > "Jazz57" wrote:
> > > >
> > > > > 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
> > > > >
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/10/2007 3:35:01 PM
|
|
Thanks 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:
> You 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:
>
> > Hi 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:
> >
> > > If 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:
> > >
> > > > 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:
> > > >
> > > > > 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.access.hookom.net/UCP/Default.htm
> > > > >
> > > > >
> > > > > "Jazz57" wrote:
> > > > >
> > > > > > 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
> > > > > >
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/10/2007 4:05:01 PM
|
|
Try 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:
> Thanks 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:
>
> > You 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:
> >
> > > Hi 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:
> > >
> > > > If 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:
> > > >
> > > > > 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:
> > > > >
> > > > > > 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.access.hookom.net/UCP/Default.htm
> > > > > >
> > > > > >
> > > > > > "Jazz57" wrote:
> > > > > >
> > > > > > > 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
> > > > > > >
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/10/2007 5:19:03 PM
|
|
Good 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 questions you made to find a solution
Once again , Many thanks to you
--
Adriano Santos
"Duane Hookom" escreveu:
> Try 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:
>
> > Thanks 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:
> >
> > > You 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:
> > >
> > > > Hi 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:
> > > >
> > > > > If 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:
> > > > >
> > > > > > 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:
> > > > > >
> > > > > > > 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.access.hookom.net/UCP/Default.htm
> > > > > > >
> > > > > > >
> > > > > > > "Jazz57" wrote:
> > > > > > >
> > > > > > > > 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....
> > > > > > > >
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/11/2007 3:02:04 PM
|
|
You 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:
> Good 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 questions you made to find a solution
>
> Once again , Many thanks to you
> --
> Adriano Santos
>
>
>
> "Duane Hookom" escreveu:
>
> > Try 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:
> >
> > > Thanks 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:
> > >
> > > > You 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:
> > > >
> > > > > Hi 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:
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/11/2007 3:34:04 PM
|
|
Hi 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:
> You 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:
>
> > Good 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 questions you made to find a solution
> >
> > Once again , Many thanks to you
> > --
> > Adriano Santos
> >
> >
> >
> > "Duane Hookom" escreveu:
> >
> > > Try 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:
> > >
> > > > Thanks 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:
> > > >
> > > > > You 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:
> > > > >
> > > > > > Hi 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
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/11/2007 4:36:06 PM
|
|
Again 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:
> Hi 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:
>
> > You 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:
> >
> > > Good 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 questions you made to find a solution
> > >
> > > Once again , Many thanks to you
> > > --
> > > Adriano Santos
> > >
> > >
> > >
> > > "Duane Hookom" escreveu:
> > >
> > > > Try 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:
> > > >
> > > > > Thanks 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:
> > > > >
> > > > > > You 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?
|
|
|
0
|
|
|
|
Reply
|
Utf
|
12/11/2007 4:45:01 PM
|
|
|
14 Replies
101 Views
(page loaded in 0.32 seconds)
|
Search |
Post Question |
Groups |
Stream |
About |
Register
6343 articles. 
16 followers. 