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