Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Enter data only for records in a subform that are 'checked'

Posted on 2006-11-16
11
Medium Priority
?
279 Views
Last Modified: 2008-03-06
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
0
Comment
Question by:Galisteo8
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
11 Comments
 
LVL 44

Accepted Solution

by:
Leigh Purvis earned 1000 total points
ID: 17962885
Some would advocate a field in the tblEmployees tables - a boolean (Yes/No) field for you to check the values.
Then it's a trivial case of

UPDATE tblEmployees
SET NotesField = NotesField + (Chr(13) & Chr(10)) & 'Some new Text'
WHERE CheckedFlag = True

Personally - I think that's a slightly nasty hack.  Updating multiple rows of the same table - twice.

I'd keep a separate table - and outer join to it in a query.
It can be as simple as
EmployeeID (Long FK)   Selected (Yes/No)
Include in your query something like

SELECT EmployeeID, EmployeeName, Selected
FROM tblEmployees LEFT JOIN tblEmployeesSelected
ON tblEmployees.EmployeeID = tblEmployeesSelected.EmployeeID
ORDER BY EmployeeName

base a form on that.
When you click selected you'll be adding rows to tblEmployeesSelected.
So then you can do an update of

UPDATE tblEmployees
SET NotesField = NotesField + (Chr(13) & Chr(10)) & 'Some new Text'
WHERE tblEmployees.EmployeeID In (SELECT EmployeeID FROM tblEmployeesSelected WHERE Selected = True).

(You could use a listbox too - but that's more effort).
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 17963052
I'll give that a shot tomorrow.

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...
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17963063
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).
0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 8

Author Comment

by:Galisteo8
ID: 18328426
I should get back to this db project this week or next.  Please stand by...
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 18490385
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
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 18508916
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
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18509118
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)"
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 18509191
Ah!  Many thanks!

Glad I didn't wind up bailing on this question.  :)  And I'm glad you were on tonight.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18509193
:-)
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18509242
No problem!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question