Solved

Form's txtbx Control lookup SQL

Posted on 2004-10-25
822 Views
Last Modified: 2012-08-14
In all the Access Forms is a Header where Client Demographic Info is displayed, so User can confirm what Client they are working on.

The Header (Which is actually in the forms detail area) contains:
Record #, Record Date, Clinic, Last Name, First Name, DOB.

But I have re-designed how Record Date and Branch is entered and stored: From Client Table one to many ServiceList Table.
Key field in both is CustomerID.
Both the Record Date and Branch now needs to come from ServiceList Table.

I need the fields(form controls) Date and Branch to lookup to the ServiceList Table and get the latest (last) [ServiceBranch] for the current Client (CustomerID) selected in the Form.

I got a query to work with a static value (23837) in the WHERE clause to isolate the latest/last record, but I need it to get the current forms CustomerID.  Which is in the current recordset.
I don't know how to get the WHERE clause right, but have a bunch of guesses.
See the "OR WHERE" below.

========= CODE START ==========
SELECT TOP 1 dbo.ServiceList.ServiceBranch AS MaxSB

FROM dbo.Clients INNER JOIN

dbo.ServiceList ON dbo.Clients.CustomerID = dbo.ServiceList.CustomerID

WHERE ((dbo.Clients.CustomerID) = (23837))

-- or WHERE ((dbo.Clients.CustomerID) = (Me.CustomerID))
-- or WHERE ((dbo.Clients.CustomerID) = (Me.RecordSource ))
-- or WHERE (dbo.Clients.CustomerID = Me.Form.RecordSource.CustomerID)
-- or WHERE (dbo.Clients.CustomerID = Me.Form.txtbxCustomerID.value)

ORDER BY dbo.ServiceList.[NSCL-Date] DESC
========= CODE END ==========


What is the WHERE, that I need.

oh yea that is clear as mud. :-)

I have also thought about, leaving the txtbox Controls unbound and setting them in the Open or Current event of the Form.
  But I don't know how to embedd a SQL query into a Access event and properly state the WHERE clause.
     Private Sub Form_Current()
           ???
     End Sub

     
0
Question by:Suburb-Man
    21 Comments
     
    LVL 1

    Author Comment

    by:Suburb-Man
    This doesn't work but I hope you will get the idea:

    Private Sub Form_Current()
       Me.tbxBranch = (
              SELECT TOP 1 dbo.ServiceList.ServiceBranch AS MaxSB
              FROM dbo.Clients INNER JOIN
              dbo.ServiceList ON dbo.Clients.CustomerID = dbo.ServiceList.CustomerID
              WHERE ((dbo.Clients.CustomerID) = (23837))
              -- or WHERE ((dbo.Clients.CustomerID) = (Me.CustomerID))
              -- or WHERE ((dbo.Clients.CustomerID) = (Me.RecordSource ))
              -- or WHERE (dbo.Clients.CustomerID = Me.Form.RecordSource.CustomerID)
              ORDER BY dbo.ServiceList.[NSCL-Date] DESC
              )

    End Sub


    0
     
    LVL 1

    Author Comment

    by:Suburb-Man
    #$!%#^$
    I still can't get it to work. I had to put it in an Event, because I don't know how to get it to run in txtbox's Control Source.
     
    "DoCmd.RunSQL strSQL" get the following error
    Run-time error '2342'
    A RunSQL action requires an argument consisting of an SQL statement.
     
     
     
    ========= Start =========
    Private Sub Form_Current()
       
        Dim strSQL As String

        strSQL = "SELECT TOP 1 dbo.ServiceList.[ServiceBranch] " & _
            "FROM dbo.ServiceList " & _
            "WHERE customerid = ' " & Me.CustomerID & " ' " & _
            "ORDER BY [NSCL-Date] DESC;"

        DoCmd.RunSQL strSQL
       
        Me.tbxBranch = ServiceBranch
     
    End Sub
    ========= End =========

    0
     
    LVL 58

    Expert Comment

    by:harfang
    OK, OK, OK, stop cursing :)

    The problem is that "Me" is a VBA reference to the current form/report. JetEngine does not know about it. On the other hand, it know the words "Form", "Forms", "Report" and "Reports".

    Form: designates the current form (used for the record source of controls, but not of subforms), as in:
       WHERE lngCustomerID = Form!txtCustomerID
    (the first is a long field, the second is a text box on the current form)

    Forms: collection of open forms, as in:
       WHERE strCustomerID = "'" & Forms!frmCustomerEdit!txtCustommerID & "'"
    (grabs the content of a text box from the specified form, used in the same form or anywhere else, e.g. a report)

    Report: identical to Form, but for Reports :)
    Reports: collection of open Reports (never used in queries, in my experience)

    Bottom line: when learning Access, you must also learn SQL/JetEngine, and VBA. They do not always communicate well :)

    Cheers

    0
     
    LVL 41

    Assisted Solution

    by:shanesuebsahakarn
    RunSQL does not return a value. You will need to retrieve the value from a recordset like so:

        Dim strSQL As String
        Dim rs As DAO.Record

        strSQL = "SELECT TOP 1 dbo.ServiceList.[ServiceBranch] " & _
            "FROM dbo.ServiceList " & _
            "WHERE customerid = '" & Me.CustomerID & "' " & _
            "ORDER BY [NSCL-Date] DESC;"

        set rs=CurrentDb.OpenRecordset(strSQL)
        Me.tbxBranch = rs!ServiceBranch

       rs.Close
       Set rs=Nothing
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Just a clarification:

    In the original question, the "CODE" was an SQL statement, used in the SQL view of a query or as RecordSource property in a table or control. In SQL, you cannot use "Me".

    The second code fragment is Visual Basic. After strSQL =, everything inside the quotes is still SQL (you cannot use "Me"), but outside of the quotes, Me.CustomerID works, as the code is in the forms Class module.

    Still, apparently, you try to feed that information to a text box called txtBranch. You can do this directly with an expression, by including the SQL in a DLookup() function.

    For the text box txtBranch, following the above thread, (I'm not familiar with the two-part table names, e.g. dbo.ServiceList, but that seems irrelevant if your original query worked), enter as control source:

    = DLookup("ServiceBranch", "SELECT TOP 1 dbo.ServiceList.[ServiceBranch] FROM dbo.ServiceList WHERE customerid = '" & [CustomerID] & "' ORDER BY [NSCL-Date] DESC")

    Note: [CustomerID] is taken from the local context, which includes all controls of the current form and all fields of the underlying query. It is totally equivalent with "Form!CustomerID". Me! does not work here, as we are no longer within VB...
    The "where" parameter of the function is ommited as is is already contained in the "source".

    Finally, the same SQL can be added as subquery in the form's main query. This looks like this (assuming the main query contains dbo.Clients) in a separate column of the query grid:

    Branch: (Select Top 1 ServiceBranch From dbo.ServiceList Where dbo.ServiceList.CustomerID = dbo.Clients.CustomerID Order By [NSCL-Date] DESC)

    Here, "dbo.Clients.CustomerID" cannot be found in the local subquery, thus it is searched in the main query and found, so it works. If it is not found there, the entire form is searched, but that is not needed here.

    Of course, set the control source of txtBranch to Branch (or whatever you called the above field).

    In some slightly more complex cases, subquery make the main query read-only (not editable). In this case, I'm sure it will leave all other fields editable. Else, use the calculated field on the form (previous example).


    I hope this does in fact clarify and not confuse you further :)

    Cheers
    0
     
    LVL 41

    Expert Comment

    by:shanesuebsahakarn
    You cannot use an SQL statement in a domain aggregate function.
    0
     
    LVL 58

    Accepted Solution

    by:
    Oops, you are right, of course. Got confused with my own lookup functions.

    Just replace the lookup with:

    = CurrentDb.OpenRecordSet("SELECT TOP 1 dbo.ServiceList.[ServiceBranch] FROM dbo.ServiceList WHERE customerid = '" & [CustomerID] & "' ORDER BY [NSCL-Date] DESC")!ServiceBranch

    No, that is just a joke (works, but not reliably...)

    Create a function in the form instead, like shane said above:

    Function Branch(pvarCustomerID)
       With CurrentDb.OpenRecordSet( _
          "SELECT TOP 1 dbo.ServiceList.[ServiceBranch]" _
          & " FROM dbo.ServiceList" _
          & " WHERE customerid = '" & pvarCustomerID & "'" _
          & " ORDER BY [NSCL-Date] DESC")
             Branch = !ServiceBranch
       End With
    Exit Function
    0
     
    LVL 1

    Author Comment

    by:Suburb-Man
    Thanks guys, for correcting yourselves.

    I tried a few of the first posts unsuccesfully, and since I've been sick I haven't tried the latest proposals.
    Should have something Friday.

    Thanks again
    0
     
    LVL 1

    Author Comment

    by:Suburb-Man
    Ok I'm lost.

    I've tried all the combinations from the above.

    Lastly creating a function and using it in the Form's control [txbxBranch]:
      It's data control source:  "=Branch([CustomerID])" or "=Branch()" or "=Branch"
         
         Function Branch(pvarCustomerID)
       
            With CurrentDb.OpenRecordset( _
               "SELECT TOP 1 dbo.ServiceList.[ServiceBranch]" _
               & " FROM dbo.ServiceList" _
               & " WHERE customerid = '" & pvarCustomerID & "'" _
               & " ORDER BY [NSCL-Date] DESC")
         
             Branch = !ServiceBranch
            End With
         End Function

        #Error or ?Name is all I get in [txbxBranch].

    So I went back to the "ON Current" event when the form opens:

    Private Sub Form_Current()
       
        Dim strSQL As String
        Dim rs As Recordset
        'Dim rs As ADODB.Recordset
        'Dim rs As DAO.Recordset

        strSQL = "SELECT TOP 1 [ServiceBranch], [NSCL-Date] " & _
            "FROM ServiceList " & _
            "WHERE customerid = '23837' " & _
            "ORDER BY [NSCL-Date] DESC;"
       
        Debug.Print strSQL


        'Set rs = CurrentDb.OpenRecordset(strSQL)
        'CurrentDb.OpenRecordset (strSQL)
        Set rs = CurrentDb.OpenRecordset(strSQL)
       
        Debug.Print rs
       
        Me!tbxBranch = rs!ServiceBranch
        Me!tbxRecord_Date = rs![NSCL-Date]
       
        rs.Close
        Set rs = Nothing

    End Sub

    But all I get here is "Object variable or With block variable not set (Error 91)"

    HELL p!
    0
     
    LVL 1

    Author Comment

    by:Suburb-Man
    Cont.

    Debug halts at:
    Private Sub Form_Current()
    ...
        Set rs = CurrentDb.OpenRecordset(strSQL)

    and gives error ""Object variable or With block variable not set (Error 91)"
    0
     
    LVL 58

    Assisted Solution

    by:harfang
    Oooop, I'm terribly sorry. I wasted your time with an incomplete solution. Sorry!

    Three comments:

    1) "=Branch([CustomerID])"
    is correct, but it looks for a *public* function. By  default, functions within a class module (e.g. in the code behind a form) are *private* = not visible from outside.
    I should of course have written "Public Function Branch..." as I said myself to create it in the form's module.
    Sorry again... :(

    2) "Debug.Print rs"
    This says, please print the string representation of rs, a Recordset. There is no such thing, and VB does not know what to do. Working examples include "Debug.Print rs.RecordCount", "Debug.Print rs.Filter" and of course "Debug.Print rs!ServiceBranch" (current value of one field).

    3) Debug halts at: "Private Sub Form_Current()"
    No, it just cant start the entire Sub because of an error inside. Please use Compile! In your project, you can choose "Debug / Compile". This way you will see syntax errors before trying to run them.


    Good Luck, Hope this will work now...
    0
     
    LVL 1

    Author Comment

    by:Suburb-Man
    Thank you

    Sorry about the DEBUG confusion. Allow me to Clarify.

      'Within the Form Section:
        Private Sub Form_Current()
      'Debug halts at the line:
        Set rs = CurrentDb.OpenRecordset(strSQL)

    I just added the "Debug.Print rs" to try to verify value of "rs", lucky the code execution never made it that far.
    0
     
    LVL 1

    Author Comment

    by:Suburb-Man
    Form has "=Branch([CustomerID])" as Textbox's Control Source.


    Module in Database:

    Public Function Branch(pvarCustomerID)
       
       With CurrentDb.OpenRecordset( _
          "SELECT TOP 1 ServiceList.[ServiceBranch]" _
          & " FROM ServiceList" _
          & " WHERE customerid = 23837 " _
          & " ORDER BY [NSCL-Date] DESC")
         
        Branch = !ServiceBranch
       End With
       
    'Exit Function
    End Function

    Get error "Object variable or With block variable not set (Error 91)" with debugger stoping at the WITH statement.
    0
     
    LVL 58

    Expert Comment

    by:harfang
    Is this cut and paste from your code? I don't understand.....

    In the Immediate pane (debug window), does this work?:
        ? CurrentDb.Name

    Does the module compile? Do you have "Option Explicit" towards the top of the module?

    Does the following query work? (e.g. create query and test)

        SELECT TOP 1 ServiceList.[ServiceBranch]
        FROM ServiceList
        WHERE customerid = 23837
        ORDER BY [NSCL-Date] DESC;

    If yes to all the above.........

    Does this change work? If not where does it stop?

    Public Function Branch(pvarCustomerID)
       
       With CurrentDb
          With .OpenRecordset( _
             "SELECT TOP 1 ServiceList.[ServiceBranch]" _
                & " FROM ServiceList" _
                & " WHERE customerid = 23837 " _
                & " ORDER BY [NSCL-Date] DESC")
         
             Branch = !ServiceBranch
          End With
       End With
       
    'Exit Function
    End Function

    Sorry, I can't see the fault.

    Report back and we will look into it.

    Cheers :)
    0
     
    LVL 1

    Author Comment

    by:Suburb-Man
    >Cut and Paste? = ABSOLUTLY (ctrl+c & ctrl+v)

    >? CurrentDb.Name = error "Object variable or With block variable not set (Error 91)"

    >Does the following query work? (e.g. create query and test) = YES
         Created VIEW, saved, ran, and it returned "Northern Branch".  (Correct Data)
              SELECT     TOP 1 ServiceBranch
              FROM         dbo.ServiceList
              WHERE     (CustomerID = 23837)
              ORDER BY [NSCL-Date] DESC

    Do I need to mention that this is an ADP project?  Connected to SQL Server.
    That is why you saw the "dbo" in dbo.ServiceList.[column-name], because I was connecting thru SQL authenication as a User.
    When connecting thru Windows AD authenication or as a User with owner rights dbo... isn't needed. dbo=DataBase Owner

    Created separate DB just for this Form and Module.
    Thus there is only one module, the one we (you) made.

    Here is another cut and paste of our only module:
    ---START---
    Option Compare Database

    Public Function Branch(pvarCustomerID)
       
       With CurrentDb.OpenRecordset( _
          "SELECT TOP 1 ServiceList.[ServiceBranch]" _
          & " FROM ServiceList" _
          & " WHERE customerid = 23837 " _
          & " ORDER BY [NSCL-Date] DESC")
         
        Branch = !ServiceBranch
       End With
       
    'Exit Function
    End Function
    ---END---
    0
     
    LVL 58

    Expert Comment

    by:harfang
    > Do I need to mention that this is an ADP project?  Connected to SQL Server.

    Hmm, yes, that changes everything.

    Basically, you do not have a "CurrentDB" in that case. You will need to use whatevery syntax is needed to provide the databse object instead. Let me look it up, I am not an ADP guru :(

    What's more, this looks like something that could be stored on the client side for better efficiency. Hopefully an SQL server expert will come accross this question. You might want to create another question with the keywords "SQL Server" and "ADP" in the title to attract some attention. It could read: "here is  a functin that works fine with a locally attached database, it there a better way to achieve the same thing in an ADP (SQL Server) context? For example using stored procedures?"

    I'll be back if I find something useful

    Good Luck
    0
     
    LVL 1

    Author Comment

    by:Suburb-Man
    My sincere apology, Ignorance is bliss, until you learn more. L

    I will still give you the points because it is a solution within the original scope: MS Access.
    And mostly you deserve it, for hanging in there.

    Thank you.

    I believe changing the control from a Textbox to Listbox will work.
    Row Source type and Row Source:
    Will easily allow a SQL statement, since the build wizard helps me.  Whee!

    I believe shanesuebsahakarn should get some points too, 20% seems fair what do you think?


    0
     
    LVL 58

    Expert Comment

    by:harfang
    Do not worry too much about points (at least I don't :)

    Reading through the documentation, I found that the closest equivalent to "CurrentDB" in your context would be "CurrentProject.Connection", as you have an open connection to your server instead of a local database. This equivalent to ".OpenRecordset" is then ".Execute".

    I can't test this in my environment, so this is the best I can offer ...

    -----------------------------------
    Option Explicit

    Public Function Branch(pvarCustomerID)
       
        With CurrentProject.Connection.Execute( _
            "SELECT TOP 1 ServiceList.[ServiceBranch]" _
                & " FROM ServiceList" _
                & " WHERE customerid = 23837 " _
                & " ORDER BY [NSCL-Date] DESC" _
                & " WITH TIES")
         
                Branch = !ServiceBranch
        End With
       
    End Function
    -----------------------------------

    This is not guaranteed to work, but I hope it will point to the right direction...
    I added the clause "WITH TIES" at the end of the query, because this is required using SQL Server 7.0. In version 6.5, "Top 1" does not work, so that you should remove both "Top 1" and "With Ties" from the sample.

    Sorry I couldn't be of more help.

    Cheers!

    BTW: my previous post suggested using stored procedures... that is wrong. stored procedures are *action* queries only, not lookup queries.
    0
     
    LVL 1

    Author Comment

    by:Suburb-Man
    Thanks you again

    I believe I might be able to use a call to a SP and pass it something like ~@CustomerID = Me.CustomerID
    I'll publish my results here for the future of all.

    BTW: Both are 2000 versions: Access and SQL Server.

    As a workaround I embedded a Visible=NO (hidden) sub-form using our query as it's data source and CustomerID as the Parent =Child link.
    0
     
    LVL 58

    Expert Comment

    by:harfang
    From Access 2000 help:
    * Working with Microsoft Access Projects
        * Comparing Access Projects to Access Databases
            * Comparison of Microsoft Access and SQL Server SQL Syntax

    For "TOP":
    SQL Server 6.5 does not support.
    SQL Server 7.0 requires an explicit WITH TIES clause.

    That is all I know, basically.

    But there one last thing to try:

    -----------------------------------
    Option Explicit

    Public Function Branch(pvarCustomerID)
       
        With CurrentProject.Connection.Execute( _
            "SELECT ServiceList.[ServiceBranch]" _
                & " FROM ServiceList" _
                & " WHERE customerid = 23837 " _
                & " ORDER BY [NSCL-Date] DESC")
         
                Branch = !ServiceBranch
        End With
       
    End Function
    -----------------------------------

    After all, the function itself performs the "Top 1", as it returns only one record, which happens to be the first.
    Just a wild guess :)
    0
     
    LVL 1

    Author Comment

    by:Suburb-Man
    Here is the current 4th revision code thanks to Arbert.
    Remember this is Win2k and Access2k.
    Getting the correct connection string was the hardest.
    So users didn't need Win/SQL admin/owner rights.

    ==================================
    Private Sub lstWaiting_DblClick(Cancel As Integer)
    On Error GoTo Err_Handler


    Dim sql As String
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim db As Recordset
    Dim conn As New ADODB.Connection


    With conn
       .ConnectionString = CurrentProject.Connection.ConnectionString
       .CursorLocation = adUseServer
       .Open
    End With


        sql = "update queuing set questatus=2 ,quetimestamp_servicing=getdate() where Quekeyid=" & lstWaiting.Column(7)
       
         
    If InStr(UCase(lstWaiting.Column(5)), "X-RAY") > 0 Then
        stDocName = "X-Ray Questionaire"
        stLinkCriteria = "[CustomerID]=" & Val(lstWaiting.Column(0)) - 1000
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        Application.Forms.Item("X-Ray Questionaire").txtQueueId = lstWaiting.Column(7)
        sql = sql & " and quetype=3"
    ElseIf InStr(UCase(lstWaiting.Column(5)), "SKIN") > 0 Or InStr(UCase(lstWaiting.Column(5)), "DR'S CONSULTATION") > 0 Or InStr(UCase(lstWaiting.Column(5)), "NR'S CONSULTATION") > 0 Then
        stDocName = "Mantoux Test Form"
        stLinkCriteria = "[CustomerID]=" & Val(lstWaiting.Column(0)) - 1000
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        Application.Forms.Item("Mantoux Test Form").cmdRecordSearch.Visible = False
        Application.Forms.Item("Mantoux Test Form").txtQueueId = lstWaiting.Column(7)
       
       
    End If
    'Debug.Print sql
    conn.Execute (sql)



    lstWaiting.Requery 'unbound list box
    lstInService.Requery 'unbound list box

    Exit_Handler:
        Exit Sub

    Err_Handler:
       'Stop
        MsgBox Err.Description & Chr(13) & "Error #: " & Err.Number
        Resume Exit_Handler


    End Sub
    ==============================
    Here is another but it might require User to have evelated rights
    ==============================

    '[Form_Mantoux Test Form 2 OnCurrent v4]
    '
    'Me!tbxGetSubFrmBranch is a field in the referring form [Form_Mantoux Test Form 2].
    'Me!tbxRecord_Date is a field in the referring form [Form_Mantoux Test Form 2].
     
    Private Sub Form_Current()
        On Error GoTo Err_Handler
       
    Dim conn As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    Dim SQLstr As String
    Dim IDlink As Long

    IDlink = Me!CustomerID 'FYI CustomerID is a Idenity,keyed,index,auto incrementating, one to many.

    conn.Open "provider=SQLOLEDB;data source=stb-sbserver1;initial catalog=testing;integrated security=sspi"
     
        SQLstr = "SELECT TOP 1 [ServiceBranch], [NSCL-Date] " & _
            "FROM ServiceList " & _
            "WHERE customerid = " & IDlink & _
            " ORDER BY [NSCL-Date] DESC;"
       
        'Debug.Print SQLstr
       
    Set RS = conn.Execute(SQLstr)
       
        Me!tbxGetSubFrmBranch = RS("ServiceBranch")     'rs!ServiceBranch returns error
        Me!tbxRecord_Date = RS("NSCL-Date")             'rs![NSCL-Date] returns error
       
    conn.Close
    Set RS = Nothing
       
    Exit_Handler:
        Exit Sub
       
    Err_Handler:
        MsgBox Err.Description & Chr(13) & "Error #: " & Err.Number
        Resume Exit_Handler
       
    End Sub
    =============================
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Shellfire Box VPN + Lifetime Subscription

    The Shellfire Box easily connects all of your devices, even those that don't offer the possibility to establish a safe vpn connection. Access blocked content and surf safely, no matter where in the world you are located.

    Suggested Solutions

    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    856 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now