Solved

Questions about Access 2007 DB

Posted on 2011-09-29
38
324 Views
Last Modified: 2012-05-12
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
Comment
Question by:sscal
  • 18
  • 15
  • 2
  • +1
38 Comments
 
LVL 75
ID: 36818842
I think you forgot to attach the db ...?
0
 
LVL 1

Author Comment

by:sscal
ID: 36818853
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Access MVP) earned 400 total points
ID: 36818898
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
 
LVL 75
ID: 36818918
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
 
LVL 1

Author Comment

by:sscal
ID: 36818924
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
 
LVL 75
ID: 36818929
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
 
LVL 1

Author Comment

by:sscal
ID: 36818933
I will try this out tomorrow. Thank you. Will post my results.
0
 
LVL 75
ID: 36818938
ok
0
 
LVL 1

Author Comment

by:sscal
ID: 36818970
So I installed the software you suggested to take a look at it but what should I be searching for?
0
 
LVL 75
ID: 36818988
Part of that phrase you see in that prompt message ...

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

mx
0
 
LVL 1

Author Comment

by:sscal
ID: 36892920
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
 
LVL 75
ID: 36892979
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
 
LVL 1

Author Comment

by:sscal
ID: 36893184
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
 
LVL 1

Author Comment

by:sscal
ID: 36893187
The error is received when I select an employee form the dropdown and click on the Preview button.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) earned 400 total points
ID: 36893230
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
 
LVL 1

Author Comment

by:sscal
ID: 36893290
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
 
LVL 75
ID: 36893559
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
 
LVL 1

Author Comment

by:sscal
ID: 36893596
Thank you very much and will do on the code for future requests.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 75
ID: 36893685
ok ... tell me exactly ... in words - how you are wanting to filter this report.

mx
0
 
LVL 1

Author Comment

by:sscal
ID: 36893745
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
 
LVL 75
ID: 36893875
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
 
LVL 1

Author Comment

by:sscal
ID: 36893938
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
 
LVL 75
ID: 36893997
OK ... what is the RecordSource for the Report ? (see image)

mx
Capture1.gif
0
 
LVL 1

Author Comment

by:sscal
ID: 36894019
It says: Consulting by Lead Director (Active Project)
0
 
LVL 75
ID: 36894041
ok ... is that a Table or Query ?
And ... is this field present:  [Lead SSC Director]  
?mx
0
 
LVL 1

Author Comment

by:sscal
ID: 36894131
That is a query. Yes, Lead SSC Director is present.
0
 
LVL 75
ID: 36894318
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
 
LVL 1

Author Comment

by:sscal
ID: 36894385
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
 
LVL 1

Author Comment

by:sscal
ID: 36910859
Good day. Any news on my second request?
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 36914376
Since this question is already quite a long thread, could you clarify what is the problem at this point?
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 100 total points
ID: 36914450
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36914454
If that answers your question, you should give the points to mx.  These threads should really be limited to a single problem/topic.
0
 
LVL 75
ID: 36914653
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
 
LVL 1

Author Comment

by:sscal
ID: 36918194
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
 
LVL 1

Author Comment

by:sscal
ID: 36918205
by the way, I tried changing the column tags as instructed by mbizup and it didn't make a difference.
0
 
LVL 1

Author Comment

by:sscal
ID: 36918637
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now