Katrina-
You added the Vendor Information table, but there's no link between that and the
invoice log table. You need to JOIN the two tables so that you get invoices
related to the vendor you pick. What are the key fields in the two tables?
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
----------------------------------------
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of ka0t1c_ang3l
Sent: Tuesday, March 27, 2012 5:26 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: open form based on combo box selection
The following is the SQL view of the report's record source as you reqeusted.
SELECT [Capital Project Invoice Log 2012].[Invoice Date], [Capital Project
Invoice Log 2012].[Invoice Number], [Capital Project Invoice Log 2012].[Invoice
Amount], [Capital Project Invoice Log 2012].[AIP Project Number], [Capital
Project Invoice Log 2012].[Non AIP Project Number], [Capital Project Invoice Log
2012].[Engineering Number], [Capital Project Invoice Log 2012].[Date to Airport
Engineer], [Capital Project Invoice Log 2012].[Authorization For Payment],
[Capital Project Invoice Log 2012].[Date to City Hall], [Capital Project Invoice
Log 2012].[Vendor Name], [VENDOR INFORMATION].[VENDOR NAME]
FROM [Capital Project Invoice Log 2012], [VENDOR INFORMATION]
GROUP BY [Capital Project Invoice Log 2012].[Invoice Date], [Capital Project
Invoice Log 2012].[Invoice Number], [Capital Project Invoice Log 2012].[Invoice
Amount], [Capital Project Invoice Log 2012].[AIP Project Number], [Capital
Project Invoice Log 2012].[Non AIP Project Number], [Capital Project Invoice Log
2012].[Engineering Number], [Capital Project Invoice Log 2012].[Date to Airport
Engineer], [Capital Project Invoice Log 2012].[Authorization For Payment],
[Capital Project Invoice Log 2012].[Date to City Hall], [Capital Project Invoice
Log 2012].[Vendor Name], [VENDOR INFORMATION].[VENDOR NAME]
HAVING ((([Capital Project Invoice Log 2012].[Vendor Name])=[Forms]![Vendor
Dialog Box]![Vendor Name]))
ORDER BY [Capital Project Invoice Log 2012].[Vendor Name], [VENDOR
INFORMATION].[VENDOR NAME];
--- In MS_Access_Professionals@yahoogroups.com, Duane Hookom <duanehookom@...>
wrote:
>
> Share the SQL view of the report's record source.
>
> To: MS_Access_Professionals@yahoogroups.com
> From: no_reply@yahoogroups.com
> Date: Tue, 27 Mar 2012 13:59:55 +0000
> Subject: [MS_AccessPros] Re: open form based on combo box selection
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Yes! I'm completely confused! lol I did as you instructed I changed the
criteria in the [Vendor Query] to the other Vendor Name and the report does
display records now. However, it's not displaying the records for the vendor
I've selected. It is displaying all vendors. I don't know how to fix that
problem.
>
>
>
> Thanks in advance for your help!
>
>
>
> Katrina
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "Duane" <duanehookom@> wrote:
>
> >
>
> > You've been confused by the horrible lookup fields again. Change the
criteria in [Vendor Query] to the other [Vendor Name] field/column.
>
> >
>
> > Duane Hookom
>
> > MS Access MVP
>
> >
>
> > --- In MS_Access_Professionals@yahoogroups.com, ka0t1c_ang3l <no_reply@>
wrote:
>
> > >
>
> > > Duane -
>
> > >
>
> > > Thanks again! I have all of my reports working now, except for the
"Invoice by Vendor" report. I added in the [VENDOR INFORMATION] table and it
still won't show any records. I've been working on this for most of the day and
I just can't seem to find the issue with this report. All the others worked by
adding the [VENDOR INFORMATION] table. I've uploaded an updated database if you
could please take a look at it for me I would appreciate it.
>
> > >
>
> > > I'm sorry to be such a bother!
>
> > >
>
> > > Thanks!
>
> > >
>
> > > Katrina
>
> > >
>
> > > --- In MS_Access_Professionals@yahoogroups.com, "Duane" <duanehookom@>
wrote:
>
> > > >
>
> > > > You are using lookup fields in your table which many of us veterans
consider horrible practice. You might want to read about the evils of lookup
fields at http://access.mvps.org/access/lookupfields.htm.
>
> > > >
>
> > > > If you want the Vendor Name in the report, add [VENDOR INFORMATION]
table to your report's record source so you can include the actual vendor name
rather than your Vendor ID.
>
> > > >
>
> > > > Duane Hookom
>
> > > > MS Access MVP
>
> > > >
>
> > > > --- In MS_Access_Professionals@yahoogroups.com, ka0t1c_ang3l <no_reply@>
wrote:
>
> > > > >
>
> > > > > Duane -
>
> > > > >
>
> > > > > Thanks for the assistance! I've got the forms opening now based on the
selection I've made. However, when I'm running a report, the report opens but
instead of seeing the name of the Vendor the ID number is displayed. I've looked
at this all morning and still can't seem to find what I'm doing wrong. I've
uploaded my database to the AssistantNeeded folder its under Capital Project
Invoice Log.
>
> > > > >
>
> > > > > Your help is greatly appreciated!!
>
> > > > >
>
> > > > > Thanks!
>
> > > > >
>
> > > > > Katrina
>
> > > > >
>
> > > > > --- In MS_Access_Professionals@yahoogroups.com, "Duane" <duanehookom@>
wrote:
>
> > > > > >
>
> > > > > > Your form record source [Capital Project Invoice Log 2012] doesn't
have the field [Vendor Name] so it is asking for it.
>
> > > > > >
>
> > > > > > Duane Hookom
>
> > > > > > MS Access MVP
>
> > > > > >
>
> > > > > > --- In MS_Access_Professionals@yahoogroups.com, ka0t1c_ang3l
<no_reply@> wrote:
>
> > > > > > >
>
> > > > > > > I've tried a few more things but still getting the same parameter
box. Can anyone help please.
>
> > > > > > >
>
> > > > > > > Thanks!
>
> > > > > > >
>
> > > > > > > Katrina
>
> > > > > > >
>
> > > > > > > --- In MS_Access_Professionals@yahoogroups.com, ka0t1c_ang3l
<no_reply@> wrote:
>
> > > > > > > >
>
> > > > > > > > I'm not exactly sure what you want me to try? I'm not an expert
at access, so I'll need more instruction. I looked at the file you put in the
assistance needed file, but don't know which part you want me to look at.
>
> > > > > > > >
>
> > > > > > > > Thanks!
>
> > > > > > > >
>
> > > > > > > > Katrina
>
> > > > > > > >
>
> > > > > > > > --- In MS_Access_Professionals@yahoogroups.com, eldigul
<no_reply@> wrote:
>
> > > > > > > > >
>
> > > > > > > > > try this... (temp.rar, in the assistanceneeded folder).
>
> > > > > > > > >
>
> > > > > > > > > --- In MS_Access_Professionals@yahoogroups.com, ka0t1c_ang3l
<no_reply@> wrote:
>
> > > > > > > > > >
>
> > > > > > > > > > I have a database that has a form "Find Invoice" and this
form has a combo box "vendor id" and a command button that has a macro to open
up the "New Invoice" form based on the selection made. When I select a vendor
and click "go" instead of opening the form with the information it opens a
parameter box that asks me to enter the vendor name. How can I avoid that
parameter box and have the form open automatically? I have uploaded my capital
project invoice log in the assistance needed folder.
>
> > > > > > > > > >
>
> > > > > > > > > > Any help is appreciated!
>
> > > > > > > > > >
>
> > > > > > > > > > Thanks!
>
> > > > > > > > > >
>
> > > > > > > > > > Katrina
>
> > > > > > > > > >
>
> > > > > > > > >
>
> > > > > > > >
>
> > > > > > >
>
> > > > > >
>
> > > > >
>
> > > >
>
> > >
>
> >
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Tidak ada komentar:
Posting Komentar