Whats wrong with my code

[chrisd49]

Hi All
I have some code which allows me to click on a contract no and then display the details for that no. It works perfectly for the first number but displays the first nuber details for all subsequent numbers.
Here is the code I'm using

Private Sub ContractNo_DblClick(Cancel As Integer)
On Error GoTo Err_ContractNo_DblClick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmGeneralInfo"

If IsNull(Me.ContractNo) Or Me.ContractNo = "" Then
MsgBox "This record is empty", vbInformation, "No Data"
Me.ContractNo.SetFocus
Else
stLinkCriteria = "[ContractNo]=" & Me![ContractNo]
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_ContractNo_DblClick:
Exit Sub

Err_ContractNo_DblClick:
MsgBox Err.Description
Resume Exit_ContractNo_DblClick
End Sub

frmGeneralInfo has 6 tabbed forms to display all the relevant data. All the forms are unbound.
Any help much appreciated.
ChrisD

 

[whatever]

Just a guess but if your contact number field is a sting then you need to include single quotes to either side of the contactNo field:

stLinkCriteria = "[ContractNo]='" & Me![ContractNo] & "'"

HTH


kevin

 

[chrisd49]

Hi Whatever
Thanks for the suggestion but I've added your suggestion and I still get the same result. Every contract number only brings up the first site record.
Thanks
Chrisd

 

[missinglinq]

stDocName = "frmGeneralInfo"
......
stLinkCriteria = "[ContractNo]=" & Me![ContractNo]
DoCmd.OpenForm stDocName, , , stLinkCriteria
......
frmGeneralInfo has 6 tabbed forms... All the forms are unbound.

I'm not sure I understand your post completely, but if frmGeneralInfo is, indeed, an unbound form, then I think I'm right in saying that there's no way that DoCmd.OpenForm stDocName, , , stLinkCriteria can work! You're asking frmGeneralInfo to retrieve its record that has a matching ContractNo to the one in your calling form, and being unbound, frmGeneralInfo has no records to retrieve!

 

[chrisd49]

Hi All
I have now changed my form so I only have a main form but with the same fields as before. These fields are all generated from a query to display all the records for an over view of the sites in the tables.
I have also changed the code slightly but now I get an error message when I double click on the Contract No field to generate the next form view.
Code below:

Private Sub ContractNo_DblClick(Cancel As Integer)
On Error GoTo Err_ContractNo_DblClick

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmGeneralInfo"

If IsNull(Me.ContractNo) Or Me.ContractNo = "" Then
MsgBox "This record is empty", vbInformation, "No Data"
Me.ContractNo.SetFocus
Else
stLinkCriteria = "[ContractNo]=" & Me![ContractNo] & ""
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

Exit_ContractNo_DblClick:
Exit Sub

Err_ContractNo_DblClick:
MsgBox Err.Description
Resume Exit_ContractNo_DblClick
End Sub

The error message: A problem occurred while communicating with the OLE Server or ActiveX control.

Any ideas much appreciated as this is beginning to drive me crazy.

ChrisD

 

[chrisd49]

Whats wrong with my Code

I have attached my DB incase anyone wants to have a look and see whats wrong
ChrisD

 

[Wiz47]

I have attached my DB incase anyone wants to have a look and see whats wrong
ChrisD

Private Sub List20_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.List20
    Forms!vendors1.Bookmark = rst.Bookmark

Exit_vendorlist_DblClick:
    Exit Sub

Err_vendorList_DblClick:
    MsgBox Err.Description
    Resume Exit_vendorlist_DblClick
End Sub

I use a slightly different method to go to a specific record in a table. On doubleclick (from a listbox) in the above case, I open a new form that displays the records, but goes to the specific one I clicked on in the listbox. I've set the criteria in the SQL to the PK for the table. [ContactID]

 

[Wiz47]

I tweaked the db a bit to show you how this would work using a simple listbox. When you doubleclick the highlighted line, it will open up the form and go to that specific record.

 

[chrisd49]

Hi Wiz47
Thanks very much for your help on this. I've looked at what you suggest and have finally managed to incorporate it into my DB. It works a treat and my client is happy, so I'm happy and the wife's happy (I'm still getting paid).
ChrisD

 

[Wiz47]

Hi Wiz47
Thanks very much for your help on this. I've looked at what you suggest and have finally managed to incorporate it into my DB. It works a treat and my client is happy, so I'm happy and the wife's happy (I'm still getting paid).
ChrisD

And that my friend is the most important thing. Glad I could help.