Solved

Function not getting table fields via sql

Posted on 2011-09-09
9
177 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
[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
  • 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 34

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

 
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 34

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying 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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

688 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