Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-16-2019, 02:26 PM   #46
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 978
Thanks: 20
Thanked 250 Times in 243 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Smart combo boxes

Quote:
When you choose 2016, in the second drop-down should appear Tercer Trimestre y Cuarto Trimestre; and in 2017 and 2018, Primer Trimestre, Segundo Trimestre, Tercer Trimestre and Cuarto Trimestre
I think you are mistaken and has nothing to do with the FAYT. First you need to fix your sql string.

Code:
SELECT [01-D Trimestres]!Trimestre
is incorrect
Table names and field names are seperated by a . in SQL. A ! is used for Access collections in sql [Forms]![FormName]![ControlName]
Code:
SELECT [01-D Trimestres].Trimestre
However, once if fix it I added a debug.print strSql. I get
Code:
SELECT [01-d trimestres].[trimestre] & ' trimeste' AS Expr1, 
       [01-d trimestres].[c�digo del trimestre], 
       Year([fecha de la factura])                 AS Año 
FROM   [01-d trimestres] 
       INNER JOIN [01-e compras] 
               ON [01-d trimestres].[c�digo del trimestre] = 
                  [01-e compras].trimestre 
GROUP  BY [01-d trimestres].[trimestre] & ' trimeste', 
          [01-d trimestres].[c�digo del trimestre], 
          Year([fecha de la factura]) 
HAVING (( ( Year([fecha de la factura]) ) = '2016' )) 
ORDER  BY [01-d trimestres].[c�digo del trimestre], 
          Year([fecha de la factura])
I get
Code:
Expr1	Código del trimestre	Año
Primer trimeste	1	2016
Segundo trimeste	2	2016
Tercer trimeste	3	2016
Cuarto trimeste	4	2016
So you will get back Primer Trimestre, Segundo Trimestre, Tercer Trimestre and Cuarto Trimestre. Just looking at the data for 2016 there is clearly a record in every Trimestre.

MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
zelarra821 (01-18-2019)
Old 01-16-2019, 02:47 PM   #47
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 978
Thanks: 20
Thanked 250 Times in 243 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Smart combo boxes

If you want to FAYT on numbers then convert the filter to a string and if necessary keep the original value in the bound column. Here I used your postal codes. This makes sense to me. I think it is overkill on the very small pull downs you have. For sure it does not make sense for a list of 2016,2017,2018. To filter you have to type in the whole number since they only differ by the last digit. Would make more sense if you had something big like 1900 -2020.

Code:
SELECT [01-a codigos postales].id, 
       Cstr([codigopostal]) AS StrPostal 
FROM   [01-a codigos postales] 
ORDER  BY [01-a codigos postales].[codigopostal];
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
zelarra821 (01-18-2019)
Old 01-17-2019, 12:28 AM   #48
zelarra821
Newly Registered User
 
Join Date: Jan 2019
Posts: 25
Thanks: 29
Thanked 0 Times in 0 Posts
zelarra821 is on a distinguished road
Re: Smart combo boxes

He is right. There were values of the first and second quarter of 2016. I got involved because we started the store in July, but I did not remember that there were records from before.
I have changed the dates to try it, and the! for a point, and now it works.
What I'm going to do is finish testing it on all the other forms that are called "Diálogo", and I'll tell you. What I note for the next step when I finish with this is to simplify the 28 "Diálogos", because it is crazy to modify it when I have to do something.
What I do not understand is because to pass to the text string the values. What advantage does that bring me?
Thank you!

zelarra821 is offline   Reply With Quote
Old 01-17-2019, 04:40 AM   #49
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 978
Thanks: 20
Thanked 250 Times in 243 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Smart combo boxes

Quote:
What I'm going to do is finish testing it on all the other forms that are called "Diálogo"
It is up to you, but I do not see the utility of using find as you type on these comboboxes with a small list of values. I do not think you gain anything and it adds a little more complexity. It provides a lot of benefit for something like Articulo that is a long complex list of text. Especially where you might not know exactly what you are looking for. If you know what you are looking for like Mes than it may function better not to do a FAYT. or at least have it search from beginning. If I type in A (assume I want Agosto ), but if I tell it to search anywhere it returns mayo,marzo as well

Quote:
the next step when I finish with this is to simplify the 28 "Diálogos", because it is crazy to modify it when I have to do something
I think you should be able to consolidate a lot.

Quote:
What I do not understand is because to pass to the text string the values. What advantage does that bring me?
I do not understand the question. Can you provide specifics?
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
zelarra821 (01-18-2019)
Old 01-17-2019, 09:47 AM   #50
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 978
Thanks: 20
Thanked 250 Times in 243 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Smart combo boxes

Here is a generic Fecha_Personalizada.
This is how I like to make generic popups. I call them and then the calling form pulls the information it needs from the popup. (see the trick of hiding the form). This provides far more flexibility. Write it once, use it everywhere. The form simply "returns" a filter to the calling form if both the desde hasta are filled in. The calling form is responsible for doing something with it. I added a cancel button on the form as well. To make it easier to retrieve the values I added two public properties.


Code:
Option Compare Database
Option Explicit
Private mFiltro ' private variable to store filter
Private mArgumento 'private variable to store argument
Public Property Get ElFiltro() As String
  ElFiltro = mFiltro
End Property
Public Property Get MiArgumento() As String
  MiArgumento = mArgumento
End Property
Private Sub cmdCancel_Click()
  'If they cancel close the form and nothing happens
  DoCmd.Close acForm, Me.Name
End Sub
Private Sub cmdOK_Click()
 'need to be dates
 If IsDate(Me.txtDesdeF) And IsDate(Me.txtHastaF) Then
   'Ensure Start less than end
   If Me.txtDesdeF <= Me.txtHastaF Then
       'set the value of the filter since we have good dates
       ElFiltroFecha
       'Set it to invisible which will give control back to the calling form
       Me.Visible = False
   End If
 Else
        MsgBox "Es necesario introducir las dos fechas", vbCritical, "Faltan datos"
 End If
End Sub
Private Sub ElFiltroFecha()
  'You already ensured there is a date in the to from field
  Dim desde As Date
  Dim hasta As Date
  Dim strDesde As String
  Dim strHasta As String
  
  desde = Me.txtDesdeF
  hasta = Me.txtHastaF
  strDesde = "#" & Format(desde, "mm/dd/yyyy") & "#"
  strHasta = "#" & Format(hasta, "mm/dd/yyyy") & "#"
  
  'set the private variables
  mFiltro = " BETWEEN " & strDesde & " AND " & strHasta
  mArgumento = " - Del " & Year(desde) & " " & Format(desde, "mm") & " " & Format(desde, "dd") & " hasta el " & _
                Year(hasta) & " " & Format(hasta, "mm") & " " & Format(hasta, "dd")
End Sub
To use it anywhere to get a BETWEEN string you do something like
Code:
Private Sub Etiqueta100_Click()
  Dim frm As Form_Fecha_Personalizada  'Need Form_ before the name of the pop up
  Dim strFilter As String
  Dim strArgs As String
  DoCmd.OpenForm "Fecha_Personalizada", , , , , acDialog, "04-A Gastos;[Fecha de la Factura]"
  'code is stopped until the dialog is hidden or closed
  If CurrentProject.AllForms("Fecha_Personalizada").IsLoaded Then
    'If hidden it is loaded
    'If the user canceled out then you do not want to do anything anyways
     Set frm = Forms("Fecha_Personalizada")
     strFilter = frm.ElFiltro  'Public properties
     strArgs = frm.MiArgumento 'Public properties
     DoCmd.Close acForm, frm.Name
     DoCmd.OpenReport "04-A Gastos", acViewPreview, , "[Fecha de la Factura] " & strFilter, , strArgs
  End If
End Sub
if you want to use it for another form, the only thing that changes is the last line.
You can do the same thing with Anos, Anos Trimestre, Anos Mes, etc.
MajP is offline   Reply With Quote
The Following User Says Thank You to MajP For This Useful Post:
zelarra821 (01-18-2019)
Old 01-18-2019, 01:43 AM   #51
zelarra821
Newly Registered User
 
Join Date: Jan 2019
Posts: 25
Thanks: 29
Thanked 0 Times in 0 Posts
zelarra821 is on a distinguished road
Re: Smart combo boxes

Hi, thank you a lot for your help. Regards.

zelarra821 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Smart Searching using a combo box OzGreg General 9 12-13-2015 05:44 PM
Smart Search/Dumb Programmer - Populating a combo box; Error 2118 tfurnivall Modules & VBA 2 03-06-2013 02:19 PM
smart key prompt in combo box bartw72 Forms 2 07-14-2006 02:29 PM
Smart Combo Box? Frosty_4 Forms 2 09-04-2002 06:32 PM
Smart Combo Box? Zacharyjuniorman Forms 2 05-22-2001 05:52 PM




All times are GMT -8. The time now is 09:17 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World