Opening Form with InputBox or ComboBox?

[Giorgio]

Thanks all for your patience, a smal sample is on https://we.tl/t-8xlnjlwnLg

[Welshgasman]

Please just compact that DB, and zip it and upload here.

I do not want to agree to a bunch of T&C of a site I will likely never use again.

No even sure I can get the file unless I register either.? Something else I do not wish to do.?

[Giorgio]

Trying to attach it here DB 2021 Small sample.zip

[Welshgasman]

So what is the form that is meant to run when you select from the combo?

[Welshgasman]

OK,
I managed to track down what is meant to be used despite the language difficulty.
I used the Builder option to enter the criteria and mine came up as '[Forms]![MSKXSelezione]![CASCOMBXSelezione]' without the quotes ?

I have not used a foreign language access versions, but that criteria works (AS LONG AS THAT FORM WITH THE COMBO IS OPEN!)

HTH

[Welshgasman]

You might also want to set the copyright caption to
'© 1999 - ' & Year(Date())

so you do not need to change it every year?

[Giorgio]

Hi Welshgasman, yes it works thank you, really.

But opening the form, selecting the event and then clicking on the button, is somehow complicate.

what I would need is, from the Switchboard Panel, to click of the button to search the orders and not seeing this:



but this (image drawn by me, not real)



So having the form to pop up with the selection of events just after clicking on the button.
otherwiese, in 95% of the cases it will be quicker to remember the event code rather than having to search it on a form that will have to be opened on purpose.

If it is not possible, no problem we shall give up

Thansk anyway for your time. And yes the complete copyright says also 2021...

[Welshgasman]

I am not sure of your logic.?
If I wanted to use your process, then I would have a button on that form with the combo, that opened the ordini form.?

OR I would have combos on the Ordini form that allowed me to filter for relevant data.?

Not knowing your business, hard for me to say, but if I was doing it from what I know now, I would use the second option?

OR again put the combo on that switchboard form as you have drawn?, but I do not know if other combos would be involved, so still the second option for me, so each form is filtered for whatever data is required.

[Giorgio]

I'd need to have a combo opening upon clicking the red button (Gestione Ordini), as in the image I have drawn.

So, without having to open the form first, select the event, going back to the Switchboard and then click on the red button to open the form with the results.

It should be:
1) I click on the red button (Gestione Ordini) on the switchboard
2) The combo box for the event selection pops out.
3) I select the event (maybe i have to add an OK button?)
4) The form with the results opens

I hope it is clear, thanks

[Welshgasman]

So change the switchboard to open the Selection form and not the ordini form.?
Then an event, (double click perhaps) opens the ordini form.?

However if the other buttons need to do the same thing, then you would need to tell the selection form, which form to open after a combo record had been selected.?

However as you are using the switchboard, that involves, either changing the code behind it and/or adding extra fields to switchboard table?
I've done that in the past for security levels for forms, but it is not an easy task TBH.

Perhaps open the selection form as a popup in the Form Open of ordini, set the value to a TempVar, and close the popup form. The form will then use the TempVar as it's criteria.

I've not used a form in that way, so do not know when it looks at it's data source, also you can set that up anyway with VBA.

[Giorgio]

I would say that the option:

So change the switchboard to open the Selection form and not the ordini form.?
Then an event, (double click perhaps) opens the ordini form.?

is the easiest one, I think. No idea if a double click or a OK button to confirm, whatever is easier---

[Welshgasman]

Well I tested with a tempvar and the selection as a popup, however it appears the source is registered as soon as the form is opened, so it was always one value behind.

The trouble with your method now, is that the forms are all 'tied' to each other.? You would not be able to open ordini, unless you use selection.?

So I am guessing all your switchboard options would need selection forms first.?

In that case I would go with the combos on each form header and set filters?

I think you need Vlad to advise more than me, as he has way way more experience and would know of other ways.? Sorry.

[Gicu]

Hi Giorgio,
You can change the code on the switchboard to open the Selection form as a dialog -WidowMode (Docmd.OpenForm.......acDialog), and add a button on it to open the Ordini form for the selected event.
Cheers,

[Giorgio]

YESSS! it worked, I changed the Switchboard to open the selection form where I had added a button to open the orders from. THANK YOU! it is now exactly as we needed it.

I thought to do the same with the Contacts form (the form which opens not the orders, but the contacts we have for each event) but I faced another problem.
The contact form has, upon opening after having chosen the event, a check if there are contacts for the selected event, and the a second option, whether we want to include the VOID contacts or not (the ones who told us they are not attending the event).
This is because if a company does not order, its order simply does not exist, while for the contacts, even if at the end they do not particpate, the contacts exists.

The opening of the from is under VBA, as follows:

Private Sub Form_Open(Cancel As Integer)Const cstrORDER = "ORDER BY TBLClienti.NomeAzienda, TBLClienti.CodiceCliente"
    
    Dim strFiera As String
    Dim strSQL As String
    Dim strWHERE As String
    
    strSQL = "SELECT TBLContatti.SiglaFiera, TBLClienti.NomeAzienda, TBLClienti.CodiceCliente, " & _
             "       TBLClienti.Località, TBLClienti.Provincia, TBLClienti.Regione, TBLClienti.Nazione, TBLClienti.PrivacyInviata, TBLClienti.DataInvioPrivacy, TBLClienti.Telefono1, " & _
             "       TBLClienti.Telefono2, TBLClienti.Telefono3, TBLClienti.Fax1, TBLClienti.Fax2, " & _
             "       TBLClienti.[Email1], TBLClienti.[Email2], TBLClienti.SitoWEB1, TBLClienti.SitoWEB2, " & _
             "       TBLClienti.FormaGiuridica, TBLClienti.DataAggiornamento, TBLClienti.[AuguriNatale], " & _
             "       TBLClienti.[ClienteParalleli], TBLClienti.Sospeso " & _
             "FROM TBLClienti " & _
             "INNER JOIN TBLContatti ON TBLClienti.CodiceCliente = TBLContatti.CodiceCliente "
             


    strFiera = InputBox("FIERA", "INSERISCI LA FIERA")
    If DCount("IDContatto", "TBLContatti", "SiglaFiera = '" & strFiera & "'") = 0 Then
        MsgBox "NON ESISTONO CONTATTI PER QUESTA FIERA", vbOKOnly + vbInformation, "MI DISPIACE"
        Cancel = True
        Exit Sub
    End If
    strWHERE = "WHERE (TBLContatti.SiglaFiera = '" & strFiera & "')"
    
    If MsgBox("Vuoi visualizzare anche i VOID?", vbYesNo + vbCritical + vbDefaultButton2, "SCEGLI SI o NO") = vbYes Then
        ' in questo caso devi visualizzare tutti i contatti
        strSQL = strSQL & " " & strWHERE & ""
    Else
        ' in questo caso devi visualizzare solo i contatti che non sono VOID
        strSQL = strSQL & " " & strWHERE & " AND (TBLContatti.VOID = False) "
    End If
    
    strSQL = strSQL & " " & cstrORDER
    
    Me.RecordSource = strSQL
End Sub

So far, the instruction

strFiera = InputBox("FIERA", "INSERISCI LA FIERA")

opens a small input box where we have to input the event code (having to remeber it). The Italian text says "INSERT THE EVENT"

I created a different form for event selection called MSKXSelezioneFieraOrdini, identical to the other selection form, but having the button opening the contacts form (MSKContattiFiera) and not the orders one.

and I changed the code to:

Private Sub Form_Open(Cancel As Integer)Const cstrORDER = "ORDER BY TBLClienti.NomeAzienda, TBLClienti.CodiceCliente"
    
    Dim strFiera As String
    Dim strSQL As String
    Dim strWHERE As String
    
    strSQL = "SELECT TBLContatti.SiglaFiera, TBLClienti.NomeAzienda, TBLClienti.CodiceCliente, " & _
             "       TBLClienti.Località, TBLClienti.Provincia, TBLClienti.Regione, TBLClienti.Nazione, TBLClienti.PrivacyInviata, TBLClienti.DataInvioPrivacy, TBLClienti.Telefono1, " & _
             "       TBLClienti.Telefono2, TBLClienti.Telefono3, TBLClienti.Fax1, TBLClienti.Fax2, " & _
             "       TBLClienti.[Email1], TBLClienti.[Email2], TBLClienti.SitoWEB1, TBLClienti.SitoWEB2, " & _
             "       TBLClienti.FormaGiuridica, TBLClienti.DataAggiornamento, TBLClienti.[AuguriNatale], " & _
             "       TBLClienti.[ClienteParalleli], TBLClienti.Sospeso " & _
             "FROM TBLClienti " & _
             "INNER JOIN TBLContatti ON TBLClienti.CodiceCliente = TBLContatti.CodiceCliente "
             


    strFiera = "MSKXSelezioneFieraOrdini.CASCOMBXSelezione"
    If DCount("IDContatto", "TBLContatti", "SiglaFiera = '" & strFiera & "'") = 0 Then
        MsgBox "NON ESISTONO CONTATTI PER QUESTA FIERA", vbOKOnly + vbInformation, "MI DISPIACE"
        Cancel = True
        Exit Sub
    End If
    strWHERE = "WHERE (TBLContatti.SiglaFiera = '" & strFiera & "')"
    
    If MsgBox("Vuoi visualizzare anche i VOID?", vbYesNo + vbCritical + vbDefaultButton2, "SCEGLI SI o NO") = vbYes Then
        ' in questo caso devi visualizzare tutti i contatti
        strSQL = strSQL & " " & strWHERE & ""
    Else
        ' in questo caso devi visualizzare solo i contatti che non sono VOID
        strSQL = strSQL & " " & strWHERE & " AND (TBLContatti.VOID = False) "
    End If
    
    strSQL = strSQL & " " & cstrORDER
    
    Me.RecordSource = strSQL
End Sub

Imagining that the instruction

 strFiera = "MSKXSelezioneFieraOrdini.CASCOMBXSelezione"

, replacing the one calling for the InputBox, would have opened the orders form.

But the only thing I get is the message "There are no contacts for this event" (in Italian: NON ESISTONO CONTATTI PER QUESTA FIERA") as, most probably the strFiera is not recognized as the right event code, so the answer is that there are no contacs. Or maybe all the

'" & strFiera & "'

has to be changed now that he input comes from a form and is not inputted in the InputBox, I do not know...

[Gicu]

Hi Giorgio,
It doesn't work that way, simple referencing the combo will not open the form. You have some alternatives depending if you want to open the contacts form in an "unfiltered" state (to show all contacts for all events. If not and you want to open it always showing the event then the easiest is to do it similarly to the orders: the button opens the selection form with the combo and the button to open the contacts. On that selection form also add an unbound checkbox where you can select if to include the Void or not (SHOW VOID?). Comment out the code in the open event of the contacts form and edit its recordsource to take the event code and Void from the new selection form. If you want to include all events use Like Forms!NewSelectionForm!SiglaFiera & "*".
Cheers,
Vlad