Assist requested. Providing a Query with criteria from a field on a form. (1 Viewer)

ChoiceTom

New member
Local time
Today, 00:41
Joined
Aug 6, 2020
Messages
9
Goal:

To see a list of child-records populate a Listbox placed in a Tabcontrol on a form, with the Parent record information from a different table populating the main form fields.

Problem Statements:

1) When the Training Form first loads, I'm getting a Query Parameter dialog asking me to input a value. Not a feature, the form should simply execute awaiting person selection from a form field, then the query should execute using the identified field for Criteria.

2) Selecting different Employee Names, which causes the Empl ID field next to it to change properly, isn't causing anything to happen down in the Tab control List Box. Nothing happens.

Note: If I go into the Query and manually type in an Employee ID (e.g. 165) then run the query, it works. If I leave 165 in the Query criteria field and open the Form, it works: The tab control is listing training records for ID 165 only. However, if change the Query Criteria to: FORM![TrainingDash21]![txtTraineeID], I'm back to a parameters request when the form first loads, followed by non-responsive to changes on the Form during use.

Tables:

The "parent" record is a basic person-table with an Employee ID (numeric) and Name, and a several other non-material info fields. The Empl ID is a company provided number, it's not the auto-number from Access.

The child-records are in the EmplTrainLog table. The link between Employees and Training records is the Employee ID field. This table simply has the EmplID as the first field after the access auto-number, followed by several simple info fields identifying the type of training, start and end dates, etc.

Form Setup:

A Login dialog for the Instructor/Trainer. This works fine, and passes data to the Training form assuming the login/password is correct. The primary training form appears with the Trainer's Employee Name & ID.

Middle of the primary training form: Two fields - A ComboBox listing the NAMES of employees from the Employee table to select from, and a text-field that is updated with the EmployeeID for the employee selected. THIS WORKS. I can pick any name I want, and the "ID" field always updates to the correct ID number of the Student.

At the bottom of the Form is a Tab Control, the first Tab titled "Training Log". What I want to have dynamically happen is: Each time an Employee is selected from the Combo Box in the middle of the form and the field next to it updates/changes to that person's ID, a list of the existing Training Records for that person populates the Listbox in the Training Log tab.

Row Source for the Listbox is set for the Query in question.

The Query

Super basic, about 5 fields/columns. The first column is the EmplID containing the criteria for selection out of the entire table.

Again, if I manually type "165" as example, in the query criteria, the listbox in the tab control on the Form shows the relevant records, but of course is non-responsive to data changes on the Form.

Suspicions:

I don't have the structure right somewhere to tie the update or change of the form field triggering the Query when I expect it to.

I don't have the correct grasp of form initiation control, followed by dynamic updating control of the tab-control list box based on changes to the contents of the "txtTraineeID" form field. When I first open the whole form I'm getting a parameters entry dialog box if I've got the field reference in the query criteria area.

Apologies if this was too wordy, I was trying to anticipate the basic questions.

Bottom Line: Form with a Combo-box paired with a form-field. Combo-box shows selections from an Employee Table. Pick one, and the ID field to the right reflects the EmplID field for that person.

Tab control with a List box of fields, row-source set for a Query. What should happen is each time I select a new Employee, and the ID changes, the tab control should dynamically update with only Training records for that employee.

I have played with variations of refreshes, openquery, etc., and no change. Probably a real basic issue I'm missing.

Thanks in advance.
 

bob fitz

AWF VIP
Local time
Today, 04:41
Joined
May 23, 2011
Messages
4,717
Can you post a copy of the db with just a few fictitious records to illustrate the problem
 

hipromark

New member
Local time
Yesterday, 23:41
Joined
Oct 20, 2020
Messages
3
By this information FORM![TrainingDash21]![txtTraineeID] I understand that you are trying to retrieve the value from a text box to use this in your criteria, try to save the value in an actual field from a table instead and then use the field in your criteria.

Also use a Macro with saveRecord, then gotoControl (move the cursor to a control on the form that will show the results) and then sendKeys F9 to refresh.
 

Minty

AWF VIP
Local time
Today, 04:41
Joined
Jul 26, 2013
Messages
10,355
When the Training Form first loads, I'm getting a Query Parameter dialog asking me to input a value
This suggests that the missing query parameter is mistyped with regard to what you think it should be (presumably it's a reference to the form control?)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:41
Joined
May 7, 2009
Messages
19,169
your Criteria should be using Forms not Form:

FORM
![TrainingDash21]![txtTraineeID]

should be:

FORMS![TrainingDash21]![txtTraineeID]
 

Users who are viewing this thread

Top Bottom