Open form to selected record from listbox

[ScrmingWhisprs]

Yet another question.

I have a listbox on a form that show all Files for a particular volunteer. I have an "Edit File" button below the listbox to be able to edit that particular file. Is there a way about opening that form to that specific record without using a query?

Thanks
ScrmingWhisprs

 

[Wiz47]

Yet another question.

I have a listbox on a form that show all Files for a particular volunteer. I have an "Edit File" button below the listbox to be able to edit that particular file. Is there a way about opening that form to that specific record in without using a query?

Thanks
ScrmingWhisprs

Private Sub VendorList_DblClick(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo Err_vendorList_DblClick

DoCmd.OpenForm "Vendors1"

Set rst = Forms!vendors1.Recordset.Clone

rst.FindFirst "[ContactID] = " & Me.VendorList
Forms!vendors1.Bookmark = rst.Bookmark

Exit_vendorlist_DblClick:
Exit Sub

Err_vendorList_DblClick:
MsgBox Err.Description
Resume Exit_vendorlist_DblClick

End Sub

Use this code as an ON dblclick EVENT for the Listbox. When you double click on that record in the listbox, it will open up the edit form to that specific record.

Change the appropriate parts for your specific db, fields, and forms.

 

[boblarson]

There's an even easier way (with only one line of code):

DoCmd.OpenForm "Vendors1", acNormal, , "[ContactID] = " & Me.VendorList

Remember to change the form name, field name, and controlname to your own.

 

[Wiz47]

There's an even easier way (with only one line of code):

DoCmd.OpenForm "Vendors1", acNormal, , "[ContactID] = " & Me.VendorList

Remember to change the form name, field name, and controlname to your own.

Yep - nice efficient code there.

Question - We always want to have an error handler though, right?

 

[boblarson]

You don't ALWAYS need one. There are some events that it just doesn't make sense to have one on (Pat Hartman's commented on that before). But, for something that has the possibility of failure, then yes. This could be one of those cases as the id may not be in the recordset opened by the form. I tend to not specify the error handler in my code suggestions here to simplify matters as we all have different preferences in the way we write the error handler.

 

[pbaldy]

I'll point out that those 2 methods will do different things. The first will open the form with all records available, but with the selected record displayed. The second method will open the form with only the selected record available. In other words, a filtered vs. unfiltered view. One or the other may be appropriate, depending on the overall goal.

 

[boblarson]

Actually, since I haven't used it all that much, I forgot about that. Thanks pbaldy - you come through again where I misstep.

 

[pbaldy]

I certainly wouldn't call it a misstep. I usually use your method and have recommended it to others, only to have them say "but I want to be able to get to other records". Thus I just wanted to clarify that they do slightly different things. In any case, teamwork is better anyway!

 

[boblarson]

Well, in this case I do call it a "misstep" (not a mistake as it is a valid way of doing something) but a misstep, nonetheless, as I suggested it as a direct substitution when it is not. But, thanks again -

At least we have plenty of varied experience here so if one person says something off, someone will usually catch it. As you say, teamwork is better anyway.

 

[Wiz47]

You don't ALWAYS need one. There are some events that it just doesn't make sense to have one on (Pat Hartman's commented on that before). But, for something that has the possibility of failure, then yes. This could be one of those cases as the id may not be in the recordset opened by the form. I tend to not specify the error handler in my code suggestions here to simplify matters as we all have different preferences in the way we write the error handler.

I'm new to Access, but am one of those "old fogies" who programmed in C++, Pascal, and Basic back in the day. I always included error handlers for my functions and subs, but I can certainly see your point that they aren't always necessary - and your reasoning for not including them here in examples also makes sense.

 

[ScrmingWhisprs]

I don't know if this has anything to do with an error handling, but one bug that I have just found is if the Edit File button is clicked when there is nothing selected in the listbox, or there is nothing in the listbox, a runtime error box comes up with the options to end or debug. Is there a way to make a msgbox pop up saying something like "You must make selection." then cancel that event so the runtime error box doesn't show up?

 

[boblarson]

Yes,

If IsNull(YourListBoxNameHere) Then
    MsgBox "You must make a selection before...etc.", vbExclamation, "Selection Error!"
    Exit Sub
End If

Put this at the top of the Edit File Button code.

 

[lemieux66c]

There's an even easier way (with only one line of code):

DoCmd.OpenForm "Vendors1", acNormal, , "[ContactID] = " & Me.VendorList

Remember to change the form name, field name, and controlname to your own.

Hello,

I believe I can use this (or something similar) to fix a problem I'm having and which I mentioned in my post yesterday, "Open form to new record, etc". I'm trying to make it so that only the selected record is available to edit. When I was trying to implement it in my database it leaves me with a blank form...so I'm not sure if I was putting the code in the wrong place, was supposed to delete a line of code somewhere, named something incorrectly, or even some other reason I can't think of. If you guys have a few minutes, could you take a look at the database that I uploaded to my thread? Using Wiz47's updates might be best.

Thanks everyone.