Access ListBox selection to launch other form 1

[LOSERMANN]

hello all, i need a bit o'help with this. i have a form that contains three listboxes. each list box shows a listing of clients that are working with a particular job developer. the list box info is just the name of the client and the date refered to us. i have a second form that contains a great deal more info on the client. what id like to do is select a name by double clicking and have the second more detailed for open to that record.

side note, just in case anyone can field it. someday id liek to be able to drag a client from one list box to another to transfer this caselod to another job developer.

warmest regards,


Milk

 

[jebry]

Hi Milk!

In the DoubleClick event for the list box use the following:

DoCmd.OpenForm "YourForm", , , , , "YourClientID = '" & Me!YourListBox.Value & "'"

This assumes the rowsource of the list box includes the client ID and it is in the bound column. Also, I am unsure how many commas you need to get to the Where clause in the OpenForm method.

Finally, to transfer between two list boxes I think it is easier to set up a couple of command buttons with arrows indicating which way the item is moving. Then in the click event of the command buttons you will change the job developer for the client and requery the list boxes so the change will show. Access doesn't support drag and drop and, granting that you could write you own drag and drop function, I think the above is easier.

hth


Jeff Bridgham

 

[wemeier]

You could try using the OnDoubleClick event on the listbox to trigger code to open the form with the selected name:

Private Sub lstClients_DblClick(Cancel As Integer)
    DoCmd.OpenForm "frmClientInfo", , , "ClientName='" & Me.lstClients & "'", , acDialog
End Sub

This code assumes that the name of the listbox you are double-clicking is named lstClients, the name of the form is frmClientInfo and the name of the field holding the key to the table is named ClientName. Change these accordingly.


Cruising the Information Superhighway
[sub] (your mileage may vary)[/sub]

 

[LOSERMANN]

Thank you both for your quck reply. absolutly wonderful. the post from wemeier worked to open the form, thank you as well Jeff, like you said there seemed to have been a comma issue. still cant thank you enough.

now that i have to form opening nicely, ill have to beg one more thing from you all. the new form open without a record. its blank. im certain there is a simple fix for this but allas im a bit on the 'braindead' status lately.

regards,

milk

 

[wemeier]

My code should take you to the proper record when you open the form. This is provided the form is bound to the table that contains the client information.

Make sure that the field you pass in the OpenForm method is the name of the field that contains the key to the table.

Cruising the Information Superhighway
[sub] (your mileage may vary)[/sub]

 

[LOSERMANN]

so sorry for not 'getting it'. ill try not to bother you all with this much more. the second form opens wonderfully but i get an error...

run-time error 3075

syntax error(missing operator) in query expression 'CaseNumber=60000112

'that case number is the number something we generate ourselves and the key field.

the first form is record sourse qryClients
the list box pulls some fields from this and the bound column is the key field [CaseNumber]

syntax for the listbox row source is ...SELECT [qryClients].[ClientLastName], [qryClients].[ClientFirstName], [qryClients].[Case Number], [qryClients].[Referal Date] FROM qryClients;

bound column is 3

the second form (which opens nicely) is called frmClientInformation. it has a record source of the same qry.

im perplexed


milk

 

[wemeier]

First, is CaseNumber a text field or a numeric field? If it's a text field, you must surround it with quotes. The OpenForm method would be:

DoCmd.OpenForm "frmClientInformation", , , "[Case Number]=[b][COLOR=red]'[/color][/b]" & Me.lstClients [b][COLOR=red]& "'"[/color][/b], , acDialog

If your [Case Number] is numeric, however, you can't use the quotes:

DoCmd.OpenForm "frmClientInformation", , , "[Case Number]=" & Me.lstClients, , acDialog

Cruising the Information Superhighway
[sub] (your mileage may vary)[/sub]

 

[LOSERMANN]

thanks...that took care of the error. the second form still opens to a blank form though.

the field [caseNumber] is numeric.

the record source for the second form is the same qry that the listbox pulls data from. should i have placed anyhting in the filter property, or some other property?

regards,

 

[wemeier]

Adding the WHERE parameter to the OpenForm method creates a filter when opening the form. Make sure the AllowFilters property is set to Yes.

If you leave the WHERE parameter off the OpenForm method, does the second form show data?


Cruising the Information Superhighway
[sub] (your mileage may vary)[/sub]

 

[LOSERMANN]

the allow filter is set to yes.

the form opens to a new record. id rather it opened to the record im dblclicking on in the listbox.

cant thank you enough. hats off to ya.


milk

 

[LOSERMANN]

musta been something i did to the form at an earlier time to mess it up.

i just fixed it. what i did was create a new form and copy all the controls from the old 'frmclientinformation' into the new one, now the selection from the list box works wonderfully!!!!!!


thanks so much for your efforts in setting me straight.

star to you, cause yor a superstar!


Milk