ACCESS FEED: [MS_AccessPros] Re: open form based on combo box selection

John -

The key fields are the Vendor Name in both the Invoice Log table and the Vendor Information table.

Thanks!

Katrina

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> 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]
> >
>