How do I click on a subform to bring up the corresponding record?

[Nick Pedder]

uHi,

Was wondering if anyone could help me. I'm sure this is an easy question,
but am no expert on Access, so am somewhat stuck!

I have a simple database where there is a table of potential customers
linked to a table of contacts by a one to many relationship.

The corresponding contact details form is a subform contained within the
potential customers form. What I want to do is have a list of all the
potential customers on the left hand side of the main form which upon double
clicking, will bring up the relevant details in both the potential customer
form and the contact details form.

I've made a query listing all the customers, then based a continuos form on
this and inserted it as a subform into the potential customers, but I can't
see how to link the two together. Incidentally, if I play around with the
master and child fields, I can get it so the record on the potential
customers form brings up the correct record on the customer list subform.

Any help would be greatly apprecaited. Many thanks,

Nick

 

[Ken Sheridan]

Nick:

I'd suggest that you don't use a subform for the customers; instead use a
list box with the query as its RowSource and put the list box in the main
form. In the list box's DblClick event procedure put code along these lines:

Dim rst As Object

Set rst = Me.Recordset.Clone

rst.FindFirst "CustomerID = " & lstCustomers
Me.Bookmark = rst.Bookmark

where lstCustomers is the name of the list box and CustomerID is the numeric
primary key of the Customers table.

This code assumes that the bound column of the list box is CustomerID, so if
this field is not returned by your query change it and make it the first
column in the query's result set. You can hide the CustomerID column by
setting the ColumnWidths property of the list box. Say your query returns 2
columns:

SELECT CustomerID, CustomerName
FROM Customers
ORDER BY CustomerName;

You would set the BoundColumn property of the list box to 1, its ColumnCount
property to 2 and its ColumnWidths property to 0cm;8cm or rough equivalent in
inches. The important thing is that the first dimension is zero to hide the
CustomerID; the second dimension isn't crucial so long as is wide enough to
hold the data.

Ken Sheridan
Stafford, England

 

[Nick Pedder]

Thanks Ken. That looks like it might do the trick. I'll give it a go now.

 

[Nick Pedder]

Ok, so I'm almost certainly being stupid here, but I've tried Ken's
suggestion and it doesn't work!

The error message that comes up goes along the lines of....

The expression you entered as the event property setting produced the
following error: A problem occured while Access was communicated with OLE
server or ActiveX control.

The expression may not result of a macro, a user-defined function or event
procedure. There may have been an error evaluating the function, event or
macro

I used the wizard to create the list box, selecting 'find record on form
from value I select' (I'm not exactly conversant with Basic), but the code
looks pretty much the same as below, so I'm assuming it should work.

Would anyone be able to help me? Would be much appreciated!

Thanks,

Nick

 

[Ken Sheridan]

Nick:

Post back with the code from the event procedure. That way we'll be better
able to see if there is a problem with the code.

Ken Sheridan
Stafford, England

 

[Nick Pedder]

Hi Ken,

Here's the code from the event procedure.

Just to note, I've tried using a list box with a fresh database that I've
quickly knocked up using one table and a form, and it seems to work as it
should do.

As far as I can see, I'm not doing anything different....

Thanks,

Nick


Private Sub List146_DblClick(Cancel As Integer)

End Sub
Private Sub List148_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![List148], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub List150_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![List150], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Ken Sheridan]

Nick:

The code looks OK, so the problem may lie deeper. You'll fund a discussion
of the error in question at:

http://www.thescripts.com/forum/thread451336.html

Ken Sheridan
Stafford, England

Hi Ken,

Here's the code from the event procedure.

Just to note, I've tried using a list box with a fresh database that I've
quickly knocked up using one table and a form, and it seems to work as it
should do.

As far as I can see, I'm not doing anything different....

Thanks,

Nick


Private Sub List146_DblClick(Cancel As Integer)

End Sub
Private Sub List148_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![List148], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub List150_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![List150], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Nick:

Post back with the code from the event procedure. That way we'll be better
able to see if there is a problem with the code.

Ken Sheridan
Stafford, England