Results 1 to 8 of 8
  1. #1
    J_PR1CE is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    4

    Keep entered data when table is re-queried

    Hi everyone, I have created a database to keep track of built items at our production facility. The main table [dbo_Orders] is from a server and I have created a 'make table' query [qry_CasingSets] from it. That table feeds an archive table [qry_CasingSetsAppend] that saves the data when a user types in a field that automatically time stamps the date/time in the field. I have the field data set as [dbo_Orders]![plannedshipdate]+2 and then that field shows white font with a white background so that it appears blank but when you click on it, the field is updated with the now(). The problem is however that when the table is re-queried, the original formula of [plannedshipdate]+2 is shown and the archived time/date goes away. How do keep it up so that when an employee refreshes it stays up as completed time/date?


    Click image for larger version. 

Name:	CaptureForm.PNG 
Views:	32 
Size:	120.6 KB 
ID:	46709Click image for larger version. 

Name:	CaptureQueryDesignView.jpg 
Views:	32 
Size:	107.1 KB 
ID:	46710

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    What is the form's record source? How do they refresh? I assume you are talking about the field Complete. You don't want to make them click in a blank field, it is better to move the update code to the BeforeUpdate event of the form so all they need to do is enter their initials. And the field should be bound to the field from the table, not the calculated expression. You can use conditional formatting using DatePart() function to display the dates that have a time component vs the default ones added by the append query (assuming those don't have it).

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    J_PR1CE is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    4
    The record source is [tbl_CasingSets] which is created from the [qry_CasingSets] make table.
    Yes, the complete field is the column I created called [complete]. I needed a blank date field but couldn't get it to populate so I pulled in the [plannedshipdate] so that it kept the format. It technically has the [plannedshipdate]+2 day in it but I have conditional formatting hiding it so it appears to be blank. The same thing with the [employee name] field. I have it set to mimmick the [plant] code but if it is 'OHP" it is hidden in white font. When the [plannedshipdate] does not match the [complete] field and the [employee name] is not "OHP" then it adds the information to the History table.
    Click image for larger version. 

Name:	CaptureTable.jpg 
Views:	30 
Size:	147.3 KB 
ID:	46714

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    So what is the problem then, the fact that you overwrite the data when you run the make table? If you move the updates to the history table you just need to add an update query after you run the make table to repopulate the field(s) from the history table.
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    A question: why copy data to access tables when you obviously have a SQL server to store the data? In my career I have very bad experience with copying data to, and maintain in 2 separate systems. When I look at your data, all you need is a correct reporting system and maybe, a few extra fields in your database table. So: make a report in SQL reporting, eventually with a procedure that alters the last used date behind the scenes.

  6. #6
    J_PR1CE is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    4
    Sorry, i was away from the office over the weekend. When the [tbl_CasingSets] is re-queried from [qry_CasingSets] then the 'complete' column is back to default value instead of holding onto the archived complete date. So what I need the form to do is to hold onto the date that is time stamped so it looks complete. The first picture attached shows it after i click archive and it sends it to the folder. Second photo is the archive photo confirming that it took only the modified dates. The third picture though shows that when i requery the table, it goes back to the default [plannedshipdate]+2 value.
    Click image for larger version. 

Name:	CaptureFormLog.PNG 
Views:	14 
Size:	81.7 KB 
ID:	46747Click image for larger version. 

Name:	CaptureArchiveTable.jpg 
Views:	14 
Size:	75.9 KB 
ID:	46748Click image for larger version. 

Name:	CaptureFormAfterRequery.PNG 
Views:	14 
Size:	67.7 KB 
ID:	46749

  7. #7
    J_PR1CE is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    4
    I am trying to accomplish having multiple production cells to be able to use their computer to log when completion times are done. I do not have access to modify the server information, just to pull from it. I am wanting the time stamp so that in the future I could continue to have this pull the percentage of completion and the start/finish times to give me a parts per hour count and let the loading department know that their material has been 25,50,75,100%, etc. complete so that they don't waste their time going to find something that isn't ready.
    I am very, very limited in coding. I am not afraid of it but I don't know how to do anything beyond what I can mimic from watching videos.

  8. #8
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Have you tried what I suggested in post # 4? In the code behind the Archive button you need to add one more update query in which you join the tbl_CasingSets to the archive table on the primary keys and update the completion field in tbl_CasingSets to the one in the archive.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 10
    Last Post: 03-12-2019, 09:18 AM
  2. Replies: 14
    Last Post: 03-15-2017, 08:33 PM
  3. Replies: 9
    Last Post: 08-11-2016, 11:30 AM
  4. Lookup queried value in another table
    By snobordin8 in forum Queries
    Replies: 3
    Last Post: 03-27-2013, 11:26 AM
  5. Replies: 2
    Last Post: 07-30-2012, 03:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums