Open form based on list box selection

[Guest]

Is there a way to do this? I have a list box in one form that displays all
linked records from another table. Can I double click the list box, have the
other form open and go to the selected record? I can do it now but the result
filters for my selection. I need to be able to move between records in the
form. Do I use bookmarks?

 

[Allen Browne]

Yes. Bookmarks would be good.

This example assumes the list box shows clients, and its Bound column is the
ClientID autonumber field:

Private Sub lstClientID.DblClick(Cancel As Integer)
Dim frm As Form
Dim strWhere As String

strWhere = "[ClientID] = " & Me.lstClientID
Set frm = Forms![YourOtherFormNameHere]
With frm.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found. Is the form filtered?"
Else
frm.Bookmark = .Bookmark
End If
End With
End Sub

 

[Guest]

Thank you for your response Allen.
I put the code in:
Private Sub Listbox_Linked_Interface_DblClick(Cancel As Integer)
Dim frm As Form
Dim strWhere As String

strWhere = "[ID] = " & Me.Listbox_Linked_Interface
Set frm = Forms![Form_Interfaces]
With frm.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found. Is the form filtered?"
Else
frm.Bookmark = .Bookmark
End If
End With



End Sub

But I am getting a run time error of a missing operator in expression. When
I debug the '.FindFirst strWhere' line is selected. You have to excuse me but
I am still relatively unfamiliar with code. Cheers!

 

[Allen Browne]

The code assumes that:
a) this is not a multi-select list box;
b) the bound column of the list box is a number field;
c) the number field is named ID;
d) some value is chosen when you double-click the list box.

In form design, right-click the list box and choose Properties.
On the Data tab of the Properties box, is the Bound Column property 1?
What is the RowSource? Which is the first field in this table/query?

Change the "ID" if the field has a different name.
Add extra quotes if the field is a Text type (not a number type), e.g.:
strWhere = "[ID] = """ & Me.Listbox_Linked_Interface & """"

 

[Guest]

Sorry to keep bothering you Allen, but the help ia appreciated. I am still
havein g alittle trouble I'm afraid. The listbox in my form does display the
data I need, the bound columns are numbers and everything seems to be ok. But
when I debug the code, on hovering over the me.List_Linked_Interface text
Access tells me that the value is null. But I can see data in the box. I have
this box requeried and repainted on the form oncurrent event if that has
anything to do with it.

The code assumes that:
a) this is not a multi-select list box;
b) the bound column of the list box is a number field;
c) the number field is named ID;
d) some value is chosen when you double-click the list box.

In form design, right-click the list box and choose Properties.
On the Data tab of the Properties box, is the Bound Column property 1?
What is the RowSource? Which is the first field in this table/query?

Change the "ID" if the field has a different name.
Add extra quotes if the field is a Text type (not a number type), e.g.:
strWhere = "[ID] = """ & Me.Listbox_Linked_Interface & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your response Allen.
I put the code in:
Private Sub Listbox_Linked_Interface_DblClick(Cancel As Integer)
Dim frm As Form
Dim strWhere As String

strWhere = "[ID] = " & Me.Listbox_Linked_Interface
Set frm = Forms![Form_Interfaces]
With frm.RecordsetClone
.FindFirst strWhere
If .NoMatch Then
MsgBox "Not found. Is the form filtered?"
Else
frm.Bookmark = .Bookmark
End If
End With



End Sub

But I am getting a run time error of a missing operator in expression.
When
I debug the '.FindFirst strWhere' line is selected. You have to excuse me
but
I am still relatively unfamiliar with code. Cheers!

 

[Guest]

here is the SQL for the list box.
SELECT Link_Table_Interface_DE.DE_ID, Interfaces.Int_Short_Name FROM
Link_Table_Interface_DE INNER JOIN Interfaces ON
Link_Table_Interface_DE.Interface_ID=Interfaces.ID GROUP BY
Link_Table_Interface_DE.DE_ID, Interfaces.Int_Short_Name HAVING
(((Link_Table_Interface_DE.DE_ID)=forms!Data_Elements!ID));

 

[Allen Browne]

Make sure the MultiSelect property of the list box is turned off (on the
Other tab of the Properties box.)

Add the line:
Debug.Print strWhere
to the code ahead of the FindFirst line.
It should look like the WHERE clause of a query, e.g.:
[DE_ID] = 999

When the code fails, you should now see that printed in the Immediate Window
(Ctrl+G)

 

[Guest]

I did that and it tells me that the list box is showing a null value. I
looked at it and when I try to click on a selection it doesn't turn black.
What can I do to make the listbox able to take a selection? Thanks Allen.

Make sure the MultiSelect property of the list box is turned off (on the
Other tab of the Properties box.)

Add the line:
Debug.Print strWhere
to the code ahead of the FindFirst line.
It should look like the WHERE clause of a query, e.g.:
[DE_ID] = 999

When the code fails, you should now see that printed in the Immediate Window
(Ctrl+G)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

here is the SQL for the list box.
SELECT Link_Table_Interface_DE.DE_ID, Interfaces.Int_Short_Name FROM
Link_Table_Interface_DE INNER JOIN Interfaces ON
Link_Table_Interface_DE.Interface_ID=Interfaces.ID GROUP BY
Link_Table_Interface_DE.DE_ID, Interfaces.Int_Short_Name HAVING
(((Link_Table_Interface_DE.DE_ID)=forms!Data_Elements!ID));

 

[Allen Browne]

Some of the things that might stop you selecting a value in a list box:

1. The form's AllowEdits property is set to No.

2. The list box's Locked property is Yes, or Enabled is No.

3. The list box is bound to an expression (not a field).

4. The list box is bound to a calculated field.

5. The form's RecordSource is read-only (e.g. a non-editable query,
inadequate permissions, or from read-only network share or media.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I did that and it tells me that the list box is showing a null value. I
looked at it and when I try to click on a selection it doesn't turn black.
What can I do to make the listbox able to take a selection? Thanks Allen.

Make sure the MultiSelect property of the list box is turned off (on the
Other tab of the Properties box.)

Add the line:
Debug.Print strWhere
to the code ahead of the FindFirst line.
It should look like the WHERE clause of a query, e.g.:
[DE_ID] = 999

When the code fails, you should now see that printed in the Immediate
Window
(Ctrl+G)

here is the SQL for the list box.
SELECT Link_Table_Interface_DE.DE_ID, Interfaces.Int_Short_Name FROM
Link_Table_Interface_DE INNER JOIN Interfaces ON
Link_Table_Interface_DE.Interface_ID=Interfaces.ID GROUP BY
Link_Table_Interface_DE.DE_ID, Interfaces.Int_Short_Name HAVING
(((Link_Table_Interface_DE.DE_ID)=forms!Data_Elements!ID));

 

[Guest]

Form edits was set to no. You beauty Allen! Works a treat. Thanks very much
mate.

Some of the things that might stop you selecting a value in a list box:

1. The form's AllowEdits property is set to No.

2. The list box's Locked property is Yes, or Enabled is No.

3. The list box is bound to an expression (not a field).

4. The list box is bound to a calculated field.

5. The form's RecordSource is read-only (e.g. a non-editable query,
inadequate permissions, or from read-only network share or media.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I did that and it tells me that the list box is showing a null value. I
looked at it and when I try to click on a selection it doesn't turn black.
What can I do to make the listbox able to take a selection? Thanks Allen.

Make sure the MultiSelect property of the list box is turned off (on the
Other tab of the Properties box.)

Add the line:
Debug.Print strWhere
to the code ahead of the FindFirst line.
It should look like the WHERE clause of a query, e.g.:
[DE_ID] = 999

When the code fails, you should now see that printed in the Immediate
Window
(Ctrl+G)


here is the SQL for the list box.
SELECT Link_Table_Interface_DE.DE_ID, Interfaces.Int_Short_Name FROM
Link_Table_Interface_DE INNER JOIN Interfaces ON
Link_Table_Interface_DE.Interface_ID=Interfaces.ID GROUP BY
Link_Table_Interface_DE.DE_ID, Interfaces.Int_Short_Name HAVING
(((Link_Table_Interface_DE.DE_ID)=forms!Data_Elements!ID));