Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

Questions about Access 2007 DB

Good day everyone. We have an Access DB that I have been tasked with changing a bit. I have made some changes and addition to it as well as redesigned one of the forms. I have two issues (one is actually a question)

1) There is a form that we run to produce all of the information requested for a specific amployee. Ever since I added the field "Notes" it now requests to enter a parameter value when the button to Preview or Print to the reports is clicked on. I am including a screenshot as well as a copy of the DB. This is on Untitled.jpg

Can anyone tell me how to get rid of the prompt or why no matter what I enter in the prompt, I can't get data to view on the report anymore?

2) We have a form (the same form above) that I believe the Build Events could be done a bit differently. This is on Untitled1.jpg. Currently when the Preview or Print button or selected, it goes through a Case statement to figure out what employee was selected. We have a table labeled Team member that houses all of this information.

Is there anyway to rewrite the code of the build event (there are two the are similar) so that it queries the table instead of having to make changes to those build events everytime a new employee is added to our company?

Any help is greatly appreciated. If anyone needs further information, please let me know. I didn't want to post the entire database because I didn't want it out for the public to see.
Untitled.jpg
Untitled1.jpg
0
sscal
Asked:
sscal
  • 18
  • 15
  • 2
  • +1
3 Solutions
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
I think you forgot to attach the db ...?
0
 
sscalAuthor Commented:
I explained why I didn't want to attach the db in the last sentence of my post. If I need to I can send it on a per Genius basis. Don't want to put it online for the public to see.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Missed that.  Per Genius is not allow, sorry.

As far as the parameter prompt ... that's a bit strange because it doesn't look like a field name. But typically this is caused by a typo or non-existent field and/or table and/or query name.

And you say 'reports' ... is the same recordsource for this from being used on this report?

But really ... you can save yourself a LOT of time be getting this excellent tool and doing a search for part of the prompt phrase and see immediately where the issues:

http://www.rickworld.com/products.html#Find%20and%20Replace%209.0

I use this daily and have since its inception many years ago.

mx
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
RE:

"so that it queries the table "
What is the RowSource for cmbName - assuming that is a combo box ?

Seems you would want something like

SELECT Table1.ShortName, Table1.FullName
FROM Table1
ORDER BY Table1.FullName;

for the RowSource ... then make the selection, and go from there.  No need to Select Case.

mx

0
 
sscalAuthor Commented:
I will take a look at the software you mentioned and see if it helps. I might end up posting the db but could I have the mods delete it once my issue ois resolved and the questions are answered?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You can ask the mods to do that ... and they probably will.  But you should not upload sensitive data ... all that is required is enough to solve the problem. And I promise Rick's F&R will find your issue.  It's exactly the first thing I will use if you upload :-)
0
 
sscalAuthor Commented:
I will try this out tomorrow. Thank you. Will post my results.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok
0
 
sscalAuthor Commented:
So I installed the software you suggested to take a look at it but what should I be searching for?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Part of that phrase you see in that prompt message ...

"Consulting Hot Sheet"  or whatever it takes to locate that.

mx
0
 
sscalAuthor Commented:
Good day DatabaseMX. So I tried the softwrae you suggested and you were right. It found where the issues were. I have fixed that problem. All that remains is cleaning up the Case statement.

I commented out the Case statement and placed the query you suggested in its place. A lot of the information is red as if it can't see the table information. The field name of FullName in the table is called Name and the field of Shortname in the table is Initial.

The cmbName is a dropdown as shown in Untitled.jpg. It stores all of the Names (full names) of the employees.

Can you also asisst me in finishing up the build event as I am not too familiar with what would need to happen after the query?

Thank you


Untitled2.jpg
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Actually, I meant to put that SQL in the RowSource Property of the combo box, not in code - which you can't do - at least not like that:
See Image

Also, you are using Access Reserved Words (Name in this case), So ... they need to be surrounded in brackets to avoid issues. In general, avoid using Access Reserved Words.



Capture1.gif
0
 
sscalAuthor Commented:
OK, I made those changes to the RowSource. I also made some changes to the code in the Build Event but I receive an error. I am sure there is something I am missing.
Untitled4.jpg
0
 
sscalAuthor Commented:
The error is received when I select an employee form the dropdown and click on the Preview button.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
fyi ... can you please post the actual code in this text box so it can be copied and pasted.  Hard to work with the screen shots in that context.

But basically, you want to do this:

FullName = Me.cmbName.Column(1)  ' 2nd physical column
ShortName = Me.cmbName   ' same as Column(0) - default ... and physical column 1

mx
0
 
sscalAuthor Commented:
OK. You are sure very helpful. We are much closer as the report actually displayed but not information showed up in the report, no matter who I choose on the list. Below is the code for the report.


Thank you.
ReportFilter = "[Consulting by Lead Director (Active Project)]![Lead SSC Director] ='" & FullName & "' or [Consulting by Lead Director (Active Project)]![Team Members] = '" & ShortName & _
    "' or [Consulting by Lead Director (Active Project)]![Team Members-2] = '" & ShortName & "' or [Consulting by Lead Director (Active Project)]![Team Members-3] = '" & ShortName & _
    "' or [Consulting by Lead Director (Active Project)]![Team Members-4] = '" & ShortName & "' or [Consulting by Lead Director (Active Project)]![SSC AA Assignment] = '" & FullName & "'"

    DoCmd.OpenReport "Consulting by Team Members (Active Project)", acViewPreview, , ReportFilter, acWindowNormal

Open in new window

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok ... and just one more future request. Paste code into *this* window, not the code window ...because, the Code window does not have word wrap -- and it requires a LOT of scrolling around, etc.

Give my just a few minutes - there is this luncheon thing I need to make an appearance at ... back in a few.
0
 
sscalAuthor Commented:
Thank you very much and will do on the code for future requests.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok ... tell me exactly ... in words - how you are wanting to filter this report.

mx
0
 
sscalAuthor Commented:
OK. What happens after the button to Preview or Print is pressed is a report comes up pulling only the records fields of Lead Direcotor, Client, Project, Team Members (which shows Team member, Team Member-2, Team Member-3, Team Member-4), etc

esentially pulling only the records where the employee choosen is either the Lead Director, the Team Member (with Initials), or the SSC AA Assignment. Below are a few screenshots of the report.
Untitled5.jpg
Untitled6.jpg
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
OK ... lets break these down one at a time.  Try this for the first test - and see if you get the results that this filter implies: (  Chr(34) is a double quote)

ReportFilter = "[Lead SSC Director] = " & Chr(34) & FullName & Chr(34)

    DoCmd.OpenReport "Consulting by Team Members (Active Project)", acViewPreview, , ReportFilter, acWindowNormal
0
 
sscalAuthor Commented:
OK, so I commented out the entire ReportFilter and just left what you placed above. Nothing changed. It still just pulls up a blank report view. Below is what I ran. The entire center section is commented out.

ReportFilter = "[Lead SSC Director] = " & Chr(34) & FullName & Chr(34)
     
   
    'ReportFilter = "[Consulting by Lead Director (Active Project)]![Lead SSC Director] ='" & FullName & "' or [Consulting by Lead Director (Active Project)]![Team Members] = '" & ShortName & _
    '"' or [Consulting by Lead Director (Active Project)]![Team Members-2] = '" & ShortName & "' or [Consulting by Lead Director (Active Project)]![Team Members-3] = '" & ShortName & _
    '"' or [Consulting by Lead Director (Active Project)]![Team Members-4] = '" & ShortName & "' or [Consulting by Lead Director (Active Project)]![SSC AA Assignment] = '" & FullName & "'"

    DoCmd.OpenReport "Consulting by Team Members (Active Project)", acViewPreview, , ReportFilter, acWindowNormal
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
OK ... what is the RecordSource for the Report ? (see image)

mx
Capture1.gif
0
 
sscalAuthor Commented:
It says: Consulting by Lead Director (Active Project)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok ... is that a Table or Query ?
And ... is this field present:  [Lead SSC Director]  
?mx
0
 
sscalAuthor Commented:
That is a query. Yes, Lead SSC Director is present.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
OK ... then in theory this:

ReportFilter = "[Lead SSC Director] = " & Chr(34) & FullName & Chr(34)

s/b working.  Now ... are the FullName values definitely in the [Lead SSC Director] field?

In fact, can you post (in this window) the SQL for that query?

mx
0
 
sscalAuthor Commented:
OK, to make it easier on both of us I went through and cleaned up all the data and here is the entire databasewith some dummy data.
Copy---Copy.mdb
0
 
sscalAuthor Commented:
Good day. Any news on my second request?
0
 
Vadim RappCommented:
Since this question is already quite a long thread, could you clarify what is the problem at this point?
0
 
mbizupCommented:
The full name appears in your combo box's first column.
Column numbers are zero-based, meaning the count starts at zero.

>  FullName = Me.cmbName.Column(1)

The above refers to the second column in your combo box, which is the 'initials' column.

To refer to the Full Name column, and get the data showing on the report, you need to refer to the first column ( column 0 ) like this:

 FullName = Me.cmbName.Column(0)
0
 
mbizupCommented:
If that answers your question, you should give the points to mx.  These threads should really be limited to a single problem/topic.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Yes ... it was really kind of two big questions.  Although I somehow missed the post on 8-30.

I suggest you open a new question - summarizing where things are at.

mx
0
 
sscalAuthor Commented:
DatabaseMX, you were helping me on this issue. Not sure what a new question would accomplish. I stated exactly what I was having trouble with in my original question which was two parts.

I also added the access db file to one of my previous posts. I am trying to not have a Case statement to search for the proper employee name but instead do a query when the dropdown name is selected to pull the fullname and initials and display a report of all records which have the name or initials in at least one of the 6 fields I am searching on.
0
 
sscalAuthor Commented:
by the way, I tried changing the column tags as instructed by mbizup and it didn't make a difference.
0
 
sscalAuthor Commented:
I wnated to retract my last post. mbizup was exactly correct, I just had some other changes I needed to revert that I had made yesterday. Changing the olumn fields (switching them) was the key. The reports are pulling up correctly now. I am closing this question and assigning points.

Thanks everyone.
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 18
  • 15
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now