none
Want to use a ListBox value in a updateQuery RRS feed

  • Question

  • Hi

    I Want to use a ListBox value in a updateQuery. The value is in the ListBox but the result of the Updatequery is Null.

    The value I want the Updatequery to use is (in this case) "185", the value to the right of "NEW Ferment Protocol #"

    But the query gives a Null value.

    The Query is like this:
    "UPDATE TEMPJäsprotokollRader SET TEMPJäsprotokollRader.JäsprotokollID = Int([Formulär]![Sammanställning Splitta bryggder i primärjäsning]![Listbox]);"

    What am I doing wrong?


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Wednesday, February 3, 2016 7:16 PM

Answers

  • The Record Source is:

    SELECT Last(Jäsprotokoll.JäsprotokollID) AS SistaförJäsprotokollID FROM Jäsprotokoll; 

    And yes there is always only one result.


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Hi Peter. If there's always only going to be one item returned in the listbox, then you can use the following:

    UPDATE TableName
    SET FieldName=DLast("IDField","TableName")

    Hope that helps...

    • Marked as answer by ForssPeterNova Thursday, February 4, 2016 6:32 AM
    Wednesday, February 3, 2016 9:21 PM

All replies

  • Hi Peter,

    Not sure if it was just in the way the image was taken, but if the Listbox is not a multi-select listbox, then the item must first be selected before you can use its value in your query.

    Just my 2 cents...

    Wednesday, February 3, 2016 7:49 PM
  • Hi DB

    Is there a way. by code, to select the item in the listbox?


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Wednesday, February 3, 2016 7:58 PM
  • Hi DB

    Is there a way. by code, to select the item in the listbox?


    You could try (executed from behind the form):

    Me.ListboxName.Selected(0)=True

    Hope that helps...

    Wednesday, February 3, 2016 8:10 PM
  • DB Please help.

    Me.Listbox.Selected(0) = True - gives Error Invalid use of Me keyword

    Me.[Listbox].Selected(0) = True - gives Error Invalid use of Me keyword

    ...


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Wednesday, February 3, 2016 8:24 PM
  • Hi. The invalid use of Me keyword is due to where the code is executed. As I try to indicate above, the sample code I provided is intended to be executed behind the form. If you're trying to use a Standard Module, then you'll need to change the syntax to something like:

    Forms!FormName.ListboxName.Selected(0)=True

    By the way, what is the Record Source of the Listbox? Will it always only have one result?

    Wednesday, February 3, 2016 8:42 PM
  • The Record Source is:

    SELECT Last(Jäsprotokoll.JäsprotokollID) AS SistaförJäsprotokollID FROM Jäsprotokoll; 

    And yes there is always only one result.


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Wednesday, February 3, 2016 9:10 PM
  • Function SplittToTomTank()
    
    On Error GoTo SplittToTomTank_Err
    
    
        Forms![Sammanställning Splitta bryggder i primärjäsning].[Listbox].Selected(0) = True
           
        DoCmd.OpenQuery "Splitt 4 till TEMPJäsprotokollRader NEWJaesID", acViewNormal, acEdit
    
    
    SplittToTomTank_Exit:
        Exit Function
    The code doesnt work :-(

    Best // Peter Forss Stockholm and Sigtuna GMT +1.00


    Wednesday, February 3, 2016 9:16 PM
  • The Record Source is:

    SELECT Last(Jäsprotokoll.JäsprotokollID) AS SistaförJäsprotokollID FROM Jäsprotokoll; 

    And yes there is always only one result.


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00

    Hi Peter. If there's always only going to be one item returned in the listbox, then you can use the following:

    UPDATE TableName
    SET FieldName=DLast("IDField","TableName")

    Hope that helps...

    • Marked as answer by ForssPeterNova Thursday, February 4, 2016 6:32 AM
    Wednesday, February 3, 2016 9:21 PM
  • Great help DB

    Thank you very very much

    my code is:

    UPDATE TEMPJäsprotokollRader SET TEMPJäsprotokollRader.JäsprotokollID = DLast("JäsprotokollID","Jäsprotokoll");


    Best // Peter Forss Stockholm and Sigtuna GMT +1.00


    Thursday, February 4, 2016 6:33 AM
  • Hi Peter. You're welcome. If using DMax() works as well, I would recommend using it over DLast() just in case you end up upscaling your database to SQL Server or other RDBMS. Good luck with your project.
    Thursday, February 4, 2016 4:05 PM