Full Version: change highlighted row in listbox when coming from another form
UtterAccess Forums > Microsoft® Access > Access Forms
Pages: 1, 2
tpd15
Hi
I have a listbox in my main form. The list box is populated with various sample ID's, and when a line is selected in teh list box the main form filters to show just that sample's info.
I have another form that lists test vs samples. Someone can come back to this mainform by selecting a row on the tests vs samples form (via DoCmd.OpenForm stDocName, , , stLinkCriteria).
Question: how do I make the maionform's list box update its highlighted row to reflect whatever record is now showing in the main form? I've tried stuff in the tests vs samples form's goto button like
CODE
Forms!frm_sampledetails!lstbox_SelectSample = "[SampleID]=" & Me![SampleID]

Alternately, I guess I could just go wth having the list box "unhilight" all rows, but I also don't know how to do that. Any ideas?
Jack Cowley
This code clears all the highlighted items in the list box:
CODE
Dim i As Integer
For i = 0 To ListBoxName.ListCount - 1
        Me.ListBoxName.Selected(i) = False
Next i

This code is close to what you need to 'highlight' a particular item in the list box:
CODE
Dim i As Integer
For i = 0 To ListBoxName.ListCount - 1
If ListBoxName.ItemData(i) = "YourID" Then
        Me.ListBoxName.Selected(i) = True
End If
Next i

No guarantees the code above for the ID is correct, but it should get you started...
Jack
tpd15
Hi Jack,
eeing as I'd prefer to show the correct line as higlighted in the list box, I went with your second piece of code. I wasn't too sure what to do with it, so stuck it in the OnCurrent event of the main form & changed the list box & ID names. Nothing happened. Not too sure where to go from here...
Jack Cowley
Let's assume that the bound column of the list box has the primary key. I will assume that the form that has the list box is closed and you are opening it and want to pass the ID in question to the form and the list box. Here is the code to open the form:
oCmd.OpenForm "NameOfFormWithListBox", , , , , , Me.NameOf ControlOnCurrentFormWithTheID
In the On Open event of form with the list box:
If Not IsNull(OpenArgs) Then
Dim i As Integer
For i = 0 To ListBoxName.ListCount - 1
If ListListBoxName.ItemData(i) = OpenArgs Then
Me.ListboxName.Selected(i) = True
End If
Next i
End If
hth,
Jack
tpd15
Hi Jack
Yck
Your assumptions are correct, as I understand them, except that the form with the list box will more than likely be open. Even with it closed, the code errored out, saying "The expression you entered refers to an object that is closed or doesn't exist". The DoCmd.OpenForm I had worked fine before inserting the openargs bit into it... wierd. Anyway, here's the two functions I have:
CODE
Private Sub btn_GotoSampleDetails_Click()
On Error GoTo Err_btn_GotoSampleDetails_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "frm_SampleDetails"
    
    stLinkCriteria = "[SampleID]=" & Me![SampleID]
    DoCmd.Close
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.SampleID
etc..
CODE
[color="green"]' this is the form that has the list box[/color]
Private Sub Form_Open(Cancel As Integer)
If Not IsNull(OpenArgs) Then
Dim i As Integer
For i = 0 To lstbox_SelectSample.ListCount - 1
If lstbox_SelectSample.ItemData(i) = OpenArgs Then
Me.lstbox_SelectSample.Selected(i) = True
End If
Next i
End If
End Sub
Jack Cowley
Modify your code below as shown and try again:
'Dim stDocName As String
'Dim stLinkCriteria As String
'stDocName = "frm_SampleDetails"
'stLinkCriteria = "[SampleID]=" & Me![SampleID]
---Remark out the 4 lines of code above
DoCmd.Close acForm, "NameOfFormToClose"
DoCmd.OpenForm stDocName, , , , , , Me.SampleID
tpd15
Same error as before unfortunately (also, I had to leave in the line with stDocName = "frm_SampleDetails"). For some reason, it's the openargs section of the DoCmd.openform cmd that is hicupping. If I comment that out, it opens the form ok, but without any linking or clearing of the listbox.
oth forms have text controls called SampleID, both of which refer to fields called SampleID. I use the wizard to make forms, and always forget to go back later & rename the controls... Could this be part of it?
Jack Cowley
I think the problem is that the two lines of code should be reversed and I should have noticed this sooner:
oCmd.OpenForm stDocName, , , , , , Me.SampleID
DoCmd.Close acForm, "NameOfFormToClose"
Odon't think the problem is with your control names...
Hopefully this will fix the problem as I am starting to feel pretty dumb!!!
Jack
tpd15
Ah ha! That mostly did it. Funnily enough, just having ".Close" without naming the form worked ok before we started adding code (!).
Ok, so I then added back in the lines of code you'd suggested remming out. This means now when one form closes the other opens to the correct record, and the list box shows the correct row higllighted.
But -- it only does this the once. The mainform will probably stay open all the time. People will have the other form as a pop-up that they can scan through, then click a button and be transported back to the mainform & the desired record. How do I make the listbox highlighting fire each time they come back to the mainform? Having it in the OnOpen only works the first time. I tried putting it in the OnCurrent, but that had no effect.
Jack Cowley
It is always a good idea to explicitly close a form rather than just using Close... As for where to use the code since the form remains open - Try the forms On Got Focus event. You should look this up in Access help to see the sequence of events and when they fire, as events like On Activate do NOT fire if the form gets the focus from another form...
I'm glad that some positive progress is being made!!!!
Jack
tpd15
I tried the form's GotFocus, but got no joy. Also tried making it run in the GotFocus property of one control, then set my other form to target that control. It got a bit hicuppy, and have managed to get that working yet.
But then something occurred to me -- does the OpenArgs even work if the target form is already open?
Jack Cowley
Good point about OpenArgs!!! You can find out by putting the word Stop, before the existing code in the On Open event, and when you run your code and the form is already open you will know if that event is triggered as the code editor will open with the Stop highlighted, but I doubt that it is.
On addition to OpenArgs you can set a global variable and refer to that variable when you need to get the value for the listbox. If I weren't so busy I would try to find a solution for you but things are a bit hectic at the moment...
Jack
tpd15
Tried the Stop thing, had not heard of that, it's a great idea to test code (I've used a msgbox before). Had trouble opening my form! It kept going to the debugger! Once I got around that I was able to test, and no, openargs doesn't seem to have an effect as the OnOpen only seems to fire that once...
Oh well, will try to find another way around this thing! Thanks for the help.
And hectic? Shouldn't you be kicking back with a glass of good wine about now? Isn't that what retirement's about?
Jack Cowley
I had an idea and I tried it and it works. I did it with a command button but it should work in the button that closes the form. Here is the code:
CODE
Dim i As Integer
For i = 0 To [Forms]![NameOfYourOtherOpenForm]![NameOfListBoxOnThatForm].ListCount - 1
If [Forms]![NameOfYourOtherOpenForm]![NameOfListBoxOnThatForm].ItemData(i) = SampleID Then
        [Forms]![NameOfYourOtherOpenForm]![NameOfListBoxOnThatForm].Selected(i) = True
End If
Next i

Still a bit early for the wine....but the day is flying by and I have some nice white chilling...
Jack
tpd15
Hi Jack,
tried your code, and still keep getting that "The expression you entered refers to an object this is closed of doesn't exist". But I used your "Stop" method to see where the issue was occurring. I just kept moving the Stop down the code until it faulted out. So here it is: it like the first major line
For i = 0 To [Forms]![NameOfYourOtherOpenForm]![NameOfListBoxOnThatForm].ListCount - 1
but failed when it got to the next one
If [Forms]![NameOfYourOtherOpenForm]![NameOfListBoxOnThatForm].ItemData(i) = SampleID Then
That the previous line works says to me the [Forms]![NameOfYourOtherOpenForm]![NameOfListBoxOnThatForm] bit is ok, so I'd think it's some other part of that line. SampleID? It's on both forms as a control name, and also the field bound to the control.
I'm kinda stumped. I can easily post the dbase if it helps.
Jack Cowley
In this line of code:
If [Forms]![NameOfYourOtherOpenForm]![NameOfListBoxOnThatForm].ItemData(i) = SampleID Then
I did you change "NameOfYourOtherOpenForm" and "NameOfListBoxOnThatForm" to the actual names of your form and your list box?
If not, do that then leave the Stop in your code but move it up before the For i = 0 line of code. When the code editor opens click the Step-Into icon on the toolbar to move through the code. When the If line is highlighted place the cursor over SampleID and a little window should open and tell you the value in this variable. That should help you track down the problem.
I will be leaving shortly and will not be back until about 2pm Pacific Time....
Jack
tpd15
Yep, I had the code with my names in it:
If [Forms]![frm_SampleDetails]![lstbox_SelectSample].ItemData(i) = SampleID Then
o I did like you described, by stepping thru the code. When I got to SampleID I hovered over it, and the tool-tip said:
SampleID=<the expression you entered refers....>
So I guess the issue is there. Will attempt to debug that, just wanted to let you know before you left that appears to be where the issue is.
tpd15
OK, my bad. I had moved the docmd.close up, and was closing the form so the sampleID variable was lost. Fixed that so that teh vble is getting picked up ok, but now I'm thinking the issue still lies with line:
If [Forms]![frm_SampleDetails]![lstbox_SelectSample].ItemData(i) = SampleID Then
This is because I placed all the code into a button on my main form (and updated the references into Me. format), then modified the code to match your first post for blanking the list box. That worked! Other than the third line ending with a True vs a False (which I tested for, and that didn't cause the problem), the only diff in the code if the If Then line. That's as far as I can get.
Jack Cowley
Toby -

I did you put a Stop in the code and then step through the code to see what value is in SampleID? Is SampleID the bound column in the subform?

At this point I am not sure what is going on so I have attached a small demo that does what I think you want to do and maybe that will help clear up a couple of thngs for you.

Oops! Forgot to tell you to open both forms and then enter a number between one and 8 in Form2 then click the button..

Jack
Edited by: Jack Cowley on Thu May 18 16:50:30 EDT 2006.
tpd15
Hi Jack
Ok, I have isolated things a bit. Your demo dbase helped greatly! I was baffled why, when I even copied the code from your demo, it didn't work for me. What I've come down to is how I'm getting the value for SampleID. You have an unbound text box (Text4) the value of which is then grabbed by the routine & used to highlight the row on the other form.
I have a continuous form, and am grabbing a value already displayed in the control txt_SampleID. I tried putting a msg box into my button's code to check I was grabbing the right value (button's OnClick = MsgBox (SampleID) ). This was good.
So I tried using a combo on your Form2 instead of the unbound text box. Even just set to pull one column of data, the method fails here!
I've modified your dbase with some different examples & posted it here. There are a bunch of modified forms, your orignal Form2 is unchanged, and I just copied it a few times to try different methods. See how the one with the bound controls in continuous-form mode fails (same situaiton as I have in my dbase). Same for the one with the combo.
Phew!
Jack Cowley
Very interesting! I am not sure what is going on now, but I am on the case...
ack
Jack Cowley
Toby -
Since you have a Continuous form I worked on that one first. In your form Form2-2 in the demo use this code in the On Click event of the command button:
CODE
Dim i As Integer
Dim a As String
a = Me.Text4
For i = 0 To [Forms]![Form1]![List27].ListCount - 1
If [Forms]![Form1]![List27].ItemData(i) = a Then
        [Forms]![Form1]![List27].Selected(i) = True
End If
Next i

This is working for me.... Let me know what you find...
Jack
tpd15
Hi Jack,
How this is just getting wierd. That didn't do anything for me. I inserted a msg box [ MsgBox (a) ] just to make sure that it was grabbing your "a" variable, which is twas correctly. It just doesn't do anyting with it....? Did it work for you?
Jack Cowley
Toby -
pen the form1 and form2-2 in the attached and see if it works for you as it is working for me...
Jack
tpd15
Sorry, it wouldn't open -- a later version than 2000?
jmcwk
Jack,
got the same result Unrecognized Format
Jack Cowley
Toby -
Sorry about that! Try the attached...
Jack
Jack Cowley
John -
On my haste I created the demo in 2002... A 2000 version has been posted...
Jack
tpd15
Hey, that works! And looking thru the code it's no different to what you've posted before, correct? I'm baffled...
jmcwk
Jack,
Just be getting to be that time of Day! Your Head is SPINNING and time for a vino
Jack Cowley
John -
My head is spinning as I am trying to juggle too many code things at once! A wine sounds great and it won't be long....
Jack
tpd15
Jack,
This is starting to wig me out. I even imported my 2-2 form into your dbase. It has the exact some code behind it and doesn't work! It's identical to the form you have (at least looks like it) but I can't get it to highlight back on Form1!
Jack Cowley
There is the added variable 'a' that is set to a string.
im i As Integer
Dim a As String
a = Me.Text4
For i = 0 To [Forms]![Form1]![List27].ListCount - 1
If [Forms]![Form1]![List27].ItemData(i) = a Then
[Forms]![Form1]![List27].Selected(i) = True
End If
Next i
Jack
Jack Cowley
Toby -
The form may be corrupt. Have you done a compact and repair? Try importing your forms into a new, blank db and see if that fixes the problem. If not, delete Form2-2 and try creating a new one from scratch. Also, look carefully at the code and make sure that it is line for line identical. Can could copy and paste the code from my demo and paste it into yours and see if that works.
Jack
tpd15
string ah -- ha ha.. [cue crazed, maniacal laughter]. Yep, that did it!
just went back and propogated that change into the other versions (the combo, etc..) and it worked. I had figured it had to be something fundamental. And I had meant to ask why in your first chunk of code you had declared a as Var but didn't use it (until later code versions).
Jack Cowley
Toby -
The 'a' variable was left over from some other code and I forgot to delete it!!! When I realized that a string worked but an integer didn't I 'borrowed' back the variable as a string and was back in business....
Jack
tpd15
Good detective work, sir. Looks like we may have this case solved! I'm at home so don't have the "Tests & Samples" dbase for which this all belongs, but will take a shot at incorporating it in the morning & let you know. Cheers!
Jack Cowley
Toby -
You are very welcome and thanks for sticking with me as I hacked and slashed my way through this!
Be sure and let us know if you hit any other walls crazy.gif
Jack
tpd15
Hi Jack
rolled the corrected code into my full app, and discovered that it doesn't play nice with the existing code to open the other form and show the correct record. If I have a button to do each task, they operate fine, ie: one button to open the other form and show the correct record, and a second button to then update the list box.
If works fine once, then every subsequent time (while the main form is still open) I get a msg saying "The OpenForm Action was cancelled", sort of logical, as the main form was already open. But I don't get this issue if the For..Next loop is excluded. Here's the total code in my button:
CODE
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim i As Integer
    Dim a As String
    a = Me.SampleID
    stDocName = "frm_SampleDetails"
    
    stLinkCriteria = "[SampleID]=" & Me![SampleID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
    For i = 0 To Forms!frm_SampleDetails!lstbox_SelectSample.ListCount - 1
    If Forms!frm_SampleDetails!lstbox_SelectSample.ItemData(i) = a Then
        Forms!frm_SampleDetails!lstbox_SelectSample.Selected(i) = True
    End If
    Next i
    DoCmd.Close acForm, "frm_sampletotest"
I can't then close the main form without incurring some strange "Update or CancelUpdate without AddNew or Edit" msg.
Oplaced a Stop into the code so that I could step thru it. The first run thru is goes fine. On the second total run thru, the error occurs at the "DoCmd.OpenForm stDocName, , , stLinkCriteria" line, ie: the code steps to that line, then immediately to the error handler.
Not too sure where to go from here as I've played with moving stuff around, selectively remming stuff out, etc..
Jack Cowley
Toby -
et me see if I have this right - You open form 'frm_SampleDetails' to a specific record. After the form is open you set the value of a list box on that form. Later you want to move to a different record on form 'fm_SampleDetails' from form 'frm_sampletotest' and set the list box again. Is that right? Would it be correct to say that each time that form 'frm_SampleDetails becomes the active form that you want a specific record shown and the same SampleID highlighted in the list box?
One more question - When you get the error on the DoCmd.OpenForm line of code is form 'frm_SampleDetails open?
Jack
tpd15
Hi Jack,
orrect on all counts. Seeing as a picture is worth a thousand words, etc.. I've attached the dbase, and stripped out any iffy data. When it opens, you're seeing the "main form". About half way down there is a little hyperlink to click to open the other form. On that 2nd form are 3 buttons off to the right side. The left-most one is supposed to be the real deal, but when I got issues I reconstructed is from scratch as the right-most button (which also has the Stop in its code). The button in the middle runs your code to highlight the list box only. Hope this helps clarify things.
Jack Cowley
Toby -
The db shuts down and wants to send a message to Microsoft when I try the code in either the first or third button. Not sure what is going on but it is certainly not working! I will fiddle with it for a while, but no guarantees I can sort out the problem...
Jack
tpd15
Thanks, that's interesting! I haven't had any shut-down problems.. Maybe you could import the objects into a new container?
Jack Cowley
Toby -
I am not sure what is going on, but it appears that the main form does not like being changed from the second form. I have tried a couple of bits of code and they both fail. What does work is to close the main form when you click the search button and then the code in the last button on the second form works fine. It may not be an elegant solution, but it does work and since the main form is hidden behind the second form no one is the wiser...
Will that work for you?
Jack
Jack Cowley
I got rid of the shut down problem but I could not find a solution other than shutting down the main form and reopening....
ack
tpd15
So do you think we're cooked? It can't be as bad as all that? :-)

edit>> ah, this is kinda strange. I just fired the dbase up, and.. it.. works.. fine.. ! I have no idea why, as I haven't changed anything, but the combined button (with the >>'s) just works across all the code. I tried importing everyting into a new container, and it's still working fine (attached). Huh?
Edited by: tpd15 on Fri May 19 14:07:49 EDT 2006.
Jack Cowley
Toby -
Is it a problem to close the form then reopen it from the other form? We may not be 'cooked', but unfortunately I do not have the time to try and sort out what needs to be changed to make the code work without closing the form... I admit to taking the easy way out, but it is possible it is the only way though I do NOT know that for a fact...
Jack
tpd15
Jack,
id you get to read the edit to my previous post? I only just put it up there...
BUT I isolated the problem. Further to my last edit, if you open the main form then go straight to the hyperlink & open the second form, you'll get the error.
but if you open the main form, click around in the list box a few times, then open the second form, no problems at all, ever!!
Jack Cowley
I did not get your edit, but it sounds like you may be on to something! I am pretty busy at the moment so I won't be able to 'follow along'. Good luck and let me know what you did to solve the problem!
ack
tpd15
Hi Jack,
My 23rd post of this thread (!!). I basically got the issue resolved, if not completely fixed. It hinged around what I'd discovered in my last post. By setting the focus to the listbox in the form's OnOpen event I somehow "activated" it enough that the problem couldn't occur. Why? It's a mystery to me!
Oalso had to remove your For..Next loop that was in the OnOpen event (the one that highlighted the correct row upon form opening, less the openargs bit). For some reason this interfered with the linking action such that the first time I tried to link back to the main form it wouldn't filter for the selected record, but any time after that it worked ok.
So I have the linking & highlighting from the pop-up working, but just not the highlighting upon first opening of the mainform, which is something I can live with. Thanks for all the help on this one, I think it may be a record for me...!
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.