open record in form from listbox

[and]

Hi People.

Simple this I am sure but I not so hot with Access....

If I have a listbox (namelist) on form1 that is populated with values from one field of a table, how do I make it open the record that I select in the listbox in form2?

TIA for any help.

Andrew.

 

[dazzer123]

If your ok with VBA then put a button on your form and in that buttons onClick event put

DoCmd.OpenForm "Form2"
Forms!Form2.RecordSource = "Select * FROM Table1 WHERE ID = '" & Me!ListBox1 & "';"

Substituting Form2 to be your form name, Table1 to be your table name ID to be the field you want to reference and Me!ListBox1 to be the name of your listbox

 

[and]

Thanks Dazzar,

Followed your instructions, but when I click the button, it opens the correct form, but I get a Runtime error '2001'.
Here's the code behind my button:

Private Sub Command34_Click()
DoCmd.OpenForm "client_data_form"
Forms!client_data_form.RecordSource = "Select * FROM Client_Table WHERE ClientID = '" & Me!clientlist & "';"
End Sub

 

[and]

Ooops..posted too soon....a little more detail:

It doesn't give me the right record. Just the first record in dbase.

The list box contains ClientID, First Name, Family Name taken from Client_Table.

I know v.little about writing VB syntax. Any ideas what I have got wrong?

Do I need to set the RecordSource of my Form1 (this is just a menu form) to Client_Table, so that when I do the listbox wizard I can choose the option: "Find a value on my form based on the value I selected in my listbox" .... or do I just choose:"I want the listbox to look up values in a table or query" .....?

Thanks for your help,
Andrew.

 

[sillysod]

2 things that might be the problem

Private Sub Command34_Click()
DoCmd.OpenForm "client_data_form"
Forms!client_data_form.RecordSource = "Select * FROM Client_Table WHERE ClientID = '" & Me!clientlist & "';"
End Sub


the
Me!clientlist

i think should be Me!clientlist.selection so you are referencing the the value you have selected in the list box

and

WHERE ClientID = '" & Me!clientlist

You need to make sure that the bound column of your list box is the same column which clientID is stored, which by the sounds of it from your last post it will be.

 

[and]

Cheers Sillysod.
I tried your suggestions....but I'm getting 'Run time error 438 - object doesn't support this method'

Private Sub Command34_Click()
DoCmd.OpenForm "client_data_form"
Forms!client_data_form.RecordSource = "Select * FROM Client_Table WHERE ClientID = '" & Me!clientlist.selection & "';"
End Sub

 

[PHV]

Provided that the bound column of your listbox is ClientID and Client_Table.ClientID is numeric, you may try something like this:
Forms!client_data_form.RecordSource = "Select * FROM Client_Table WHERE ClientID = " & Me!clientlist
i.e. get rid of the single quotes.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884