Display all company details after double click in list view.

[freddiel]

I have been asked to fix an issue after converting data from Access 2000 to 2007. It is only in one Form the problem exists.

The task required is to go from a list view of clients to a detailed view of one single record in the list by double clicking the record.

Should be simple and it works in 5 screens, but not in the main screen.

I have copied the code below (seems like a bit too much duplication to achieve this...): The code below DOES NOT work and always brings the same record up. Not the 1st or last record, but record number 7 with ID=1453

Option Compare Database
Private Sub Jump_To_Detail()
Dim inLink As Integer
inLink = [ID]
DoCmd.OpenForm "Client Detail"
Forms![Client Detail]![ID].SetFocus
DoCmd.FindRecord inLink
Forms![Client Detail]![Name].SetFocus
End Sub
Private Sub ExitButton_Click()
DoCmd.Close
End Sub
Private Sub Address_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Email_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Fax_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub ID_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Name_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Notes_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Phone_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Postcode_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Website_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub



An identical approach in another screen works great. Code that works below. I cannot see any real difference and believe it is a bit unexplainable why the first code stopped working after moving to Access 2007.

Option Compare Database
Private Sub Jump_To_Detail()
Dim inLink As Integer
inLink = [ID]
DoCmd.OpenForm "Enquiry Detail"
[Forms]![Enquiry Detail]![ID].SetFocus
DoCmd.FindRecord inLink
[Forms]![Enquiry Detail]![ClientID].SetFocus
End Sub
Private Sub Client_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Project_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Contact_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Date_Chased_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Date_Letter_Sent_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Enquiry_Date_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Notes_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Origin_of_Enquiry_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub
Private Sub Site_Address_DblClick(Cancel As Integer)
Call Jump_To_Detail
End Sub



Any pointers on how to either rewrite this code or fix it would be appreciated. I expect a FindRecord is failing due to a wrong index or something.

I was much better at faultfinding in FoxPro using DOS....

Thanks.

 

[Thinh]

Please double check you [ID]. This could be confusing since there are two instance of it. Try the following code to open the form. Look up the Docmd.OpenForm it has other parameters that may be useful in your situation.

Private Sub Jump_To_Detail()
DoCmd.OpenForm "Enquiry Detail", acNormal, , "ID=" & ID
End Sub

 

[freddiel]

Thanks for the reply. I just tested your simplified code and it still does not work for Client Details, but it works for the other entire identical pick list. I guess what this is telling me is that it is using the wrong value for ID.
When working with FoxPro we had some simple but effective trace tools to see values of a specific database field as well as the active database. I have tried this with Access 2007 using Watch Expression, but seem to not get the expect result.

What is the best/simplest way to debug for an Access Novice?

 

[freddiel]

I finally cracked this. I guess I did not expect the application to have been written by a moron and therefore expected the fault to have been introduced by the 2003/7 conversion. Oh, no it was something as basic as the original coder never setting a proper sort order on the data.

Due to my inexperience in debugging newer applications (i.e. not FoxPro DOS or Win 3.11/95/98...) it took me many frustrating hours to fix something that was just the sort order. When on the phone and discussing the issue with a friend I happened to right click the “Client” field and change the sort order (or actually sorting it), it then started to work. I guess seeking for an ID in the data works best when the data is actually sorted (or indexed)

God bless my lack of knowledge of Access 2007 and the idiot who first programmed it....
Rule 1. Never assume anything.