Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253

    having trouble making a text box equal to a query result

    here is the code I have that I am trying to use to make textbox "txtSN1" = to the result of the query. The query gives me the correct result but for some reason I cannot get the textbox to take that info.



    It is probably a small thing that I just don't know yet.

    Code:
    Me.txtSN1.RecordSource = "SELECT qry343sInProc.SerialNumber, qry343sInProc.FixturePosition " & _
        "FROM qry343sInProc WHERE (((qry343sInProc.FixturePosition)=1))"

  2. #2
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    First off, a Control (Textbox, in this case) doesn't have a RecordSource...it has a Control Source!

    Secondly, you cannot set a Control to the result of a Query! To do this sort of thing, you need to use the DLookUp Function, using the Criteria parameter, which would be the same as your current 'Where' clause.

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
        Dim rst As DAO.Recordset
        Dim sSQL As String
        sSQL = "SELECT qry343sInProc.SerialNumber, qry343sInProc.FixturePosition " _
        & "FROM qry343sInProc WHERE (((qry343sInProc.FixturePosition)=1))"
        Set rst = CurrentDb.OpenRecordset(sSQL)
        With rst    
            .MoveLast
            .MoveFirst
            debug.print !SerialNumber
            debug.print !FixturePosition
        End With
        set rst = nothing
    Your query selects 2 fields. Above is how to acquire both. The code assumes there is one record returned by the query.

  4. #4
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Thank you Linq. I originally tried control source but that didn't work either so I thought I would try a recordsource.

    Thank you Dave. I don't completely understand how your code works but I will try to study it and figure it out for the future.

    Here is the code I used to make it work.

    Code:
    txtSN1 = DLookup("[serialnumber]", "qry343sInProc", "[FixturePosition]=1")
    Last edited by NightWalker; 02-10-2017 at 04:48 PM. Reason: added code solution used

  5. #5
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Glad we could help!

    Good luck with your project!

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  6. #6
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Ok I don't know if I should add this here but it is another query question. I have a query that works great for getting me the info I need(qry from above). I also made a private function that does some math conversions. The trouble I am having now is combining the two. I need the function to take the value from one of the query result fields(PreReading) and convert it then I need to take the 2 values the the function returns and put each value into a separate text box on a form.

    Here is the code for the qry:
    Code:
    SELECT tbl_343sTested.FixturePosition, tbl_343sTested.SerialNumber, tbl_343sTested.TestedDate, tbl_343sTested.Technician, tbl_343sTested.TankTestedIn, tbl_343sTested.LoadTested, tbl_343sTested.PreReading, tbl_343sTested.HighReading, tbl_343sTested.PostReading, tbl_343sTested.Recheck, tbl_343sTested.PassFail, tbl_343sTested.Tr343Id, tbl_343sTested.MeggerID, tbl_343sTested.PressureIndID, tbl_343sTested.Comment
    FROM tbl_343sTested
    WHERE (((tbl_343sTested.TestedDate)=Date()) AND ((tbl_343sTested.LoadTested)=DMax("LoadTested","tbl_343sTested","[TestedDate]= #" & Date() & "#")));
    Here is the code for the Function:
    Code:
    Private Function UnconvertUnits() As String
        Select Case ValueTo
            Case 1000 To 999999
                ValueFrom = (ValueTo / 1000)
                UnitsFrom = "K"
            Case 1000000 To 999999999
                ValueFrom = (ValueTo / 1000000)
                UnitsFrom = "M"
            Case 1000000000 To 999999999999#
                ValueFrom = (ValueTo / 1000000000)
                UnitsFrom = "G"
            Case Is < 1000
                ValueFrom = ValueTo
        End Select
    End Function
    Any help would be greatly appreciated. Thank you for your help in advance.
    Walker

  7. #7
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Firstly, I'd certainly start a new thread

    Then look into this

    http://allenbrowne.com/ser-16.html

    Your function needs to return two values and currently it returns one.

    If you write the function in the way shown (or create two almost identical functions), you can extend your SQL to include expr1: the functionreturn (And expr2:functionreturn2) which you can then put into the appropriate textboxes.

  8. #8
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    I don't understand why it only returns one value. it should return "ValueFrom" and "UnitsFrom". If that is not correct could you please explain why.

  9. #9
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The "function" is not a function at all. It doesn't return ANY function value as written. It's actually performing as a sub, which is OK as long as you understand what it is actually doing.
    Here's a function that returns a value:
    Code:
    function fcnReturnEcho(arg as string) as string 
        select case arg 
            case = "Hello"   
                fcnReturnEcho = "Hello"
            case  = "Bye"
                fcnReturnEcho = "BYE"
            case else
                fcnReturnEcho = "Beats Me"
        end select
    end function
    To see what this function returns, put it in a module, then in the immediate window type
    Print fcnReturnEcho("HELP")

    Meanwhile,
    What/where is ValueTo? textbox on a form?
    What/where is ValueFrom?
    What/where is UnitsFrom?
    and what do they have to do with the query?
    What's not working?

  10. #10
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Thank you Dave. I understood your coding. I didn't think I was returning a single value.
    ValueFrom, and UnitsFrom are textboxes on a form and ValueTo is a variable that gets stored into the table. when the initial conversion is done. what I am trying to do now is convert a number like 14000000 to 14 M. This is my unconvert sub. the 14000000 is in one of the returned field of the query(PreReading). the 14 and M need to go back into the form in the valueFrom and UnitsFrom textboxes respectively.


    I am having trouble with the vba coding or the query to make the sub convert the numbers to the 2 separate items so I can place them back into the form.

  11. #11
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2007
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Your initial coding seems ok

    try saving these in a module from where you'll be able to access them in a query

    Private Function valuefrom(valueto as integer) As variant
    Select Case ValueTo
    Case 1000 To 999999
    ValueFrom = (ValueTo / 1000)

    Case 1000000 To 999999999
    ValueFrom = (ValueTo / 1000000)

    Case 1000000000 To 999999999999#
    ValueFrom = (ValueTo / 1000000000)

    Case Is < 1000
    ValueFrom = ValueTo
    End Select
    End Function


    Private Function unitsfrom(valueto as integer) As String
    Select Case ValueTo
    Case 1000 To 999999

    UnitsFrom = "K"
    Case 1000000 To 999999999

    UnitsFrom = "M"
    Case 1000000000 To 999999999999#

    UnitsFrom = "G"
    Case Is < 1000
    Unitsfrom = ""
    End Select
    End Function

    Some others may be able to write SQL code to do the job as well.

    In the qry design window you can access the functions as UDF (user defined functions)

  12. #12
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    SELECT FixturePosition, SerialNumber, TestedDate, Technician, TankTestedIn, LoadTested, PreReading, HighReading, PostReading, Recheck, PassFail, Tr343Id, MeggerID, PressureIndID, Comment
    FROM tbl_343sTested
    WHERE (((TestedDate)=Date()) AND ((LoadTested)=DMax("LoadTested","tbl_343sTested","[TestedDate]= #" & Date() & "#")));
    In what context is this query? Is it the recordsource for the form? If not you need a way to get to the PreReading field. You can do that with the code like I gave you in post #3.
    Then you will need two functions, one to convert the scale and another to get the Letter. The reason for two functions is that a function can only return one value.

    Use the code like #3 to get the value of PreReading and store it in a variable like HoldPreReading.
    Then
    Code:
    ValueFrom = UnconvertScale(HoldPreReading)
    UnitsFrom = UnconvertLetter(HoldPreReading)
    Code:
    Private Function UnconvertScale(arg as variant) As variant
        Select Case arg
            Case 1000 To 999999
                UnconvertScale = (arg / 1000)
            Case 1000000 To 999999999
                UnconvertScale = (arg / 1000000)
            Case 1000000000 To 999999999999#
                UnconvertScale = (arg / 1000000000)
            Case Is < 1000
                UnconvertScale = arg
        End Select
    End Function
    
    
    Private Function UnconvertLetter(arg as variant) as string
        Select Case arg
            Case 1000 To 999999
                UnconvertLetter = "K"
            Case 1000000 To 999999999
                UnconvertLetter = "M"
            Case 1000000000 To 999999999999#
                UnconvertLetter = "G"
            Case Is < 1000
                UnconvertLetter = ""
        End Select
    End Function

  13. #13
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    Wow I think I am getting better at this. Thank you Dave for explaining things in easy to understand lingo. I actually split the function into the 2 parts before I read your post. Thank you for all your help. I will try to finish this off today.

  14. #14
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    SELECT FixturePosition, SerialNumber, TestedDate, Technician, TankTestedIn, LoadTested, PreReading, HighReading, PostReading, Recheck, PassFail, Tr343Id, MeggerID, PressureIndID, Comment
    FROM tbl_343sTested
    WHERE (((TestedDate)=Date()) AND ((LoadTested)=DMax("LoadTested","tbl_343sTested","[TestedDate]= #" & Date() & "#")));
    NightWalker,
    Looking at this query, a question. Can this query return more than one record from tbl_343sTested?
    If so, we need to modify the code that extracts PreReading. As it is, if more than one record is returned by the query, only the first will be looked at.

  15. #15
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    yes it will return up to 9 records and it will be usually 9 records

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

Similar Threads

  1. Making a Recordset trouble
    By NightWalker in forum Programming
    Replies: 30
    Last Post: 12-23-2016, 05:00 PM
  2. Replies: 4
    Last Post: 11-19-2013, 06:53 PM
  3. Replies: 14
    Last Post: 01-03-2013, 11:35 PM
  4. Replies: 11
    Last Post: 10-18-2012, 02:23 PM
  5. making an array out of a query result
    By Richie27 in forum Programming
    Replies: 4
    Last Post: 05-01-2012, 01:53 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