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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:


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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Here is your original db and working per my post @ http:#a37356857 - text version

mx
Audit-Database--12-30-2011--for-.zip
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 Architect / Systems AnalystCommented:
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 Architect / Systems AnalystCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.