[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Function not getting table fields via sql

Posted on 2011-09-09
9
Medium Priority
?
180 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 35

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 42

Accepted Solution

by:
dqmq earned 2000 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 35

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

656 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