Solved

Function not getting table fields via sql

Posted on 2011-09-09
9
175 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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
 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

830 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