asked on

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

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Try this - w/o the ItemData(0) - which is the first row

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

Rey Obrero (Capricorn1)

use the load event of the form

private sub form_load()

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

end if

end sub

mbizup

>> 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 & "'"))

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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) ))

gdunn59

ASKER

mbizup:

I tried your suggestion, but the form opens blank.

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

gdunn59

ASKER

DatabaseMX:

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

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

mbizup

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)

gdunn59

ASKER

capricorn1:

I also tried your suggestion, still nothing.

private sub form_load()

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

end if

gdunn59

ASKER

mbizup:

This is the rowsource query for HelpIndexList:

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

Thanks,
gdunn59

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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) ))

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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) ))

Rey Obrero (Capricorn1)

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

mbizup

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

gdunn59

ASKER

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

Rey Obrero (Capricorn1)

can you upload a copy of the db..

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

SELECT tblHelp.FieldName, tblHelp.HelpContent FROM tblHelp ORDER BY tblHelp.FieldName;
Column(1)

gdunn59

ASKER

Is it because the lookup is looking in the same table that the HelpIndexList query is based off of?

Thanks,
gdunn59

Rey Obrero (Capricorn1)

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"

gdunn59

ASKER

No duplicate values.

Rey Obrero (Capricorn1)

upload a copy of the db

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

gdunn59: Did you try using Column(1) as suggested @ http:#a37356901 ?

gdunn59

ASKER

DatabaseMX:

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

Thanks,
gdunn59

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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

mbizup

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

gdunn59

ASKER

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

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

You should post your current version here so everyone has access (no pun) to it ...

mx

gdunn59

ASKER

Here is the latest version of the database.

Thanks,
gdunn
Audit-Database--12-30-2011--for-.accdb

Rey Obrero (Capricorn1)

try this, created a new form "form1"

it does what you want to do

Audit-Database--12-30-2011.accdb

gdunn59

ASKER

capricorn1:

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

Thanks,
gdunn59

mbizup

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)

Rey Obrero (Capricorn1)

your listbox is not selecting any item on click or double click event/ could be corrupted
just delete it and rename the new form.

gdunn59

ASKER

capricorn1:

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

Thanks,
gdunn59

Rey Obrero (Capricorn1)

well, it is working here.

ASKER CERTIFIED SOLUTION

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

You just need to set allow edits to 'yes'
...

And use column 0

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

Rey Obrero (Capricorn1)

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

gdunn59

ASKER

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

Rey Obrero (Capricorn1)

did you do this

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

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

gdunn59: Did you try the working version I uploaded ?

mx

gdunn59

ASKER

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

DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

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

mbizup

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.