Generate a value in subform on change (1 Viewer)

[andy1968]

I would like to enter a record in a table if a value of a control changes on a form.



I would also like to enter record in the same table if the record is new with a differing value in one of the fields.



I can code the first part, but have not been able to figure out a way to get the 2nd part to work. Any ideas? Not sure where I could run this code.



I have tried to code to run on close, before update etc.


Here is the code I use when changing the field:

Private Sub Who_AfterUpdate()
If Me.Who <> Me.Who.OldValue Then
 Dim strwho As String
Dim itgID As Integer
Dim strwhat As String
Dim ddd As Date
Dim tblCustomers As DAO.Recordset
DoCmd.SetWarnings False
ddd = Now()

strwho = Me.Who
itgID = Me.ID
'Dim intnewrec As Integer


'intnewrec = frmToDoListEntry.NewRecord


    Set tblToDoListLog = CurrentDb.OpenRecordset("SELECT * From[tblToDoListLog]")
    tblToDoListLog.AddNew
    tblToDoListLog![ID] = itgID
    Debug.Print itgID
    tblToDoListLog![Who] = strwho
    tblToDoListLog![DateAction] = ddd
    tblToDoListLog![What] = "Reassigned to " & strwho
    tblToDoListLog.Update
    tblToDoListLog.Close
    Set tblToDoListLog = Nothing
 End If


End Sub

 

[theDBguy]

Hi. Sounds like you should be able to use the form's BeforeUpdate event and check if it's a new record, so you can execute your code.

 

[andy1968]

Sounds like a good plan, but I'm not clear on how to check that in the BeforeUpdate event.

 

[theDBguy]

Something like...


If Me.NewRecord Then

 

[andy1968]

I get an error message saying "You cannot add or change a record because a related record is required in table "tblToDoList""


This record would be created in the form - so after updating, I guess.

 

[theDBguy]

At this point, we would need more information about your database, and it would probably be easier/faster if you could post a sample copy of your file with test data.

 

[andy1968]

Here you go.


The form I am working with is frmToDoListEntry

 

[theDBguy]

Okay, thanks. I see the form. What am I looking for? Can you give me a step-by-step instruction on what the user would do and what you would like to happen?

 

[andy1968]

Sure.


The user would enter a contract, the who (person), and the what on the form.



Then when closing the form (or moving to a new record), a record in tblToDoListLog would be created with the name of the person in the Who Field, today's date in the DateAction field, the ID of the todo item in the id field and "Created" in the What field.


Basically, I want to use the tblToDoListLog to track when a todo item is created, assigned to a new person, closed, or reopened.


I can't get it to add a record on the create action.

 

[theDBguy]

Hi Andy,


Thanks, that's a start, but you'll have to talk to me as if I don't know anything about your database, because I don't (I think none of us do). So, you may have to explain even the seemingly basic stuff to us. For example, when I open your file, I get a form with buttons. If I click on the button labeled "Add To Do List Items," I get a form with tabs and it's on the first tab "To Do List Entry." It shows "Line and System" in the Contract box with a Due Date of 1/31/19. There's already a "Dan Elshire" entered in the "Who" box, but the "What" box is empty. If I enter some text in the "What" box, what do I do next to see what you're saying should happen?

 

[andy1968]

OK. Thanks for bearing with me, and more importantly, your interest!



The data base is used for assigning items for various people to do; i. e. a to do list, or Responsibility tracker. I am using it as a project management tool.


The "To Do List Entry" tab is the one to focus on. This is for entering new tasks, and assigning them to individuals. It can also be used for editing existing tasks but is set to data entry when opened from the Main Menu.



"Line and System" and "Dan Elshire" are the defaults; default Contract (or project) and default Who respectively. The Due Date defaults to 7 days from today.


The Phone Number control pulls from the Who combo box. Just handy if you want to call the person to discuss.


The What control is where you enter the task.


The subfrom on this tab is where notes are added, e.g. "Dan said he called the supplier and they said the product will not be ready until 2021"


Hyperlinks tab is for adding hyperlinks related to the to do item. Usually related files.



All to do this contract tab should show all the do to for this contract - though I don't think it is working.


Log of E-mail Sent tab would show any e-mails sent from this form regarding this to do item (there is a control button that will generate an e-mail to the person in the who).


Log of This Todo Item tab should show the log of when this to do item was created, if the person it was assigned to was changed, date it was closes, and date it was re-opened.





Here is what I am struggling to get to work:


When the user leaves the current record, or closes the form, a record in tblToDoListLog should be created with the name of the person in the Who Field, today's date in the DateAction field, the ID of the todo item in the id field and "Created" in the What field.

 

[theDBguy]

Hi Andy. There are a few things I'd like to discuss about your design but I just tried to make it do what you were asking for now. I hope I understood it correctly.

 

[andy1968]

Thank! works like a charm.


Would appreciate any other feed back.



Access is kinda a hobby for me - and I know there is a lot I could do better.


Been playing around with it for 20 years, mostly learning on my own through the web.

 

[theDBguy]

Cool. Don't let me forget. You may have to ping me sometimes or shoot me an email. I'm a little tied up at the moment. Cheers!

 

[andy1968]

I set a reminder for 2/8/2018.


Thanks!