Control Will Not Update
in [Form]
|
Prev: Access frontend with SQL Server backend: my solution for ODBCCall error
Next: Linking two Subforms & showing name in place of ID?
From: Dirk Goldgar on 13 May 2010 17:01 "DJM2290" <DJM2290(a)discussions.microsoft.com> wrote in message news:166668C3-F24C-4212-A5AF-84B087392ED7(a)microsoft.com... > Below is the SQL from the source...the relevant controls are as follows.. > > Name: SKU # > Type: Text Box > Countrol Source: SKU # > > Name: SKU Description > Type: Text Box > Countrol Source: SKU Desc > > There is a combo box, but I don't think it is relevant to the problem. > Name: Ad Date > Control Source: Ad Date > Row Source: Ad Dates > Bound Column: 1 > Column Count :1 > > SELECT [Ad Detail].[Ad Date], [Ad Detail].Division, > INTREPIDD_SKUMSTR.SRS_DEPT AS Dept, INTREPIDD_SKUMSTR.SRS_CLASS AS Class, > [Ad > Detail].[SKU #], [Ad Detail].[Placeholder Dept], INTREPIDD_SKUMSTR.SM_DESC > AS > [SKU Desc], [Ad Detail].[Placeholder Description], [Ad > Detail].[Placeholder > Cost (per unit)], [Ad Detail].[Include for 12 Week Meeting (type Y if > yes)], > INTREPIDD_SKUMSTR.SM_LAST_COST AS [Last Cost], [Ad Detail].Status, [Ad > Detail].[Current Retail], [Ad Detail].[Event Retail], [Ad Detail].[Comp > Retail], [Ad Detail].[# Stores], [Ad Detail].[Baseline Units], [Ad > Detail].[Unit Sales Forecast], [Ad Detail]![Event Retail]*[Ad > Detail]![Unit > Sales Forecast] AS [Sales Forecast $], [Ad Detail].[Ad Prep], [Ad > Detail]![Unit Sales Forecast]/[Ad Detail]![Ad Prep] AS [Ad Sell Thru], [Ad > Detail].[Basis for Projection], [Ad Detail].Comments, ([Ad > Detail]![Current > Retail]-[Ad Detail]![Event Retail])*[Ad Detail]![Unit Sales Forecast] AS > [Mkdn Forecast $] > FROM ([Ad Detail] LEFT JOIN INTREPIDD_SKUMSTR ON [Ad Detail].[SKU #] = > INTREPIDD_SKUMSTR.SRS_SKU) LEFT JOIN INTREPIDD_DEPTTBL ON > INTREPIDD_SKUMSTR.SRS_DEPT = INTREPIDD_DEPTTBL.SRS_DEPT > WHERE ((([Ad Detail].[Ad Date])=[Which Ad Date? Format must be mmddyy]) > AND > (([Ad Detail].Division)=[What Division?])) > ORDER BY [Ad Detail].[Ad Date], [Ad Detail].Division, > INTREPIDD_SKUMSTR.SRS_DEPT, INTREPIDD_SKUMSTR.SRS_CLASS, [Ad Detail].[SKU > #]; I'm puzzled. I don't see any reason why that query wouldn't do the "autolookup" that comes in so handy. A test query that I made, which also involves two left joins in a similar fashion, works fine in Access 2007; likewise a form that I based on the query. Would you be interested in sending me a copy of your database to look at? If so, you can send it to the address derived by removing NO SPAM and ".invalid" from the reply address of this message. If that address isn't visible to you, you can get my address from my web site, which is listed in my sig. Do *not* post my real address in the newsgroup -- I don't want to be buried in spam and viruses. It would be best if you could make a cut-down copy of the database, containing just the elements necessary to demonstrate the problem, compact the copy, and and zip it for sending. -- Dirk Goldgar, MS Access MVP Access tips: www.datagnostics.com/tips.html (please reply to the newsgroup)
From: DJM2290 on 17 May 2010 12:58
Yes...it is strange. It workd fine before I converted it to 2007. Note that the main table in the query is in a backend database which is still in an older version of Access. I have sent a trimmed down version of the database. Thanks again. "Dirk Goldgar" wrote: > "DJM2290" <DJM2290(a)discussions.microsoft.com> wrote in message > news:166668C3-F24C-4212-A5AF-84B087392ED7(a)microsoft.com... > > Below is the SQL from the source...the relevant controls are as follows.. > > > > Name: SKU # > > Type: Text Box > > Countrol Source: SKU # > > > > Name: SKU Description > > Type: Text Box > > Countrol Source: SKU Desc > > > > There is a combo box, but I don't think it is relevant to the problem. > > Name: Ad Date > > Control Source: Ad Date > > Row Source: Ad Dates > > Bound Column: 1 > > Column Count :1 > > > > SELECT [Ad Detail].[Ad Date], [Ad Detail].Division, > > INTREPIDD_SKUMSTR.SRS_DEPT AS Dept, INTREPIDD_SKUMSTR.SRS_CLASS AS Class, > > [Ad > > Detail].[SKU #], [Ad Detail].[Placeholder Dept], INTREPIDD_SKUMSTR.SM_DESC > > AS > > [SKU Desc], [Ad Detail].[Placeholder Description], [Ad > > Detail].[Placeholder > > Cost (per unit)], [Ad Detail].[Include for 12 Week Meeting (type Y if > > yes)], > > INTREPIDD_SKUMSTR.SM_LAST_COST AS [Last Cost], [Ad Detail].Status, [Ad > > Detail].[Current Retail], [Ad Detail].[Event Retail], [Ad Detail].[Comp > > Retail], [Ad Detail].[# Stores], [Ad Detail].[Baseline Units], [Ad > > Detail].[Unit Sales Forecast], [Ad Detail]![Event Retail]*[Ad > > Detail]![Unit > > Sales Forecast] AS [Sales Forecast $], [Ad Detail].[Ad Prep], [Ad > > Detail]![Unit Sales Forecast]/[Ad Detail]![Ad Prep] AS [Ad Sell Thru], [Ad > > Detail].[Basis for Projection], [Ad Detail].Comments, ([Ad > > Detail]![Current > > Retail]-[Ad Detail]![Event Retail])*[Ad Detail]![Unit Sales Forecast] AS > > [Mkdn Forecast $] > > FROM ([Ad Detail] LEFT JOIN INTREPIDD_SKUMSTR ON [Ad Detail].[SKU #] = > > INTREPIDD_SKUMSTR.SRS_SKU) LEFT JOIN INTREPIDD_DEPTTBL ON > > INTREPIDD_SKUMSTR.SRS_DEPT = INTREPIDD_DEPTTBL.SRS_DEPT > > WHERE ((([Ad Detail].[Ad Date])=[Which Ad Date? Format must be mmddyy]) > > AND > > (([Ad Detail].Division)=[What Division?])) > > ORDER BY [Ad Detail].[Ad Date], [Ad Detail].Division, > > INTREPIDD_SKUMSTR.SRS_DEPT, INTREPIDD_SKUMSTR.SRS_CLASS, [Ad Detail].[SKU > > #]; > > > I'm puzzled. I don't see any reason why that query wouldn't do the > "autolookup" that comes in so handy. A test query that I made, which also > involves two left joins in a similar fashion, works fine in Access 2007; > likewise a form that I based on the query. > > Would you be interested in sending me a copy of your database to look at? > If so, you can send it to the address derived by removing NO SPAM and > ".invalid" from the reply address of this message. If that address isn't > visible to you, you can get my address from my web site, which is listed in > my sig. Do *not* post my real address in the newsgroup -- I don't want to > be buried in spam and viruses. It would be best if you could make a > cut-down copy of the database, containing just the elements necessary to > demonstrate the problem, compact the copy, and and zip it for sending. > > -- > Dirk Goldgar, MS Access MVP > Access tips: www.datagnostics.com/tips.html > > (please reply to the newsgroup) > |