Opening a form, showing a specific record based on the selection in a list box (1 Viewer)

[shery1995]

Hi All,
I have a little issue. I am trying to open a form, showing a specific record based on the selection in a list box. I am using the following code and getting "mismatch" error:

DoCmd.OpenForm "frmClients", , , _
"[tblClient.ClientID]=" & "'" & Me.lstSearch.Column(0) & "'"

The form I am opening is "frmClients", which is opened by double clicking on a list box, named "lstSearch". ClientID is a Autonumber. Anyone have any ideas?

Thanks,

 

[Gasman]

No quotes if a number?

DoCmd.OpenForm "frmClients", , , _
       "[tblClient.ClientID]=" & Me.lstSearch.Column(0)

 

[mike60smart]

Hi
Gasman beat me to it

The syntax would be something like this

DoCmd.OpenForm "frmClients", , , "[ClientID]=" & [LstSearch].Column(0)

 

[shery1995]

No quotes if a number?

DoCmd.OpenForm "frmClients", , , _
       "[tblClient.ClientID]=" & Me.lstSearch.Column(0)

Thank you for your reply it opens up the form but shows no record at all.

 

[shery1995]

Hi
Gasman beat me to it

The syntax would be something like this

DoCmd.OpenForm "frmClients", , , "[ClientID]=" & [LstSearch].Column(0)

Thank you for your reply it opens up the form but shows no record at all.

 

[Gasman]

Debug.print the value of your list column

Plus that column does not appear to be used if you Google 'Open form from listbox'?

opening a form using a listbox and a button - Microsoft Access / VBA

opening a form using a listbox and a button. Microsoft Access / VBA Forums on Bytes.

bytes.com

 

[mike60smart]

In Design View of the Form make sure that on The Data Tab the Data Entry is set to NO

 

[shery1995]

In Design View of the Form make sure that on The Data Tab the Data Entry is set to NO

Data entry is set to No but still not showing the record.

 

[mike60smart]

Can you upload a zipped copy of the database?

 

[mike60smart]

Try this method


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmClients"

stLinkCriteria = "[ClientID]=" & Me![LstSearch]
DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[Gasman]

Try this method


Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmClients"

stLinkCriteria = "[ClientID]=" & Me![LstSearch]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Add Debug.Print strLinkCriteria first before trying to open the form and post back what that displays.

 

[Micron]

Plus that column does not appear to be used if you Google 'Open form from listbox'?

If the listbox is unbound and you don't use the Column property, the result will be Null. We don't know if the listbox is bound or not. If using the column property provides a result - e.g. in the immediate window type ?forms!NameOfFormHere.ListboxNameHere.Column(0) and hit enter. If the expected value is correct yet produces no records, something else is at play:

- there are no records using that value as a filter (examine or test via query to be sure)
- form Open or Load code is screwing it up
- the field linked to the column value is a multi value field
- ?

 

[shery1995]

In Design View of the Form make sure that on The Data Tab the Data Entry is set to NO

Thank you so much it's working fine now, it was me who unchecked the ClientID in row source that is why it was not working. Many thanks again

 

[mike60smart]

We were glad to help