Double-Click on List Box and have another Form Open

I want to be able to open a pop-up form by double clicking a list box (IndexHelpList) and a Help Form (frmHelpContent) opens and shows the help for that field that is double-clicked in the List Box (IndexHelpList).  The list box displays field names 'FieldName' in the query behind the list box.

The way I have it set up now, the pop-up form Help Content form opens, but no matter what Field Name on double-click on in the List Box, the same help content opens every time.

 This is what I've got:

Private Sub HelpIndexList_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
Dim HelpVal As String

HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldName] = '" & Me![HelpIndexList].ItemData(0) & "'"))

DoCmd.OpenForm "frmHelpContent", WindowMode:=acDialog, OpenArgs:=HelpVal

End Sub


Private Sub Form_Open(Cancel As Integer)
Me.helpbox.Value = OpenArgs
Me.helpbox.SetFocus
Me.helpbox.SelLength = 0
End Sub

What am I doing wrong?

Thanks,
gdunn59
LVL 1
gdunn59Asked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Here is your original db and working per my post @ http:#a37356857 - text version

mx
Audit-Database--12-30-2011--for-.zip
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try this - w/o the ItemData(0) - which is the first row


HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldName] = '" & Me![HelpIndexList] & "'"))
0
 
Rey Obrero (Capricorn1)Commented:
use the load event of the form

private sub form_load()

if me.openargs & ""<>"" then
  Me.helpbox=me.openargs
 
end if


end sub
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mbizupCommented:
>> Me![HelpIndexList].ItemData(0)

What is the exact query for your listbox?  Is this indeed passing the correct column (zero based count) for the field name?

Maybe try this:

HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldName] = '" & Me!HelpIndexList & "'"))

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Also ... if FieldName is Numeric, use this:

HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldName] = " & Me![HelpIndexList] ))

If Text, use this:


HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldName] = " & Chr(34)  & Me![HelpIndexList] & Chr(34) ))
0
 
gdunn59Author Commented:
mbizup:

I tried your suggestion, but the form opens blank.

HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldName] = '" & Me!HelpIndexList & "'"))


0
 
gdunn59Author Commented:
DatabaseMX:

I also tried your suggestion, but it also opens the form with nothing.

HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldNM] = '" & Me![HelpIndexList] & "'"))

0
 
mbizupCommented:
What is the rowsource query for HelpIndexList?

This will refer to the very first column in any given row:

HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldName] = '" & Me!HelpIndexList.column(0) & "'"))

Open in new window

Then column(1),2, etc... (zero-based)


0
 
gdunn59Author Commented:
capricorn1:

I also tried your suggestion, still nothing.

private sub form_load()

if me.openargs & ""<>"" then
  Me.helpbox=me.openargs
 
end if
0
 
gdunn59Author Commented:
mbizup:

This is the rowsource query for HelpIndexList:

SELECT tblHelp.FieldName, tblHelp.HelpContent FROM tblHelp ORDER BY tblHelp.FieldName;

Thanks,
gdunn59
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
If your Help Value is not the Bound Column of the list box, try using the Column Number Minus One of the physical column that contains the Help value.  Change N below to that number

HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldName] = " & Me![HelpIndexList].Column(N) ))

If Text, use this:


HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldName] = " & Chr(34)  & Me![HelpIndexList].Column(N) & Chr(34) ))
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Then:

IF Numeric

HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldName] = " & Me![HelpIndexList].Column(1) ))

If Text, use this:


HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldName] = " & Chr(34)  & Me![HelpIndexList].Column(1) & Chr(34) ))
0
 
Rey Obrero (Capricorn1)Commented:
that means you are not passing anything in the openargs option


HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldName] = '" & Me!HelpIndexList.column(0) & "'"))

if helpVal & ""<>"" then

DoCmd.OpenForm "frmHelpContent", WindowMode:=acDialog, OpenArgs:=HelpVal

else
msgbox "Help Value Is " & helpVal

end if


0
 
mbizupCommented:
gdunn,

<<

This is the rowsource query for HelpIndexList:

SELECT tblHelp.FieldName, tblHelp.HelpContent FROM tblHelp ORDER BY tblHelp.FieldName;

>>

Then this should do the trick by referring to column zero (the first column, zero-based): http:#a37356892
0
 
gdunn59Author Commented:
capicorn1:

When I try your suggestion (listed below), the message box doesn't show anything, so you're right for some reason it is not passing anything to the openargs option.  Can't figure out why.

HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldName] = '" & Me!HelpIndexList.column(0) & "'"))

if helpVal & ""<>"" then

DoCmd.OpenForm "frmHelpContent", WindowMode:=acDialog, OpenArgs:=HelpVal

else
msgbox "Help Value Is " & helpVal

end if
0
 
Rey Obrero (Capricorn1)Commented:
can you upload a copy of the db..
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:


SELECT tblHelp.FieldName, tblHelp.HelpContent FROM tblHelp ORDER BY tblHelp.FieldName;
                                                            Column(1)
0
 
gdunn59Author Commented:
Is it because the lookup is looking in the same table that the HelpIndexList query is based off of?

Thanks,
gdunn59
0
 
Rey Obrero (Capricorn1)Commented:
Is it because the lookup is looking in the same table that the HelpIndexList query is based off of?

NO,

are there  duplicate values in field "FieldName"
0
 
gdunn59Author Commented:
No duplicate values.
0
 
Rey Obrero (Capricorn1)Commented:
upload a copy of the db
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
gdunn59:  Did you try using Column(1) as suggested @ http:#a37356901  ?
0
 
gdunn59Author Commented:
DatabaseMX:

Yes I tried it with both (0) and (1).  Still nothing.

Thanks,
gdunn59
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Well, actually it should be working with Column(0) as posted by others, per my first post w/o column number since you need to find FieldName.  So, something else is going on.

mx
0
 
mbizupCommented:
See Form1 here.

This is applying the code that I posted earlier (http:#a37356892) to a sample you posted in a related question. Using column 0 is the only change.
Audit-Database--12-21-2011--for-.accdb
0
 
gdunn59Author Commented:
mbizup:

That is an older version of the database.  In the sample that you uploaded, it is using the field ControlName from the lookup table "tblHelp", and I am now using the field "FieldName" in the lookup table "tblHelp".

I was using the ControlName field first before because I was referencing the actual control name on the form, that has since changed.

Thanks,
gdunn59
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You should post your current version here so everyone has access (no pun) to it ...

mx
0
 
gdunn59Author Commented:
Here is the latest version of the database.

Thanks,
gdunn
 Audit-Database--12-30-2011--for-.accdb
0
 
Rey Obrero (Capricorn1)Commented:
try this, created a new form "form1"

it does what you want to do


Audit-Database--12-30-2011.accdb
0
 
gdunn59Author Commented:
capricorn1:

So what was the issue, or what did you change (just curious)?

Thanks,
gdunn59
0
 
mbizupCommented:
Set the allow edits property on your form to 'Yes'

It is currently set to 'no', so double clicking your list box has no effect (the value remains null)
0
 
Rey Obrero (Capricorn1)Commented:
your listbox is not selecting any item on click or double click event/ could be corrupted
just delete it and rename the new form.
0
 
gdunn59Author Commented:
capricorn1:

I just downloaded the database that you attached with the new form, and I still get nothing.

Thanks,
gdunn59
0
 
Rey Obrero (Capricorn1)Commented:
well, it is working here.
0
 
mbizupCommented:
You just need to set allow edits to 'yes'
...

And use column 0

HelpVal = Nz(DLookup("[HelpContent]", "tblHelp", "[FieldName] = '" & Me!HelpIndexList.Column(0) & "'"))
0
 
Rey Obrero (Capricorn1)Commented:
actually you don't need a dlookup, you just need

to set the following property of the list box
Column COunt 2
Column Witdh  1;0


then use

HelpVal = Me!HelpIndexList.Column(1)


Private Sub HelpIndexList_DblClick(Cancel As Integer)
Dim stDocName As String
Dim stLinkCriteria As String
Dim HelpVal As String
 
 
HelpVal = Me!HelpIndexList.Column(1)
 

DoCmd.OpenForm "frmHelpContent", WindowMode:=acDialog, OpenArgs:=HelpVal

 

 


End Sub
0
 
gdunn59Author Commented:
capricorn:

I tried your suggestion below, and I get an error about Invalid Use of Null:

Dim stDocName As String
Dim stLinkCriteria As String
Dim HelpVal As String
 
 
HelpVal = Me!HelpIndexList.Column(1)
 

DoCmd.OpenForm "frmHelpContent", WindowMode:=acDialog, OpenArgs:=HelpVal

0
 
Rey Obrero (Capricorn1)Commented:
did you do this


to set the following property of the list box
Column Count 2
Column Witdh  1;0

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
gdunn59:  Did you try the working version I uploaded ?

mx
0
 
gdunn59Author Commented:
DatabaseMX:

I thought I had downloaded your version before, but I must have downloaded one of the other versions that had the issue.

It appears to be working now.  I imported the two forms "frmHelpContent" and "frmHelpIndex" from your database and now it appears to be working.,

I have to leave now, but will get back on tonight and try some further testing to make sure.

Thanks everyone and Happy New Year!

gdunn59
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
gdunn59:
Although I did not see mbizup's post re "You just need to set allow edits to 'yes'' prior to posting the db, I too noticed that also ... as part of the problem. The other part of the issue was the ItemData(0) syntax ... which I initially corrected. So, to be fair, you should spit the points with mbizup.

mx
0
 
mbizupCommented:
mx,

Thanks for recognizing that - I appreciate it.


gdunn59,

Don't worry about the points.  The Mods have a huge backlog of Community Support requests right now :)


Happy New Year, everyone.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.