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.

 

[Pat Hartman]

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?

 

[Pat Hartman]

I just looked at the database. I'm with plog. You have a complete rewrite in your future. Do it now before you have more objects to fix. And while you're at it, clean up your naming standard. All object names should contain ONLY letters(upper and lower), numbers (0-9) and the underscore (_). names should never contain spaces or special characters and you also need to take care to avoid using reserved words such as Name, Month, Order, By, etc. Function and property names such as Month and Name are particularly problematic. You could easily run into situations in code where Access thinks you mean it's reserved word meaning rather than that you are referencing one of your column names.

 

[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

 

[Pat Hartman]

Are you being prompted for the variable names? Just because a query doesn't raise an error doesn't mean that it actually works. Although, it just occurred to me that you might be using TempVars. It would be better to use a naming standard that identifies the variables as TempVars rather than form fields. A "tv" prefix would clarify.

Of course you can update an existing record. You simply use an update query. Your unbound form method is doing you in if that is what is happening. You're going to have to create custom updates so that you only change the value of a specific field in a specific record.

Think about why you are not using bound forms. You are causing a great deal of extra work for yourself and missing out on the best RAD feature of Access.

 

[murray83]

Are you being prompted for the variable names?

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

 

[Pat Hartman]

I think you didn't read the next sentence where I asked if you were using TempVars.

If you want help with a redesign, we can help you. If you like what you have, you have a great deal of VBA code in your future and you are probably going to end up hating Access.

 

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