Hi!
This seems like it should be so simple--basic even--but I'm having a really terrible time with this. How hard can it be to go from one form to another and back?
I have two tables, each with corresponding forms to display their data. The first table is tSelection, which holds items that are to be or have been graded. The second table is tGrade, which holds the actual grades. For each selection there are 2 to 10 grades (criteria upon which they are to be evaluated). The common field between them is SelectionId. The form for displaying tSelection Data is (what else?) fSelection, and the grade form is fGrade. The record source for fSelection is a select query that uses an already filtered subset of selection records to be displayed (qvSelectedGrds4AudWk). There are two areas I'm having trouble with:
1. On the fSelection form, there is a button which, when clicked, should bring up the fGrade form showing only those records with the same SelectionId displayed in the txtSelId text box on the fSelection form when it was clicked.
2. Conversely, once the fGrade form is up, there is a button to return to the previously-displayed tSelection record.
For problem 1, I tried several things, some of which worked until I tried to return from fGrade to fSelection. Here are 3 of my attempts:
A. I had a query that would select the grades based on the content of the txtSelId text box displayed on the Selection form:SELECT DISTINCT *The query works fine when run as a query. But when I run it from within the form, Access doesn't seem to understand the "Forms!fAuditWorkSelection!txtSelId" part (it asks for it as if it's a parameter to be entered.)
FROM qvSelectedGrds4AudWk
WHERE SelectionId = Forms!fAuditWorkSelection!txtSelId;
B. I've also tried running it as a DoCmd.RunSql string, using either the same form control reference or a variable that I've assigned with the SelectionId value. But I guess you can't use such references or variables in a SQL string, as it is rejected as not being a valid SQL statement.
C. Lastly, I've tried throwing the SelectionId into a 1-record, 1-field table in the fSelection's Current event (tCurrentId), and filtering the Grades on that, which worked fine going into fGrade, but gave me problems returning to fSelection. (It either returned to the top of the recordset (not the Selection from which it came) or else it said there was no current record when it hit the fSelection form.)
Problem 2 will probably depend on how Problem 1 is solved.
Without asking me what I did, can someone please just tell me how to do it? I'm really under some pressure here, and this has eaten up way too much of my time. I would be SO grateful for any help!