Form List double click to open record

[chris-uk-lad]

Hi,

I currently have a search form where as i type in the name of an owner and it displays a list of records relating to that owner. I want to then be able to click on one of the records in the list and it open the form displaying the results (frmAddRecord in this case). Below is the code im using.

Option Compare Database
Option Explicit
Dim strSQL As String
Const strSQL1 = "SELECT DISTINCT Sheet1.Owner, Sheet1.[Request Type], Sheet1.Comments FROM Sheet1 WHERE Sheet1.Owner ="
Private Sub cmdClear_Click()
    Me!lstResults.ColumnCount = 3
    Me!lstResults.ColumnWidths = "0cm;5cm;9cm"
    Me!lstResults.RowSource = ""
End Sub
Private Sub cmdClose_Click()
    DoCmd.Close
End Sub
Private Sub cmdSearch_Click()
    sBuildSQL
End Sub
Sub sBuildSQL()
    If Not IsNull(Me!txtOwner) Then
        strSQL = strSQL1 & "'" & Me!txtOwner & "'"
        Me!lstResults.ColumnCount = 5
        Me!lstResults.ColumnWidths = "0cm;5cm;9cm;1cm;1cm"
        Me!lstResults.RowSource = strSQL
    End If
End Sub
Private Sub lstResults_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmAddRecord", , , "[Owner]=" & Forms!frmViewUserList!lstResults
End Sub

But this gives me the error: 3075
Syntax error (missing operator) in query expression '[Owner]=Chris Jones".

Any Thoughts??

Thanks

 

[DCrake]

You need to put single quotes around the persons name

"[Owner]= '" & Forms!frmViewUserList!lstResults & "'"

 

[chris-uk-lad]

Thanks for that, as the result was showing name i didnt figure that was needed :x.

 

[DCrake]

Not sure, but the Owner could be misleading Access as Owner could be a reserved word.

What is also confusing is that you are opening a form called FrmAddRecord for a particular user. Is the record you are adding a new person or is it a record that is a child of the person?

If you only want to display the name of the person that appears in the list box then on the OnLoad of the frmAddRecord form state

Me.TxtOwner = Forms("frmViewUserList")("LstResults")

David

 

[chris-uk-lad]

Its ok, i solved it (edited post at same time lol).

Its because i had changed the first value in the SQL statements to [Log Reference] and Forms!frmViewUserList!lstResults takes the result of the first value in the statement. It all works fine with Owner as the first value , but now that i had adapted to be [Log Reference] (an id number) it just wont work. Forms!frmViewUserList!lstResults shows as 1 (correct id).

Const strSQL1 = "SELECT DISTINCT Sheet1.[Log Reference], Sheet1.Owner, Sheet1.Scheme, Sheet1.[Business Group],Sheet1.[Request Type], Sheet1.Comments FROM Sheet1 WHERE Sheet1.Owner ="

Private Sub lstResults_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmAddRecord", , , "[Log Reference]= '" & Forms!frmViewUserList!lstResults & "'"
End Sub

I now get the error "the openform action was canceled"

 

[DCrake]

You need to refer to the specific column in your list box such as

Me.LstResults.Column(2)

Don't forget the first column is column 0

 

[chris-uk-lad]

I dont understand, as '" & Forms!frmViewUserList!lstResults & "'" already indicates its set as the ID, the problem appears to stem from "[Log Reference]= but i cannot be sure.

 

[chris-uk-lad]

Still not able to specify the Log reference but believe it now to relate to the value being a number as i added a date column and tried that which also shows an OpenForm error yet anything with text seems to link to a record no problem. Really need it to relate to log number though

 

[Rich]

Something like

DoCmd.OpenForm "YourForm", acNormal, "", "YourId =" & Me!YourList acFormEdit, acNormal

 

[chris-uk-lad]

That works exactly as requested, thank you so much

 

[Rich]

You're welcome