Galisteo8
asked on
Enter data only for records in a subform that are 'checked'
I have an Access 2000 database with a table for employees (tblEmployees) which includes a large text field (txtNotes) for adding comments to each record. We have occasions where we need to add the same note to many of the employee records all at once. Not necessarily ALL of the records, but most of them.
What I’m envisioning is a form where the user enters the text. A subform shows a list of all employees, and the user can check or uncheck these employees as appropriate. Clicking “Save” on the form would insert the given text into the txtNotes fields of the employee records that were checked.
Anyone ever done something like this? How can you do this kind of a “check box” approach?
Thanks in advance,
Galisteo8
What I’m envisioning is a form where the user enters the text. A subform shows a list of all employees, and the user can check or uncheck these employees as appropriate. Clicking “Save” on the form would insert the given text into the txtNotes fields of the employee records that were checked.
Anyone ever done something like this? How can you do this kind of a “check box” approach?
Thanks in advance,
Galisteo8
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Exactly. But since it's a local table you're free to do so at will (before you begin the selection process instead if you prefer - just to be sure).
ASKER
I should get back to this db project this week or next. Please stand by...
ASKER
I'm sorry. This particular project is just a "side" project (i.e. low priority) and I will not be able to try lpurvis' suggestion at this time. So... I don't know if it will work in my actual situation. If/when I get a chance to implement his suggestion and it works out, then I'll post a point-giving question for him. In the meantime, I'll just close this one.
Sorry 'bout that!
--Galisteo8
Sorry 'bout that!
--Galisteo8
ASKER
LPurvis,
Hi, there. I found myself working over the weekend and thought I'd give this a go! :) This is a great solution, it's coming along nicely -- thank you! however, something is still not working right...
I have set up the form based on the SELECT query as a subform on another form; this parent form then has a textbox where the additional text is to be entered, and also a command button for the user to click to actually append the text to the Notes field of the selected employees. What I find is that the new text only gets appended to the Notes field if there is already something in the Notes field. If an employee's Notes field is blank, then the new text will not get put in.
Here's the event code on the command button:
**************************
Dim strQuery As String
strQuery = "Update tblEmployees Set Notes = Notes + '" & Chr(13) & Chr(10) & Me.txtNote2Add & "' WHERE tblEmployees.EmployeeID In (SELECT EmployeeID FROM tblEmployeesSelected WHERE Selected = True)"
CurrentDb.Execute strQuery
**************************
is there some reason that Chr(13) doesn't work in a NULL field, thereby rending the entire reference to my new text void?
We're almost there!
--Galisteo8
Hi, there. I found myself working over the weekend and thought I'd give this a go! :) This is a great solution, it's coming along nicely -- thank you! however, something is still not working right...
I have set up the form based on the SELECT query as a subform on another form; this parent form then has a textbox where the additional text is to be entered, and also a command button for the user to click to actually append the text to the Notes field of the selected employees. What I find is that the new text only gets appended to the Notes field if there is already something in the Notes field. If an employee's Notes field is blank, then the new text will not get put in.
Here's the event code on the command button:
**************************
Dim strQuery As String
strQuery = "Update tblEmployees Set Notes = Notes + '" & Chr(13) & Chr(10) & Me.txtNote2Add & "' WHERE tblEmployees.EmployeeID In (SELECT EmployeeID FROM tblEmployeesSelected WHERE Selected = True)"
CurrentDb.Execute strQuery
**************************
is there some reason that Chr(13) doesn't work in a NULL field, thereby rending the entire reference to my new text void?
We're almost there!
--Galisteo8
That's just down to your concatenation operator choice. (+ instead of &)
' Here \/
strQuery = "Update tblEmployees Set Notes = Notes & '" & Chr(13) & Chr(10) & Me.txtNote2Add & "' WHERE tblEmployees.EmployeeID In (SELECT EmployeeID FROM tblEmployeesSelected WHERE Selected = True)"
In fact - what I'd recommend (to prevent unecessary carriage returns at the start of Notes fields (which is what you'd get with the above).
strQuery = "Update tblEmployees Set Notes = Notes + (Chr(13) & Chr(10)) & '" & Me.txtNote2Add & "' WHERE tblEmployees.EmployeeID In (SELECT EmployeeID FROM tblEmployeesSelected WHERE Selected = True)"
' Here \/
strQuery = "Update tblEmployees Set Notes = Notes & '" & Chr(13) & Chr(10) & Me.txtNote2Add & "' WHERE tblEmployees.EmployeeID In (SELECT EmployeeID FROM tblEmployeesSelected WHERE Selected = True)"
In fact - what I'd recommend (to prevent unecessary carriage returns at the start of Notes fields (which is what you'd get with the above).
strQuery = "Update tblEmployees Set Notes = Notes + (Chr(13) & Chr(10)) & '" & Me.txtNote2Add & "' WHERE tblEmployees.EmployeeID In (SELECT EmployeeID FROM tblEmployeesSelected WHERE Selected = True)"
ASKER
Ah! Many thanks!
Glad I didn't wind up bailing on this question. :) And I'm glad you were on tonight.
Glad I didn't wind up bailing on this question. :) And I'm glad you were on tonight.
:-)
No problem!
ASKER
Shouldn't I also delete all the rows from tblEmployeesSelected when all is said and done? The next time it's needed, the user may select completely different employees. Also, the table would just keep getting bigger and bigger...