Results 1 to 7 of 7
  1. #1
    singhpriyanka is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2018
    Posts
    5

    Attach result of a Query to the Text box on a Form

    I have created a query called qryOwnerDetails that is as follows - (based on the SQL view)

    SELECT [OwnerFirstName] & " " & [OwnerLastName] AS OwnerFullName
    FROM tblOwner INNER JOIN tblProductInfo ON tblOwner.OwnerID = tblProductInfo.OwnerID
    WHERE (((tblProductInfo.Productname)=[Forms]![frmAdminShowRecords]![ComboProductName]) AND ((tblOwner.DomainID)=[Forms]![frmAdminShowRecords]![Domain Name]));


    The result of this query is a single Field Called - OwnerFullName , a single record

    I would like to populate a TextBox on my form with the result of the query above i.e textbox should have text based on input from the combo box called ProductName in the Form- frmAdminShowRecords.
    Please help me , I have a hit a dead end trying to use Dlookup to populate the field of the textbox adding to its Control Source property and also trying to use it in VBA code. nothing helps.
    Following are solutions I have tried-

    Solution1 - Trying to Pass the query in to the Dlook statement in its Domain
    Me.TextOwnerFullName = (DLookup("[OwnerFullName]", "qryOwnerDetails", "[Productname]= '" & [Forms]![frmAdminShowRecords]![ComboProductName] & "'" And "[DomainID]= '" & [Forms]![frmAdminShowRecords]![DomainID] & "'"))

    Solution2- Using String variable to openrecordset and access the the output filed of the query called - OwnerFullName and assign the string value of this recordset to the textbox.text

    Dim rs As DAO.Recordset


    Dim strSQL As String
    strSQL = "SELECT tblOwner.OwnerFirstName FROM tblOwner" "INNER JOIN " tblProductInfo ON tblOwner.OwnerID = tblProductInfo.OwnerID "WHERE (((tblProductInfo.Productname)=[Forms]![frmAdminShowRecords]![ComboProductName]) AND ((tblOwner.DomainID)=[Forms]![frmAdminShowRecords]![Domain Name]"))
    Me.TextOwnerFullName.SetFocus
    [TextOwnerFullName].Text = strSQL

    Nothing seems to work. Appreciate some guidance.

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 7 32bit Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Can you post a copy of your db with a few dummy records
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Code:
    Dim rs As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT tblOwner.OwnerFirstName FROM tblOwner INNER JOIN  tblProductInfo ON tblOwner.OwnerID = tblProductInfo.OwnerID WHERE (((tblProductInfo.Productname)=[Forms]![frmAdminShowRecords]![ComboProductName]) AND ((tblOwner.DomainID)=[Forms]![frmAdminShowRecords]![Domain Name]))
    debug.print strSQL
    set rs = Currentdb.openrecordset(strSQL)
    rs.movelast
    rs.movefirst
    Me.TextOwnerFullName=rs!OwnerFirstName & " " & rs!OwnerLastName
    set rs = nothing
    Make sure the query runs on its own to show properly when executed in datasheet mode.
    You'll have to add OwnerLastName to the query.

  4. #4
    singhpriyanka is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2018
    Posts
    5
    Hi Davegri, Thanks for the prompt response.
    Few Points from my side. The field "OwnerFullName" is automatically created in the query by concatenating the OwnerFirstNAme and OwnerLastName fields from the Owner table by using the Expression - OwnerFullName: [OwnerFirstName] & " " & [OwnerLastName]. And this is the only filed created as the output of the query , it is a single record. The value of OwnerFullName depends on two Fields - ProductName and DomainID which are combox and textbox (and also fields from a separate table in the query, so basically two tables Owner and Product are i) on the form respectively.



    Besides after I ran your code, I get the following error-
    RunTime error- '3061'
    Too Few parameters. Expected 2.
    This points to the statement-
    Set rs = CurrentDb.OpenReordset(strSQL)



    Please help.

  5. #5
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Too Few parameters. Expected 2.
    That's a problem in your SQL string. I used the SQL you show from the bottom of your post which did not have the LastName included. Maybe the SQL from the top of you post would work better.
    Paste that sql into a query design SQL view and see if it works. The form has to be open with proper data displayed for the query to run.
    If the query does run OK on its own, use that query as the DAO.recordset recordsource.
    Last edited by davegri; 07-10-2018 at 03:25 PM. Reason: clarif

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Form and report parameters are not available from Jet (vba) IIRC, thus the parameters error. As for the DLookup problem, you can only lookup on one domain using criteria on that domain. You cannot say (essentially) in one DLookup WHERE THIS = 'A' [HERE] AND THAT = 'B' [OVER THERE]. You could probably fix this one of several ways.
    - put your working sql into the control source property of the textbox
    - or concatenate 2 DLookups:
    WHERE something = DLookup("[OwnerFullName]", "qryOwnerDetails", "[Productname]= '" & [Forms]![frmAdminShowRecords]![ComboProductName] & "'") AND Something = DLookup...."

    - or use DLookups for 2 separate hidden textboxes and a 3rd displays their concatenation (not my favourite solution).
    - or assign the form control reference to a variable and pass the variable to the sql (allow preceding code to put a value in strFullName) - at least I think it would work
    - or create a query def and define the parameters in code
    - or define the parameters in the query properties itself. IIRC, the query deciphers [Forms]![frmAdminShowRecords]![ComboProductName] when it is called.
    DLookups are slower than sql or queries, plus calculated controls are not updatable. Thought I'd mention that in case you solve this then find you can't modify the contents of the control.
    That's all I have for now.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Code:
        Dim rs As DAO.Recordset
        Dim strSQL As String
        TextOwnerFullName = vbNullString
        strSQL = "SELECT [OwnerFirstName] & ' ' & [OwnerLastName] AS OwnerFullName " _
        & "FROM tblOwner INNER JOIN tblProductInfo ON tblOwner.OwnerID = tblProductInfo.OwnerID " _
        & "WHERE ((tblProductInfo.Productname)='" & [Forms]![frmAdminShowRecords]![ComboProductName].Column(1) & "' " _
        & "AND ((tblOwner.DomainID)=" & [Forms]![frmAdminShowRecords]![Domain Name] & "));"
        Debug.Print strSQL
        Set rs = CurrentDb.OpenRecordset(strSQL)
        If rs.RecordCount = 0 Then
            MsgBox "Either no Product or no domain match"
            Exit Sub
        End If
        rs.MoveLast
        rs.MoveFirst
        Me.TextOwnerFullName = rs!OwnerFullName
        Set rs = Nothing
    End Sub
    Got rid of the "too few parameters" with changes to sql above.
    The debug.print should show something like:
    SELECT [OwnerFirstName] & ' ' & [OwnerLastName] AS OwnerFullName FROM tblOwner INNER JOIN tblProductInfo ON tblOwner.OwnerID = tblProductInfo.OwnerID WHERE ((tblProductInfo.Productname)='Whistle' AND ((tblOwner.DomainID)=1));
    The red is the values pulled from the form.



    I had to make some assumptions on your form and data.
    The ComboProductName.Column(1) assumes that the rowsource for that combobox is
    Code:
    SELECT ID, [ProductName] FROM tblProductInfo;
    Also, since I don't know your form, I couldn't determine which event to attach the code to. Just for testing I used FORM_CURRENT.

    If still problems, best way to resolve is to post DB here.
    Last edited by davegri; 07-10-2018 at 09:01 PM. Reason: debug.print expo

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 02-07-2017, 02:40 PM
  2. Replies: 3
    Last Post: 11-12-2014, 09:13 AM
  3. Replies: 1
    Last Post: 07-03-2014, 08:27 PM
  4. Replies: 1
    Last Post: 01-06-2013, 01:47 PM
  5. Replies: 12
    Last Post: 06-25-2012, 01:42 PM

Tags for this Thread

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