Double Click Listbox

[HelpDesk]

I have a listbox in a form that I populate with search results. This listbox has a total of 3 columns. I would like to be able to double click a result in that listbox and edit the record using another form that will populate 4 textboxes (I do mean 4, the master record list has 4 columns) with the data from the record selected. I have the edit record form ready I just can't seem to link them together.



I found this basic code online and have tried other stuff but I just keep getting this error.



Here is my event procedure code:

Private Sub List0_DblClick(Cancel As Integer)
    DoCmd.OpenForm "EditRecordForm", , , "[Device ID]=" & Me.List0.Column(0)
End Sub

Device ID is the column heading.

List0 is the listbox I am trying to pull from.

Any ideas?

[June7]

Why are you duplicating data? Why do you need to save all 4 values? Why not just save record ID?

I just tested listbox DblClick event and it worked perfect.

Is [Device ID] a text field? Use apostrophe delimiters.

"[Device ID]='" & Me.List0 & "'"

[HelpDesk]

Why are you duplicating data? Why do you need to save all 4 values? Why not just save record ID?

I just tested listbox DblClick event and it worked perfect.

Is [Device ID] a text field? Use apostrophe delimiters.

"[Device ID]='" & Me.List0 & "'"

So the way I have my current setup is as follows. Two listboxes in a 'Results' form are populated by two temp table created by a function when running the initial search. All the data that gets populated in the two temp tables is taken from a master 'Devices' Table. I want to be able to edit values in the master Device table by double clicking a value in one of the list boxes and opening up the subsequent record from the master Devices table. I have an 'EditRecordForm' that can open records from the master table and I want it to pull up the record to edit using the record ID from the value you just double clicked in the listbox.

[ItsMe]

...I have an 'EditRecordForm' that can open records from the master table and I want it to pull up the record to edit using the record ID from the value you just double clicked in the listbox....

The reason you are getting the error may have to do with a control on your 'EditRecordForm'. Are you able to open your 'EditRecordForm' in form view by double clicking it?

[HelpDesk]

The reason you are getting the error may have to do with a control on your 'EditRecordForm'. Are you able to open your 'EditRecordForm' in form view by double clicking it?

I can open the 'EditRecordForm' by double clicking on it in the sidebar but not from the listbox.

[ItsMe]

Like June mentioned, there may be a better way to approach the bigger task at hand. As for the error, I would trouble shoot your DoCmd statement. Perhaps you can create another form and place a single control button on it.

Hard code something like this
DoCmd.OpenForm "EditRecordForm", , , "[Device ID]= 30"

Where 30 represents the ID number for the record you were previously trying to open via the list box. Perhaps there is an issue with an OLE field in the table the other form is bound to. It may be specific to a single record or a few records (not the first record).

Another test would be to see what the value is of your listbox's bound column. On your original form, you can comment out the other code and then place a msgbox
MsgBox Me.List0.Column(0)

[HelpDesk]

Like June mentioned, there may be a better way to approach the bigger task at hand. As for the error, I would trouble shoot your DoCmd statement. Perhaps you can create another form and place a single control button on it.

Hard code something like this
DoCmd.OpenForm "EditRecordForm", , , "[Device ID]= 30"

Where 30 represents the ID number for the record you were previously trying to open via the list box. Perhaps there is an issue with an OLE field in the table the other form is bound to. It may be specific to a single record or a few records (not the first record).

Another test would be to see what the value is of your listbox's bound column. On your original form, you can comment out the other code and then place a msgbox
MsgBox Me.List0.Column(0)

The biggest problem I have right now is trying to get the double click action to work, no matter what code I put into the event procedure for double click that error in my first post pops up. Won't even bring up a msgbox just that error. Any idea what throws that error?

[ItsMe]

...no matter what code I put into the event procedure for double click that error in my first post pops up...

I offered you some tips on how to troubleshoot errors. You need to isolate the error. Saying, "no matter what code" is not offering much insight. You need to approach it methodically and then detail the results for each approach. If you do not desire to try the exercises I proposed, state so.

[HelpDesk]

I offered you some tips on how to troubleshoot errors. You need to isolate the error. Saying, "no matter what code" is not offering much insight. You need to approach it methodically and then detail the results for each approach. If you do not desire to try the exercises I proposed, state so.

Ok so what I ended up doing was deleting the original form and recreating it from scratch. This allowed the double click event to work when running the following code:

MsgBox Me.List0.Column(0)

This pulls up a message box with the value in the first column that I have selected.

I then used the original code with June7 suggestion to use Apostrophe delimiters and it worked, the EditRecordForm comes up with the data selected from the listbox.

Weird how I needed to create a new form, I wonder what caused the old one to throw that error.

[ItsMe]

It may have been your listbox was corrupt. However, if your ID was of type text, this would cause an error also. It may have been two issues. It was probably only one issue, though.

This is why it is important to isolate as you trouble shoot. It is the only way to identify the problem. Now you will always wonder what the problem was and it will be difficult to avoid the problem in the future.

[June7]

Why temp tables?

Sometimes corruption will occur and cause will never be known.

[HelpDesk]

Why temp tables?

Sometimes corruption will occur and cause will never be known.

I have temp tables because that is the only way to organize the search data. If you look at my post about Electrical Hierarchy you can see why I am using temp tables.

And I guess it was just corrupted...

Thanks for your help guys!

[June7]

Okay, that does sound familiar. But don't create the temp tables every time procedure is run. The tables are permanent but records are temporary. Just delete records when the procedure ends as preparation for next run.