Setting default value on a form from a Query

[rjgriffin46]

I have a combo box field on a Student Details form that displays the Student's class number. For a new record, I am able to set the default value in the control's Default Value property with a literal in quotes (ie., ="2015-1"). But I want to set the default value from a query field. However, when I specify the query field in an expression ((=[Current Class]![Class Number]), "#Name?" appears in the field when initiating a new record on the form.



The control source for is the field in the Students table ([Students.[Class Number]).

The Row Source is a query of the Class table.

How can I set the control's default to the Class Number value in the Current Class query?

Thanks

[orange]

Can you tell us more about your database, the tables and relationships, and the purpose of the database?

[June7]

I'm with orange - need to know more about data structure. What data is in Students and StudentDetails?

What does Class Number represent?

If CurrentClass is the query and it is not included as part of the form RecordSource then the field is not available for reference. Cannot refer directly to tables/queries in ControlSource expression like that. Can use a domain aggregate (DLookup, DMax, DSum, etc) to pull data from query/table.

[rjgriffin46]

I've described various parts in prior posts, but here is what I think is relevant to this thread:

This is a Student administration database built & modified from the MS Access Students template. It is being used to capture information on all students accepted into our 16 week Food Service Training Academy classes. We generally run three classes a year and number the classes YYYY-N (eg., 2015-1 is the 1st class in 22015). The subject Tables are:

Students: Keeps all the basic student information such as name, address, contact info, status, class number, emergency contact.
Class:

The form I am working on was the main original from called Student Details.

[June7]

The Class Number is in Students record then I see no reason to also have it in StudentDetails. However, if you want to accommodate repeat students and you want history of their participation, then Class Number probably should not be in the Students record and some db redesign might be needed.

[rjgriffin46]

Sorry for premature release:

==========================
Well as I was composing my responses below, I tried the DLookUp as follows: =DLookUp("[Class Number]","Current Class"). I must confess it took several shots, and I invoked Access Help and looked at several examples in order to get the parentheses and brackets correct. Amazingly it seems to work! Too bad the expression builder doesn't do that for you....or does it? Anyway, this will be an invaluable piece of knowledge going forward and I thank you for pointing me in the right direction,

Please advise, however, of anything else I should know related to this issue.

Thanks!
===========================
Here is the additional information you asked for.

I've described various parts in prior posts, but here is what I think is relevant to this thread:

This is a Student administration database built & modified from the MS Access Students template. It is being used to capture information on all students accepted into our 16 week Food Service Training Academy classes. We generally run three classes a year and number the classes YYYY-N (eg., 2015-1 is the 1st class in 2015). The subject objects are:

Tables:

• Students: Keeps all the basic student information such as name, address, contact info, status, class number, emergency contact.
  
  
• Class: In addition to the Class Number, has start and graduation dates.
  

Queries:

• Students Extended: This was also in the original Students template database. It is based on the Students table and adds expressions to concatenate First and Last name in a couple of ways (eg., Greg Farmer and Farmer, Greg)
  
  Current Class: A Select query of the Class table with criteria to determine which class is current based its start and graduation dates. The class number from this query is what I would like to use in the Student Details form as a default Class Number for new records.

Form:

• Student Details: this was the main original from in the Students template database. the Students Extended query is its record source. It has a header where the Student Name, Class Number (the field in question) and Status appear and currently 7 pages/tabs for General, Notes, Medical, Absence, Tests & Grades, Evaluations, Employment (an 8th for Counseling is future development). This is the form in which I would like to default the Class Number to that of the Current Class query.
  
  

[June7]

The DLookup you show will pull the first Class Number value it encounters in the Current Class query. Is this want you want? Is there only one record in that query? Could possibly just include that query in the form RecordSource and then the field will be available and DLookup not needed.

[rjgriffin46]

Reply to Post #7
Yes, this is what I want. There is only one record resulting from the Current Class query. Keep in mind, this is not like a High School, College or Trade school with multiple overlapping curriculum and classes. There is only one "class" or subject in each class, and the classes occur serially. So, for example, Class 2014-3 started 8/18/14 and ended 12/4/14. The next class, 2015-1, starts 1/5/15 and ends 4/23/15.

As I mentioned before, the Record Source for the form is the Students Extended query and includes a join to the Current Class query. In fact, I can use this relationship to limit the students to only those in the current class if I want to. The field is available to the Student Details form, but, per Post # 1, I still get the "#Name?" result when I try to use it for the Class control's Default value.

Reply to Post # 5:
("The Class Number is in Students record then I see no reason to also have it in StudentDetails. However, if you want to accommodate repeat students and you want history of their participation, then Class Number probably should not be in the Students record and some db redesign might be needed.")

Response: I think the Class Number has to be in the Student Table since it is needed to identify which class the student was in - I can't think of another way to do that. The Class record also has the Start and Graduation dates which are not stored in the Students table. Class number is in the Student Details form so it can be entered when creating new student records. The possibility exists that a student in a new class may have been a student who dropped out of a prior class. Its going to be pretty rare, though. We just create a new Student record, in which case we may have to change the name by 1 character (eg, add a middle initial). We couldn't re-use the original record unless, as I think you were suggesting, Class were separated from the Students table. For the small likelihood of that happening its probably not worth the re-design.

Thanks again for your insights!

[June7]

If you want history of repeat students then consider:

tblStudents
StudentID
LastName
FirstName
DOB
Address
Phone

tblClasses
ClassNumber
ClassStart
ClassName

tblStudentClasses
StudentID
ClassNum


Did you try in the textbox ControlSource: =[Class Number]

[rjgriffin46]

Thanks for ideas for repeat students.

Not sure I understand your last question ("Did you try in the textbox ControlSource: =[Class Number]"). The class number field in the Student Details form is a combobox, not a textbox. I've attached and Excel file with a couple screen shots of the form in Design view that will hopefully clarify.

Screen Capture -Student Details Class Number control.zip

[June7]

Sorry, lost track of what control type this concerned. If there is always only one active class, why use a combobox? Could just be a locked textbox and you control the input with DefaultValue and/or code. If purpose of combobox is to select filter criteria, then it should be UNBOUND and not used to edit record.

I don't see anything wrong with DLookup calc as DefaultValue, should work.

Debugging from images is hard. If you want to provide db for analysis, follow instructions at bottom of my post.


Advise no spaces or special characters/punctuation (underscore is exception) in naming convention.

[rjgriffin46]

Its not a filter.

There is only 1 "Current" class, but the form allows you to select students from prior classes and the Class Number control displays their Class Number. My only objective here was to provide a default = to the Current Class so that the administrator can enter all the Students for the new class without having to select the Class Number for each.

The whole database could use analysis. How much are you willing to do?

[June7]

You say the combobox is not a filter input but then you also say the form allows to select students from prior classes. How do you accomplish that if the combobox is not the filter input?

[rjgriffin46]

Sorry I am not explaining this well. You just scroll the drop down list till you find the student you want. It's sorted by class in descending order then last & first name ascending. With the current/most recent class appearing at the top of the list, and usually no more than 50 students per class, it works well. Even scrolling through the entire list of 1000 students is quite speedy. And, if you don't know the class, you can start typing a name in the box instead of dropping the list down and get a match.

Maybe this constitutes a filter in a sense but the drop down list is not filtered at all.

[June7]

I still don't understand purpose of the combobox.

Again, if it used to select student from list and find that student record on the form, it must be UNBOUND and have code behind the combobox. If you use the intrinsic search/filter tools from the ribbon or right click menu, cannot select student from list because that would change the record.