Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Form's txtbx Control lookup SQL

Posted on 2004-10-25
21
Medium Priority
?
839 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
Comment
Question by:Suburb-Man
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 2
21 Comments
 
LVL 1

Author Comment

by:Suburb-Man
ID: 12404822
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
ID: 12406106
#$!%#^$
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
ID: 12407753
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 400 total points
ID: 12408570
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
ID: 12412212
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
ID: 12412245
You cannot use an SQL statement in a domain aggregate function.
0
 
LVL 58

Accepted Solution

by:
harfang earned 1600 total points
ID: 12418478
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
ID: 12436139
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
ID: 12465715
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
ID: 12465766
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
harfang earned 1600 total points
ID: 12469779
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
ID: 12477980
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
ID: 12478394
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
ID: 12480083
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
ID: 12486665
>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
ID: 12487039
> 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
ID: 12487564
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
ID: 12491341
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
ID: 12501196
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
ID: 12501452
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
ID: 12759746
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

Featured Post

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

596 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