Help with Dlookup problem in subform/main form

Hello,

I am trying to make a subform that opens on a main form.  The code
works when I open the subform, but when the subform opens on the main
form, it gives me an error message.  Details:

I have 2 subforms called
"AutosGasData1sub" and "AutosGasData2sub".  There is a field on each
subform called "Best MPG" that pulls the best MPG from queries called
"AutosGasStats1" and "AutosGasStats2" respectively.  The subforms are
attached to a form called "AutomobileGasolineSearch".

I have added lookup fields to find the date of the Best MPG, and it
works when I open the subforms in form view.  However, when I open
the
subforms on the Main form, I get an #Error.

This is the code:

=DLookUp("[Fill Date]","[AutosGasStats1]","[AVG MPG]='" & Forms!
AutosGasData1sub![Best MPG] & "'")

Thanks

0
nybaseball22
11/14/2007 6:48:51 PM
access.formscoding 7493 articles. 0 followers. Follow

1 Replies
932 Views

Similar Articles

[PageSpeed] 31

Hello  .

<nybaseball22@gmail.com> wrote:
> Hello,
>
> I am trying to make a subform that opens on a main form. The code
> works when I open the subform, but when the subform opens on the
> main form, it gives me an error message. Details:
>
> I have 2 subforms called
> "AutosGasData1sub" and "AutosGasData2sub". There is a field on
> each subform called "Best MPG" that pulls the best MPG from queries
> called "AutosGasStats1" and "AutosGasStats2" respectively. The
> subforms are attached to a form called "AutomobileGasolineSearch".
>
> I have added lookup fields to find the date of the Best MPG, and it
> works when I open the subforms in form view. However, when I open
> the subforms on the Main form, I get an #Error.
>
> This is the code:
>
> =DLookUp("[Fill Date]","[AutosGasStats1]","[AVG MPG]='" & Forms!
> AutosGasData1sub![Best MPG] & "'")
>
> Thanks

> =DLookUp("[Fill Date]","[AutosGasStats1]","[AVG MPG]='" & Forms!
> AutosGasData1sub![Best MPG] & "'")

I guess that [Best MPG] is a number? And the subform controls have
the same name as the subforms? Then try this:

=DLookUp("[Fill Date]","[AutosGasStats1]","[AVG MPG]=" & Forms!
AutomobileGasolineSearch!AutosGasData1sub.Form![Best MPG])

When the main form is opened, the two subforms are opened "within"
the main form and are not contained in the forms collection. To
access values of a subform, use the syntax:
Forms![MainFormName]![SubformControlName].Form![ControlName]
MainFormName is the element of the forms collection.
SubformControlName is the subform control's name on the main form.
..Form refers to the form that is the source object of the subform
control.
ControlName is the name of the control on the subform.

-- 
Regards,
Wolfgang 


0
Wolfgang
11/14/2007 10:19:14 PM
Reply:

Similar Artilces:

disk full problem on a server
i am a virtual user in CT. my office/network is in Atlanta. we use a VPN to get into our network. i have cable modem access. one of the servers is something called a "Celerra" (whatever that is) whereupon i gain access to a shared file in Excel. I have Version 11.6355.6408 SP1. i have Office 2003 and Windows 2000 Professional. i keep getting 'disk full' messages when trying to do updates to that shared file. the server has 29 GIG free. my workaround is to save the file to my local harddrive then copy it back into the server. my desktop folks and network services folk...

problems passing OpenAgrs
behind a command button click event, I have this code (where manufacturer and catalogno are valid fields on the form): ... Dim strOpenArgs As String strOpenArgs = "true" & "~" & Me.Manufacturer & "~" & Me.CatalogNo DoCmd.OpenForm "frmFixtureCatalogueCostHistory", acNormal, , "[Manufacturer] = '" & _ Me.Manufacturer & "' AND [CatalogNumber] = '" & Me.CatalogNo & "'", , , strOpenArgs behind form: frmFixtureCatalogueCostHistory, I have this code Pr...

Help! I've inherited a messed up plan :
I just came onto a project and got a 1700 line project plan from the previous 4 project managers. Q. Is it normal for tasks with unassigned ? empty resources to show that task in the Resource Usage window? And then on the Gantt View, when I add a resource under that column, it then shows up on the Task line as a Task! I've never seen this happen before. Any ideas?????? J > > Q. Is it normal for tasks with unassigned ? empty resources to show > that task in the Resource Usage window? > Sure - it shows up under the "Unassigned" label. &...

Help with my upgrade path.
We have to accomplish two things. 1. Moving the CRM server to a new machine 2. Upgrading our system to 1.2 What is the best order for this? Jake, I'm not sure it really matters which one you do first, as long as you test each step before moving on to the next. I would probably recommend installing the other server first though. That way, you will have the v1.0 server available if you run into problems with the upgrade. (Granted, it won't funciton if the DB was already upgraded, but will be there for reference). Matt "Jake Horn" <jhorn@order4chaos.com> wrote ...

RPC problem...
Hi All, I have a problem with my Exchange 5.5 server. The hardware on the server is sufficient - 2 GB RAM, PIII for 150 Users... The problem is that when I monitor through Performance monitor the RPC bytes Read and Write - The values are extremly high for a long time . (These are not jumps in the usage- but simply consistent high values). What could cause this ? Thanks for your help, Shira. Below is an explaination of the counters. RPC Read Bytes/Sec - Bytes read from clients, sent via RPCs. RPC Write Bytes/Sec - Bytes written to clients, sent via RPCs Check that you don't...

plz help in to creat formula of excel
hello to all i want to discuss my problem with you related to MS excel. i want to know or find out formula of excel which can help me in such way.... i want to make grade point of my college students in such a way.. for example.... marks grade point average 50 1 53 1.3 60 2 68 2.8 70 3 76 3.6 80 4 84 4 89 4 90 4 98 4 100 4 this is example the student who get 50 marks the GPA will be 1 and who get 80 marks or above 80 the GPA should be 4 GPA should not less than 1 or grator than 4 please tell me the formula so that i can make this GPA thanks a lo -- khushe --------------------------...

Problem with Exchange HTTP virtual server..pls help
Hi, I created Exchange Virtual Server using ESM & the same is not getting reflected under IIS.. What could be the problem here? pls guide... At present, i have 2 Exchange Virtual Servers (http servers) under Protocols > HTTP section of ESM. However, when i open IIS i only see Default Website? Also, i am unable to start/stop newly created Virtual Server using ESM as these options are grayed out. Any help appreciated. I would re-write my query in more detail as follows : Hi, I have my scenario as follows : 1 Front end server 1 Back end server Front end server has 2 ip ad...

user forms #5
Hello, I have created a user form (click on New Employee). How do I get the information entered in to this form to appear in the correct columns in the Payroll data sheet? And how do I get the combo boxes to display the drop down information from the sheet called input. Regards Runner77 -- Runner77 ------------------------------------------------------------------------ Runner77's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30388 View this thread: http://www.excelforum.com/showthread.php?threadid=500502 ...

Integrity Problems in PivotTables
Hi All, I have used this workbook for some time and it appears the same as many others I use, but lately after opening it a couple of times I receive an error message that - 1, "Cannot read file", followed by 2, all my pivotTables lose functionality "Due to Integrity Problems" and I have to reset all the ranges again. I am using 2003 version and the range in question for the first table is in another workbook on the server. TIA Roger. Hi Roger, (reply in excel.misc) I don't have Excel 2003, but you might double check the several pa...

HELP! Seeking Instructions to Create Report of POS (NOT RMS) Discounts Applied
I really need a solution to this problem... We really need to run ongoing reports to determine how many of our different Discounts are claimed/used and the proceeds not tendered. We have offered a number of discounts to customers and employees as incentives and cannot find a method to track how each discount is applied. The built-in reports within POS (not RMS) are not nearly as robust as we wish/require. Is there a query we can run on a transaction table to reveal the discounts applied to those transactions? In the POS Query Tool, running the query "select * from [Transaction]"...

formula help #13
I volunteered to help set up a spreadsheet for a school fundraiser. I need help with a formula. The teacher wants to keep track of student profits. Basically, if a student sells 1-10 items, they receive $20 of credit, if they sell 11-20 items, they receive $20 in the account, etc, How would I set up a formula like that?? HELP Vivian K set up a table with the quantity to sell in one column and the corresponding credit in the next column. Then you can use a Vlookup formula using the actual amount sold as the lookup value to return the credit. See the help on Vlookup to see what you n...

problems switching speadsheets in excel 2000 using windows xp
I have a pc running XP Pro and Excel 2000 (office). The user has two or three speadsheet open at once. They are having problem using the alt tab to switch between sheets. If the use the windows they see them open and can click to change but would like to use alt tab ...

Subforms won't refresh after new record 11-17-09
Access 2003, XP Pro I have a main form with two separate subforms. I view various records by clicking on some list boxes. To add a new record, I select Records, Data Entry. I then (1) Make a selection in a combo on the main form. (2) Make a selection in a combo and enter some text in a textbox on subform1 (3) Make a selection in a combo on subform2 Subform2 has some code on the After Update event that saves the new record and goes to a previously selected record in cbo1 on the main form (cbo2 has code on the After Update event to go to that record). Here's the cod...

Query Problems 07-20-07
Hi all,i am a new user of access,i am now using access 2007,i wanted to link the 3 tables together,to create a query that will returns records of company,part number,description,selling price and date of purchase.below are my tables in datasheet view; Customer details Products detail sales detail id id id Company part number company Address alternate part number Telephone model description ...

HELP! Need Access DB to show realtime changes in different location
Hi Guys, I was wondering about how access refreshes it's data within forms and it seems that it doesn't. If i have a form open in one location on a network and update fields on the form the changes are not shown on the form opened in another location. (I.e. if 2 users are in the same form at the same time and one adds a new record the other users form will not show this change). Is there anyway that Access can be setup to show real-time changes over multiple locations? Preferably without having to do a manual refresh by clicking a button or something. All i want to do is show change...

OAB Problem
I have 2 Exchange 2K3 SP1 servers running in the same admin group, different physical locations. Server A hosts the public folders. I have installed Outlook 2K3 SP1 in cache mode on a user on Server B. A week later, I notice that anyone on Server B can't download the Offline Addressbook in Outlook...? Any ideas...? Will replicating the public folders to Server B resolve the problem. Thanks, Tom... In the Store's properties on ServerB, what's listed under "Offline address list"? Replicating OAB wouldn't hurt. -- Bharat Suneja MCSE, MCT ------------------...

Form Problem 01-07-08
I have a form setup with several items bound to certain fields in the database. How do I stop my form from adding a new record each time the form opens. I only want the record saved when the user hits the save button. Hi, on the form's data tab on the property sheet, set the Data Entry property to No. Jeanette Cunningham "CribbsStyle" <cribbsstyle@gmail.com> wrote in message news:48c1d1e7-0e2f-4819-b30b-da5e4fe5cbc8@r60g2000hsc.googlegroups.com... >I have a form setup with several items bound to certain fields in the > database. How do I stop my form from adding...

Problem Editing Transfer IN/OUT after HQ Xfer created
If I create the Transfer from Store1 -> Store 2 in HQ, I can edit the quantities in Store 1 after the xfer is created via Style 330. Question: Will my edits in Store 1 be visible in Store 2? If so, what is the procedure for doing so? David My question might be a little confusing. If I use Style 330 to create an X-fer from Store 1 -> Store 2, I can still edit the POs. Note: I run 401 before running 330. If I go into SO Mgr for Store 1, I can edit the TO, but my edits don't synchronize with Store 2 - this is after I ran a 401. Why? Is this a limitation of RMS? How do I edi...

gst/vat caculation problem
Can anybody tell me if the caculation is supposed to be correct, or how to fix it if it appears not to be working. I have 12.5 entered as gst/vat but for exapmle I put in 10.00 and it shows an amount of 1.11. Money 2005. This is the way it has always worked. With A VAT/GST rateof 12.5%, 8.89 is the amount which would generate the VAT of 1.11 - which sums up to a total of 10.00, so yes, it's correct -- Glyn Simpson, Microsoft MVP - Money http://money.mvps.org Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny or http://money.mvps.org/faq for UK tips and fixes for MS...

Internet Explorer Problem???
I think I have a sepperate issue from RMS...I recently hooked up the internet to my network, and so all of my computers have IE access. What seems to be happening is that for whatever reason ONLY Register 2 gets kicked out of POS, and any other program that may have been running shuts down as well, and it asks send error report, or don't send error to MS. What could possibly be going on??? -- Thank You Vince :) ...

create main configuration file for solution.
Hello. How can I create main configuration file for my solution (Win-Form : VS 2008. VB.NET), like a general ini-file. Thanks :) "Mr. X." wrote: > Hello. > How can I create main configuration file for my solution (Win-Form : VS > 2008. VB.NET), like a general ini-file. > It's called an app.config. There is a name space you can use to mainulupate the app.config from a program. I see it on the project source directory (not for solution). I cannot see it on the release directory. Is there any convention to read / write from app.config...

Need help on a formula 05-20-10
I need a formula to calculate the following information please: I will have someone enter a time (ex 6:31) in cell C3. If the time matches one of the times in cells B17-B22 I need it to display 4.6, if it matched one of the times in cells B23-28 I need it to display 4.7, if it matches one of the times in cells B29-34 I need it to display 4.8 and so on. Does anyone have a simple formula I can do for this please? =IF(COUNTIF(B17:B22,C3)>0,4.6,IF(COUNTIF(B23:B28,C3)>0,4.7,IF(COUNTIF(B29:B34,C3)>0,4.8,"no match"))) Can't do the "and so on" part, bu...

Interesting TIF file problem
At the company I work for, we have someone that exports AutoCAD drawings to a TIF file, then inserts the TIF file into an Excel spreadsheet. We have quite a few of these. Other users can open this spreadsheet, view it, do a print preview, but when they try to print the file the inserted picture does not show up. All that prints is a box with the broken url red x document image. It seems that the problem is with Office XP. The Excel files are created with Office 2000, and they print fine from Excel 2000. On Office XP and Office 2003, the file won't print properly (all systems are ru...

Error 2455 Closing Access 2007 database with form open
I have a form with a subform that is requeried when you select a new key for the main form from a combo box. Everything works fine - usually. But sometimes if you have the form open when you close the database down you get the following error message (twice) in a pop up. You say OK (twice) and the database closes OK "2455 you entered an expression that has an invalid reference to the property form/report" If I close the form before the database I never get the error. If I do not touch the form before you close the database I don't get the error. If I update a field by t...

Safe Pay Check Amount Problem
I am trying to setup Safe Pay. Everything seems to be working fine except for one annoying quirk. Our bank's Positive Pay data format gives us 12 characters for check amounts ($$$$$$$$$$¢¢). The file that Safe Pay generates is working perfectly except for those checks that are greater than $9,999,999.99 (9 characters). If the check amount is greater than that (say $10,000,000.00), the value that the Safe Pay file shows for that check is 002147483647. And, even if none of the individual checks exceed $9,999,999.99, if the total of the checks in the batch exceeds that a...