Page 1 of 3 123 LastLast
Results 1 to 15 of 39
  1. #1
    Giorgio is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2021
    Posts
    36

    Opening Form with InputBox or ComboBox?

    Hello everybody.

    I have a form that, before opening, asks for a value in a pop up box. Based on this value the Form will open showing the related records (Clients and Orders)

    We have different events and would like to show in the mask all the orders for a specific event (which is the value to be inputted)
    The query below generates the pop up and asks for the input

    Code:
    SELECT TBLOrders.EventCode, TBLEvent.*, TBLClient.*, TBLOrders.* FROM TBLEvent 
    INNER JOIN (TBLClient INNER JOIN TBLOrders ON TBLClient.IDClient = TBLOrders.IDClient) ON TBLEvent.EventCode= TBLOrders.EventCode 
    WHERE (((TBLOrders.EventCode)=[INPUT EVENT])) 
    ORDER BY TBLClient.CompanyName;
    It works well.

    But so far we have to remember the EventCode to input in the message box. As in fact the EventCode already exists in the Database, I'd like to show the list of all the events in the pop up message box.


    It sounds like tranforming the input box in a combo box, linked to TBLEvent.EventCode or to TBLOrders.EventCode, so that instead of having to remember the EventCode, we can choose it form a list.

    Any suggestion? Thanks in advance

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    You should use a form (hopefully you have a main form from where you call this) and add a combo to that to list your events (include the event code and name and hide the code). Remove the event parameter from the query and replace it with a reference to the combo.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    Giorgio is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2021
    Posts
    36
    Yes I have a form where the Event data are stored (MSKTBLEvent) and I created there a (hidden) combobox with the two fields EventCode and EventName.
    Then I changed the query to
    Code:
    SELECT TBLOrders.EventCode, TBLEvent.*, TBLClient.*, TBLOrders.* FROM TBLEvent INNER JOIN (TBLClient INNER JOIN TBLOrders ON TBLClient.IDClient = TBLOrders.IDClient) ON TBLEvent.EventCode= TBLOrders.EventCode 
    WHERE (((TBLOrders.EventCode)=[Forms].[MSKTBLEvent].[COMBOBOXXSelection]]))  
    ORDER BY TBLClient.CompanyName;
    but it did not work.
    The only thing that changed was the title of the Msg box that now insead of displaying INPUT EVENT, shows [Forms].[MSKTBLEvent].[COMBOBOXXSelection]... but the msgbowx did not become a combo...

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    If it is a hidden combobox how is the user meant to select an item?
    Plus you have two ]] after the combo name in the query. Not sure if that would matter though.?

    Debug.print [Forms].[MSKTBLEvent].[COMBOBOXXSelection] and see what it produces.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Giorgio,
    The msgbox will not become a combo; it will be replaced by a combo. So you need to turn the combo visible so the users can select the event first and then click the button to open the event form. In your query you need to add Forms![MSKTBLEvent].[COMBOBOXXSelection] (or better use the Builder in query design to browse to the control on the form. And just to be sure we are assuming that you already have the MSKTBLEvent form open with the combo and a command button and you want to open another form (mentioned in the original post) to view the details of the selected event.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  6. #6
    Giorgio is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2021
    Posts
    36
    I think I did not make myself clear. We organize events and we have a database in which we have, in separate tables, Companies (TBLClienti), Events (TBLFiere) and Orders (TBLOrdini). On the mail Control Panel, there's a button (Gestione Ordini) that, once clicked, opens a form (MSKTBLOrdini) showing the orders for one event. In order to select which event we want to look into, BEFORE that MSKTBLOrdini opens, a pop up message will ask which event we want to choose.

    As per the Orders.jpg imageClick image for larger version. 

Name:	Orders.jpg 
Views:	29 
Size:	32.1 KB 
ID:	44777 this pop up message shows a field in which we have to insert the event code. And, so far, we have to remember the event code.
    What we would need is to transform this message box into a combo box (or any other a tool) that will propose the list of events, rather than having to remember the code we want.

    To answer Welshgasman, the double ]] was just my mistake in pasting the code in the forum, but in the database the bracket is only one.


    I repeat here the SQL we use to open the MsgBox (first) and then, once inputted the event code (SiglaFiera) the form MSKTBLOrdini :

    Code:
    SELECT TBLOrdini.SiglaFiera, TBLFiere.NomeCompleto, TBLFiere.Edizione, TBLFiere.InizioFiera, TBLFiere.FineFiera, TBLFiere.Cittą, TBLFiere.Padiglione, TBLClienti.CodiceCliente, TBLClienti.NomeAzienda, TBLClienti.Localitą, TBLClienti.Provincia, TBLOrdini.* 
    FROM TBLFiere INNER JOIN (TBLClienti INNER JOIN TBLOrdini ON TBLClienti.CodiceCliente = TBLOrdini.CodiceCliente) ON TBLFiere.SiglaFiera = TBLOrdini.SiglaFiera 
    WHERE (((TBLOrdini.SiglaFiera)=[Immettere Fiera])) 
    ORDER BY TBLClienti.NomeAzienda;


    I assume, but I may be wrong, that we have to change something in the part
    Code:
    WHERE (((TBLOrdini.SiglaFiera)=[Immettere Fiera]))
    (Immettere Fiera means Insert Event) in order to have here a combo box or anyway a suggestion, a list of all the events , so to be able to choose it from a list rather than having to remember it.

    Thanks again


  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Open the query in the query editor, and use the Builder to get the correct syntax for the criteria for your form control.
    Form will need to be open for this to work, else you will just get prompted again?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    Giorgio is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2021
    Posts
    36
    Still trying.. I created a new form named MSKXSelezione with only a combo box displaying the event code (Fiera) and the event name (Nome), as per below image. the combo box name is CASCOMBXSelezione.

    Click image for larger version. 

Name:	MSKXSelezione.png 
Views:	28 
Size:	15.4 KB 
ID:	44783

    This should be the input form I'd like to use to select the event for which I want the details. The form takes the event code from TBLOrdini and the event name from TBLFiere.

    Then I changed the SQL to

    Code:
    SELECT TBLOrdini.SiglaFiera, TBLOrdini.CodiceCliente, TBLClienti.*, TBLOrdini.*, TBLClienti.NomeAzienda, TBLFiere.*FROM TBLFiere INNER JOIN (TBLClienti INNER JOIN TBLOrdini ON TBLClienti.[CodiceCliente] = TBLOrdini.[CodiceCliente]) ON TBLFiere.SiglaFiera = TBLOrdini.SiglaFiera
    WHERE (((TBLOrdini.SiglaFiera)=[Forms].[MSKXSelezione].[CASCOMBXSelezione]))
    ORDER BY TBLClienti.NomeAzienda DESC;
    But what I get on click the button that opens the form is

    Click image for larger version. 

Name:	No Good.png 
Views:	28 
Size:	15.7 KB 
ID:	44785

    so it seems that the input box has just changed its name...

    Apart from this it works, I'd just like to be able to select the event from a kind of drop down menu, not having to remeber it each time..

    Thanks again

  9. #9
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    So what is Maschere ?

    If you has used the Builder as I sugegsted you would see it would return something along the lines of

    Code:
    Forms![Form1]![Combo16]
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Giorgio,
    Please open the query in design view, click inside the criteria row for tblOrdini.SiglaFiera field and on the Design Ribbon click on the Builder. Now navigate to the open forms (Maschere) anf find MSKXSelezione and the combo. I assume the end result would look like Maschere!MSKXSelezione!CASCOMBXSelezione.
    The input box didn't change name, that is now the Enter Parameter prompt Access uses when it cannot find a field or a control.
    It is hard for us to give support as your system is setup for Italian and ours is in English, but if you use the built in tool as I suggested you should get it to work.
    Click image for larger version. 

Name:	Capture.PNG 
Views:	27 
Size:	61.3 KB 
ID:	44786
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  11. #11
    Giorgio is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2021
    Posts
    36
    Maschere is the Italian word for Form; even if i type [Forms].[whatever .. it is changed to [Maschere].[whatever..

    I did exactly as suggested, see below
    Click image for larger version. 

Name:	IMG1.png 
Views:	27 
Size:	48.7 KB 
ID:	44791
    to get this in the SQL
    Click image for larger version. 

Name:	IMG2.png 
Views:	27 
Size:	19.5 KB 
ID:	44794
    But when I run the query

    Click image for larger version. 

Name:	IMG3.png 
Views:	27 
Size:	29.1 KB 
ID:	44795

  12. #12
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    I did say the form had to be open?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  13. #13
    Giorgio is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2021
    Posts
    36
    If I first open the form MSKXSelezione and then click on the button that opens the form MSKTBLOrdini, there is not more pop up message and the form opens showing no records ..

  14. #14
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Can you upload a small sample of DB, else we are going to be here all day.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  15. #15
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The combo's bound column is 1 right (the SiglaFiera column)? Can you check if you have a record for the event you select if you remove the criteria in the query (what I mean is if you didn't assign a client yet you the query will not show anything even if you have the order and event records). Could you also open MSKTBLOrdini in design view and check the filter property to make sure you didn't save a filter for it.
    A sample would help a lot!
    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Opening HyperLinks from a Combobox.
    By Torinjr in forum Forms
    Replies: 3
    Last Post: 05-06-2015, 03:27 PM
  2. Replies: 5
    Last Post: 01-24-2015, 12:59 AM
  3. Dialog Form in place of InputBox
    By EddieN1 in forum Forms
    Replies: 7
    Last Post: 04-11-2014, 09:03 AM
  4. Opening form with defined combobox value
    By Kivan in forum Programming
    Replies: 2
    Last Post: 08-10-2012, 04:30 AM
  5. Opening Different Reports on Combobox Selection
    By confusedlilly19 in forum Reports
    Replies: 1
    Last Post: 06-24-2012, 03:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums