AboutScottgem Expertise I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.
Experience I've been designing databases for over 15 years working with dBase, FoxPro, Approach and Access.
Organizations Author of Microsoft Office Access 2007 VBA Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing
Thanks as always for your thoughtful and timely responses. My database, though rudimentary, seems to have come a long way. I have a few additional questions.
1. A technical question. I would like to format a number, which will be used as our patient identification number. This is different from the PatientID autonumber, which serves perfectly well to uniquely identify patients. I must recreate the program patient identification number, as it's something the program has always used. I don't have to store it in the tables per se but it does have to be on reports, and as such can simply be a query.
The algorithm is as follows: This is a 10-number code. The first 3 numbers are the site code (3-digit codes associated with each of our provider sites); the next 2 numbers are associated with the first letter of the last name, ie A=01, B=02,...Z=26; the next 4 numbers are sequential numbers, eg 2550, 2551, 2552 (although I am not sure for the moment what number they begin with); the last number is the last digit of the year, eg 2004="4".
Can I create some tables or combo boxes with 2 columns and pull from them the numbers for the site code, last name, and last digit of the year? Ideally, I want the number to be pulled from the data that is input about each client on the form. I understand there is a way to auto-generate sequential numbers as well. And then one must format the 4 fields using & ? Any suggestions are appreciated.
2. Perhaps more importantly than #1 above, I am trying to figure out a way to have information in my tables updated via information that is queried. I have queries/reports that will run indicating those clients that are due to receive transportation vouchers to appointments. The user runs the report every week, prints pre-made labels, mails them off... and then will have to go back to the main form and hand check those clients who were sent vouchers, as well as the date they were sent. This means searching for records on the form and clicking check boxes and filling in the dates.
I am sure the experts know of a way to auto-update these records by way of an update query or append query upon printing the labels or printing the report. Is there a reasonable way to do this without resorting to complex code or expressions? Or is there another, easier way to approach this that I am not thinking of?
3. Finally, any links you might provide, Scott, regarding where to research using combo boxes (or list boxes) as parameter queries for the user, ie so that user does not have to type name of parameter correctly in a query or report?
Many, many thanks.
Geoff
Answer 1. Wherever you need to display this code you use an expression like:
The question here becomes where does the SEQNUM come from. Does your system have to generate it or is stored someplace when the patient data is entered?
2. I'm not clear on this, but anything that can be pulled in a SELECT query can then be appended to a table or used to update records. As an example: you run a select query of all those due transportation vouchers. And use that query to print labels to mail the vouchers. After printing, you can then run an update query to update the appointment records that the vouchers were sent. Lests say the Select query is named qryVouchersDue and there is a Yes/No field indicating the vouchers were sent named VoucherSent. Your Update query would look like this:
UPDATE tblAppointments SET VoucherSent = True
WHERE AppointmentID IN(SELECT AppintmentID FROM qryVouchersDue);
To append records you make a copy of your SELECT query and turn it into an APPEND query.
3. Parameter queries are queries where you embed a prompt that asks the user for a value. You do NOT want to use Parameter queries, what you want to do is use a form to enter criteria and then reference the value in the control in your query criteria. This works because you can reference the value of a control on an open form using the syntax:
=Forms!formname!controlname
As an example, you might have a report that should only list a specific, user selected site. So you would open a form (frmListbySite) and add a combobox (cboSite) to select a site. Then, in your query you set the criteria in the site column to:
=Forms!frmListbySite!cboSite
When the report opens it reads the query whihc looks to the form for its value. That's all there is to it.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
 check those clients in query who were actually sent the vouchers and (b) the date vouchers were sent'))