Solved

The specified field could refer to more than one table listed in the FROM clause of your SQL statement

Posted on 2006-10-25
2
548 Views
Last Modified: 2012-05-05
I get the message above when I try to open a form using a control button on another form.  The form being opened is frmTrainee and its query's SQL looks like this:

SELECT tblTrainees.StaffTrainingID, tblTrainees.StaffID, tblStaff.StaffLastName, tblStaff.StaffFirstName, tblTrainees.TrainingID, tblTrainees.TTBookedBy, tblTrainees.StatusID, tblTrainees.TTBooked, tblTrainees.TTBeforeLetter, tblTrainees.TTAfterLetter, tblTrainees.TTAssessment, tblTrainees.TTAssessmentReminder, tblTrainees.TTAssessmentReceived, tblTrainees.TTAssessmentNotes, tblTrainees.TTCertificate, tblTrainees.TTUpdateReminder, tblTrainees.TTUpdateBooked, tblTrainees.TTRef, tblTrainees.FunderID, tblTrainees.TTFundingAgreed, tblTrainees.TTFundingAmount, tblTrainees.TTInvoiceAmount, tblTrainees.TTFundingInvoicePay, tblTrainees.TTFundingInvoiceSent, tblTrainees.TTFundingPaid, tblTrainees.TTFundingNotes, tblTrainees.TTNotes
FROM tblStaff RIGHT JOIN (tblTrainees LEFT JOIN tblTraining ON tblTrainees.TrainingID = tblTraining.TrainingID) ON tblStaff.StaffID = tblTrainees.StaffID
ORDER BY tblStaff.StaffLastName, tblStaff.StaffFirstName, tblTraining.TrainingDate DESC;

The code for the control button that opens frmTrainee is:

    Dim stDocName As String
    Dim stLinkCriteria As String

  stDocName = "frmTrainee"

  'Open training records for this staff member
 If DCount("*", "tblTrainees", "[StaffID]=" & Me![StaffID]) <> 0 Then
         stLinkCriteria = "[StaffID]=" & Me![StaffID]
         DoCmd.OpenForm stDocName, , , stLinkCriteria
 
 'If there are none then create a new training record for this staff member
   Else
         DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
         Forms!frmTrainee!StaffID = Me![StaffID]
    End If

The same form also has a control button that opens frmTraineeProgramme (which has similar code to the above except that it says frmTraineeProgramme and tblTraineeProgramme in the relevant places) but that one works fine.  I'm puzzled.  Any ideas?

0
Comment
Question by:Ludique
  • 2
2 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 17809007
Hi Ludique,
>          stLinkCriteria = "[StaffID]=" & Me![StaffID]
Try changing this line to:

         stLinkCriteria = "tblTrainees.StaffID=" & Me![StaffID]


0
 
LVL 61

Expert Comment

by:mbizup
ID: 17809025
Glad to help ;-)
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question