Open Form and goto record with Listbox selection

[GaryShelton]

I am trying to open a form and navigate to a record. Here is the code:



Private Sub btnPoolDetails_Click()
On Error GoTo btnPoolDetails_Click_Err
Dim PoolNumber As String

If Me.lbxCustomerPoolList.ListIndex < 0 Then
MsgBox "Select a Pool from list"
Else
PoolNumber = Me.lbxCustomerPoolList.Column(1)
End If
MsgBox "[tbl1Pools]![ID]=" & PoolNumber
DoCmd.OpenForm "frm1AllPoolsDetails", acNormal, "", "[tbl1Pools]![ID]=" & PoolNumber, , acNormal

btnPoolDetails_Click_Exit:
Exit Sub
btnPoolDetails_Click_Err:
MsgBox Error$
Resume btnPoolDetails_Click_Exit
End Sub

The message Box returns the proper ID, however I get a parameter request asking for a PoolNumber with the correct Poolnumber in the message


any Help??

[davegri]

Does the form's recordsource contain the field P04C0009? If not, it would cause the parameter request.

[GaryShelton]

No the P04C0009 is the ID in the record table

[orange]

Can you post a copy of the database? We don't need all the data, just enough to highlight this issue and instructions to get to and understand it.

[GaryShelton]

I am just getting started with Access and think this should be a simple implementation, one I can see using often so I must simply be missing something.

Just 2 steps:

1) Open a Form from another one, no problem there
2) Filter the second form, or even navigate to a record with a value from the first one. Where that value comes from shouldn't matter.
In this case I get it from a ListBox column and can verify that through the msgBox function.

My second form has a table as it's record source and I want to filter/find a unique index "ID" of P04C0009

Thanks for the help

[accesstos]

PoolNumber is text and it must be between single quotes.
You have to say: DoCmd.OpenForm "frm1AllPoolsDetails", acNormal, "", "[tbl1Pools]![ID]='" & PoolNumber & "'", , acNormal

[GaryShelton]

The single quotes were it!! Thanks so much. I knew it was something simple I was missing. I have worked in Delphi up until now so the Quote double quote sometimes () sometimes not can be confusing. As well as my failing eyesight, probably looked at examples and missed the single quote betwen the doubles.

Thanks again!

[accesstos]

You are welcome!