Acces VBA - DLookup() - Remove Prompt Enter Parameter Value

[Sorbjar]

Dear all,

I seem to have an issue with a piece of VBA code in acces.

Extrapolated from the larger code, I've got the snippet below.

fileDirectory = "GC_Annexes"
temp = "[DirectoryName] = '" & fileDirectory & "'"
counter = DLookup("[CounterValue]", "tblCounter", temp)

The problem now is that when I run this code I get a prompt wich says 'GC_Annexes' and below it is a textbox.
Once I type GC_Annexes in the box the code is continued flawlessly. I would like to be able to skip the part where I have to type in the box.
Can anyone help here?

Thanks!!!

 

[MazeWorX]

have you tried

temp = "[DirectoryName]=" & fileDirectory

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Sorbjar]

That one gives the error:

Run-time error '2471':

The expression you entered as a query parameter produced
this error:
'GC_Annexes'

temp = "[DirectoryName]='" & fileDirectory & "'" gives me the same box as mentioned before

 

[MazeWorX]

then this should work
DLookup("[CounterValue]", "tblCounter", "[DirectoryName]='" & fileDirectory & "'")

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Sorbjar]

Still getting the Message Prompt

Title: Enter Parameter Value
Text: 'GC_Annexes'
Textbox

...

 

[PHV]

And this ?

temp = "[DirectoryName]=""" & fileDirectory & """"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MazeWorX]

fileDirectory = "GC_Annexes"

im assuming "GC_Annexes" is the value in the table to check and not a controlname?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Sorbjar]

Yes GC_Annexes is the value of DirectoryName Field of which I need to get value which is stored in the CounterValue field

I've been starting to think it might have something to do wit the references, but I'd think that the whole DLookup function would be blocked then.

@PHV
temp = "[DirectoryName]=""" & fileDirectory & """" - still asks me for input

 

[MazeWorX]

Back track to the beginning and try

DLookup("[CounterValue]", "tblCounter", "[DirectoryName] = 'GC_Annexes'")

which should work. there are 2 ways to deal with a string and Dlookup one is to use single quotes around the string - the prefered method by most or you can use double quotes in which case it would be written

DLookup("[CounterValue]", "tblCounter", "[DirectoryName] = ""GC_Annexes""")

You can also type

?DLookup("[CounterValue]", "tblCounter") without the parameter values in the intermediate window and press enter to trouble shoot the function

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[MazeWorX]

Also what data types have you declared temp & fileDirectory as?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Sorbjar]

the problem is that
DLookup("[CounterValue]", "tblCounter", "[DirectoryName] = 'GC_Annexes'") ALSO gives me the prompt

Dim fileDirectory As String
Dim temp As String
Dim counter As Integer

 

[MazeWorX]

try changing the name of counter it is a reserved word in access

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Sorbjar]

changed it to counter1...

I can't seem to figure it out....
Starting to think of a dirty solution.
Putting the tblCounter in a different db and using something like

Set ws = DBEngine.Workspaces(0)
Set dbs = ws.OpenDatabase("...\counterdb.mdb")
Set rst = dbs.OpenRecordset("SELECT * FROM tblCounter WHERE Directoryname = '" & fileDirectory & "'")
counter1 = rst!CounterValue

It's not pretty, but it might do the job

 

[MazeWorX]

what happens if you type it into the intermediate window in the vbe

?DLookup("[CounterValue]", "tblCounter") ' Should return the first value in the table

and yes that may work but there is an issue with your Access because I have simulated the table and run the code successfully just to give myself pc of mind

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Sorbjar]

That drew a blanc...

BUT!!!
Reading that you succeeded made me attempt it on a different pc (my own). I created a new db and imported all table and form data. (I did this so I wouldn't copy any incorrect settings).
The code seemed to work, then I copied the db back to the original pc and it failed yet again.

I decided to reïnstall acces and now everything seems to be in order... No more weird prompts!!! :-D

Thanks a lot for your help!
Couldn't have done it without you.

 

[MazeWorX]

cool

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>