Listbox selection to open record

tonycl69

Registered User.
Local time
Today, 13:39
Joined
Nov 14, 2012
Messages
53
Hi guys and gals lol,
Newbie to the forum and world of access.
I have searched the forum for a solution and found one that works in part,
DoCmd.OpenForm "frmfleettruck", acNormal, , "[unitid]=" & Me.searchresults.Column(0)

This takes me from a list box on a double click event which id fine but how do I modify this so that once the frmfleettruck form opens it opens to the selected record but also allows me to view all other records too. At the moment i am taken to that one specific record and cannot see any other records.
Cheers:o
 
Look at "DemoSearchRecordA20022003.mdb" (attachment, zip).
Open form and try.
Adapt it in your mdb.
 

Attachments

Thanks for that but not what I am looking for. I have a list box and I can double click in the list box to open another form with the chosen record from the list box, but all I get is the single record in the main form, in order to see all records again i need to close the main form and reopen, I am looking for any code that would open the main form with the selected record from the list box but keep all records available in the main form.
 
You DoCmd.OpenForm is setting the Where statement to return only the record(s) that match the statement. That is why you are seeing only the one record.

You need to open the form with the all records, but then GoToRecord identified by your list box. Maybe you could pass the results of your list box to the form in the arguments section of the DoCmd.OpenForm statement, rather than the Where statement? Then you could use that to help identify where you need to go?

Might not be the best way, but you could get it to work.
 
Last edited:
Well if I understood correctly, what you want is to go to a particular record selected on double click of the item in the list box.. That would need you to open the form normally and use the DoCmd.GoToRecord method in the Form current.. So I am taking a stab here.. Say you have the list box, that has the ID, so you should use the form to open and pass the open arg to the form you are trying to open..
Code:
Private Sub List2_DblClick(Cancel As Integer)
    DoCmd.OpenForm [COLOR=Blue]yourFormName[/COLOR], _
            OpenArgs := Me.List2.Column(0, List2.ItemsSelected)
End Sub
Then in the Form current of the yourFormName,
Code:
Private Sub Form_Current()
    If Len(Me.OpenArgs & vbNullString) >0 Then
        Dim rs As Object
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[[COLOR=Blue]theIDField[/COLOR]] = " & Str(Nz(Me.OpenArgs, 0))
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If
End Sub
All blue bits need to change..

EDIT: BigHappyDaddy seems to have given you the solution as well.. :) I must be pretty slow there.. :D
 
Thanks that works brilliantly, however haha! I have another problem now.
On the main form I use a combo box to goto a record, but after going from my list box and opening the main form at the correct record the combo box tries to goto the record but keeps reverting back to the list box selection. Hope I making sense here. If I need to modify the combo box could you help with the coding cheers.
 
EDIT: BigHappyDaddy seems to have given you the solution as well.. :) I must be pretty slow there.. :D

Please! ;) You gave the solution and CODE! If I where to try to give the code, I would still be typing....:eek:... 2 hours later.
 
Yes I know. I did say it works perfectly aprt from it is still restricted to the record selected from the list box, so I can't go forward a record or backwards a record or select another record from a drop down combo box. I was just asking if there was something else I should be doing to still enable me to cyclew through the records once the main form is open.
 
Sorry tonycl69. My response above was directed to pr2-eugin. My bad.:o

I want to make sure I got this straight. You have a double click list box event on Form1 that will open Form2 and go to the record identified by the list box, but all records are visible.

Then you also have a combo box on Form2 that is also used to move from record to record in Form 2, but that part is not working.

Am I right so far?

When you open the combo box, you see all your record choices, but when you select another record, it doesn't move to the new record.

I haven't really used bookmarks before, but does Me.Bookmark need to be "released". I admit, I could be way off base here... :confused:
 
Yes that's about the size of it. If I dont use the list box then a combo box looks for a record on the form and takes me there, but as soon as I use the list box double click and it takes me to the record that is fine but then the combo box nor the record selectors work correclty, they try and goto another record but then immediatley go back to the record that was selcted by the list box as though that record is not released once selected.
 
Please! You gave the solution and CODE! If I where to try to give the code, I would still be typing....... 2 hours later.
Ha ha.. :D
Thanks that works brilliantly, however haha! I have another problem now.
On the main form I use a combo box to goto a record, but after going from my list box and opening the main form at the correct record the combo box tries to goto the record but keeps reverting back to the list box selection. Hope I making sense here. If I need to modify the combo box could you help with the coding cheers.
This is because the OpenArgs that you passed on still has the value "record ID".. In other words everytime you use the Combo Box to move to another record the Form Current is triggered.. Whihc means the vale of this statement..
Code:
If Len(Me.OpenArgs & vbNullString) >0 Then
will always be > 0.. Since an openArg is read only you cannot set it back to NULL by simply using
Code:
Me.OpenArgs = vbNullString
So now you have two options

OPTION - 1 : After opening the Form via another form, you have to close it, to set back the open args to NULL..
Or
OPTION - 2 : You can create a flag and set it to TRUE from inside the ComboBox.. so that you can add that to the form current If condition...

So, to create a Flag as a common variable.. under the Option Compare Database and Option Explicit.. use this...
Code:
Option Compare Database
Option Explicit
Dim comboFlag As Boolean    [COLOR=SeaGreen]'This will by default be False[/COLOR]
So your code inside Form Current would be..
Code:
Private Sub Form_Current()
    If ((Len(Me.OpenArgs & vbNullString) >0) AND (Not comboFlag)) Then
        Dim rs As Object
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[theIDField] = " & Str(Nz(Me.OpenArgs, 0))
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If
End Sub
And also make sure the comboBox that you use to travel to record has this line at the start..
Code:
comboFlag = True
Hope that makes sense..
 
Could you move this code from the Current Event to the Open Event?
Code:
Private Sub Form_Current()
    If Len(Me.OpenArgs & vbNullString) >0 Then
        Dim rs As Object
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[[COLOR=blue]theIDField[/COLOR]] = " & Str(Nz(Me.OpenArgs, 0))
        If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    End If
End Sub

I could be wrong, but I think the Form_Open event is triggered on the initial opening of the form. Where the current event can be triggered much more frequently while the form is still loaded.

Is there something wrong with my assumptions?
 
Could you move this code from the Current Event to the Open Event?
Well that is a very good choice I missed there BigHappyDaddy.. That indeed is a far more better solution..
Is there something wrong with my assumptions?
There is absolutely nothing wrong with that.. However the better method would be OnLoad rather than OnOpen.. As OnOpen is triggered before the RecorSet is loaded on to the form so the code might not work.. Also it can be cancelled but OnLoad cannot be cancelled and also is triggered after the Form is properly loaded with the data..
 
However the better method would be OnLoad rather than OnOpen..
I admit, I thought that OnLoad occurred before OnOpen. I double-checked it after I read your post. :o

Dang, I feel really silly now. I little bit for the suggestion above, but more so for all the 'extra' code I have written for my own projects when OnOpen didn't work like I expected. :banghead:

Thanks for putting me back on the straight and narrow! :D
 
I admit, I thought that OnLoad occurred before OnOpen. I double-checked it after I read your post. :o

Dang, I feel really silly now. I little bit for the suggestion above, but more so for all the 'extra' code I have written for my own projects when OnOpen didn't work like I expected. :banghead:

Thanks for putting me back on the straight and narrow! :D

Nothing to feel silly about now.. We learn something new everyday.. Glad I could help.. :)
 
Hi guys and many thanks for your help we got there in the end I tried the comboflag option in the current and still had the same problem then moved it to the onload and everything works perfectly. I am self taught, I guess llike most. So any help is much appreciated. The database I have is for vehicle maintenance, I started the database 4 years ago when I started here and now time to rewrite, and I must say is looking pretty slick, however because I have several queries running at startup, it does seem an age to get to the main form. Any suggestions how to speed this up or being doing something else on screen so it doesnt seem so long.
 
Queries at start up are not really good.. They use up more resource and make the loading real slow.. The real question is do they need to run at start up? Why not run them after the main form is loaded and you are good to go?
 
Thanks for your thoughts, in some respect I could do what you have suggested, but they do need to run at startup because they are the reminders for out of date inspections etc and those due in the next month.
 
Well it all depends on how you wish the queries to give them.. What you can do is have them under buttons, and since it will a job description they have to run those at some point of the day. Or you can run the Queries after the form has loaded? Or you can even make it run at a specific time of the day?
 
I prefer to have them displayed at the startup then something can be done about them there and then, the likelyhood is the user will never go and check at least this way they will see what is due and what has to be chased up.
 

Users who are viewing this thread

Back
Top Bottom