# Solved: Access Database



## computerman29642 (Dec 4, 2007)

I am planning/trying to build a database that will track the dates certain classes were taken by users, and if the classes were passed or not.

I am very new at Access Databases so any advice, suggestions, opinions, or help anyone can offer will be greatly appreciated.


----------



## jimr381 (Jul 20, 2007)

Check out the templates that MS has online. There is one that should fit your needs there.


----------



## computerman29642 (Dec 4, 2007)

Thanks Jim. I will give that a go.


----------



## computerman29642 (Dec 4, 2007)

The database I am trying to build is based on information in Excel. Is there a way to migrate the Excel workbook into Access.


----------



## jimr381 (Jul 20, 2007)

"File">>"Get External Data">>"Import."


----------



## computerman29642 (Dec 4, 2007)

I have started to build the database. It is nothing big, but if it would help I will be more than happy to attach the file. If anything else, maybe someone could let me know if I am heading in the right direction.


----------



## jimr381 (Jul 20, 2007)

Feel free to attach it and I will eyeball it. I will be in and out all day while I work on my "Lunch and Learns."


----------



## computerman29642 (Dec 4, 2007)

I have attached the database. I really appreciate the help.


----------



## jimr381 (Jul 20, 2007)

A couple of things. If you are going to have the students take multiple tests then you will want to separate the students from the tests and link them together in a many-to-many relationship. Since some of those fields are repeated multiple times then you might want to setup lookup or drop-down lists that allow for easier selection and entry of data into those fields.


----------



## computerman29642 (Dec 4, 2007)

I will play around with the db a little more, and attach it again for viewing.

Thank you for the help and advice.


----------



## OBP (Mar 8, 2005)

This is one of the reasons that I do not particularly like "Templates" as they hardly ever do what you actually want them to do.
This results in you spending more time trying to understand how to modify it than if you built it up from what you think/know you need, (with a little help from your friends on here of course).


----------



## computerman29642 (Dec 4, 2007)

Hi Tony,

I tried to download a template to modify, but once I started and the template stopped working I realized I was just not knowledgeable enough to do so...LOL!

So, I started to try and build the database from scratch.


----------



## OBP (Mar 8, 2005)

Hi, one of the things that you want to do is to write down what you want "Out" of the database, that should lead you to what you will need to put "In" it.
Then as Jim says it is a case of getting the right data in the right tables with the correct relationships.
After that it is a case of adding the "frills" like Combos etc.


----------



## computerman29642 (Dec 4, 2007)

I have users who must get certified in order to run certian tests. Once they have been certified, I would like to place in the db the information regarding the test they are allowed to run, and the date the certification was achieved. Then have a search function to be able to search in different ways.


----------



## OBP (Mar 8, 2005)

I think the best way to learn is for you to do what you think is right and then post it here so that Jim, slurpee, Jimmy and myself can advise what is right/wrong and why.
That way you get to understand the principles of database design rather than just being given it.
However, if you just want a quick database and don't mind how it works then we could build it for you.
So make your mods and Post it again and we will have a look and offer some advice.


----------



## computerman29642 (Dec 4, 2007)

I want to learn, so I will continue to try to build the db, and then re-post it later for you all to review.


----------



## computerman29642 (Dec 4, 2007)

I have done more work on the database. I would like you all to take a look and let me know if I am on the right track.

Any suggestions, advice, or corrections would be greatly appreciated.


----------



## jimr381 (Jul 20, 2007)

Is the employeeInfo table supposed to be the table that marries the test and employee information in it? I am confused why you have the last table that just has date information in it.


----------



## computerman29642 (Dec 4, 2007)

Yes, the EmployeeInfo table is suppose to be the table that connects the other tables. Should the date (This is the date the certification was obtained) be placed in the Info Table?


----------



## jimr381 (Jul 20, 2007)

I would place it there as well. Label one as certification date and one as class date.


----------



## computerman29642 (Dec 4, 2007)

I will make that change. Is there a way to create a form to enter a new record with the data going to different tables? Code will have to be written for that to happen...correct? I could not use a query....correct?


----------



## jimr381 (Jul 20, 2007)

What do you mean data going into different tables? Are you interested in igrating data that you have already entered into a different field?


----------



## computerman29642 (Dec 4, 2007)

What I am trying to do is create a data entry form. So, once a user has completed a class and is certified, then I would be able to open a form, and fill-in the users name, the parameter, method, matrix, and date.


----------



## computerman29642 (Dec 4, 2007)

I bleieve I figured out the data entry issue. I will re-post the database for you to review.


----------



## computerman29642 (Dec 4, 2007)

I have attached an updated db.


----------



## jimr381 (Jul 20, 2007)

Your info table is referring to your class information. I would call it tblClasses. This table should have basic class information. You do not want to do multiple records where you will type out the same class name each time, so you will put the class start date and end date within the junction table which joins the students with their classes. 

The junction table should have a class start date and end date field as well as a certification date field.


----------



## computerman29642 (Dec 4, 2007)

I only need to track when the user completed the class. I do not need to know the dates of classes. Should I place a Date field with the junction table (tblEmployeeInfo)?


----------



## computerman29642 (Dec 4, 2007)

I have attached a revised copy of the db with the modifications I believe that were suggested.


----------



## jimr381 (Jul 20, 2007)

That is starting to look better. I would like that date field changed to ClassDate and a second date field be inserted for Certification date. I typically setup lookup lists in my junction table which reference the main or parent tables. This saves me time when selecting from the list.


----------



## computerman29642 (Dec 4, 2007)

I have renamed the Date field to ClassDate and added a CertificationDate field.


----------



## jimr381 (Jul 20, 2007)

With these changes we can do comparisons between the 2 fields. We could also have a third date field which is a certification due date as well. With the certification due date field we can run weekly reports to see whose certification test needs to be taken within the next month.


----------



## computerman29642 (Dec 4, 2007)

I am going to add a Certification Due Date Field to the junction table.

I need a way to add new entries (new employees, new date an employee certified, etc...). Then a way to search by different fields (Employee Name, Parameter, Method, etc...) to determine who is certified to run a certain test.

I have been trying to work on the data entry form, but a new record is created each time. Is there a way to just update, or do we want a new record each time?


----------



## jimr381 (Jul 20, 2007)

Aye you could setup another button on the switchboard that opens the form in edit mode as well. In edit mode they can add/delete/edit records.


----------



## computerman29642 (Dec 4, 2007)

I guess update is the wrong word. Will that work to append a student's record? For example, I really did not want the user to be entered more than once, and I did not really want to copy over the old date with new date.


----------



## jimr381 (Jul 20, 2007)

So you have already been doing data entry in this database? If you already have data in the wrong tables, then try using the update query to update the new fields from data from the old fields.


----------



## computerman29642 (Dec 4, 2007)

I have been thinking about starting from scratch. I will re-create the table designs like you have suggested, and then begin to try and create the forms.


----------



## computerman29642 (Dec 4, 2007)

I have attached an updated copy of the db.

The attached copy has data entered.


----------



## OBP (Mar 8, 2005)

What is wrong with your current design, at first glance it looks OK to me.
What do you want doing with it?


----------



## computerman29642 (Dec 4, 2007)

I guess what I am going to do is take a step back, and look at the picture as a whole. I was trying to do too much with the db at one time. I need to take the db piece by piece. I believe with all of you helping, the table design is fine. Now, I am going to work on the piece to get the data entry form working properly.


----------



## OBP (Mar 8, 2005)

What do you want it to do?


----------



## computerman29642 (Dec 4, 2007)

The db is going to be used to keep track of users that are certified to perform certain tests. So, I need to be able to input the data each time a user becomes certified or loses certification. I also what the db to have a search function so that it will be easy to determine what user can perform what test.

For Example, John Doe became certified to perform the follwoing tests: Test1, Test3, Test4. The date certification obtained 05/28/2008.

Do you kind of understand what it is I want the db to be able to do?


----------



## OBP (Mar 8, 2005)

Yes that won't be a problem, but as you want to learn this stuff on the way Jim and I can suggest how to proceed.
So would you like a Tabbed Main Form to enter your Employee data and the Test data in to?
If so I would suggest that you use the wizard to create a Query for you Imployee table and Sort By Lastname First and then Firstname, Queries sort form the left, so whatever order you put your field columns in the query will sort them.
Next use the Form Wizard to create an Employee Main Form.


----------



## computerman29642 (Dec 4, 2007)

In your opinion, would a Tabbed Main form be the way to go?

The query will only contain the EmpID, FirstName, & LastName fields from the Employee Table...correct?


----------



## OBP (Mar 8, 2005)

Well it has some advantages, like bringing all the data in to one place, but you will need an Employee Main Form whether it is tabbed or not, it is like the frmTest that you have now.


----------



## computerman29642 (Dec 4, 2007)

I have created the query. It contains the EmpID, FirstName, & LastName from the Employees table. 

I have the Employee Main form created as well.


----------



## OBP (Mar 8, 2005)

OK, Copy and paste it with a new name like Tabbed Mainform, that way you can try both ways to see which one you prefer.
Then create a Query and Form for the tblEmployeeInfo Table.
I have to go now so I will speek to you tomorrow on putting the forms together.


----------



## jimr381 (Jul 20, 2007)

A form with a subform will work out as well. You will have the employee information at the top with the relate junction table data at the bottom. I will be here for another 1.5 hours if you want to post what you have.


----------



## computerman29642 (Dec 4, 2007)

I have attached a copy of the database. I really appreciate all the help that has been given.


----------



## computerman29642 (Dec 4, 2007)

Would the subform contain the information in the junction table or the info table?


----------



## jimr381 (Jul 20, 2007)

Are you more interested in the what a certain employee can test for or seeing the tests and seeing what employee can administer them? If would contain the information in the junction table, but it also could contain the info from the info table as well.


----------



## computerman29642 (Dec 4, 2007)

I believe right now it would be seeing the tests and seeing what employee can administer them.


----------



## computerman29642 (Dec 4, 2007)

I have created the form using the fields from the employee and employeeinfo tables.


----------



## jimr381 (Jul 20, 2007)

Do you want to post what you have done with it so far, so we can take a look at it?


----------



## computerman29642 (Dec 4, 2007)

I have attached a copy of the database.


----------



## OBP (Mar 8, 2005)

Well that seems to be working OK, I have added some Useful item to the Form and also changed the Subform to Forms - Continuous Mode instead of Datasheet.
That allows the ID fields to be Hidden using Visible = "No".
I have also added a Find an Employee Combo for when you have lots of records.


----------



## computerman29642 (Dec 4, 2007)

I have taken a look at the database. It looks really good. Thank you.

Is there a way to have an 'All Employees' in the 'Find an Employee' combobox, and have all records viewable at one time?


----------



## OBP (Mar 8, 2005)

All of the Employees are there, you can use the Combo to "Find" them or the Record Selectors to step through them
If however you mean view them all at the same time, like in the subform, then the answer is not on this type of form, you can't have a Subform on a Form in "Continuous Mode".
If you had another Form, just for Employees or a Tabbed Mainform with another Tab on you could see them all on there.
Another alternative is to just have one Form showing both Employees and Info at the same time, which is just basically your Subform.


----------



## computerman29642 (Dec 4, 2007)

I have attached an updated copy of the database.

I have added an 'AddEmployee' form. I have also added a report.

I would like to create a form that I am able to search by the test, and then print a report of all the users that can perform that test. Is this possible?

Any suggestions regarding the report or forms, please let me know.


----------



## jimr381 (Jul 20, 2007)

Aye you would create a query and a form. The form will have a drop-down list of all the tests. The query will reference the drop-down list in the criteria section for the test name. Finally you will have a button that runs the query on the form. 

When the query is run it grabs the data from the drop-down list in the form and passes it into the query. 

If you want it to be in a report format you will just make a report based upon the query.

I have a database I just created if you want to see an example of this in action.


----------



## OBP (Mar 8, 2005)

Of course it is possible , but it I will make you do it .
Create a Query based on your Info Table, with the Parameter sorted "Ascending".
Then create an "Unbound Form", that is a Form that does not have a Record Source, you can use the "Create Form in Design View" to do this.
Add a Combo Box based on your nice new Query so that you can select a Test, include the InfoID and Parameter in the Combo in that order.
When you have got that far give us another post, with the database attached.


----------



## computerman29642 (Dec 4, 2007)

I have the following code behind the 'AddEmployee' form.


```
Private Sub cmdClose_Click()

    Dim strMessage As String

    If IsNull(Me.txtLName) = True Or IsNull(Me.txtFName) = True Then
        strMessage = MsgBox("All textboxes must contain a value. Click OK to enter missing data. Click Cancel to close without saving.", vbOKCancel)
    Else
        DoCmd.Close
    End If
    
    If strMessage = vbCancel Then
        Me.Undo
        DoCmd.Close
    Else
        Exit Sub
    End If
    
End Sub
```
When I click the close button, I get a 'Type Mismatch Error'.


----------



## jimr381 (Jul 20, 2007)

I usually have it place the cursor in the field that is missing the value instead of doing undo. I do not do VBA, but I see you have an Exit and and End sub in there. Is that what you want?


----------



## computerman29642 (Dec 4, 2007)

You may have something there regarding the Exit Sub and End Sub. Let me take another look. Thanks.


----------



## computerman29642 (Dec 4, 2007)

I have attached a copy of the database with a new form and query created. The new form is named frmInfo.


----------



## jimr381 (Jul 20, 2007)

You were missing a submit button which will run the query with the drop-down list.  Also the qryInfo query needs to reference the drop-down list within the frmInfo in the criteria section for InfoID field.


----------



## jimr381 (Jul 20, 2007)

Check out the attached file for an example.


----------



## OBP (Mar 8, 2005)

Jim, sorry I didn't see you post before my last Post  I was busy looking at the database and writing the instructions.

With regards to the VBA error, what does it highlight in the code if you click "Debug" when you get the error message?


----------



## computerman29642 (Dec 4, 2007)

I have changed the code behind the 'frmAddEmployee' form tolook like the following...


```
Private Sub cmdClose_Click()

    Dim strMessage As String
    Dim Add_FName As String
    Dim Add_LName As String
    
    Add_FName = txtFName.Text
    Add_LName = txtLName.Text
    
    If Add_FName = "" Then
        strMessage = MsgBox("You have have not entered a First Name.", vbOKOnly, "ENTRY ERROR")
        Me.txtFName.SetFocus
        Exit Sub
    ElseIf Add_LName = "" Then
        strMessage = MsgBox("You have have not entered a Last Name.", vbOKOnly, "ENTRY ERROR")
        Me.txtLName.SetFocus
        Exit Sub
    Else
        DoCmd.Close
    End If
```
Now, I get some type of SetFocus Error.


----------



## computerman29642 (Dec 4, 2007)

The debug highlights the following:


```
If strMessage = vbCancel Then
```


----------



## jimr381 (Jul 20, 2007)

Where are you loading this procedure? In the before_update section?


----------



## OBP (Mar 8, 2005)

To overcome the "'Type Mismatch Error'" do not use "Dim strMessage As String" use just 
Dim strMessage

The setfocus error means that the Field Name is not correct or the field is not accessible, when you debug, which Setfocus line does irt highlight?


----------



## computerman29642 (Dec 4, 2007)

Setfocus Error...debug is highlighting the following line:


```
Add_FName = txtFName.Text
```
I am running the code from the close button procedure.


----------



## OBP (Mar 8, 2005)

This is the code that you need 

Dim strMessage
If IsNull(Me.txtFName) Then
strMessage = MsgBox("You have have not entered a First Name.", vbOKOnly, "ENTRY ERROR")
Me.txtFName.SetFocus
Exit Sub
ElseIf IsNull(Me.txtLName) Then
strMessage = MsgBox("You have have not entered a Last Name.", vbOKOnly, "ENTRY ERROR")
Me.txtLName.SetFocus
Exit Sub
Else
DoCmd.Close
End If


----------



## computerman29642 (Dec 4, 2007)

The code worked perfectly....Thank you!

I guess it's back to working on the search form with a report.


----------



## OBP (Mar 8, 2005)

Did Jim post you something?


----------



## computerman29642 (Dec 4, 2007)

Yes, he posted a db that had an example. I was going to use it as a guide if I could nto figure it out for myself.


----------



## computerman29642 (Dec 4, 2007)

Jim,

I am looking at the db you posted. When I try running a monthly invoice from the search form, the customer name does not appear on the form. There is just a '# Error' in the name field. 

Do I need to modify anything on my end?


----------



## jimr381 (Jul 20, 2007)

You need to use the form that I have created. Try the second option down within the switchboard.


----------



## computerman29642 (Dec 4, 2007)

I am selecting the second option from the switchboard, selecting a customer name, selecting dates, and clicking the 'Monthly Invoice' option. When the report opens the customer name displays "#Error".


----------



## computerman29642 (Dec 4, 2007)

I have attached a copy of the updated db. On the frmInfo form, I have added a 'Report' button. I have also added the following code to the qryInfo query...


```
[Forms]![frmInfo]![Parameter]
```
When the frmInfo form is opned, nothing displays in the combo box. However, if you take the code above ou tof the query, the combo box will be populated.

What am I doing wrong?


----------



## OBP (Mar 8, 2005)

Forms]![frmInfo]![Parameter]
should go in the Report's Query, not the query for the combo or Form, is that where it is?


----------



## computerman29642 (Dec 4, 2007)

Yes....It is in the qryInfo query which the report is built off along with the qryEmp query. Do I need to make just one query with both the Emp table and Info table fields?


----------



## OBP (Mar 8, 2005)

The problem is that the query that you are using is also used by the Combo.
Either create aspecial query for the Report or for the Combo.

Got to go now.


----------



## computerman29642 (Dec 4, 2007)

I will take a look at that. Thanks.


----------



## computerman29642 (Dec 4, 2007)

I believe I have the frmSearch form working properly. The only thing that I noticed is that the commbo box on the frmSearch form displays the parameters twice in the list.

How can I get the tests to display only once? I have attached a copy of the db. Please let me know what else can be done to make it work better, look better, function better.


----------



## jimr381 (Jul 20, 2007)

For the monthly items to work in my database you needed to select a start and end date for the month. You probably selected a date range that did not have anything inputted for it.


----------



## computerman29642 (Dec 4, 2007)

That explains it...LOL! If you would not mind, could you take a look at the db I attached in my previous post and give me your critic?


----------



## jimr381 (Jul 20, 2007)

Do these things in the query that you are using to pull in the drop-down list values. Double click on the gray background to bring you into query properties. From here select "Unique Values." This option ignores the repeats and only shows the item once.

As an addendum, I would also drop the id number from the query that populates your lookup field and have the lookup be referenced in the corresponding section of the report query.


----------



## computerman29642 (Dec 4, 2007)

I set the Unique Values to yes in the query properties, but the tests are still showing twice.


----------



## jimr381 (Jul 20, 2007)

Check my addendum I did for my prior post.


----------



## computerman29642 (Dec 4, 2007)

I am not sure exactly what you are explaining.

Correct me if I am wrong...I need to remove the InfoID from the qrySearch query and add the InfoID to the qryReport query?


----------



## computerman29642 (Dec 4, 2007)

Do you want me to post an updated version of the db?


----------



## computerman29642 (Dec 4, 2007)

I believe I know why the tests are showing twice. Both users John doe and Jane Smith are able to perform Test2. If you look in th etblInfo table you will notice that Test2 is listed twice with two different InfoIDs.

How do I fix this?


----------



## jimr381 (Jul 20, 2007)

Remove the infoID from the qryinfo query. Apply the Unique Values property to this query. 

Setup a new drop-down list that references the qryinfo query for the values in the search form called frmSearch. 

Have your rptquery pull the value from your drop-down list and populate it in (place it) in the criteria field for the parameter field.


----------



## computerman29642 (Dec 4, 2007)

I have followed all the steps you provided. The frmSearch form works properly, but when the report opens, it does not display anything.

I have placed the following line in the Parameter field criteria of the qryReport query...


```
[Forms]![frmSearch]![Parameter]
```
*Addition:* I realized why it was not working. If I attach the updated database, would you review?


----------



## jimr381 (Jul 20, 2007)

Aye, I was going to ask you to post the database to see what is happening with it.


----------



## computerman29642 (Dec 4, 2007)

I have attached a copy of the database. Please let me know whatever you think that needs to be changed.


----------



## computerman29642 (Dec 4, 2007)

If you look at the rptParameter report, you will notice that the test are shown for each user. How can I get the test to only show once on that report? Is there a way to have the first entry in the combo box on the frmEmp form to display when the form is opened?


----------



## jimr381 (Jul 20, 2007)

Aye you did not group your report. You want to click on the "Sorting and Grouping" icon on the database toolbar. From here add the parameter in as the field to group by. Then set Group header to "Yes." From here add the Parameter field and it's label into the group header. The group header should be called Parameter Header.


----------



## computerman29642 (Dec 4, 2007)

Yep...That reolved that issue. Thank you.  Have you looked at the rest of the db yet?


----------



## computerman29642 (Dec 4, 2007)

Is there a way under "Startup" to have a custom menu where the user can either exit or print and that's all?

Is there a way to have the first entry in the combo box on the frmEmp form to display when the form is opened?


----------



## jimr381 (Jul 20, 2007)

You can disable the menu options and it basically only gives then print and exit. Look in the startup dialog box and mess around with some of the settings.


----------



## computerman29642 (Dec 4, 2007)

I unchecked everything in Startup, but when I open the database, they still can try to copy, paste, cut, etc... in the different menu items.


----------



## jimr381 (Jul 20, 2007)

For that you would probably have to create custom menus and attach it o the form on startup.


----------



## computerman29642 (Dec 4, 2007)

I will give that a shot. What about the combo box on the frmEmp form. Is there a way to hav ethe first entry display when the form is opened?


----------



## jimr381 (Jul 20, 2007)

I am not quite sure on that one. I am pretty sure you can set a default value for the drop-down list.


----------



## computerman29642 (Dec 4, 2007)

No big deal. I will keep playing around with it and see if I can figure it out.


----------



## OBP (Mar 8, 2005)

Paste this in to the frmEmp Form's On Current Event
Me.Combo8 = Me.EmpID


----------



## computerman29642 (Dec 4, 2007)

The code worked great...Thank You.


----------



## computerman29642 (Dec 4, 2007)

Would it be possible to have it where the user must select a test and enter a date range on the frmSearch form?


----------



## jimr381 (Jul 20, 2007)

What date are you searching for and why?


----------



## computerman29642 (Dec 4, 2007)

Let's say that a user was certified last year to run a test, but not this year. If I was to look up the date range of May 07 to May 08, the user would be viewable on the report. However, If I just look this year the user would not be on the report.


----------



## computerman29642 (Dec 4, 2007)

I have added a frmDateSelect Form to the database. I have attached an updated copy of the database.


----------



## jimr381 (Jul 20, 2007)

If you have a certification expiration date or a recertification date within the database, then you can just look for when those dates are greater than today's date within the query. You can even have it display the recertification date on the report as well.


----------



## computerman29642 (Dec 4, 2007)

The certification must be obtain every year. The Certification Due Date field is still in the frmEmployeeInfo table. Can that be used?


----------



## jimr381 (Jul 20, 2007)

Aye you would want to compare the certification due date to today's date and make sure the certification due date is greater than today's meaning the certification is still active. You do not want to have the certification due date be greater than or equal to today's because it might be expiring on today's date. 

I still recommend that you setup a query that will show you the certifications that will be expiring within the next month or two, so you can start hounding the people to get re-certified.


----------



## computerman29642 (Dec 4, 2007)

Would it be possible to have the user select a date range, and then have the query to determine if the ClassDate falls within that range? If the ClassDate does fall between the range, the user will be shown on the report, else the user will not be shown on the report.


----------



## computerman29642 (Dec 4, 2007)

Right Now, the Add Button on the frmAddEmployee Form has the following code:


```
Dim strMessage
    
    If IsNull(Me.txtFName) Then
        strMessage = MsgBox("You have have not entered a First Name.", vbOKOnly, "ENTRY ERROR")
        Me.txtFName.SetFocus
        Exit Sub
    ElseIf IsNull(Me.txtLName) Then
        strMessage = MsgBox("You have have not entered a Last Name.", vbOKOnly, "ENTRY ERROR")
        Me.txtLName.SetFocus
        Exit Sub
    Else
        DoCmd.Close
    End If
```
Is there a way instead of the form closing when the Add button is pressed, just to clear out the contents so another new user can be added?


----------



## jimr381 (Jul 20, 2007)

Check this out. This should do what you want.

I changed the form to be in Data Entry mode. I move the sub from your button you had before into the before update section of the form and added a button which will add new records instead of the add button you had before.


----------



## computerman29642 (Dec 4, 2007)

The form works beautifully....Thank you very much!

Do I still need all the code behind the form?


----------



## computerman29642 (Dec 4, 2007)

I just noticed that if I leave the Lastname field blank, I get the message hard coded by me, but I also get the following error message "You can't go to the specified record". Do you know what might be causing this?


----------



## jimr381 (Jul 20, 2007)

I will check it out.


----------



## jimr381 (Jul 20, 2007)

The button is setup to add a new record. It basically tells you to fill in the data as the first pop-up then the second tells you, that you cannot add the record. I will look into adding a cancelevent in there to cancel the adding of a record.


----------



## jimr381 (Jul 20, 2007)

I combined the subs from the before update to the on click procedure for the button and it seems to work fine now.


----------



## computerman29642 (Dec 4, 2007)

I tested the code and it is working great. Thank you!


----------



## computerman29642 (Dec 4, 2007)

I really would like some help with designing the tblInfo table better. As of right now, when the user uses the frmEmp form to enter what tests the users can perform, it is placing multiple tests inside the tblInfo Table.


----------



## jimr381 (Jul 20, 2007)

I will have to wait until I get get back to my desk to look at it. We are having login problems atm, so I cannot get into my PC.


----------



## computerman29642 (Dec 4, 2007)

That's fine. I was thinking of placing combo boxes on the frmEmp form. That a way the user can select the tests, methods, etc that have been entered into the tblInfo table. What do you think? if you have a better way, please feel free to share.


----------



## jimr381 (Jul 20, 2007)

That is because you are referencing the wrong table for your subform. You should be referencing the junction table instead.


----------



## computerman29642 (Dec 4, 2007)

When I try using the junction table, I get errors on the different fields. Right now, the sub form is looking at the qryEmpInfo query.


----------



## jimr381 (Jul 20, 2007)

Trying setting up a new subform.


----------



## computerman29642 (Dec 4, 2007)

I setup a new subform based on the tblEmployeeInfo table. I can get the InfoID to display, but not the test name, method or matrix.


----------



## jimr381 (Jul 20, 2007)

Please repost what you have and I will look at it.


----------



## computerman29642 (Dec 4, 2007)

I have attached a copy of the database. I have also did some work with the date range. You will notice that there are two new forms (frmDateSelect and frmDateSelect2). If you would, please take a look at the frmDateSelect2. When I select a date range from 4/1/2008 to 4/30/2008, dates for may are still apearing on the report.


----------



## jimr381 (Jul 20, 2007)

Your control names are wrong when you are passing the calendar controls names into the query, hence it showing everything instead of only certain records.


----------



## computerman29642 (Dec 4, 2007)

I believe I have figured out what I did wrong with teh frmDateSelect2 form, but I would still like for you to take a look and let me know what you think.


----------



## computerman29642 (Dec 4, 2007)

I am going to re-post the database so you can have where I fixed the frmDateSelect2 form.


----------



## jimr381 (Jul 20, 2007)

I posted what I thought was wrong, but I can take a look at what you have done with it. Whenever you want me to look at it, just post it and ask and I will take a look.


----------



## computerman29642 (Dec 4, 2007)

Here is the updated database.


----------



## jimr381 (Jul 20, 2007)

You will notice that you are getting tons and tons of values when you run your report. This is called a crossproduct query. Basically the data that you are querying does not know how to relate to the query so it matches each value from each field with all of the values from the other fields. 

I would recommend that you get all of the fields into one query for the reports data source.


----------



## computerman29642 (Dec 4, 2007)

Are you talking about the frmDateSelect2 and rptEmp report or are you talking about both reports and frmDateSelect forms?


----------



## jimr381 (Jul 20, 2007)

The frmdateselect2 form when it submits runs the rptEmp report shows the crossproduct information. 

With regards to having no data in the report and not showing it, I would setup a macro and place it in the no data event that would cancelevent, basically canceling the displaying of the data.


----------



## computerman29642 (Dec 4, 2007)

I did as you said and placed all fields need into one query. The report appears tobe workin gproperly now. Thank you. 

If you do not mind, could you please review the subform issue? I will see if I can get the macro working to cancel event.


----------



## computerman29642 (Dec 4, 2007)

I have attached a copy of the database. This version has the DateSelect2 form fixed, and the mcrCancelEvent macro has been created. The macro has been added to the no data event in both reports.


----------



## computerman29642 (Dec 4, 2007)

How will I know the cancel event macro is working properly? I tried putting in a date range where i knew there was no data, but I received a run-time error (2501).


----------



## jimr381 (Jul 20, 2007)

I think the line in the macro is actually called cancel.


----------



## computerman29642 (Dec 4, 2007)

When I select the date range with no data from the frmDateSelect2 form, this is the line that gets highlight when I click debug from the run-time error...


```
DoCmd.OpenReport "rptEmp", acViewPreview
```
If I just try to open the report from the Report object, the macro works fine. I get the runtime error when I try opening the report from the DateSelect2 form.


----------



## jimr381 (Jul 20, 2007)

Aye it seems like when it tries to access the report in the print preview it sees the conflict and gives you the error. I do not know the VBA that you would need to have it cancel the displaying of data. I typically also display a dialog box when stating that there is no data to display when they have picked a data range with no data.


----------



## computerman29642 (Dec 4, 2007)

I will see if I can figure out the VBA. Will you help me figure out the subform issue within the frmEmp form?


----------



## jimr381 (Jul 20, 2007)

Oh hehe yeah that should be feasible.


----------



## computerman29642 (Dec 4, 2007)

LOL...Thanks. You should have the newest verison of the database if you downloaded the 15 version.


----------



## computerman29642 (Dec 4, 2007)

Here is the VBA I used behind the frmDateSelect2 Form to handle the no data error..


```
Private Sub cmdVReport_Click()

    On Error GoTo Err_cmdVReport_Click
    
        DoCmd.OpenReport "rptEmp", acViewPreview
        DoCmd.Close acForm, "frmDateSelect2"
    
Exit_cmdVReport_Click:
    Exit Sub

Err_cmdVReport_Click:
    MsgBox "No Data To Display"
    Resume Exit_cmdVReport_Click

End Sub
```


----------



## jimr381 (Jul 20, 2007)

Are you using the demonstration of capabilities form for data entry purposes or for display purposes?


----------



## computerman29642 (Dec 4, 2007)

To be honest I am not really sure...LOL! I need a way to do data entry, but I also like the functionality of the form to be able to find a user, and see what test he or she can perform.

What would you do?


----------



## jimr381 (Jul 20, 2007)

If you were just using it for displaying data, then you could have it reference a query that had data from each of the tables in it. If you are interested in data entry then it gets a little weirder looking. I have one setup now that you can do data entry in, but it uses a subform nested in another subform.


----------



## computerman29642 (Dec 4, 2007)

I need a way to do data entry. Should I have a form for data entry, and then a form just for display?


----------



## computerman29642 (Dec 4, 2007)

Is there not a way to have a form like the frmAddEmployee form, but have a combo box with the user name? Then have the data be appended/updated in the tblEmployeeInfo table or the tblInfo table (not sure which one).


----------



## jimr381 (Jul 20, 2007)

I am about to head out. We will have to continue it on Monday. Maybe OBP can eyeball what you have so far and give you some other suggestions.


----------



## computerman29642 (Dec 4, 2007)

Jim,

Thank you for all of your help on this database. Have a good weekend.


----------



## OBP (Mar 8, 2005)

Well which way do you want to try and do it, modify your Current form to do Inputs and Edits as well as display, or have a Seperate form.
The other option is to have the Tabbed Form that I mentioned in post # 42, that has one mainform that you use for the employee and then tabbed Subforms for the "Detail" and Test Entry.


----------



## computerman29642 (Dec 4, 2007)

I guess the Tabbed form would be the best way to go.


----------



## OBP (Mar 8, 2005)

Before we go on to look at a "Tabbed" version of the form I have updated your current FrmEmp with the necessary data to make it an Input form as well as for displaying.
I have also Formatted the Date field as well.
You can delete the Parameter field if you want


----------



## computerman29642 (Dec 4, 2007)

Do you mean delete the Parameter field from the frmEmp form? If so, does the Parameter field really need to be displayed?


----------



## computerman29642 (Dec 4, 2007)

I have looked at the attached db. What changes did you make to the date? I noticed that when the dropdown box is selected on the frmEmp form, there are two entries for Test2. Can one of the entries be deleted?


----------



## OBP (Mar 8, 2005)

The Parameter is now shown in the Combo, so the Parameter field is redundant.
The changes to the are, it is now Formatted to a "Short Date" and it has an "Input Mask" which puts in the "/"s.
Just delete one of the Test 2 s from the Info Table.
Did you notice that you can enter the Employee using this form, so you do not need the Add Employee form.


----------



## computerman29642 (Dec 4, 2007)

I did not notice that when I first downloaded the db, but now I can see that you can now. 

Would the user need to use the navigation arrows to input a new user, or could they just type over the First and Last names on the form?


----------



## computerman29642 (Dec 4, 2007)

How would a new Test be entered so it would be displayed in the combo box?


----------



## OBP (Mar 8, 2005)

The user has to use the New Record navigation button, or you need to create a New Emlpoyee Commnad Button.
With the Parameter field still there you can just type in a new one in the New Record at the bottom of the subform.


----------



## computerman29642 (Dec 4, 2007)

Would it be possible to have a command button to enter a new test along with the method and matrix that goes with that test?


----------



## computerman29642 (Dec 4, 2007)

I believe I have an idea. I will work on that, and then re-post the db for review.


----------



## computerman29642 (Dec 4, 2007)

I have attached an update copy of the database.

I added two command buttons to the frmEmp form. One command button opens the frmAddEmployee form, and the other command button opens the frmAddParameter form. The buttons seems to work fine, except when I add a new employee I have to close down the frmEmp form before the new employee shows up in the combo box.

Please take a look at the database and let me know what you think.


----------



## OBP (Mar 8, 2005)

Why have those buttons when what you had worked?


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> The user has to use the New Record navigation button, or you need to create a New Emlpoyee Commnad Button.
> With the Parameter field still there you can just type in a new one in the New Record at the bottom of the subform.


When you made the statement above, I thought that would be good to have a command button for a new employee, as well as, a new parameter. If you do not think that they are needed, then I can get rid of them.


----------



## OBP (Mar 8, 2005)

The New Employee Button, can replace the need for Navigation Buttons on the FrmEmp Form and is a bit more obvious to the user than they are, but it only needs to Go To A New Record.
You could have the same on the Subform although the New Record is quite Obvious on a Contiuous Mode Form. 
It is your database and your Forms, but opening Forms makes it more difficult to update the Combos. 
Have a look at the After Update procedure of the FrmEmp'd "Last Name" Field and you will see that is it "Requeries" the combo to refresh it's list.


----------



## computerman29642 (Dec 4, 2007)

Yeah...I have looked at the code for the "Last Name" field, but I have not been able to find the right way to modify the code to requery after a new record has been entered when using the command button. 

So, you are saying that the command button should just take the user to a new record on the frmEmp form instead of opening a form to enter a new record....correct?


----------



## computerman29642 (Dec 4, 2007)

I have added the following code to the frmEmp form...


```
Private Sub cmdAddEmp_Exit(Cancel As Integer)
    Me.cboEmployee.Requery
End Sub
```
The cboEmployee combo box seems to update, but the sfrmEmp subform does not. Do I need to add a requery statement to the subform as well?


----------



## OBP (Mar 8, 2005)

Yes and you could do the same on the Subform as well, you can then use the VBA code (changing the combo name of course) to update the combo on the subform.

THIS IS THE ANSWER TO YOU PREVIOUS QUESTION!


----------



## OBP (Mar 8, 2005)

Don't you need to requery the frmemp as well and then go to the latest record that you have just added?


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> Don't you need to requery the frmemp as well and then go to the latest record that you have just added?


What do you mean by go to the latest record just added?


----------



## computerman29642 (Dec 4, 2007)

OBP said:


> Yes and you could do the same on the Subform as well, you can then use the VBA code (changing the combo name of course) to update the combo on the subform.
> 
> THIS IS THE ANSWER TO YOU PREVIOUS QUESTION!


Would I still have two command buttons or just one?


----------



## computerman29642 (Dec 4, 2007)

Here is how I modified the code...


```
Private Sub cmdAddEmp_Exit(Cancel As Integer)
    Me.cboEmployee.Requery
    Me.Form.Requery
End Sub
```


----------



## OBP (Mar 8, 2005)

Would you like to start a New Thread on "Tabbed Forms"?
You could post your latest version of the database on there.


----------



## computerman29642 (Dec 4, 2007)

Sure....


----------



## computerman29642 (Dec 4, 2007)

I have started a new thread called "Tabbed Forms". Should I mark this thread solved or leave it opened?


----------



## OBP (Mar 8, 2005)

That depends on whether or not you think the original question has been answered


----------



## computerman29642 (Dec 4, 2007)

I believe the original question has been answered, and new questions have come up. So, I amrked this thread solved since I opened the 'Tabbed Forms" thread.


----------



## jimr381 (Jul 20, 2007)

HEHE it only took us 185 posts.


----------



## computerman29642 (Dec 4, 2007)

LOL....Yeah, someone (ME) kept trying to make things harder than they needed to be....LOL!

I really do appreciate all the help and patience.


----------



## OBP (Mar 8, 2005)

I like it, because it can be used to show others how to go through the development process. 
gamecockfan, we could "Edit it" if you wanted to remove some of the less educational posts, but even those show others what might not be necessary when they are thinking the same as you were.


----------



## jimr381 (Jul 20, 2007)

I will debate making a database design methodology post for the guides section. Only problem is I can only update my post within the next day.


----------



## OBP (Mar 8, 2005)

Jim, what is the "Guides" section?


----------



## jimr381 (Jul 20, 2007)

There is a "Tech Tips and Tricks" and a "Do it Yourself Projects" forum as well. I figured it could be classified in either of those.


----------



## Zack Barresse (Jul 25, 2004)

OBP said:


> I like it, because it can be used to show others how to go through the development process.


I, for one, appreciated it. I've enjoyed following this thread.


----------



## computerman29642 (Dec 4, 2007)

LOL...Yeah, I bet everyone has enloyed the bone head questions I have asked.  As Zack knows, my thoughts end up making things more complex then they really need to be.


----------



## OBP (Mar 8, 2005)

That may be so, but lots of others would fall in to the same kind of "traps", so if it saves them that it has been doubly worthwhile, as you are learning form it anyway.


----------



## OBP (Mar 8, 2005)

Jim, perhaps I ought to post some things on there, if I can find time between Database work and answering on here.


----------

