Solved

Function not getting table fields via sql

Posted on 2011-09-09
9
173 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
ID: 36511375


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
ID: 36511390
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
ID: 36511463
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
ID: 36511473
Public Function GetO(origin As String) As String        
        MsgBox origin
        GetO = DLookup("TCOMBI", "OUT",  "OUTCOMBI = '" & origin & "'" )
End Function
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 61

Expert Comment

by:mbizup
ID: 36511491
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
ID: 36511573
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
ID: 36511577
Very easy function relacing heavy SQL!
0
 
LVL 33

Expert Comment

by:Norie
ID: 36511609
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
ID: 36511636
< 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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 …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

920 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

12 Experts available now in Live!

Get 1:1 Help Now