Broken VBA

[Homegrownandy]

Error:

Runtime error '3061'

too few parameters. Expected 1.

Code:

Private Sub Scheduled_Start_AfterUpdate()
Dim strSql As String
 Dim strNote As String
     strNote = InputBox("Reason for change:", _
              "Rescheduling Tracker")
 strSql = "UPDATE AuditT SET AuditT.Note = '" & strNote & "' WHERE me.jobf.Job_ID"
CurrentDb.Execute strSql
    
End Sub

Debugger highlights:

CurrentDb.Execute strSql

This is a continuation from a few weeks ago when I was working on this. Basically I have an audit table which tracks date changes. This code is so the user has to input why they change it..

Some things have changed since then.

Jobf now has a subform called workdays. Day_ID is now the record that is changing.


with the setup shown in the code I was getting the input box and then the error.


Now when I change

WHERE me.jobf.Job_ID
to
WHERE me.workdays.day_ID

I get no errors but I don't get an input box either.

I want to add a bit more functionality to this code so I'm keen to resolve it if someone could help.

Cheers.

edit: The input box is now showing. Same error persists

[JamesDeckert]

shouldn't "UPDATE AuditT SET AuditT.Note = '" & strNote & "' WHERE me.jobf.Job_ID"
be
"UPDATE AuditT SET AuditT.Note = '" & strNote & "' WHERE " & me.jobf.Job_ID
it's always helpful to drop into debug and look at any dynamically build sql to see what it looks like. It usually clears up problems.

[ItsMe]

You are missing the name of your field, too.

strSql = "UPDATE AuditT SET AuditT.Note = '" & strNote & "' WHERE [MyField] = " & me.jobf.Job_ID

[Homegrownandy]

Thanks for the replies.

ItsMe I've added the field now.

James (or anyone that wishes to answer) this is the updated code:

UPDATE AuditT SET AuditT.Note = '" & strNote & "' WHERE Day_ID = me.workdays.Day_ID

Where do I add your & symbol..

thanks so far,

also

it's always helpful to drop into debug and look at any dynamically build sql to see what it looks like

I'm not sure what you mean by this. When there is a problem I click debug and see which line is highlighted. Is that what you mean or is there something else I can do ?

[Homegrownandy]

Update:

It took me a few hours to figure out how I got the audit table working in the first place. I now see why this code has no chance of working.

I have a macro on the workdays table which runs after update on "scheduled_start".



So.. we cant update depending on Day_ID because the audit table will have many changes relating to one day. I think the input needs to be made the same time as the macro runs. either by adding to the existing macro or converting it to vba and adding the function.

any tips?

[Homegrownandy]

surely I should be able to add another set field for the note. (as shown in the picture) then assign an input box to it.

[Homegrownandy]

Can a moderator delete this and I'll start again. I believe the information here will only add to confusion now.

[ItsMe]

Can you convert your Macro to VBA?

[CJ_London]

two things about your code

UPDATE AuditT SET AuditT.Note = '" & strNote & "' WHERE me.jobf.Job_ID

1. your where clause refers to a control - but not like that - controls should not have . in the name
2. Where what? should be equal to something = perhaps WHERE AuditT.Job_ID=me.jobID or AuditT.Job_ID=forms!myform!JobID

[June7]

Refer to link at bottom of my post for debugging guidelines. Setting a breakpoint in code and stepping through are very common techniques.

[Homegrownandy]

This whole method is useless. It cant possibly work. Ill have to re structure the auditing process I think.

[ssanfu]

Have you seen this article by Allen Browne:

Creating an Audit Log
http://www.allenbrowne.com/AppAudit.html

[Homegrownandy]

I've seen it yes I didn't read too much into it though. Thanks for the link. I should know by the end of today if I need to restructure. I'm still hopefull I wont.

[Homegrownandy]

solved - RunCommand acCmdSaveRecord before opening a form that displays the last record on the audit table.

works a treat.