Click on list item and open a detail form

[Deniseobx]

Hi Everebody,

I need help with the the following: I have a form that has a listbox that gets
its data through a query, the list box only shows two colums: Company and
ProcessDate, also the query retrieves more fields, but I set the width of the
columns to zero (For instance the query retrieves the PK, which might help for what I am trying to do).
I also have a command button "CmdViewDetail" that on_click should open a new
form that will contain the rest of the information for the record I selected
on the list box.
How should I do this? , could somebody help me?
Thanks in advance!

 

[pbaldy]

It would look something like this:

  Dim stDocName As String
  Dim stLinkCriteria As String

  stDocName = "frmEditRes"
  stLinkCriteria = "res_num=" & Me.res_num

  DoCmd.OpenForm stDocName, , , stLinkCriteria

Replacing the names as appropriate, and you may need to use the column property of the listbox to get the correct column.

 

[Deniseobx]

Thank a lot!!!

 

[ejones]

Hi,
I am trying to accomplish the same/similar task where I have a form (name of form = wf edit spec start) which has a combo box that queries and populates the drop down control, the query is: SELECT [wf spec].[ID], [wf spec].[StyleName], [wf spec].[CustomerName], [wf spec].[SpecDate] FROM [wf spec] ORDER BY [StyleName]

Then I have a control button set to run the code below on click - code:

Private Sub Edit_Record_Click()
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "wf edit spec"
stLinkCriteria = "ID=" & Me.ID
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

I cannot get this to work for the life of me. Here is some more info...
Combo Box Name: Combo42
Command Button Name: Edit Record
Form Name I want to open to a specific record: wf edit spec

When the command button is clicked, the correct form opens but always to the first record.
Can somone please help me figure this out?

Thanks for any help or ideas!
Ned

 

[pbaldy]

You might want

stLinkCriteria = "ID=" & Me.Combo42

 

[ejones]

Wow, thanks! That works great. Do you know how many things/steps I have tried to figure this out? Hundreds.
I did try:
stLinkCriteria = "Combo42=" & Me.ID
but that shows you how much I know about access and vba.
Thanks again for your reply and help.
Ned

 

[pbaldy]

No problem. I have a page here with the different syntax requirements, should you ever need them:

http://www.baldyweb.com/wherecondition.htm

 

[raphael99]

I use:

DoCmd.OpenForm "My second form", , , "Customers = '" & Me.listbox name & "'"

for open a second form from a list box . It is working but when I open the second form it opens the first record of same customer name.
In example:
Marion Bette
Marion Dave
Marion Junior
When I click on on Marion Bette it opens the right record: Marion Bette
When I click on Dave or Junior it still opens Marion Bette record.
Can anyone please help?
Thanks

 

[pbaldy]

Are you using an ID field or the name?

 

[raphael99]

The name "customers"
Anyway there is a fileed ID in the table but I'm assuming "customers" in the code if is that you mean

 

[pbaldy]

Have you tried using the ID field in the code instead of the name?

 

[raphael99]

I tried:

DoCmd.OpenForm "My second form", , , "ID = '" & Me.listbox name & "'"

if that was you meaning.
But it is not working. Maybe I mistake code. I'm very poor in code! Thanks for your reply

 

[pbaldy]

Did you change the bound column of the listbox to the ID field? Or refer to it by column?

Me.listboxname.Column(x)

where x is the zero-based number of the column with the ID in it.

 

[pbaldy]

Oh, and presuming the ID has a numeric data type, you wouldn't want the single quotes surrounding the value anymore.

 

[raphael99]

I changed listbox adding ID column (number 7) to a new one

Me.listbox.column(7) does not works

ID is a numeric type.

Can you please write down the code without quote.
I tried deleting single quote but still does not works

 

[pbaldy]

The syntax for a numeric type is in the link in post 7, as well as several posts above.

 

[raphael99]

Thanks for your patience. I tried numeric type but what is happening is that access open a text box with the name of choosen record asking for inserting value. When I type it is opening the second form but blank.
I would like to avoid to open the dialog box asking for value too.
Anyway I tried also me.listboxname.column(7) in my case and routine does not works

 

[pbaldy]

Can you post the db here?

 

[raphael99]

I uploaded work.accdbView attachment work.accdb

 

[pbaldy]

Try

DoCmd.OpenForm "MascheraPrincipale", , , "ID = " & Me.SearchResults.Column(6)