Open a form via List Box selection?

[mamradzelvy]

Hi,
how would i go about opening a form (in this case a record editting form) based on my selection from a list box?

I have a neat little entry form with a listbox search on the right side and i was hoping it would be possible to double click any of the contents which came back to me via queuery and load them into a pop-up form where i then could edit them.

Now I think i could handle the SQL part of things, but i just don't know how i would go about making this work.

 

[CJ_London]

in the listbox doubleclick event put something like

docmd.openform nameofform

 

[theDBguy]

In addition, you might also use the WhereCondition argument of the OpenForm method, if you need to filter the form to a specific record; namely, the selected row in the Listbox.

 

[mamradzelvy]

In addition, you might also use the WhereCondition argument of the OpenForm method, if you need to filter the form to a specific record; namely, the selected row in the Listbox.

Could you please give a short example of this? I'm absolutely new to Access, VBA and code in general.

 

[theDBguy]

Could you please give a short example of this? I'm absolutely new to Access, VBA and code in general.

Have a look here...

DoCmd.OpenForm method (Access)

Office VBA reference topic

docs.microsoft.com

 

[mamradzelvy]

Have a look here...

DoCmd.OpenForm method (Access)

Office VBA reference topic

docs.microsoft.com

Yes, i found that, however i don't know how i would let it know which line i selected.

 

[mamradzelvy]

Have a look here...

DoCmd.OpenForm method (Access)

Office VBA reference topic

docs.microsoft.com

This is what i wrote:

Private Sub lstInventory_DblClick(Cancel As Integer)
DoCmd.OpenForm "formEditSklad", , , ID = " & Me!lstInventory.Value & "
End Sub

lstInventory being my listbox with search results, formEditSklad being my bound form with 4 bound text boxes.

it however opens to a blank form.

 

[theDBguy]

Yes, i found that, however i don't know how i would let it know which line i selected.

Me.ListboxName should give the item you selected.

 

[theDBguy]

This is what i wrote:

Private Sub lstInventory_DblClick(Cancel As Integer)
DoCmd.OpenForm "formEditSklad", , , ID = " & Me!lstInventory.Value & "
End Sub

lstInventory being my listbox with search results, formEditSklad being my bound form with 4 bound text boxes.

it however opens to a blank form.

You forgot the opening double quote before ID.

 

[mamradzelvy]

You forgot the opening double quote before ID.

I now tried this:

Private Sub lstInventory_DblClick(Cancel As Integer)
DoCmd.OpenForm "formEditSklad", , , "ID LIKE ' & Me!lstInventory & '"
End Sub

still resulting in a blank form though.

 

[theDBguy]

Is ID a number or text field?

 

[mamradzelvy]

Is ID a number or text field?

text field

 

[theDBguy]

text field

What do you get with this?

MsgBox Me.lstInventory

 

[Minty]

I think you were close - try this;

DoCmd.OpenForm "formEditSklad", , , "ID = '" & Me.lstInventory & "'"

 

[mamradzelvy]

I think you were close - try this;

DoCmd.OpenForm "formEditSklad", , , "ID = '" & Me.lstInventory & "'"

This indeed has worked, would you mind explaining why the extra set of quotation marks was necessary?

 

[Minty]

No problem. You have to concatenate the string and because your ID field is a string you need to put the single quotes around it.
Sometimes it's easier to "see" the problem if you create these things separately so for debugging and sanity preservation try something like this;

Private Sub lstInventory_DblClick(Cancel As Integer)
    
    Dim strWhere as String
    
    strWhere = "ID LIKE ' & Me.lstInventory & '"
    Debug.print strWhere
    
    strWhere = "ID = '" & Me.lstInventory & "'"
    Debug.print strWhere
    
    DoCmd.OpenForm "formEditSklad", , , strWhere

End Sub

Now if you look in the immediate window (Press ctrl+G in the editor) you will see the two strings and what the difference is.
One will be pretty meaningless to access as a where clause, and one will have the value in the format it needs to see.

 

[mamradzelvy]

Thank you very much Minty, i didn't manage to get this running in the immediate window, but i'll try to get back to it later more than likely.