Help needed with duplicate table info!

[plog]

You need to find out where it gets set and make sure it does.

 

[Stroud1977]

You need to find out where it gets set and make sure it does.

Apologies, you must be getting sick of this, but I've got no idea what that means or how to do it.

The first time I've ever encountered the 'Where/variable' command is when you posted it here in this thread and I copied it into my VBA.

How do I go about 'setting' it? Do i need do something to the code or the table?

The only thing i know is that ApptID is the unique number for the tblappointment entries. I don't know what ApptIDvariable is to be honest, i just copied it from you!

 

[Stroud1977]

I've been googling 'setting' variables in Where clause as recommended by Plog, but I can't find anything online...(not saying it's not out there, just nothing that I could interpret as being relevant).


Does anyone know how to do this? This is hopefully the final piece of the puzzle for me (famous last words )

Fingers crossed!

 

[Stroud1977]

Hi Guys, thanks for all your help so far, I've almost got these tasks working now, so here's where I'm up to..

I've attached a new zipped version of my blank database, but 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.

This is the end of the code I've currently got attached to the 'Send SMS/Email' button

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

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..

 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]'.

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

Many thanks!

Steve