Solved

Function not getting table fields via sql

Posted on 2011-09-09
9
172 Views
Last Modified: 2012-05-12
Hi,

I have the following function. It gets the 'origin' value from a table in comparison to a value on the same table. Example is

OUTCOMBI - COLUMN TITLE
GIBSONSBRITISH COLUMBIA
HOPKINS LANDINGBRITISH COLUMBIA
PORT MELLONBRITISH COLUMBIA

TCOMBI - COLUMN TITLE
VANCOUVERBRITISH COLUMBIA
VANCOUVERBRITISH COLUMBIA
VANCOUVERBRITISH COLUMBIA

So what it was doing (untile yesterday and it stopped returning values after!) It is comparing whether the 'origin' value is in OUTCOMBI column and returns the respective TCOMBI value

Could you please let me know what made it stop working? The data set extends to 5,000 rows. What it is doing now is returning the same values it returned yesterday. The function is located in Module1.

The table name is 'OUT' It is being called from 'Private Sub Cal1_Click()' via

origint = GetO(origin)  

Public Function GetO(origin As String) As String

        Dim aob As AccessObject
        Dim db As Database
        Dim td As DAO.TableDef
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim varResult As String
        
        MsgBox origin
        
        strSQL = "Select OUT.TCOMBI " & "FROM OUT " & "WHERE OUT.OUTCOMBI = '" & origin & "' " 
        Set rst = CurrentDb.OpenRecordset(strSQL)
        
        If Not (rst.EOF And rst.BOF) Then
            GetO = DLookup("TCOMBI", "OUT")
        End If
        
        Set rst = Nothing
        
End Function

Open in new window

0
Comment
Question by:Shanan212
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
Comment Utility


Public Function GetO(origin As String) As String

        Dim aob As AccessObject
        Dim db As Database
        Dim td As DAO.TableDef
        Dim rst As DAO.Recordset
        Dim strSQL As String
        Dim varResult As String
        
        MsgBox origin
        
        strSQL = "Select OUT.TCOMBI FROM OUT WHERE OUT.OUTCOMBI = '" & origin & "'"
        Set rst = CurrentDb.OpenRecordset(strSQL)
        
        If rst.EOF Then
            GetO = "No records"
            Else
            GetO = DLookup("TCOMBI", "OUT")
        End If
        
        Set rst = Nothing
        
End Function

Open in new window



How many records do you expect in return ?

you may have to change this line

      GetO = DLookup("TCOMBI", "OUT")

 with something else

0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
What is the DLookup returning?

Without criteria it will return the firstvalue for TCOMBI in the table OUT?

Is that what you want?
0
 
LVL 13

Author Comment

by:Shanan212
Comment Utility
Yes, the look up value that is being sent in is a unique value. It is only there once in 'OUTCOMBI' column. Hence, the respective return value from TCOMBI volumn would be 1 as well.

It worked fine yesterday.

Regardless of whatever value I send in, its returning "VANCOUVERBRITISHCOLUMBIA" which is one of the value on TCOMBI
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
Comment Utility
Public Function GetO(origin As String) As String        
        MsgBox origin
        GetO = DLookup("TCOMBI", "OUT",  "OUTCOMBI = '" & origin & "'" )
End Function
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 61

Expert Comment

by:mbizup
Comment Utility
I think you need to replace your if block with this:

        If Not (rst.EOF And rst.BOF) Then
            GetO = rst.TCOMBI
        End If


That will give you the 'respective value for TCOMBI where OUT.OUTCOMBI =  origin
0
 
LVL 13

Author Comment

by:Shanan212
Comment Utility
Replacing the block gave me the following error: Method/Data member not found
Thanks for the efforts. I had this error before as well but researched and inserted DLookup.

Simple Dlookup function worked charms!

Thanks all!
0
 
LVL 13

Author Closing Comment

by:Shanan212
Comment Utility
Very easy function relacing heavy SQL!
0
 
LVL 33

Expert Comment

by:Norie
Comment Utility
The DLOOKUP isn't correct.

"VANCOUVERBRITISHCOLUMBIA" looks like it's the first value.

You need criteria as, dqmq posted.
0
 
LVL 61

Expert Comment

by:mbizup
Comment Utility
< Method/Data member not found>

Ah that was a typo...

Your recordset method is a valid alternative to DLookup, but the correct syntax would have been:

GetO = rst!TCOMBI  (exclamation point instead of a period)
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 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

15 Experts available now in Live!

Get 1:1 Help Now