Hi,

I modified a code for a search form to open a form for a specific criteria as one of the search fields was not working. Now it works but the problem is I get the Enter Parameter pop up for Date; time! Here is the code:
Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = ""
If Trim(txtcustname) <> "" Then
stLinkCriteria = "[customer]=" & Me![txtcustname]
End If
If Trim(txtterms) <> "" Then
If Len(stLinkCriteria) > 0 Then
stLinkCriteria = stLinkCriteria + " And "
End If
stLinkCriteria = stLinkCriteria + "[terms#]='" + Trim(UCase(txtterms)) + "'"
End If
If Trim(txtrma) <> "" Then
If Len(stLinkCriteria) > 0 Then
stLinkCriteria = stLinkCriteria + " And "
End If
stLinkCriteria = stLinkCriteria + "[rma#] = '" + Trim(UCase(txtrma)) + "'"
End If
If Trim(txtintrma) <> "" Then
If Len(stLinkCriteria) > 0 Then
stLinkCriteria = stLinkCriteria + " And "
End If
stLinkCriteria = stLinkCriteria + "[internalrma] = '" + Trim(UCase(txtintrma)) + "'"
End If
If Trim(txtpo) <> "" Then
If Len(stLinkCriteria) > 0 Then
stLinkCriteria = stLinkCriteria + " And "
End If
stLinkCriteria = stLinkCriteria + "[po#]='" + Trim(UCase(txtpo)) + "'"
End If
If Trim(txtmemo) <> "" Then
If Len(stLinkCriteria) > 0 Then
stLinkCriteria = stLinkCriteria + " And "
End If
stLinkCriteria = stLinkCriteria + "[creditmemo]='" + Trim(UCase(txtmemo)) + "'"
stDocName = "frm_find-memo"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.close acForm, "frm_detail-find"
Exit Sub
End If
If Trim(txtquote) <> "" Then
If Len(stLinkCriteria) > 0 Then
stLinkCriteria = stLinkCriteria + " And "
End If
stLinkCriteria = stLinkCriteria + "[quote#]='" + Trim(UCase(txtquote)) + "'"
End If
If Trim(txtway) <> "" Then
If Len(stLinkCriteria) > 0 Then
stLinkCriteria = stLinkCriteria + " And "
End If
stLinkCriteria = stLinkCriteria + "[waybill#] = '" + Trim(UCase(txtway)) + "'"
stLinkCriteria = stLinkCriteria + " And "
stLinkCriteria = stLinkCriteria + "[courier] = '" + Trim(UCase(txtcourier)) + "'"
stDocName = "frm_find-part-detail"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.close acForm, "frm_detail-find"
Exit Sub
End If
stDocName = "frm_r"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.close acForm, "frm_detail-find"

Exit_search_Click:
Exit Sub

Can anyone tell what is wrong with it? Here is the modification I added:
stLinkCriteria = ""
If Trim(txtcustname) <> "" Then
stLinkCriteria = "[customer]=" & Me![txtcustname]
End If
the customer field is numeric and the txtcustname is a combo box showing the name of the customer but the bound column is the ID of the customer so both are numeric...Can anyone help!
Thanks

--
Message posted via http://www.accessmonster.com

"Abby B via AccessMonster.com" <forum (AT) AccessMonster (DOT) com> wrote:

Were the hashes (#) in the original code? Hashes are used to delimit
date/time data types in SQL. Your field names would tend to suggest that
they are not date/time data types.

Also, this:

stLinkCriteria = stLinkCriteria + " And "

looks a little odd - shouldn't that "+" be "&"?

Regards,
Keith.
www.keithwilby.com

Thanks for your reply but there were no hashes in the orginal code and no date fields at all in the search form.

--
Message posted via http://www.accessmonster.com

"Abby B via AccessMonster.com" <forum (AT) AccessMonster (DOT) com> wrote:

This may be the cause of the problem - the hashes may be being interpreted
as date/time delimiters, hence the date/time Enter Parameter prompt. Are
the hashes part of the field names? Try removing them/using a more
acceptable nomenclature.

Regards,
Keith.
www.keithwilby.com

Oh u mean the hashes in the field names! no that the field names and they didn't do any problem before. It wasn't causing the popup and the code I added doesn't have them which caused the pop up so I don't know why I get it?

--
Message posted via http://www.accessmonster.com

"Abby B via AccessMonster.com" <forum (AT) AccessMonster (DOT) com> wrote:

In that case, in your modified code, try changing

stLinkCriteria = "[customer]=" & Me![txtcustname]

to

stLinkCriteria = "[customer]='" & Me![txtcustname] & "'"

(assuming that txtcustname is a text data type).

I still get the pop up! any other suggestion.

Thanks for your help!

--
Message posted via http://www.accessmonster.com

"Abby B via AccessMonster.com" <forum (AT) AccessMonster (DOT) com> wrote:

I just re-read your OP and note that the data types are numeric, so the
quotation marks are not necessary.

I'm at a loss as to why adding the code you say you added causes the
problem - a date input prompt makes me suspect the hashes - are you sure
nothing else has been changed, including any underlying stored queries (has
any criteria been added for example)?

The original code had a wrong field and the criteria in the customer field was not working so I had to redo it and it works but the you get the pop up...Actually, the other fields are not numberic they are numbers but includes letters so it is text type...

--
Message posted via http://www.accessmonster.com