Error Message using SQL Insert

[Superman]

Hello

Hans kindly wrote some code a while back, and it's been working all right up until recently. It's currently producing an error message, although the code still actually executes, so this is more of an annoyance than anything else.
Would anyone know whether it's just that Access needs a Compact & Repair, or is there a simple workaround?

The Code is as follows:

Code: Select all

Private Sub cmdAdd_Click()
Dim lngGraduateID As Long
    Dim sql As String
    Dim stDocName As String
    Dim stLinkCriteria As String
    On Error GoTo sbCreateReviews_Error
    ' ***** Save the record if necessary *****
    If Me.Dirty Then Me.Dirty = False
    sql = "INSERT INTO tblGAP (GraduateID, GAPstaff_FK, LC, ReferralDate) Values (" & GraduateID & _
        ", " & GAPstaff_FK & ", " & LC & ", #" & Format(ReferralDate, "yyyy/mm/dd") & "#)"
    CurrentDb.Execute sql
    stDocName = "frmGAP"
    stLinkCriteria = "[GraduateID]=" & Me![GraduateID]
    DoEvents
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Exit Sub
sbCreateReviews_Error:
    If Err.Number = 3022 Then
    Resume Next
    Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Add Graduate "
    End If
End Sub

And the message is "Error 3079 (The specified field (GraduateID) could refer to more than one table listed in the FROM clause of your SQL Statement) in procedure Add Graduate.

A normal select Query would look like this, where the 2 Tables are joined:

SELECT tblGraduate.GraduateID, tblGAP.GraduateID
FROM tblGraduate INNER JOIN tblGAP ON tblGraduate.GraduateID = tblGAP.GraduateID;

If there's a quick fix, eg, just inserting the Table name before the field, that'd be great.

Thanks in advance for any help.

[HansV]

Try temporarily disabling the line

Code: Select all

    On Error GoTo sbCreateReviews_Error

by inserting an apostrophe ' in front of it; then see which line causes the error. I suspect it is the DoCmd.OpenForm line, but I'd like to know for sure.

[Superman]

It stops at "DoCmd.OpenForm stDocName, , , stLinkCriteria"

[HansV]

Thanks. Have you changed the Record Source of frmGAP? The error message indicates that the field name GraduateID occurs more than once in this record source. You could get around this by adding the table name in the line

Code: Select all

    stLinkCriteria = "[GraduateID]=" & Me![GraduateID]

It would become something like

Code: Select all

    stLinkCriteria = "[NameOfTable].[GraduateID]=" & Me![GraduateID]

where NameOfTable is the name of the appropriate table.

[Superman]

Thanks Hans. I thought that was the simplest fix, but I know you're the expert, so I think it's always best to ask you first!

As always, thanks for your help & advice.
Have a great weekend.

[HansV]

You can now remove the apostrophe from the line

Code: Select all

    On Error GoTo sbCreateReviews_Error