Update with SQL

[murray83]

DoCmd.RunSQL ("Update MTMPM set [PM Shift] = txtMTM.value, [PM Shift1] = txtMTM2.value , [PM Shift2]=txtMTM3.value, [PM When] = DateTrim.value, [PM Who] = txtUserLookup.value where [PM When] = DateTrim.value")

above is my code and it works, so whats the problem you say. Well it works a bit to well and not in the fashion i would like

see before the user presses update, he or she would of already done this

DoCmd.RunSQL ("INSERT INTO MTMPM ( [PM Shift], [PM Shift1], [PM Shift2], [PM When], [PM Who]  )VALUES (txtMTM.value, txtMTM2.Value, txtMTM3.value, DateTrim.value, txtUserLookup.value)")

so what i would like my update to work is just add/stick it on the end of the current records in table, part of me thinks this would be done by an Append but can only find that in query

please see attached and many thanks ( the code for the above is located in the MTM_Info form )

 

[plog]

This is a symptom of a very larger and foundational issue. You have not set up your tables/fields correctly. You should not have multiple tables with the same structure. Seems like every table you have is of the same structure. You've essentially created a multi-tab spreadsheet within Access, this is not how database are to work.

All those tables need to be combined into just 1 table with the same structure. I suggest you read up on normalization (https://en.wikipedia.org/wiki/Database_normalization) and properly set up your tables. Most likely with the correct set up your need to UPDATE data will vanish.

 

[murray83]

I can't believe that the first SQL statement is actually working. It's syntax is invalid.

This version assumes that all the data items are NUMERIC since you weren't using any quotes. However if any are strings, you will need to enclose them in single or double quotes.


strSQL = "Update MTMPM set [PM Shift] = " & Me.txtMTM & ", [PM Shift1] = " & Me.txtMTM2 & ", [PM Shift2] = " & Me.txtMTM3 & ", [PM When] = " & Me.DateTrim & ", [PM Who] = " & Me.txtUserLookup & " where [PM When] = " & Me.DateTrim
DoCmd.RunSQL strSQL

Append queries (Insert) add NEW ROWS to a table.
Update queries update EXISTING ROWS in a table.

And finally, why are you not using a bound form?

well it does work and has done before on another database of mine since august this year with no issues

so the short of it, i cant have data in a record in a table and then add some more to that same record ?? rather than at the minute it just wipes it all out with the updated data which has been inputted

 

[murray83]

Are you being prompted for the variable names?

Do you mean is access asking me for them if so, no i'm not

 

[static]

Your MTMPM table contains an autonumber ID field.

ID is short for IDentification. That field can not contain a duplicate value. It's purpose is to IDentify the record you are working with.

Your update SQL is updating all records for the specified date.

So, if 22 records were created by clicking 'Add PM' today, they would all get updated if you clicked 'Update PM'.

Add a combo/list box displaying values from the table so your users don't need to re-key the values. Use the .column property to insert the values into the text boxes for updating.
Use the (hidden) ID in the UPDATE's WHERE istead of the (non unique) date.



PMShift and PMShift1 are text fields in the table. You have created an input mask that only allows keying numbers. This makes no sense.

Do not use DoCmd.SetWarnings False. use currentdb.execute sql.
If you do set DoCmd.SetWarnings False turn it back on again.

Don't turn off the the close button.
Don't turn off the control box.
Everybody knows what Windows windows should look like. You aren't a good enough coder yet to go against convention.