Code has stopped working

Superman
Lounger
Posts: 32
Joined: 24 Jun 2010, 17:29

Code has stopped working

Post by Superman »

Hello

Hans kindly gave me some code to use on my Access Database, but the SQL element stopped working today. :(
The User creates a form and then clicks a button, which fires the Event.

The Event is supposed to insert the values of the form that's just been completed into the Database and then open a different new Form that contains the values that have just been inserted.

The Code does include an Error option, but no error comes up. All that happens is that the New Form opens, but it's blank as no data has been inserted.

Therefore, it looks like the SQL element is no longer working, but the rest of the code is.

Any thoughts why this would have just stopped?
Thanks for any help.

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

Superman
Lounger
Posts: 32
Joined: 24 Jun 2010, 17:29

Re: Code has stopped working

Post by Superman »

Guys, I think I know what happened. I added new Fields to the Back-end and then did a Compact & Repair. Ever since the Compact and Repair, this isn't working. :(

Would still like to know why there's no error though?

Many thanks

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code has stopped working

Post by HansV »

That's mysterious! Does it make a difference if you change

Code: Select all

    CurrentDb.Execute sql
to

Code: Select all

    CurrentDb.Execute sql, dbFailOnError
Best wishes,
Hans

Superman
Lounger
Posts: 32
Joined: 24 Jun 2010, 17:29

Re: Code has stopped working

Post by Superman »

Is it worth adding this in case it ever happens in future?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code has stopped working

Post by HansV »

If executing the SQL doesn't work, you'll get an error message.
Best wishes,
Hans

Superman
Lounger
Posts: 32
Joined: 24 Jun 2010, 17:29

Re: Code has stopped working

Post by Superman »

Great. As always, thanks Hans. I'll add it now. :)
Have a great day.

Take care

Brian

Superman
Lounger
Posts: 32
Joined: 24 Jun 2010, 17:29

Re: Code has stopped working

Post by Superman »

Hi guys

Hans kindly wrote some code for me a while back, which I need to modify slightly for a similar (cloned) database. However, when I remove 1 argument and change the name of a field, the code throws up an error message.

I tried to be really careful modifying it.

Original is . . .

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
    CurrentDb.Execute sql, dbFailOnError
    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
Modified is . . .

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, ApplicationDate) Values (" & GraduateID & _
        ", " & GAPstaff_FK & ", " & ", #" & Format(ApplicationDate, "yyyy/mm/dd") & "#)"
    ' CurrentDb.Execute sql
    CurrentDb.Execute sql, dbFailOnError
    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
All I did was to remove LC from the code and change ReferralDate to ApplicationDate, but I know get the following error message:
"Error 3134 (Syntax error in INSERT INTO statement.) in procedure Add Graduate.

Can anyone (Hans) see what I've done wrong? :(

Many thanks in advance

Brian

User avatar
Rudi
gamma jay
Posts: 25455
Joined: 17 Mar 2010, 17:33
Location: Cape Town

Re: Code has stopped working

Post by Rudi »

This is not my field...but as a :2cents: guess, try:

Code: Select all

sql = "INSERT INTO tblGAP (GraduateID, GAPstaff_FK, ApplicationDate) Values (" & GraduateID & _
        ", " & GAPstaff_FK & ", #" & Format(ApplicationDate, "yyyy/mm/dd") & "#)"
Regards,
Rudi

If your absence does not affect them, your presence didn't matter.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code has stopped working

Post by HansV »

Rudi's suggestion looks correct to me!
Best wishes,
Hans

Superman
Lounger
Posts: 32
Joined: 24 Jun 2010, 17:29

Re: Code has stopped working

Post by Superman »

Thanks guys, but now I get a different error:
Error 3075 (Syntax error in date in query expression '#'.) in procedure Add Graduate. :(

Appreciate your help greatly.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code has stopped working

Post by HansV »

When I substitute 1 for GraduateID, 37 for GAPstaff_FK, and 22-Mar-1972 for ApplicationDate, sql evaluates to

INSERT INTO tblGAP (GraduateID, GAPstaff_FK, ApplicationDate) Values (1, 37, #1972/03/22#)

This looks like valid SQL to me, so if the table and field names are correct, it should work.

Could you insert a line

Debug.Print sql

below the line sql = "...", run the code and post the result that you get in the Immediate window?
Best wishes,
Hans

Superman
Lounger
Posts: 32
Joined: 24 Jun 2010, 17:29

Re: Code has stopped working

Post by Superman »

Thanks Hans
It says
INSERT INTO tblGAP (GraduateID, GAPstaff_FK, ApplicationDate) Values (6, 1, ##)
INSERT INTO tblGAP (GraduateID, GAPstaff_FK, ApplicationDate) Values (6, 1, ##)

Does that mean the syntax error is somewhere else?

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code has stopped working

Post by HansV »

Thanks, it means that ApplicationDate is empty. Does that sound plausible? Would it be acceptable to insert a record with a blank date?
Best wishes,
Hans

Superman
Lounger
Posts: 32
Joined: 24 Jun 2010, 17:29

Re: Code has stopped working

Post by Superman »

Thanks Hans, but I filled in today's date in the test record. I double-checked it, and it's definitely got a value in it? :(

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code has stopped working

Post by HansV »

Are you certain that ApplicationDate is the name of the control on the form?
Best wishes,
Hans

Superman
Lounger
Posts: 32
Joined: 24 Jun 2010, 17:29

Re: Code has stopped working

Post by Superman »

Yes. I've uploaded it for reference. :(
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code has stopped working

Post by HansV »

If you select the ApplicationDate text box, then activate the Other tab of the Property Sheet, what do you see in the Name property?
Best wishes,
Hans

Superman
Lounger
Posts: 32
Joined: 24 Jun 2010, 17:29

Re: Code has stopped working

Post by Superman »

ReferralDate. D'oh. Thanks Hans

Sorry for wasting your time. I probably wouldn't have spotted that without you. :(

Really appreciate the help you both gave me.

Thanks so much.

Have a great day.

Brian

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Code has stopped working

Post by HansV »

Glad you found it!
Best wishes,
Hans