write to a table from a query

Thanks for the help in my related query - This works well, however

While the query to add a date where there are blanks works wonderfully, this does not 'write' to the main table....silly me

Records are updated through a user form to tblStatementDetails.  tblStatementDetails forms part of the query where  'add a date where there are blanks ' is placed.

How, if possible can I get these dates to 'write' to the tblStatementDetails so that when a user goes to update a record, the dates that were 'added' in the query are reported.....

rgds

S

shaz0503Asked:
Who is Participating?
 
8080_DiverCommented:
this impacts on when a user goes to update a record [form] and no date displays in the review date field

Okay, it there is no [SoE review date],  the Z_ActiveEmployee_tblStatementDetails calculates one (based upon the "last start date").  I am still not exactly sure what you're trying to accomplish, though.

Are you wanting to have the user presented with this calculated date and then be able to update the date and have that date inserted into the
[tblStatementDetails]?  If so, are you having trouble getting the date to display or to be saved into the [tblStatementDetails] table?

Have you considered moving that particular calculation to the form's field?
0
 
Helen FeddemaCommented:
You haven't given enough specific information about the tables involved.  Generally speaking, if you need to update field in a table other than by just entering the value in a bound control, you can use an append or update query, or a SQL statement like this:

UPDATE tblNumericDates SET tblNumericDates.RealDate = CDate(Mid(CStr([DateAsNumber]),5,2) & "/" & Right(CStr([DateAsNumber]),2) & "/" & Left(CStr([DateAsNumber]),4));
0
 
shaz0503Author Commented:
Helen

Thanks...I will give you more detail - apologies

I have a main data table that the user updates information relating to staff. [tblStatementDetails].  I also have a data table (imported twice monthly) [EmployeeData].

I then have a query based on these two tables [Z_ActiveEmployee_tblStatmentDetails]..

Within the [Z_ActiveEmployee_tblStatementDetails] i have code to add a date where the [SoE review date] field is blank.  

I need to be able to 'write' this [SoE review date] within the [Z_ActiveEmployee_tblStatementDetails] qry to the [SoE review date] field in the [tblStatementDetails]

Hope this clarifies a bit more

rgds

S
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
8080_DiverCommented:
Okay, let's clarify some terminology:
One doesn't "write" data from one table to another.  Instead, one "updates" one table from another.

So, if I understand your explanation correctly, you have a date in one table (Z_ActiveEmployee_tblStatementDetails) that you want to insert into a column in another table (tblStatementDetails).  First off, you need to figure out how to identify which row in the source table (Z_ActiveEmployee_tblStatementDetails) matches which row in the target table (tblStatementDetails).

UPDATE S
SET [SoE review date] = Z.[SoE review date] 
FROM tblStatementDetails S
INNER JOIN Z_ActiveEmployee_tblStatementDetails Z
ON S.someIDcolumn = Z.someIDcolumn;

Open in new window

0
 
shaz0503Author Commented:
Can I give some detail on the db....

The Db is used to capture and report on performance agreements.  Employee data is imported twice monthly [EmployeeData] table.

There is a table [tblStatementDetails] that is updated via a user form with information that is not available from the [EmployeeData] table (eg. dates for review, comments etc].  I have a query based on these two tables on which reports etc are based [Z_ActiveEmployee_tblStatementDetails].

To be able to update information I need to ensure that all employees are captured within the [tblStatementDetails] and the user form uses both these tables based on [EmployeeID] from the [EmployeeData] table.

One of the fields on the user form is [SoE review date].  This is 'updated' via the user form and 'updates' the relevatn record in the [tblStatementDetails].  New Employees however do not have an [SoE review date] and do not appear in reports.

In my [Z_ActiveEmployee_tblStatementDetails] qry I have code to add a date where the [SoE review date] field is blank so reports are correct.

My problem is that when a user goes to update a record, if that person is a new employee, their [SoE review date], calculated in [Z_ActiveEmployee_tblStatementDetails] qry does not appear as it does not update [tblStatementDetails].

I am sorry this is long winded but need to sort this one out if possible....

rgds

Shaz






0
 
8080_DiverCommented:
My problem is that when a user goes to update a record, if that person is a new employee, their [SoE review date], calculated in [Z_ActiveEmployee_tblStatementDetails] qry does not appear as it does not update [tblStatementDetails].

If there is no date in the Z_ActiveEmployee_tblStatementDetails, what should the SoE review date in the tblStatementDetails table be set to?

Can you provide a simple set of example data showing the SoE review date for both the Z_ActiveEmployee_tblStatementDetails and the tblStatementDetails tables and one more line for what you really want it to be in each?  (Needless to say, I am struggling to figure out exactly what you are trying to do and from what data you are trying to do it. ;-)
0
 
shaz0503Author Commented:
Will post a 'cut down' verison of the Db today - this may help

S
0
 
shaz0503Author Commented:
All

Please see attached cut down Db -

You will see tables and queries I have mentioned above.

If you go to the [tblStatementDetails] table you will see that [EmpolyeeID] 1474030 does not have a date in the [SoE review date] field

however the code to add a date in the [SoE date field] in [Z_ActiveEmployee_tblStatementDetails] does

this impacts on when a user goes to update a record [form] and no date displays in the review date field

I hope this helps further

rgds
Database.mdb
0
 
shaz0503Author Commented:
8080_Diver

No... I hadn't considered moving the calculation to the form field......  If I do so, would only those where there is NO date have the calculation applied...I can not have dates that are within this field already 'calculated'

Do I simply copy the calculation and add to the field properties, and if so where??

rgds
0
 
shaz0503Author Commented:
8080_Diver

Have got this to work.... added Z_ActiveEmployee_tblStatementDetails[SoE review date] to the form field and all looks good.... will chekc over the next little while and hopefully when new data is imported next month - all will be fine.

rgds and  thanks for your help
0
 
8080_DiverCommented:
When I opened the database to look at the Form's design, I noticed that you have some very bad coding habits.  

For one thing, you are not giving the various control opbjects on the form meaningful names (Text58 is not a meaningful name) and are, instead, leaving the default names that are assigned when you drop the control on the form.  

Another thing is that you are embedding SQL in controls and forms.  If you embed SQL in a control, then you introduce maintenance problems.  The first problem is that the SQL is hdden and, therefore, hard to find if it needs to be changed, either becuase of a change in Business Rules or because of an error in the SQL.  The second problem is that, in order to change the SQL (assuming you know where it is), you have to modify more than just the SQL . . . for instance, you have to open the form, change something, and then close the form all of which can introduce inadvertant errors in the form.

After I reviewed the SQL embedded in the form, it is apparent why you are not seeing the calculated review date in the SoE review date field (named Text58 :-/ ).  The Control Source for the field is based upon the SoE review date column of the [tblStatementDetails] table and NOT on the query you are working on.  

If you want to have the calculated value appear in the Textr58 field, then it will have to exist in the [tblStatementDetails] table.  If the calculation in the query you have been working on is based upon the Last Start Date from that same table, then why not just update the SoE review date in the [tblStatementDetails] table using that calculation?   You could even put some VBA code in the On Lost Focus event wherever the Last Start Date is entered so that it forces the SoE review date to the calculated value.

Failing that, you would need to put some VBA code in the On Got Focus event of Text58 to perform the calculation if the SoE review date column doesn't have a date in it.  The VBA code would need to see if the column is empty and then, if the column is empty, set the column to the calculated value, otherwise (i.e. if the column is not empty) not change the column's value (at that point).

By the way, did you notice that the use of Text58 was less meaningful in the above discussion than, say, txtbxSoEReviewDate? ;-)
0
 
shaz0503Author Commented:
8080_Diver

Thanks - I realise that the form is not perfect and I should use beter naming conventions....

I have now realised I jumped the gun - by doing the above (my comments) still does not update the tblStatementDetails...

With the On Got Focus what would be the appropriate code to add.....

whilst i have been 'creating' Dbs for a little while, i am still very much a VBA code novice....

thank you for you assistance thus far

rgds

S

0
 
8080_DiverCommented:
You'll need to add an IFF statement that sets the [SoE review date] value to either the same value (if it is not empty) or to the calculated value (if it is empty).  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.