List Box Double Click

[james_IT]

Hi,

I have a form (called Customer Search) with a list box of all customers details.

When i double click on one customer i want it to
1) take their customer ID number
2) close the current form (called Customer Search)
3) Open another form (called Sales)
4) insert the customer ID a Text Box (called Customer ID) in the form Sales.

Ive been trying for hours to get the right code but nothing seems to work.

Thanks

 

[FoFa]

Open new form
doevents
grab ID
doevents
insert ID in new form
close old form

Your doevents may need to be increased, or even decreased depending on performance.
Just my 2 cents

 

[james_IT]

thanks, but i need the code for dblclick comand button. maybe a dlookup function? i dont know. any help?

 

[FoFa]

isn't your key part of the listbox? I would make your key partof the listbox and set the column width to zero so you can't se it. Then just use it.

 

[gemma-the-husky]

the list box query should include both the customer id AND the customer name - that way you know what the id is without having to hunt for it. The id is (or should) be the PK of the customer table.

so either just save the id in a variable somewhere, or another reocommended technique is hide the form, and then open the new form, and you can still get the reference from the hidden form

so the only code needed in the dbl-clik is docmd.openform "OrderForm"

and in the orderform current event you need

customerid = forms!"searchform"!mycombobox

 

[jeremypaule]

Private Sub lstReports_DblClick(Cancel As Integer)
    Dim sForm       As String
    Dim sArgs       As String
    
    sForm = "New Event"
    DoCmd.Close acForm, sForm

      
    For Each row In Me.lstReports.ItemsSelected
        sArgs = Me.lstReports.Column(0, row)        'should be the event id
    Next row
    
    sArgs = "Form=" & Me.Name & ";" & _
            "Event ID=" & sArgs & ";"
    
    DoCmd.OpenForm sForm, acNormal, , , , acWindowNormal, sArgs
End Sub

here is the code I use to do what you're trying to do.

 

[james_IT]

yes, csutomer Id is the PK in that listbox query. Sorry if its blatently obvious or if im just acting like an idiot but in that code which bits do i alter to make it work with my db. thanks for everyones help

 

[jeremypaule]

well you'd need to alter box names and field names. Also, for the form that is opening you'd need to have some On Load code to find the correct records. There are different ways you can get this to work, play around with it.

 

[james_IT]

ive tried many times now and the code still wont work;

Private Sub List30_DblClick(Cancel As Integer)
Dim sForm As String
Dim sArgs As String

sForm = "Sales"
DoCmd.Close acForm, sForm


For Each Row In Me.List30.ItemsSelected
sArgs = Me.List30.Column(1, [Row]) 'should be the event id
Next Row

sArgs = "Form=" & Me.Name & ";" & _
"Customer ID=" & sArgs & ";"

DoCmd.OpenForm sForm, acNormal, , , , acWindowNormal, sArgs
End Sub

any pointers for where im going wrong? thanks for everyones help.

 

[gemma-the-husky]

try these things
and put a breakpoint in the sub, and do it a line at a time

1. its columns, not column
2. i don't think you can do rows without dimming it as a variant
3. if row is a reserved word change it to something else
4. you might need to reference row as row.date.columns etc
5. look at help, iterating list box rows

dim row (i think its a variant)

For Each row In Me.List30.ItemsSelected
sArgs = row.Columns(1)
next row

 

[james_IT]

i have no idea what you just said.. im fairly new to the whole code thing so i dont know anything...

could you perhaps give me an example of how the code should look?

thanks again

 

[pbaldy]

This is almost comical.

DoCmd.OpenForm "Sales"
Forms!Sales.[Customer ID] = Me.ListBoxName.Column(0)
DoCmd.Close acForm, "Customer Search"

Adjust the 0 in the second line to the appropriate column in the listbox source containing the value you want to copy, and of course substitute the name of the listbox.

 

[james_IT]

it was comical but that one worked thanks!!