Link to home
Create AccountLog in
Avatar of Brandon Garnett
Brandon Garnett

asked on

Dlookup Asking for Parameter Value in MS Access 2010

I am having some odd behavior from Dlookup in MS Access. I am checking a table to see if a url has already been entered before adding it as a record however I continue to get an Input Box asking me to enter a parameter value. The field is a text field and I have enclosed it in single quotes, if I type the URL into the input box the record is added, if I type an existing URL into the input box it does not add it.

If IsNull(DLookup("WebpageID", "hcc_tblWebPages", "[Webpage]='" & strsearch & "'")) Then
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

If hcc_tblWebPages is a query, then open it on it's own and make sure it doesn't prompt you for a name.

Also do your Dlookup like this:

If IsNull(DLookup("WebpageID", "hcc_tblWebPages", "[Webpage]=" & chr$(34) & strsearch & chr$(34)) Then

last, what is strSearch set to?  Put a breakpoint on the line (f9) and execute.  When it stops, do:

? strSearch in the debug window (ctrl/G to bring it up) to see what the value is or hover over it with the mouse.

Avatar of Brandon Garnett
Brandon Garnett


hcc_tblWebPages is a table
strSearch is a string that holds the value entered by the user on the form

Dim strsearch As String
strsearch = Me.txtWebPage.Value

I still get the error asking for input, if I enter it asks for me the input value for then I enter in the parameter box access gives me it adds the url to the table just like it should, I have tried using me.txtwebpage.value instead of placing the value in strsearch as well and it still reads that as needing input from the user instead of the comparison string.

Can you post the full code please?

I could not replicate the parameter prompt...

Are you typing that website in directly, ...or pasting it from somewhere?
<strsearch = Me.txtWebPage.Value>
".Value" is not needed here.

You can just use this:

You must setfocus to the control in order to access the .text property, but again, it is not needed in this case.
I have tried with and without ".value"

Here is the full code

If Me.txtWebPage.Value & "" = "" Then
MsgBox "You must enter a webpage URL first", vbOKOnly, "HCC Contacts"
Exit Sub
End If

Dim strsearch As String
strsearch = Me.txtWebPage

If IsNull(DLookup("WebpageID", "hcc_tblWebPages", "[Webpage]=" & Chr$(34) & strsearch & Chr$(34))) Then
'The webpage does not exist
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO hcc_tblWebPages (Webpage, EnteredBy, DateOfEntry) VALUES " _
& "(" & Me.txtWebPage.Value & ", " & "'" & Environ("UserName") & "'" & ", " & "#" & Now() & "#" & ")"
DoCmd.SetWarnings True
'Repopulate the list of potential matches to pull up the newly added web page
Dim sqlstr As String

sqlstr = "SELECT hcc_tblWebPages.WebpageID, hcc_tblWebPages.Webpage FROM hcc_tblWebPages WHERE WebPage = '" & strsearch & "'))"
Me.lstMatches.RowSource = sqlstr


MsgBox "The Webpage already exists, please select it from the list.", vbOKOnly, "HCC Contacts"
Exit Sub
End If
Avatar of Gustav Brock
Gustav Brock
Flag of Denmark image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
I feel like an idiot now I was wracking my brain thinking it was the Dlookup throwing the problem. Thanks it works now.
Again,  how are you entering that web page, ...i cannot replicate the parameter...

This newer sample works fine for me... with no parameter prompt
(a simplified version of your code)
Taking a guess here, your parameter prompt is coming from something other than your DLookup statement - possibly an INSERT query if that is how your records are being added.

Try this variation on your code (the use of DCount here is very similar to your DLookup check).

Does the code make it to the messagebox without showing a parameter prompt?

Dim intRecCount as Integer
intRecCount  = DCount ("WebpageID", "hcc_tblWebPages", "[Webpage]='" & strsearch & "'")
MsgBox intRecCount
If intRecCount = 0 Then    '<---- This means that there are no records meeting that criteria
   '  The rest of your code
End if

Open in new window

If the message box displays before your parameter prompt, then the issue is elsewhere.

 I am checking a table to see if a url has already been entered before adding it as a record however

Can you post the rest of the code involved in adding this record?

I should have refreshed the screen :)
It is being entered on the form I, attached the screen shot,  the error was being generated by the Insert Statement not having single quote around the web url.

Thank you.
Yeah I should have waited to give out the points..I should have split them between mbizup, boag2000 and cactus_data any one of your posts after his would have had me on the right track to resolve it.
The way you closed this was perfectly fine (first correct solution).

My own comment was long after that - I didn't refresh the screen before posting.

The important thing is that you've got a solution :)
No crybabies here either...

Thank you to all, I appreciate the fast responses and all the help.
BTW, anytime you do something with critieria or SQL, it's better to do it like this:

strSQL = "INSERT INTO hcc_tblWebPages (Webpage, EnteredBy, DateOfEntry) VALUES " _
& "('" & Me.txtWebPage.Value & "', " & "'" & Environ("UserName") & "'" & ", " & "#" & Now() & "#" & ")"
DoCmd.RunSQL strSQL

This let's you put a breakpoint on the DoCmd line before it executes and see what the actual statement is that will be executed (by looking at strSQL).  This makes it easy to spot errors like this.

Also, in places where you need a quote in the resulting string, use Chr$(34).  That helps seperates quotes that you need now for syntax from those that you want in the resulting string.