Hi Experts,

I need urgent help on this one. I have absolutely no experience with VBA, but program often in Visual Basic.
I want to create a function that looks up the value in a table (the table contains only one row and only field) and saves this value in a variable with the function name.
Then this value will be used to make a decision in a macro.

In VB I would writ something like this

Public Function TotaalBestelling() as Double
  Dim strSQL as string
  strsql = "SELECT MyField from MyTable"
  using cmdsql as sqlcommand = new sqlcommand(myconnection, strsql)
  TotaalBestelling = cdbl(cmdsql.executescalar)
 end using

I tried something in Access, but this obvisiously does not work. What am i doing wrong.

End function
 Public Function TotaalBestelling() As Double '''''''''''''''''''''''''''''''''''''''''''''      Dim MyDB As DAO.Database, MyRec As DAO.Recordset     Set MyDB = CurrentDb     Set MyRec = MyDB.OpenRecordset("Select TotaalVanKostprijs AS Totaal FROM TotaalBestellingLeverancier")    TotaalBestelling = CDbl(MyRec!.Totaal) 'MyList = Mid(MyList, 2)  '    Dim strSQL As String '    strSQL = "SELECT TotaalVanKostprijs FROM TotaalBestellingLeverancier" '    dim End Function                             
1: 2: 3: 4: 5: 6: 7: 8: 9: 10: 11: 12: 13: 

Select allOpen in new window

asked 12/12/2011 08:34

NoraWil's gravatar image

NoraWil ♦♦

5 Answers:
Try using the DLOOKUP function

sStr = DLookup("[FIELDNAME]", "TABLENAME")


Runrigger's gravatar image


if the field contains a number, then it will return as a number, I have simply assumed a string in the above example.

answered 2011-12-13 at 04:38:50

Runrigger's gravatar image


 Don't see anything wrong other then your not cleaning up they objects you create; what's the problem/error?

  Also if your just returning a single value, you might as well use Dlookup() depending on how/where your using this.


answered 2011-12-13 at 04:41:11

JDettman's gravatar image


ah this:

  TotaalBestelling = CDbl(MyRec!.Totaal)

should be:

  TotaalBestelling = CDbl(MyRec![Totaal])

  not both ! and .
  It's one or the other and generally you need ! with a field.


answered 2011-12-13 at 04:41:51

JDettman's gravatar image


Here is the look up using your field/table names

TotaalBestelling = DLookup("[TotaalVanKostprijs]", "TotaalBestellingLeverancier")

answered 2011-12-13 at 04:43:46

Runrigger's gravatar image


Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments



Asked: 12/12/2011 08:34

Seen: 216 times

Last updated: 12/13/2011 04:54