Urgent help needed with WHERE Command in Code

**Steve1977** · 03-14-2014, 04:34 PM

Hi Guys, Thanks for accepting me as a forum member and apologies for my lengthy first post.

Bascially, I'm pretty new to Access and I have no programming experience, but I've managed to put together a simple database for my entertainments agency using templates and knowledge gleamed from Forums. It's working ok, but I'm having problems getting a Where command to run in a certain piece of code.

I've attached a new zipped version of my blank database, RCE Test Database.zip and I've put a sample booking in so you can see what's going on.

On the home screen you should see a booking on the calendar for 'The Deltatones' on 21st March
If you click on the 'Tasks' button on the right above the calender, it will open up a pop up window called 'R.C.E Reminder Tasks' and inside this pop up window there will be 4 smaller forms.
The top right form is call 'Artist Reminders Due Today'. This should contain data for a task to remind 'The Deltatones' about their booking on 21st March.
If you click the button 'Send SMS/Email' in 'Artist Reminders Due Today' it will run some code which will send an email and an SMS to the artist to remind them about the booking on the 21st March.
It sends the email and the SMS just fine, the issue i've got is that when i'm trying to mark the task as completed (via a yes/no box in the table 'tbleAppointments') it is not marking the specific task as done.

The code I've currently got is attached to the 'Send SMS/Email' button

The problem is in the Where command. I first tried to have it as

Code:

WHERE ApptID=" & ApptIDVariable & ";"

but it came up blank and the Appt ID wasn't holding any info.

I was advised to 'set' the variable, so not knowing what this really meant, i tried to point to my Appt ID which is my unique ID for the bookings.

So it's currently set as..

Code:

 If MsgBox(Prompt:="SMS Sent to Artist. Delete Task?", Buttons:=vbYesNo, Title:="Delete") = vbYes Then
         On Error Resume Next
                         DoCmd.RunSQL "UPDATE tblAppointments SET tblAppointments.[Task_completed?] = 1 WHERE ApptID = [Tables]![TblAppointments]![ApptID];"
                  If Err.Number = 0 Then
            MsgBox Prompt:="Deleted", Buttons:=vbOKOnly, Title:="Deleted"
            Else
             MsgBox Prompt:="There is no record to delete!", Buttons:=vbOKOnly, Title:="Error"
        End If
            Else
        MsgBox Prompt:="Canceled", Buttons:=vbOKOnly, Title:="Canceled"
    End If

End Sub

As you can see it's not finding '[Tables]![TblAppointments]![ApptID]' and this is stopping it working.

Does anyone know what I need to change to get this working correctly?

Many thanks! (and apologies again for the long winded post)

Steve

**June7** · 03-14-2014, 04:44 PM

Can't reference a table or query directly like that. For one thing, it doesn't know which record in the table to look at.

Where was value for ApptIDVariable supposed to come from?

Is this code behind a form?

Is ApptID a field on the form? You want to update records where the ApptID in table is same as ApptID in current record of the form?

Variable must be concatenated. Reference to field/control of form is a variable.

Avoid the confirmation popups with CurrentDb.Execute.

CurrentDb.Execute "UPDATE tblAppointments SET [Task_completed?] = 1 WHERE ApptID = " & Me.[ApptID]

This assumes ApptID is a number type.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

Is Task_Completed a number field or Yes/No? Number constants for Boolean Yes/No are -1/0.

**Steve1977** · 03-14-2014, 05:14 PM

Hi! Thank you so much for replying! You're a potential lifesaver

I'll try and answer all your questions below.