Prompt for entry (2000/9.0.3821 SR1)

[Caesar3]

I tried without success to find an answer to this question in Access Help and in Woody's "Special Edition Using Microsoft Office 2000." I think I just don't know what to call the thing I'm trying to do. (I'm not a developer--just a part-time employee trying to create and improve a database, and I'm learning Access on the fly.)

I have a report that displays a student's name, SSN, and Employee ID in the report header. When we launch the report, a "parameter" box pops up to prompt the user to enter the student's Employee ID. We enter those four digits, and the report opens with all that information in the appropriate places. There is one more field (Next assessment date) that doesn't get populated because the data source rarely has that information available. The data source is a linked Excel spreadsheet (table). The linked table has a field for this item, but the fields are either empty or contain inaccurate or out-of-date information.

However, the student's (employee's) next assessment date is fairly easy to calculate in one's head using information readily available from other sources. So the boss has asked me to add another prompt for the assessment date. The process would work like this: The user launches the report and gets the prompt for the employee ID number; the user enters the four-digit number (same as now) and presses Enter. Next the user sees a prompt for the student's next assessment date. The user keys in the information, and the report appears on-screen with all the text boxes nicely filled in.

By the way, it would be nice if the procedure adds this information to the data source! <img src=/S/grin.gif border=0 alt=grin width=15 height=15> Also, if the field in the data source is empty AND the user cannot determine the date (not unusual), we'd want the field on the report to remain blank. (So we can fill it in with pen-and-ink on the paper copy later.) How can this be done?

[HansV]

I would create a form bound to the linked Excel table, for instance using one of the Wizards. Open the form in design view. Make sure that the Employee ID and Next assignment Date fields are included. Add an unbound text box txtSearchID and a command button cmdReport to the form, for instance in the form header of footer section.

Create the following code for the OnChange event of the text box:

Private Sub txtSearchID_Change()
Dim rst As DAO.Recordset
Set rst = RecordsetClone
rst.FindFirst "[Employee ID] Like '" & [txtSearchID].Text & "*'"
If rst.NoMatch Then
Beep
Else
Me.Bookmark = rst.Bookmark
[txtSearchID].SelStart = Len([txtSearchID].Text)
End If
Set rst = Nothing
End Sub

Notes:
<UL><LI>Replace "Employee ID" by the exact name of the field in your table.
<LI>This code uses DAO. In the Visual Basic Editor, select Tools/References... If Microsoft DAO n.n Object Library is not checked, look it up in the list and check it.[/list]Create the following code for the OnClick event of the command button:

Private Sub cmdReport_Click()
DoCmd.OpenReport "rptStudentReport", acViewPreview, , "[Employee ID]=" & [Employee ID]
End Sub

Notes:
<UL><LI>Replace "Employee ID" by the exact name of the field in your table.
<LI>If Employee ID is a text field, you must surround it in quotes:
<pre>"[Employee ID]='" & [Employee ID] & "'"</pre>
[/list]Save & close the form.

Next, open the report in design view. Replace the control source of the text box with the prompt by the Employee ID field. Make sure that there is a text box bound to the Next Assignment Date field.

When the user opens the form, he/she can search for an Employee ID using the navigation buttons or using the search text box. When the correct student is found, the Next Assignment Date can be entered or modified. This will be written to the table AND used in the report.

I hope that this is clear enough. If not, post back.

[Caesar3]

Hans: It's a little unclear what you mean to do in the Report (design view).

You wrote, "Replace the control source of the text box with the prompt by the Employee ID field. Make sure that there is a text box bound to the Next Assignment Date field." On my existing report, the text box that I call Payroll ID is bound to the field Payroll_ID in the underlying query. (And, of course, the query draws its information from the aforementioned linked Excel spreadsheet/table.) Likewise, the text box that displays the employee's next assessment date is bound to a field called "Next_Assess. I understand that you want me to replace something with something else...but I'm not sure I'm tracking.

I see (dimly) how this is going to work, and it appears to be exactly what I'm looking for! I've got the code written in--with a little clarification, I think this is going to work fabulously. This is great stuff, Hans! Thank you!

[HansV]

Hello Lucas,

You wrote in your first post in this thread

<hr>The data source is a linked Excel spreadsheet (table).<hr>

I took this literally; now that I know that the record source is a query, the situation is a little bit different.

(1) Is Payroll_ID a prompt in the query? If so, remove it, and if necessary, set the control source of the text box on the report displaying the student ID to the ID field in the Excel table (Employee_ID? Payroll_ID?)
(2) Where I wrote NextAssignmentDate, use Next_Assess.

I have attached a demo Access 97 database (zipped) to demonstrate the general idea behind my first reply. The form and report are based on a table however, not on a query based on a linked Excel spreadsheet.

[Caesar3]

Hans: Thanks for the prompt reply! Please accept my apology for the misleading information. The report in question does draw its information from a query, not directly from a table. The query gets its information from two tables: One with students, the other with courses.

The report is what we call a "training plan"--it's a list of courses that are available to the student. The report displays the identifying information about the student (name, SSN, etc.) in the report header. The detail section displays the courses. The information about courses comes by way of the query, but the basic source is a table of courses. So the query, you see, combines selected information from two tables to create a report.

So...do you suggest I use a make-table query to create a temporary table for all this data (student information AND course information), and then use that new table as the source for the report? It might be helpful if I were to give a very brief description of how we use this existing system. Let's say I want to create a new training plan ("report") for Joe Jones. First I open the table of courses and check off the courses Jones completed in his previous round of training. (The table has a field we call "taken"--it's formatted Yes/No. The query is set to exclude all courses marked as taken, so that when I make the new training plan the only courses listed are those Jones has not yet taken.) Next I launch the report and fill in Jones's payroll ID at the prompt. The new training plan displays on the screen, and I print it out on paper for day-to-day use in the learning lab.

No doubt there are faults with this design. For example, there's an easier way to annotate the courses Jones has completed. We have a database that keeps track of all that information, and I can import a report from that database and use a query of some sort to weed out the completed stuff...but I'm still working to crack the code on how to do that. For now, the system with all its faults seems to work--and it's a huge improvement over what we were working with six months ago!

So you see that your second reply has opened another keg or worms, so to speak. I really do appreciate your help and your ideas, Hans! What do you recommend? BTW: I'm going to study the example you sent along--thanks!)

[HansV]

Lucas,

You probably don't need a make-table query; if you have a query that selects the student info plus all courses the student hasn't taken or completed yet, you should be able to use that as record source for the query. Put the student info in the report header (as you do now) and the course info in the detail section.

From your description, I gather that the prompt for Payroll ID is in the criteria row of the query, something like [Enter Payroll ID]. There may be two different ways to replace this; not knowing the entire design, I don't know if they will work.

If the prompt is used once in the query, you can remove it and open the report with a where-condition on Payroll ID, as suggested in my first reply and in the demo I attached in my second reply.

If the prompt is used more than once, you'll probably be better off by replacing it with Forms!frmReport!txtPayrollID, where frmReport is the form used to select the student, and txtPayrollID is the name of the control bound to the PayrollID field. The report should be opened without a where-condition:
DoCmd.OpenReport "rptStudentReport", acViewPreview

You'll have to experiment a bit to see which works. Don't forget to backup your database before trying it!

[Caesar3]

Hans, you've done it again! This thing works! <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> The form with the command button allows the user to enter the missing information (next assessment date). Clicking on the command button launches the training plan (report) with the information filled in, and at the same time adds the information to the source table (tbl_AllPeople).

The prompt for the Payroll_ID was indeed in the criteria row of the query. I simply removed that prompt and replaced it with the form. I ran into a couple of syntax errors along the way, but they resulted from my own inattention to minute detail. Thank you very much!

[Caesar3]

Hans: One more question, please, about the command button on the form. We use four "training plans" (reports), color-coded to indicate the training level the student is working in. The command button I created with your help (cmdReport) opens the "Yellow" training plan. Now I want to add buttons to launch the others (Green, Purple, White). I've added the command buttons, but my attempts to build code for them almost wrecked the code for the Yellow button.

I thought I could just copy and paste the code and change the appropriate lines (replace Yellow with Green or whatever). It wasn't so easy. When I opened the "expression builder" (VBE), I saw the code for the first command button I'd created yesterday (cmdReport). I guess I got in trouble when I tried to figure out where to paste the code for the added command button. Can you suggest how to approach this task? Thank you!

[HansV]

Lucas,

I assume the code for the working button is something like what I posted earlier on:

Private Sub cmdReport_Click()
DoCmd.OpenReport "rptStudentReport", acViewPreview, , "[Employee ID]=" & [Employee ID]
End Sub

with the appropriate report name and field names substituted.

Select one of the other command buttons on the form.
Activate the Properties window.
Select the Events tab.
Click in the line of the OnClick event.
Click the dropdown button and select Event Procedure.
Click the button with three dots to the right of the line.
You should see the skeleton of a new procedure; the insertion point should be in the blank line between Sub ... and End Sub.
Here, you can type or paste the DoCmd.OpenReport instruction you need here, with the appropriate report name.

[Caesar3]

Oo-o-o! That was easy! Thanks! I don't know why I made it seem so complicated!

One more thing (and there's always one more thing, right?): This form has fields for the student's name, Payroll_ID, and next assessment date. In almost all cases, the next assessment date field appears blank (that information is missing from the source table). You'll recall that I started this thread by asking for a way to add that missing information. This form does exactly that. There's one little glitch, however. Let's say I've entered an employee's Payroll_ID, and now that person's name appears in the form. Next I compute the student's assessment date and key in that date in the field. I DO NOT press Enter or Tab--the cursor is still in the next assessment date field. Now I click on one of the command buttons to launch the training plan. The training plan launches, but the "next assessment date" field is still blank.

I've found that I can get the information to display if I press Enter before I click on a command button. Trouble is, when I press Enter, the form advances to the next record. I have to use the record selector to return to the correct record--otherwise, I get the training plan for the wrong student. I tried setting the keyboard option to make the cursor remain in the next assessment field on pressing Enter, but that still leaves the field blank when I launch the training plan. Seems I have to get the cursor out of the field before the new data "takes." Fine, but I'm looking for a way avoid having to backtrack into the record. Can I do that?

Might I have to get rid of the record selectors to make this happen?

[HansV]

Hi Lucas,

Put the following instruction in the OnClick procedure for each of the command buttons ABOVE the DoCmd.OpenReport instruction:

If Me.Dirty Then
RunCommand acCmdSaveRecord
End If

This will save the current record (if it has been changed) before the report is opened.

[Caesar3]

Cool! Thanks again, Hans!