Passing the name of a FIELD to a query from a combobox (1 Viewer)

melodyh40

New member
Local time
Today, 09:59
Joined
May 13, 2019
Messages
6
Hi!

I wish to allow to the user to select the name of a FIELD in a query from a combo box and have the query run and show only that field.

That is, a user could choose MATH101 from a combo box, which is the name of a yes/no field in a query. I added a button that assigns the name MATH101 to a temporary variable, then opens the queyr which references the MATH101 in the Field: line of the query. Of course, that just displays MATH101 in the query, instead of looking up the yes/no value for the field called MATH101.

Is there any way to dynamically make access understand that I only want to see that field in my query? It is a huge table and I want my admin to only see that one field of her choice.

Any tips would be super! Or, alternatively, let me know what to search for in this forum.

Thanks!

Melody
 

isladogs

MVP / VIP
Local time
Today, 16:59
Joined
Jan 14, 2017
Messages
18,186
I'm not clear what you are asking but combo box record sources have three options: Table/query, value list and field list. Perhaps the last of these will do what you want.
 

June7

AWF VIP
Local time
Today, 08:59
Joined
Mar 9, 2014
Messages
5,423
No, inclusion of a field cannot be dynamic via an expression in query. Field is either in the query or it isn't. This is not the same as constructing a field with an expression. Example:

Data: IIf(Forms!formname!comboboxname = "Math101", [Math101], Null)

The field name Data is fixed in the query, not dynamic. The name does not change even though the data displayed is dynamic via the conditional expression.

How many fields will user choose from?

Users should not work directly with tables and queries, just forms and reports. VBA code behind form can manage display of controls.

Actually sounds like need to normalize data structure then apply filter criteria to retrieve the "Math101" records.

Otherwise, requires VBA code to modify the query object.
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Today, 09:59
Joined
Oct 29, 2018
Messages
21,358
Hi Melody. Sounds like you may have "repeating groups" in your table if you have multiple Yes/No fields, which is probably against "normalization" rules.
 

June7

AWF VIP
Local time
Today, 08:59
Joined
Mar 9, 2014
Messages
5,423
As I said, code behind form. That example probably uses code like:

Me.Controls("NameOfControl").ColumnHidden = Me.Controls("NameOfControl").Name <> Me.comboboxname
 

Mark_

Longboard on the internet
Local time
Today, 09:59
Joined
Sep 12, 2017
Messages
2,111
Hi Melody,

If you have a check box for "Math101", I'm guessing you have a check box for "Math102"also? So what you are doing is storing DATA as FIELD NAMES. What you should be doing is storing this in a child table which would be easy to query.

Your child table would probably have
StudentID - Primary key from Student Table
ClassID - Primary key from either a "Class" table or a linking record that allows you to set up an ED plan for the student based off of a Class table.
Semester - When the student took the class
Grade

This will allow you to track, per student, when they took a class (allowing for repeats) as well as their grade.

This is a very simple version, but probably far more useful than having "MATH101" as a check box in a record.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:59
Joined
Feb 28, 2001
Messages
26,999
Skip the implied code, skip the names of control types, and tell us in descriptive terms what you were trying to accomplish. As has been noted, there is some question as to whether your setup is appropriate for the action you appear to be implementing.
 

melodyh40

New member
Local time
Today, 09:59
Joined
May 13, 2019
Messages
6
Thanks for the responses, everyone! Yes, I didn't structure this well. However, I don't have time to start over. What I decided to do was to just show the query with all the courses and freeze the left columns with the student info. Then, the Admin can just scroll over to get to whatever course she wants to enter and click her way down (exactly like she would do in MS Excel). Not super elegant, but she shouldn't have to use it often enough to worry about it. Thanks again, everyone!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:59
Joined
Oct 29, 2018
Messages
21,358
Thanks for the responses, everyone! Yes, I didn't structure this well. However, I don't have time to start over. What I decided to do was to just show the query with all the courses and freeze the left columns with the student info. Then, the Admin can just scroll over to get to whatever course she wants to enter and click her way down (exactly like she would do in MS Excel). Not super elegant, but she shouldn't have to use it often enough to worry about it. Thanks again, everyone!
Hi. Glad to hear you found a working solution. Good luck with your project.
 

melodyh40

New member
Local time
Today, 09:59
Joined
May 13, 2019
Messages
6
Hi Melody,

If you have a check box for "Math101", I'm guessing you have a check box for "Math102"also? So what you are doing is storing DATA as FIELD NAMES. What you should be doing is storing this in a child table which would be easy to query.

Your child table would probably have
StudentID - Primary key from Student Table
ClassID - Primary key from either a "Class" table or a linking record that allows you to set up an ED plan for the student based off of a Class table.
Semester - When the student took the class
Grade

This will allow you to track, per student, when they took a class (allowing for repeats) as well as their grade.

This is a very simple version, but probably far more useful than having "MATH101" as a check box in a record.

Your assessment is exactly bang on. I was pretty much an idiot when I sent it up originally and, for that database, there's no turning back now.

Just if I can impose upon you for my general education, how could I have done this correctly?

Here's the structure I should have had, I think:

tblEmployeeInfo
EmployeeID - Employee ID number - number field - Primary Key
LastName - Employee last name - text field
FirstName - Employee first name - text field

tblCourseInfo
CourseID - Course identification number - number field - Primary Key
CourseName - name of the course - text field

tblTrainingTaken (this is, I think, the child table you mention)
EmployeeIDTraining (related to EmployeeID in tblEmployee)
CourseIDTraining (related to CourseID in tblCourseInfo)
CourseTaken - has the course been completed? - yes/no field
CourseTakenDate - the date that the course was completed - date field

The interesting thing about this is that EVERY employee has to take EVERY course in tblCourseInfo as a condition of employment. So, I want tblTrainingTaken to contain a record for every employee for every course. Adding a new employee to tblEmployeeInfo would then automagically add a record for every single course in tblCourseInfo so that the Admin could then just access their training record on a single form and check whatever off. I couldn't figure out the relationships.

Does this make any sense? I'm sure I am going to encounter this in the future so now is the time to learn! If no one has the time to answer, that's OK. It's not critical.

Thanks again!

Melody
 

Mark_

Longboard on the internet
Local time
Today, 09:59
Joined
Sep 12, 2017
Messages
2,111
I'd say you are half way there with your thinking!

For myself, how I'd do this is the same table's you've laid out, but the way I'd set up the user interface is a little different.

Make a query on tblCourseInfo. Join it to tblTrainingTaken but EXCLUDE every record in TlbCourseInfo where there is a matching entry in tblTrainingTaken for a given employee ID. This lists ONLY those courses one employee hasn't taken.

Use a continuous form to display ONLY those records that need to be entered. Prompt for a date. I'd use a button to then create the linking record with the date supplied.

Oddly I'm working out an example app for how to do this, but I haven't had a chance to finish it up. Once I do, I'll post it.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 11:59
Joined
Feb 28, 2001
Messages
26,999
You asked how we might do this.

Actually, what you want to look up is a "Junction Table" which is how you "fake" a many-to-many relationship. You have many employees who must take many courses, so this is classic many-to-many. The junction table is a child to TWO parents - employees and courses. An entry in the junction table lists the course ID, employee ID, date taken, and grade - and anything else needed that doesn't clearly go into the course table by itself or the employee table by itself.

In the SET theory sense, a junction table IS the junction (overlap) between two tables each containing an independent entity (a person or a course in your case). It shows (or lists, if you prefer) how the persons and courses interacted (overlapped). That means that this record (perhaps an "attendance" record?) is DEPENDENT to both tables. Which means that relational integrity could be used so that the course ID depends on the course table and the person ID depends on the person table. TECHNICALLY this junction table doesn't require any indexes (but if you establish RI, the parent tables DO require PK-type indexes). IF you want to establish a unique index on it, it would have to be a compound index on Person, Course, and perhaps DateTaken (since if it is possible to pass, it is also possible to fail and thus need a re-take.)

From this junction table, you can JOIN to a list of classes and then look up a personnel ID to see the classes the person HAS taken, or JOIN to a list of persons and then look up a course to see who has taken it. BUT... there is this neat little syntax such that you can ask this question:

Code:
SELECT PersID, PersName FROM Persons 
WHERE NOT 
( PersID IN ( SELECT PersID From PersCourse WHERE CourseID = 'Math101' ) ) ;

In English, that is "Tell me every person who is NOT listed as having taken Math101."

Now, the beauty of this approach is that if you add a course, you add that data to the course table and the queries INSTANTLY return everybody as needing to take that new course. If you add a person to the person table, the queries INSTANTLY return every course that the new person will have to take, only depending on how you structure that sub-query.

Which means you don't change table STRUCTURE at all. No new fields for new courses. Just new records darned near as many as you need forever (or until you blow out the size limit).
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 16:59
Joined
Sep 12, 2006
Messages
15,613
I presume you don't want to just see one field from a table. That would be meaningless. I presume you want a core set of fields, and then some additional user selected fields.

So it's easy to present a list (rather than a combo box) of the fields, and then produce a select query to include just the selected fields.
 

June7

AWF VIP
Local time
Today, 08:59
Joined
Mar 9, 2014
Messages
5,423
How would you accomplish producing a select query to include just the selected fields?
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 16:59
Joined
Sep 12, 2006
Messages
15,613
iterate the selections, and concatenate the fields to a SQL statement. It's probably pretty well the same thing the the Query Designer does behind the scenes.

select [field1], [field2], [field3] from table1
 

June7

AWF VIP
Local time
Today, 08:59
Joined
Mar 9, 2014
Messages
5,423
Right, VBA code to build SQL statement. Then do what with it? Create a new query object? Basically the option mentioned in post 3.

Could set a form RecordSource and then also have to change textbox(es) ControlSource.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 16:59
Joined
Sep 12, 2006
Messages
15,613
this is aircode, so the syntax may be slightly off on these.

Code:
on error resume next
'delete any existing query first.
currentdb.querydefs.delete("xxTemp")
on error goto 0  ' cancel error handler

currentdb.CreateQueryDef("XXTEMP")
currentdb.QueryDefs("xxtemp").sql = "the sql string"
docmd.openquery "xxtemp"
 

sxschech

Registered User.
Local time
Today, 09:59
Joined
Mar 2, 2010
Messages
791
Expanding on the air code from previous post. Put this into a module. This function will Edit an existing query or Create a new query if it doesn't exist.
Code:
Function EditQryDef(ByVal stQueryName As String) As DAO.QueryDef
'http://www.tek-tips.com/viewthread.cfm?qid=1758828
'20151119
    On Error GoTo NewQueryDef
    
    Set EditQryDef = CurrentDb.QueryDefs(stQueryName)
    
QryDef_Exit:
    Exit Function
    
NewQueryDef:
    Set EditQryDef = CurrentDb.CreateQueryDef(stQueryName)
    Resume QryDef_Exit
End Function

Here is an example of how to call it.

Code:
Sub qrytesting()
    Dim stsql As String
    Dim qd As DAO.QueryDef

    stsql = "SELECT FieldName1, FieldName2 " & _
                        "FROM YourTable_Query "
                Set qd = EditQryDef("qryYourQueryToModify")
                qd.SQL = stsql
End Sub
 

Mark_

Longboard on the internet
Local time
Today, 09:59
Joined
Sep 12, 2017
Messages
2,111
I was pretty much an idiot when I sent it up originally and, for that database, there's no turning back now.

I wouldn't say Idiot, not by a long shot. Not versed in data normalization, but if it accomplishes what you need, definitely not an idiot.

It also isn't to late to go back and update. I'd check to see if there is a requirement for each of the classes to actually have a "Completion date" or a "Class Number" or other such unique identifier. If this is a requirement, HR will normally want enough to identify and verify compliance.

You CAN create a query your existing table to add the new child records. It would insert in to the child table each employee number and a "ClassID" that you hard code in, but you would use a where clause for the matching field in your existing table to drive this.

For future design needs, do any of the classes employees have to take expire? I deal with Nurses, so we have to track medical training and health records that often need to be renewed yearly or every other year.
 

Users who are viewing this thread

Top Bottom