Solved Smart combo boxes (1 Viewer)

zelarra821

Registered User.
Local time
Today, 20:30
Joined
Jan 14, 2019
Messages
803
I have copied the new function with the changes, but when I applied it to the form, it still gives me an error. If you could give me an example of how I should implement it, because I suppose you have tried it and it will have worked for you.

Thank you.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:30
Joined
May 21, 2018
Messages
8,463
give me an example of how I should implement it
I did not change 03-E Dialogo IVA, you seem to have done it correctly as far as I can tell. I only modified my class module and then it worked. Can you repost your db.
 

zelarra821

Registered User.
Local time
Today, 20:30
Joined
Jan 14, 2019
Messages
803
Here you have it. Note that in the form 03-E Dialogo IVA, 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. Right now, that does not happen.
 

Attachments

  • Tienda BD.zip
    1 MB · Views: 53

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:30
Joined
May 21, 2018
Messages
8,463
I will take a look, but as I said this was really designed for filtering big lists of strings. I also never though about filtering numbers, so did not account for that. One thing I noticed you have a lot of forms (03E-Dialogas) that could likely be combined into 1 or at least a few. They are so similiar. You could show, hide, and modify certain features based on openArgs (the form that called them). It would be a lot easier to maintain.
 

zelarra821

Registered User.
Local time
Today, 20:30
Joined
Jan 14, 2019
Messages
803
Ahh, okay, I did not understand you. I use numbers because I work with numeric codes. In fact, I've always worked like that.
As for simplifying the database, you have the whole reason of the world. I already use the OpenArgs to pass a custom title to the report. I will consider it for a future. Now I'm interested in solving this. If you could take a look, I'd appreciate it.

Thank you. Goodnight.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:30
Joined
May 21, 2018
Messages
8,463
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

You've got your good things, and you've got mine.
Local time
Today, 15:30
Joined
May 21, 2018
Messages
8,463
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];
 

zelarra821

Registered User.
Local time
Today, 20:30
Joined
Jan 14, 2019
Messages
803
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!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:30
Joined
May 21, 2018
Messages
8,463
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

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.

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

You've got your good things, and you've got mine.
Local time
Today, 15:30
Joined
May 21, 2018
Messages
8,463
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.
 

Users who are viewing this thread

Top Bottom