Parameter Required when already shown data

[SpankYou]

Hey,

I'm using an access 2000 database, and am currently creating a form that searches for any records that meet the search criteria. This all works fine. The problem I'm having is once the relevant records details are placed in a list box, I want to be able to double click the list box on the relevant record and the record will load up in another form. Now all this works, but when I double click the list box to load the form and the relevant record, it brings up an input box that requests the sales Order No, despite the fact I have said in the code this is equal to the value of the list box. I have paused the code during run time n the listbox operates as I want it two but for some reason I still get this parameter request? Can anyone help?

My code is below...


Thanks For all your help

Sam

Option Compare Database

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT TblWorkRequest.SalesOrderNo, TblWorkRequest.NameOfRecipient, TblWorkRequest.WRNumber, TblWorkRequest.OriginationDate, TblWorkRequest.TargetDate " & _
"FROM TblWorkRequest"

strWhere = "WHERE"

strOrder = "ORDER BY TblWorkRequest.SalesOrderNo;"


'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form

If Not IsNull(Me.txtRName) Then '<--If the textbox txtFName contains no data THEN do nothing
strWhere = strWhere & &quot; (TblWorkRequest.NameOfRecipient) Like '*&quot; & Me.txtRName & &quot;*' AND&quot; '<--otherwise, apply the LIKE statment to the QueryDef
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox
Me.lstWorkRequestInfo.RowSource = strSQL & &quot; &quot; & strWhere & &quot;&quot; & strOrder

End Sub



Private Sub lstWorkRequestInfo_DblClick(Cancel As Integer)
'Open frmWorkRequest based on the SalesOrderNo from lstWorkRequestInfo listbox
DoCmd.OpenForm &quot;FrmWorkRequest&quot;, , , &quot;[SalesOrderNumber] = &quot; & Me.lstWorkRequestInfo


End Sub

&quot;You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine.&quot; - Homer

 

[SpankYou]

Hey,

Before I get any complaints, this work is from another tek tip user I can't remember who but It works perfectly in their example.

The only reason I posted the rest of the code was incase there was some sort of conflict with the double click event,

Cheers For any help

&quot;You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine.&quot; - Homer

 

[SpankYou]

Don't Worry I sorted this problem using a query very inefficient in comparison so if anyone has any ideas on how to sort this it will be much appreciated,

Cheers

Sam

&quot;You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine.&quot; - Homer

 

[TonyJollans]

Hi Sam,

Do you have SalesOrderNo (as in your SQL) or SalesOrderNumber (as in your OpenForm command)?

Enjoy,
Tony

 

[SpankYou]

Hey Tony your point was spot on, they should both have been SalesOrderNo, but since I got that part sorted, it now comes up with the error &quot;Open Form Action Has Been Cancelled&quot;, any ideas?

Cheers Sam

&quot;You couldn't fool your mother on the foolingest day of your life if you had an electrified fooling machine.&quot; - Homer

 

[TonyJollans]

Sorry, Sam,

Nothing I can see quickly in the posted code should trigger that. I'll try and have closer look tomorrow.

Enjoy,
Tony