Dlookup not working

[sambrierley]

Hi all,

im trying to return a single value from a table and assign it to a string to be used later but Dlookup isnt working at all. below is the code im using and the error message im recieving is "wrong number of arguements or invalid property assignment"

Sub boo()
Dim result As Integer
   result = dlookup("Definition", "Config", "Parameter = 'Mail Folder'")
End Sub

thanks
p.s i have an earlier post trying to solve the same problem via SQL, I will close both when one is answered. thanks .

 

[Galaxiom]

Parameter is a reserved word. Try putting square brackets around it.

 

[sambrierley]

nope that makes no difference, its almost like access doesnt recognise it. if i press f1 there isnt an entry in the help which is weird

 

[pr2-eugin]

Show the updated Code. So you have a table or Query named Config and there is a field/column in that table/query called Definition. Is this all correct?

 

[sambrierley]

thats all correct yes.

I have attached an image that shows everything you will need to see.
the code should return "TS" but its just failing.
I genuinely have no clue where im going wrong??

 

[Cotty42]

This should work:

Sub boo()
Dim result As Variant
   result = dlookup("Definition", "Config", "[Parameter] = 'Mail Folder'")
End Sub

By setting result as Integer will result in an error because DLookup will return "TS"

 

[sambrierley]

Ive played around with defining result as variant, string and integers and all 3 generate the same result.

I have attached the actual database if you could have a look. I do understand however if you dont wish to download it, it is purely a test bed that i use to test thingsbefore adding them into the actual database so theres nothing really in it.

thanks for your time on this btw, greatly appreciated.

 

[jdraw]

I downloaded your database.
Created a query

SELECT Config1.ID, Config1.Definition, Config1.Parameter
FROM Config1
WHERE (((Config1.Parameter)="Mail Folder"));


RESULT:

ID Definition Parameter
4 TS Mail Folder

The reason that DLookup is not working for you is because you have a local sub called DLookup() which takes precedence over the intrinsic DLookup (part of Access/vba)

Sub dlookup()

Dim strX As String
'strX = "SELECT  * FROM Config"
       '" FROM Config " & _
     ' " WHERE Config.[Parameter] = 'Mail Folder';"
        



'varX = "SELECT Config.Definition FROM Config WHERE Config.Parameter = 'Mail Folder';"
DoCmd.RunSQL "DELETE * FROM Config;"

End Sub

When I change the name of your Dlookup to DLookupc()

Sub dlookupc()

Dim strX As String
'strX = "SELECT  * FROM Config"
       '" FROM Config " & _
     ' " WHERE Config.[Parameter] = 'Mail Folder';"
        



'varX = "SELECT Config.Definition FROM Config WHERE Config.Parameter = 'Mail Folder';"
DoCmd.RunSQL "DELETE * FROM Config;"

End Sub

and run DLookup in the immediate window, it works just fine.

?Dlookup("DEfinition","Config1","[Parameter] =""Mail Folder""")
TS

You should be cautious when naming subs or functions. If you use the name of an Access/vba routine, you are replacing that sub or function with your version.

 

[sambrierley]

Fantastic but how do i store that result as something i can use in VBA?

for example the Defininition for Mail i.e. TS is the name of a folder in an outlook account therefore to refer to the correct folder TS will be required.

currently in my code refering to the folder it is fixed in the code but this may change in the future and the users of the database are likely to be unaware of how to change the code to accomoadte this change.

 

[Cotty42]

What are you using to call the sub routine?

 

[sambrierley]

A button in a form

 

[Cotty42]

I have set up a with a Combobox and a textbox. The Combobox allows the selection of Parameter via an SQL statement and the result of the DLookup is shown in the textbox as a result of an 'AfterUpdate' sub Routine, see below.

Private Sub Text3_AfterUpdate()
    Dim Result As String
    Result = DLookup("Definition", "Config", "[Parameter] = '" & Me.Text3 & "'")
    MsgBox Result
    Me.Text5 = Result
    
End Sub

This retirns the following:
Text3 Text5
Mail Folder TS
Download 1
Archive 2

I think your issue is how you are trying to call the sub-routine.

Cheers
Dave

 

[Cotty42]

To run the code on a button click use the "On Click" Event and enter the following code.

You will also have to code what you want to do into the sub-routine unless you have declared the variable 'result' as a public variable in public declarations within the database/form otherwise it is only avaialble in the sub-routine/function where it is declared.

Private Sub btnName_Click()

    Dim Result As String
    Result = DLookup("Definition", "Config", "[Parameter] = 'Mail Folder'")
    MsgBox Result
 
'   Code what you want to do here
    
End Sub

Hope this helps

Dave

 

[sambrierley]

tbh i think im going to have to find some other way of doing this.

I would of thought this is pretty simple in a database but evidently not.
there is something wrong that i dont think anyone quite understands.

Ive tried everything now and im still getting the same compile error, wrong number of arguements blah blah blah

thanks anyway