Help needed with duplicate table info!

[Stroud1977]

Hi Guys,

You've all helped me so much on my current (first ever) database that I feel bad asking for more help, but here goes..

This is going to be an entertainments agency database which deals with booking artists/bands etc.

I've got a table (tblAppointments) where all my booking info goes. (artist, venue and client details etc)
I set up a task system which sets up tasks using the info in 'tbl appointments' to remind the artists about the booking via SMS and Email 7 days before the gig.
This works fine, but I've set it to ask if you want to delete the task after actioning, which you need to, otherwise it just hangs around, but it's deleting the actual booking info and not just the task, because it's all coming from the one table 'tblAppointments'.
So I thought what I needed to do was to have a duplicate of 'tblAppointments' and call it 'Tasks' and use an append query to autopopulate the new 'Tasks' table.

But everything I've read on here has convinced me that duplicating data in tables is a BIG no no, so I've come to you and humbly ask, what is the best way to achieve this? I'm pretty sure it's relationship based, but as you can all see, I'm way in over my head and would love some simple advice

Many thanks in advance..i'll attach a copy of the database so you can see what i'm up to.

Kind regards,

Steve

 

[plog]

Generally you don't delete or move data--you mark it. After a task is complete, mark it complete. Set up a Yes/No field and when it is complete, set that field to Yes. Now you know which tasks are complete and not complete.

PS, your file had an unrecognizable format so I couldn't open it.

 

[Stroud1977]

Generally you don't delete or move data--you mark it. After a task is complete, mark it complete. Set up a Yes/No field and when it is complete, set that field to Yes. Now you know which tasks are complete and not complete.

PS, your file had an unrecognizable format so I couldn't open it.

Hi Plog, thanks for the advice.

My task comes up as a pop up form with only limited info which is linked to a tblappointments query. It's basically a big button saying 'Send SMS/Email'.
Do i need to put a Yes/No field in the table or the query and then link to it on the form?

Is there a way that it could automatically be set to mark the task and then delete it from my task pop up?

My file is WinRAR ZIP archive (.zip) if that helps?

 

[Stroud1977]

 

[plog]

I'm able to unzip it and see your database, the database just doesn't open.

Data exists in tables, so that's where you should ad it. Its up to you if it needs to be in the form--is that something you need to see?

The way I would make it work is that when the Send SMS/Email button is clicked and executes succesfully, I would have that code update the Yes/No field to Yes.

 

[Stroud1977]

I'm able to unzip it and see your database, the database just doesn't open.

Data exists in tables, so that's where you should ad it. Its up to you if it needs to be in the form--is that something you need to see?

The way I would make it work is that when the Send SMS/Email button is clicked and executes succesfully, I would have that code update the Yes/No field to Yes.

Thanks for the reply!

Sorry about this, I've no idea why it's not opening, I can open it ok here and I shared it with another forum member a few days ago this way with no issues :banghead:

Anyway, you have been very helpful, I think I'm getting somewhere thanks to your help.

You're right I dont need the yes/no on the form. So I've taken your advice and tried to insert some code into my SMS button

 If MsgBox(Prompt:="SMS Sent to Artist. Delete Task?", Buttons:=vbYesNo, Title:="Delete") = vbYes Then
         On Error Resume Next
         DoCmd.RunCommand "UPDATE tblappointments SET Task Completed?=1"
         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

It won't surprise you that this code doesn't work, (just comes up with 'there is no record to delete!' probably because I'm an idiot when it comes to code and I'll have certainly done something silly.

If you can see the flaw would you be good enough to point it out?

Many thanks

Steve

 

[plog]

I don't think DoCmd.RunCommand is the right method. I think you want DoCmd.RunSQL. Check out this link:

http://social.msdn.microsoft.com/Fo...nd-needed-to-run-update-query?forum=accessdev

Also, make a backup of your table and you are going to need a WHERE clause on the SQL statement, otherwise you will update everything.

 

[Stroud1977]

I don't think DoCmd.RunCommand is the right method. I think you want DoCmd.RunSQL. Check out this link:

http://social.msdn.microsoft.com/Fo...nd-needed-to-run-update-query?forum=accessdev

Also, make a backup of your table and you are going to need a WHERE clause on the SQL statement, otherwise you will update everything.

I totally apologise, this must be frustrating for you, but most of the above went over my head. I'm really a complete beginner when it comes to all this.

I replaced the DoCmd.RunCommand line of the code with DoCmd.RunSQL and it doesn't seem to have made any difference. I'm afraid I don't really know how to write a WHERE clause or even 'where' to put it if I did.

I googled Where clause and gave it a go though, this is as far as I got

 If MsgBox(Prompt:="SMS Sent to Artist. Delete Task?", Buttons:=vbYesNo, Title:="Delete") = vbYes Then
         On Error Resume Next
         DoCmd.RunSQL "UPDATE tblappointments SET Task Completed?=1"
         SELECT Task Completed?
         FROM tblappointments
WHERE Task Completed?='1';
         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

naturally, this isn't right, but am I getting closer?

A thousand thank yous!

 

[plog]

It's no problem, I don't really need to see your database at this point--maybe if you keep having issues with your code, but for now I can give instructions with the code you've posted.

The 3 lines under your DoCmd.RunSQL line are doing nothing. You ended the quote marks so those 3 lines of text are out in no man's land. You're entire SQL statement must be inside the quotes.

However, even if you did put those lines inside the quotes it isn't valid SQL. You're not going to have a SELECT nor FROM statement in an UPDATE query, so those are doing nothing. Also, you're WHERE clause needs to include a specific record. Do you have a unique ID field in tblappointments? That's the field that should be used there.

Lastly, you have a tool in Access that will tell you if you're SQL is good or not--its the Query Designer. Stop with this script and go make an UPDATE query using that. Then when it is set up correctly copy the SQL from there into this script. That way you will have good SQL and won't be flying blind trying to write it in your VBA.

 

[Stroud1977]

Again, a thousand thanks for your patience, but unfortunately I think this is slightly beyond me.
I cannot emphasise how much of an access novice I am.

I tried to create an Update Query, but I must have done it wrong and I'm not entirely sure what I'm trying to accomplish any-more and I'm just getting confused!

Ok, the SQL of the query was this

 "UPDATE tblAppointments SET"
         WHERE (((tblAppointments.[Task completed?])=True));

and i tried to put it in my code and got this

    DoCmd.RunSQL "UPDATE tblAppointments SET"
         WHERE (((tblAppointments.[Task completed?])=True));
         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

Which resulted in a 'Compile Error / Syntax error' on this line

    WHERE (((tblAppointments.[Task completed?])=True));

It's sure it's probably obvious to everyone else where I've gone wrong, but not to me unfortunately :banghead:

 

[plog]

W3 schools is your friend, this is the format of an UPDATE query: http://www.w3schools.com/sql/sql_update.asp

They also have other queries and languages as well. Stop trying to writing SQL in a VBA module and write it in a query. From the ribbon: Create->Query Design. That will open a query that you can create to get your UPDATE statement written. Once it is and it works, you Left click on it and select 'SQL View', that will have the SQL already written for your query, you then move it from there to your VBA code, making writing the proper SQL moot.

 

[Stroud1977]

W3 schools is your friend, this is the format of an UPDATE query: http://www.w3schools.com/sql/sql_update.asp

They also have other queries and languages as well. Stop trying to writing SQL in a VBA module and write it in a query. From the ribbon: Create->Query Design. That will open a query that you can create to get your UPDATE statement written. Once it is and it works, you Left click on it and select 'SQL View', that will have the SQL already written for your query, you then move it from there to your VBA code, making writing the proper SQL moot.

Thank you so much! I think I'm finally getting somewhere.

I've successfully created the update statement in the query wizard, I've copied that into my VBA code and it all runs great.

My only problems now is that it's updating all the appointments in my table as 'Yes' in the Yes/No box instead of just the single appointment listed in the task.
Now i just have to figure out how to apply it to just the selected task and once it does how to remove that single task from the task list.
I can see light up ahead though!

 

[plog]

That's going to take a WHERE clause. Basically something like:

" WHERE taskID=" & TaskIDVariable

That part will probably be in the code just above what you've given me snippets of, or possibly if this thing is activated off a form, that may be a field on the form. The key is identifying the exact task you are working with and being able to map it back to that specific record in the table.

 

[Stroud1977]

That's going to take a WHERE clause. Basically something like:

" WHERE taskID=" & TaskIDVariable

That part will probably be in the code just above what you've given me snippets of, or possibly if this thing is activated off a form, that may be a field on the form. The key is identifying the exact task you are working with and being able to map it back to that specific record in the table.

Thanks again! I feel I'm almost there, I added the 'WHERE' clause as follows (ApptID instead of TaskID)

If MsgBox(Prompt:="SMS Sent to Artist. Delete Task?", Buttons:=vbYesNo, Title:="Delete") = vbYes Then
         On Error Resume Next
         WHERE ApptID = " & ApptIDVariable"
         DoCmd.RunSQL "UPDATE tblAppointments SET tblAppointments.[Task_completed?] = 1;"
         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

but get the following error

Compile error:

Sub or Function not Defined

and when I debug it highlights ApptID which is the unique ID for the tasks.

 

[plog]

You put a WHERE clause in your VBA code, but not in your SQL. And the way you put it in, I'm surprised you aren't getting an error on that line.

Check out the link I sent you again. There is a very specific order to an SQL statement, the WHERE clause is the last portion of an UPDATE statement. It also needs to be inside the string you have for your SQL statement.

DoCmd.RunSQL "UPDATE tblAppointments SET tblAppointments.[Task_completed?] = 1 WHERE ApptID=" & ApptIDVariable" & ";"

 

[Stroud1977]

Hi, and thanks again, just to be clear, I am only putting this code in my VBA.

The code you've kindly supplied is unfortunately still giving me the same compile error on ApptID. (I had to hit space bar to put the WHERE part on a new row as it just stayed red if i kept it as one long line of code?)

 

[plog]

Yes it is only good in VBA, however i screwed up and put an extra double quote in there. The below line should completely replace the corresponding line in your code:

DoCmd.RunSQL "UPDATE tblAppointments SET tblAppointments.[Task_completed?] = 1 WHERE ApptID=" & ApptIDVariable & ";"

Also be sure to take out that other WHERE line that appears before.

 

[Stroud1977]

Thanks again! (I owe you a beer...at least!)

OK, that new code works, no errors, but it's still not working perfectly

as is, the code works, but doesn't update the yes/no box on any records in tblappointments.

If I remove

WHERE ApptID=" & ApptIDVariable & "

then it updates the yes/no on all records in tblappointments.

I just need to update the particular record I'm working on in the form.

 

[plog]

What exactly is in ApptIDVariable?

My guess is it isn't hold the ApptID that it is suppose to. Add this line of code right before the line of code we were working on:

MsgBox("ApptIDVariable = " & ApptIDVariable)

Then run it. It will tell you what value the variable has.

 

[Stroud1977]

'ApptIDvariable= '

It came up blank!

ApptID is just the unique ID for each appt in tblappointments