Create form base on query and print a report

[BSC5905]

I'm currently building a database to track employees that have been trained on certain test procedures. So that I can track who has been qualified to perform certain test. I have created three tables. They are, Table of Technicians, Table of Test Methods, and Table of Training Records.

tblTechnician – (EmployeeID is my primary key)
EmployeeID – AutoNumber
FirstName – txtbox
LastName– txtbox
JobTitle – txtbox
ShiftID – txtbox
ActiveEmployee - Yes/No

tblTestMethods – (TestMethodID and TestMethodRevision together makes my primary key)
TestMethodID – txtbox
TestMethodRevision – txtbox
TestMethodTitle – txtbox


tblTrainingRecords – (TrainingRecordID is my primary key)
TrainingRecordID - AutoNumber
EmployeeID – Lookup combo box from tblTechnician
TestMethodID – Lookup combo box from qryTestMethods
TrainingDate – Date/Time


I have also created two Queries. Query of Test Methods and Query of Training Records.

qryTestMethods
TestMethodID
TestMethodRevision
TestMethodTitle

This query was created to concatenate the two fields that make up my primary key from tblTestMethods (TestMethodID and TestMethodRevision). The concatenated field data is then used to populate the TestMehodID field in tblTrainingRecord as a look up combo box.

qryTrainingRecords
EmployeeID
LastName & FirstName - (concatenation from tblEmployees)
TestMethodID -
TrainingDate
JobTitle
Shift


I want to create a form based on my qryTrainingRecords. On the form I want to use a lookup combo box which filters the TestMethID. I would also like to add two additional command buttons. One that will allow me to preview the report before printing and one to print the report.

Your help would be greatly appreciated.

Thank you.

 

[PHV]

And the problem is ? ...

 

[BSC5905]

How do I to create a form based on my qryTrainingRecords. On the form I want to use a lookup combo box which filters the TestMethID. I would also like to add two additional command buttons. One that will allow me to preview the report before printing and one to print the report.

 

[Pampers]

Just create a form (by using the wizard) and set the control source to your query. Drag en drop your fields into the form.

Create the combox and set a filter in the AfterUpdate-event of the combox.

About the printbuttons (you probably only need one), put vba-code like this behind it: Docmd.Openreport "Your Report", acPreview. (In this case you can also use the wizrd to create the button....) If the Preview looks ok, right click the report and it will give you a print option.




Pampers
There is only one way to change a diaper - fast

 

[TheAceMan1]

How are ya BSC5905 . . .

[blue] . . . This query was created to concatenate [purple]the two fields that make up my primary key[/purple][/b][/purple] from tblTestMethods (TestMethodID and TestMethodRevision) . . .[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

How are ya BSC5905 . . .

Hit submit too soon.

[blue]. . . This query was created to [purple]concatenate the two fields that make up my primary key[/purple] from tblTestMethods (TestMethodID and TestMethodRevision) . . .[/blue]

[blue]Compound keys[/blue] have every tendency to complicate a db. Before yo get to deep . . . have a look below & make every effort to get away from these types of keys (I'm not saying you don't need them . . . but you've not proven you do!). Its an unwelcome comodity for any db if nothing else can be done. As a matter of reference . . . see the following:

Fundamentals of Relational Database Design

Normalizing Tables

Table Relationships

See Ya! . . . . . .

 

[BSC5905]

I created the form but I do not know how to create the combo box and set a filter in the AfterUpdate-event of the combo box. Could you please give me more detail on how to do this.

Thank you very much for your assistance.

 

[Pampers]

You can make the combox by using the wizard.

Once you created that combox, set a filter on the afterupdate of the combox. In the afterUpdateEvent-property, set the following code.

Me.Filter = "YourTableID = " & YourCombox (nummeric)
   Me.FilterOn = True

Pampers
There is only one way to change a diaper - fast