Opening a form, showing a specific record based on the selection in a list box

[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 - Post.Byes

Hey room, in Access 2000 in the VB part. Im trying to open a form after selcting a value from a list box. Once you select the value you click a button which then opens a form with the details from the list box on it. The list box is called list15 and the button command12. The form has not been...

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.

 

[Deleted member 147267]

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