Querying for idle projects. Yes/No field to autopopulate a text field with a set string, "no new update". (2 Viewers)

[Missomissou]

This thread is related to another I posted earlier. I am trying to query for projects that have gone idle. Users input data into a long text field, "UpdateText" for project reports. This is a method of communicating with the team. They need to be able to enter this information freeform. If there is nothing new to report, they generally write "no new updates", but this text varies with the entry and user. I had been querying based on the string "update", using wildcards to try and capture every entry. @ebs17 gave some very helpful feedback with the query/subquery coding. Based on those responses, I tried a new approach and added a new field, "NewUpdateYN", with Yes/No as the field type, so that users can simply check a box. If the box remains unchecked (I guess that's no?), then I would like to autopopulate the next field, "UpdateText" with the string "No new updates". Users will enter data via a form, so I'm envisioning an update query here, but am not sure that's the right approach (or how to execute one for this instance). Any ideas? Thank you!

 

[Gasman]

Yes, an update query would do all at once. Else the AfterUpdate/Lost Focus event of your checkbox.

 

[ebs17]

This is a method of communicating with the team.

Explain this in more detail, communicating is a broad field. If someone writes "there was sweet porridge today" - what do you want to do with it and why?

Targeted answers can be specified using a table/combobox, so that a user only has to make a simple selection, which is then very easy to evaluate. In this selection, a message like “nothing new” may well be an option.

 

[plog]

You should have a table just for comments.

Sounds like 1 project can have multiple comments/updates. Sounds like the timing of those comments is important. Therefore, this is the structure of the comments table:

tblComments
com_ID, autonumber, primary key
ID_Project, number, foreign key to Projects
com_Date, date/time, captures date and time comment was entered
com_Notes, text, actual text of the comment the user is inputting.

Then from a form perspective you can handle 'No New Updates' with just a single button. User clicks the button and it inserts 1 record into tblComments with 'No Updates' in the com_Notes field. User doesn't need to do anything but click that button for no updates.

 

[Missomissou]

You should have a table just for comments.

Sounds like 1 project can have multiple comments/updates. Sounds like the timing of those comments is important. Therefore, this is the structure of the comments table:

tblComments
com_ID, autonumber, primary key
ID_Project, number, foreign key to Projects
com_Date, date/time, captures date and time comment was entered
com_Notes, text, actual text of the comment the user is inputting.

Then from a form perspective you can handle 'No New Updates' with just a single button. User clicks the button and it inserts 1 record into tblComments with 'No Updates' in the com_Notes field. User doesn't need to do anything but click that button for no updates.

Hi @plog, yes, you are exactly right. My field names are different but the setup is the same as the one you proposed. See below for the field names/table relationships. I have a Secondary table for comments, TblBMonthlyUpdates. It connects to the Primary Projects table (TblAALWRIProjects_New) via the ALWRIProjectID. My UpdateDate corresponds to your suggested com_Date, to capture the moment of entry. And my UpdateText corresponds to your suggested com_Notes. Your last suggestion is spot-on too,

"from a form perspective you can handle 'No New Updates' with just a single button. User clicks the button and it inserts 1 record into tblComments with 'No Updates' in the com_Notes field. User doesn't need to do anything but click that button for no updates.".

That is the question I was trying to ask in the initial post, above. I know how to put a checkbox on the form, but I'm unsure of how to get Access to then autopopulate the comments field with the string "No new updates".

@ebs17 truly, by "communicate" here, I really meant "check-in". The update comments are meant to be a casual, conversational, update for the team, so a list of canned responses wouldn't work here. The only standardized response is "No new update".

 

[plog]

On the button's code, in VBA, you simply construct an INSERT statement using all the values that need to go into the table:

SQL INSERT INTO Statement

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Then you execute that INSERT using DoCmd.RunSQL:

DoCmd.RunSQL method (Access)

Office VBA reference topic

learn.microsoft.com

Give it a shot then post back here your code and the issue you are having if you can't get it.

 

[ebs17]

casual, conversational

You can read something like that easily and casually if you want. but do not evaluate programmatically for important things.

 

[Missomissou]

On the button's code, in VBA, you simply construct an INSERT statement using all the values that need to go into the table:

SQL INSERT INTO Statement

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Then you execute that INSERT using DoCmd.RunSQL:

DoCmd.RunSQL method (Access)

Office VBA reference topic

learn.microsoft.com

Give it a shot then post back here your code and the issue you are having if you can't get it.

Hey there, I'm pretty new to coding (I'm learning!). If, on the data entry form, I click the "view code" button on the top menu, the coding window opens up, containing code I previously entered when I created an edit button for the form/records (That code follows below). Based on the coding links you suggested above, I think the code I should insert to create an INSERT command button would look like this:

INSERT INTO TblBMonthlyUpdates (NewUpdateYN)
VALUES ('No new update');

But I'm not sure where this new code would fit in with the pre-existing code. [I just edited out a bit of comment regarding the execute command, which I see you also linked above].

Here's the existing code for the form's Edit command button:

Option Compare Database

Private Sub Form_Current()
If Me.NewRecord Then
With Me
.cmdEdit.Caption = "Edit"
.cmdEdit.ForeColor = 0
.cmdEdit.FontBold = False
.AllowEdits = True
.cmdEdit.Enabled = False
End With
Else
With Me
.AllowEdits = False
.cmdEdit.Caption = "Edit"
.cmdEdit.ForeColor = 0
.cmdEdit.FontBold = False
.cmdEdit.Enabled = True
End With
End If
End Sub


Private Sub cmdEdit_Click()
Dim cap As String
cap = Me.cmdEdit.Caption
Select Case cap
Case "Edit"
With Me
.AllowEdits = True
.cmdEdit.Caption = "Lock"
.cmdEdit.ForeColor = 128
.cmdEdit.FontBold = True
.Refresh
End With
Case "Lock"
With Me
.AllowEdits = False
.cmdEdit.Caption = "Edit"
.cmdEdit.ForeColor = 0
.cmdEdit.FontBold = False
.Refresh
End With
End Select
End Sub

 

[Missomissou]

You can read something like that easily and casually if you want. but do not evaluate programmatically for important things.

Thank you @ebs17 . I will take note of this. Programmatic evaluation occurs several levels up from me! In this project, I am just a data tracker. The update text is meant for the staff/director. My primary interest here is whether or not a project has gone idle (indicated by 3 continuous months of "no new updates".).

 

[ebs17]

My primary interest here is whether or not a project has gone idle (indicated by 3 continuous months of "no new updates".).

The UpdateDate field is sufficient for this. This has clear information.

 

[plog]

In design view of the form, right click the button, click on Properties on the pop up menu, then click on the Event tab of the Properties form, Click on the elipses next to the On Click line. That will create a new Sub on your code for the button click. Put the code for the INSERT there.

However, don't try to freehand SQL if you don't have to. Before writing code in that section, create a new query and build the INSERT query there. This will help you avoid a lot of the syntax errors and make sure you get it right before struggling with vba. Looking at the SQL you built, you are going to need more values. You will need to INSERT values for every field but the ID of that field, so be sure you do that.

Once you have the INSERT correct, go to SQL view of the query, copy the SQL and then go to the coding area and create a variable and assign the SQL to it.

 

[Missomissou]

The UpdateDate field is sufficient for this. This has clear information.

Interesting--that's true, from my perspective, but our supervisor wants to know if a person has stopped work on a project, or just has nothing to say for that month. I think there's qualitative information here that is valuable. If I'm understanding you correctly, as you present it, no update date could mean there's truly no update, or it could mean a person just hasn't entered an update yet, or it could mean that a person has forgotten to do a job for the month. I think the record of some employee interaction during the month is important for the supervisor to make sure projects and people are active. I really appreciate you giving me this thought exercise--it's really helpful to think about the data in this way.

 

[Missomissou]

In design view of the form, right click the button, click on Properties on the pop up menu, then click on the Event tab of the Properties form, Click on the elipses next to the On Click line. That will create a new Sub on your code for the button click. Put the code for the INSERT there.

However, don't try to freehand SQL if you don't have to. Before writing code in that section, create a new query and build the INSERT query there. This will help you avoid a lot of the syntax errors and make sure you get it right before struggling with vba. Looking at the SQL you built, you are going to need more values. You will need to INSERT values for every field but the ID of that field, so be sure you do that.

Once you have the INSERT correct, go to SQL view of the query, copy the SQL and then go to the coding area and create a variable and assign the SQL to it.

Oh dear. I do understand what you are saying about not freehanding the SQL code, but trying to work out how to create a query to do this might be beyond my capacity--at least for the moment. I might have to work out a different way to achieve this results, even if it's less elegant than your suggestion. Thank you so much for your time/consideration on this. I'll take a look again after I've gotten some sleep.

 

[Pat Hartman]

Never use a Y/N field for something like "new updates". You force the user to remember to check this box in addition to writing a comment. A better solution is to have a DateAdded field that has a default of Date() or Now() depending on whether or not you want it to include time of day. Then use this date to find "new" items. Just look for a date range.