Update statement is not working

[Philyn Gwee]

Hi I am new here. I am trying to see what is the problem with my database. One of the Update statement does not seem to be working anymore (I realised it was since Dec 2019). I am not sure what happened as I did not make any changes to it. Hope someone could point me in the right direction.

There is a form which was created to enter staff personal records. One of the info in this form is the mobile number of the staff. When there is an entry in this mobile number field, I want it to be updated in both the table for staff personal records as well as another table which also has a mobile number field. So upon saving the form, I need two tables to be updated.

I have been trying to check the problem but not sure what is wrong, nothing seems to work.

SP_<> - These fields belong to the qrytbl_StaffPrivate table
S_<> - These fields belong to the qrytbl_Staff table

First Table -> This works fine, all updates are done.

'Update the staff personal records table
dbs.Execute ("UPDATE qrytbl_StaffPrivate_" & Me.SP_Branch.value & " SET SP_DOB = '" & Me.SP_DOB.value & "', SP_AddLine1 = '" & Me.SP_AddLine1.value & "', SP_AddLine2 = '" & Me.SP_AddLine2.value & "', SP_AddCode = '" & Me.SP_PostalCode.value & "', SP_TELHome = '" & Me.SP_TELHome.value & "', SP_TELMOB = '" & Me.SP_TelMobile.value & "', SP_Email = '" & Me.SP_Email.value & "', SP_Citizenship = '" & Me.SP_Citizenship.value & "', SP_Marital = '" & Me.SP_Marital.value & "', S_NOK_NAME = '" & Me.NOK_NAME.value & "', S_NOK_No1 = '" & Me.NOK_MainTel.value & "', S_NOK_No2 = '" & Me.NOK_AltTel.value & "', S_NOK_Relationship = '" & Me.NOK_Relationship.value & "', S_NOK_EMAIL = '" & Me.NOK_Email.value & "', S_Med_Conditions = '" & Me.SP_Condtions.value & "', S_Diet = '" & Me.SP_Diet.value & "', S_Notes = '" & Me.SP_Notes.value & "', SP_SeasonPark = " & Me.SP_SeasonPark.value & " WHERE SP_NRIC = '" & Me.SP_NRIC.value & "'")

Second Table -> Updates not done, it was working previously

'Mobile field is not empty
If (Not RequireInputVal(Me.SP_TelMobile.value)) Then
'Update the staff table
dbs.Execute ("UPDATE qrytbl_Staff_" & Me.SP_Branch.value & " SET S_Phone = '" & CStr(Me.SP_TelMobile.value) & "' WHERE S_NRIC = '" & Me.SP_NRIC.value & "'")
End If

 

[Gasman]

Well I cannot tell you why it stopped working.
I would be examining your variables in the debug window.?

I would walk through the code with F8 and see what RequireInputVal produces ?
Not sure why you have a table for each branch either?

 

[plog]

Lots of questions. Normally I'd ask why are you not using a bound form. But I know the answer--you can only bind a form to one table. So, that means I have to ask why multiple tables? Also, how many "qrytbl_Staff_" tables do you have? Finally, why prefix a table with "qry", are they tables or queries?

Some answers. Your need to find out what query you are running. Sure you posted the VBA that builds the SQL, but you need to see the actual SQL. Build the SQL in a string then Debug.print it so you can see it.

One note. You should check the WHERE value if your UPDATE as well. You do some sort of test on the mobile value to decide if you run an UPDATE, you should also check the NRIC otherwise NULL/invalid values could get into your UPDATE query

 

[Philyn Gwee]

Well I cannot tell you why it stopped working.
I would be examining your variables in the debug window.?

I would walk through the code with F8 and see what RequireInputVal produces ?
Not sure why you have a table for each branch either?

Hi thanks for your reply. I did a debug but nothing shows up but yet it is not updating the field.
Different people are given rights to access each branch's records. The tables are uploaded and shared in SharePoint with relevant people I need to share with in each branch.

 

[Philyn Gwee]

Lots of questions. Normally I'd ask why are you not using a bound form. But I know the answer--you can only bind a form to one table. So, that means I have to ask why multiple tables? Also, how many "qrytbl_Staff_" tables do you have? Finally, why prefix a table with "qry", are they tables or queries?

Some answers. Your need to find out what query you are running. Sure you posted the VBA that builds the SQL, but you need to see the actual SQL. Build the SQL in a string then Debug.print it so you can see it.

One note. You should check the WHERE value if your UPDATE as well. You do some sort of test on the mobile value to decide if you run an UPDATE, you should also check the NRIC otherwise NULL/invalid values could get into your UPDATE query

Hi thank for your reply. I have multiple tables as different people are given rights to access each branch's records. The tables are uploaded and shared in SharePoint with the relevant people I need to share with in each branch. I have several of such qrytbl_Staff_. These are queries not tables. I recall there were some updates done by Microsoft last year and the Update statements could not work with tables anymore. We had to create queries for each table and have the Update statements update the queries instead of the table, then it work. There was also no error when I did a debug. The NRIC value would not be Null as it is a mandatory field when you create the record so that should not be a problem.

 

[isladogs]

Normally I'd ask why are you not using a bound form. But I know the answer--you can only bind a form to one table.

Sorry @plog but I'm sure you know that isn't correct.
A form can of course be bound to a query or sql containing more than one table.
Providing that its an updateable query, the form will be editable

 

[The_Doc_Man]

We had to create queries for each table and have the Update statements update the queries instead of the table, then it work.

Just for point of clarification, I had this same problem eight or more years ago. It isn't due to a recent Microsoft update. Having said that, I can't tell you why it happened - but it did and the "single-table query" was the way I fixed what was broken.

As to the other problem, security differences are one reason to do things that normally aren't done that way. However, if you had a "role-based" system set up where each user has a "role" associated with the username, you would be able to make your forms filter for that role and get by with keeping things all in one table. You would add a tag to each record that was subject to filtering and change the form's recordsource to include a WHERE clause to implement the filter.

 

[plog]

But I know the answer--you can only bind a form to one table.

My mistake was using 'table' when I meant data source.

 

[Philyn Gwee]

Since the update query seems to be updating a query, have you checked to see if something changed in that query making it no longer updateable?

Really not sure, nothing has been changed. I have checked but could not find what is the problem. It is a mystery that I am still trying to solve.

 

[Philyn Gwee]

Just for point of clarification, I had this same problem eight or more years ago. It isn't due to a recent Microsoft update. Having said that, I can't tell you why it happened - but it did and the "single-table query" was the way I fixed what was broken.

As to the other problem, security differences are one reason to do things that normally aren't done that way. However, if you had a "role-based" system set up where each user has a "role" associated with the username, you would be able to make your forms filter for that role and get by with keeping things all in one table. You would add a tag to each record that was subject to filtering and change the form's recordsource to include a WHERE clause to implement the filter.

Thanks for sharing that it happened to you too. I had to create the single-table query to fix the issue only in Dec 2019. Before that it was working fine.

Ah.. I understand the role idea you shared. Will see if we could improve the database using this method in future. Thanks!