DLookup Method with subform

I am performing a valdidation rule on a field on a subform in the form of:

DLookup("[Invoice #]","Query1","[amt] = &" Form.subform.control)

This is with Access 97.  What is the syntax to refer to the control on the 
subform when using DLookup?

Thanks,
-- 
BT
0
Utf
2/12/2008 9:22:00 PM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
1084 Views

Similar Articles

[PageSpeed] 36

Forms!Myform.MySUBForm.Form.Mycontrol


"BobT" wrote:

> I am performing a valdidation rule on a field on a subform in the form of:
> 
> DLookup("[Invoice #]","Query1","[amt] = &" Form.subform.control)
> 
> This is with Access 97.  What is the syntax to refer to the control on the 
> subform when using DLookup?
> 
> Thanks,
> -- 
> BT
0
Utf
2/12/2008 9:34:07 PM
Well I still seem to have an auomation error.  The exact syntax I am using is:

=DLookUp("[Rev Amt]","[InvoiceTotalCompareforPayments]","[Invoice #] =" & 
[Forms]![Payment Test 500].[Payments Subform2].[Invoice #])

Within the control named "rev amt"of the subform named "payments subform2" 
the above is set for the validation rule. Where "rev amt" in query 
invoicetotalcompareforpayments is queried where invoice # in query matches 
invoice number entered in the subform above.  
-- 
BT


"roger" wrote:

> Forms!Myform.MySUBForm.Form.Mycontrol
> 
> 
> "BobT" wrote:
> 
> > I am performing a valdidation rule on a field on a subform in the form of:
> > 
> > DLookup("[Invoice #]","Query1","[amt] = &" Form.subform.control)
> > 
> > This is with Access 97.  What is the syntax to refer to the control on the 
> > subform when using DLookup?
> > 
> > Thanks,
> > -- 
> > BT
0
Utf
2/13/2008 1:40:00 AM
1. you don't need [brackets] in VB (qeries and forms yes but not VB)
2. you forgot .FORM. between subform name and control name
3. I don't think you can name a field with a #.
try renaming that field "InvoiceNo"

so you should have:
=DLookUp("Rev Amt","InvoiceTotalCompareforPayments","InvoiceNO =" & 
Forms!Payment Test 500.Payments Subform2.FORM.InvoiceNO

hth
"BobT" wrote:

> Well I still seem to have an auomation error.  The exact syntax I am using is:
> 
> =DLookUp("[Rev Amt]","[InvoiceTotalCompareforPayments]","[Invoice #] =" & 
> [Forms]![Payment Test 500].[Payments Subform2].[Invoice #])
> 
> Within the control named "rev amt"of the subform named "payments subform2" 
> the above is set for the validation rule. Where "rev amt" in query 
> invoicetotalcompareforpayments is queried where invoice # in query matches 
> invoice number entered in the subform above.  
> -- 
> BT
> 
> 
> "roger" wrote:
> 
> > Forms!Myform.MySUBForm.Form.Mycontrol
> > 
> > 
> > "BobT" wrote:
> > 
> > > I am performing a valdidation rule on a field on a subform in the form of:
> > > 
> > > DLookup("[Invoice #]","Query1","[amt] = &" Form.subform.control)
> > > 
> > > This is with Access 97.  What is the syntax to refer to the control on the 
> > > subform when using DLookup?
> > > 
> > > Thanks,
> > > -- 
> > > BT
0
Utf
2/13/2008 11:28:01 AM
Reply:

Similar Artilces:

Tutorial Search: Subforms
I'm having trouble getting some controls to work in my subforms and forms and all of the queries and subqueries that I've designed. Can anyone point to a good, concise tutorial that'll help with this. All I can find are very basic tutorials that are far short of something like this. Basically, I want to select a name from a drop down list. Once selected, I have 2 subforms that need to refresh and display the data related to that name. Then I need to change a few data fields, and have that data written to the correct table. And then do it over again. Thoughts? On Tue, 11 Mar 2...

dlookup question
I have the following dlookup =DLookUp("CountOfAPP_ID","qrylookupattend","PAT_ID=" & [PAT_ID]) if there is nothing to look up then the text box is blank, as i would expect. What i need to happen is that if there is nothing to lookup then the text box is set to zero. I have tried setting the default value to zero but it looks as if the dlookup command is overwritting it. Can anyone help??? Thanks Andy =Nz(DLookUp("CountOfAPP_ID","qrylookupattend","PAT_ID=" & [PAT_ID]),0) If the DLookup does not find a match, a Null is...

If query/subform is empty set calculation value to 0
Using a subform, I am summing a field across multiple records, then adding multiple subform totals to provide a single total on the main form. However, in some cases, some subforms may not return any records (Due to no records existing for the item), and in this case, i get a #Error response, and the rest of the calculations on the main form fails. How do I check to see of the table is empty, and if so return a zero value, otherwise, return the sum value being sought? Tony Consider using queries to fill the forms/subforms. Then use the Nz() function in your query to conver...

Form.Subform.Subform.LinkMasterFields
Hi What is the correct way to do this? [Forms]![FM_MainDetails]![FMS_Boat_subform]! [FMS_JobDetails].LinkMasterFields = "BoatJobNo" I get an error message: Object doesm't support this method. Stapes You need to include the .Form bit: [Forms]![FM_MainDetails]![FMS_Boat_subform].Form![FMS_JobDetails].Form.LinkMasterFields = "BoatJobNo" Explanation: Referring to Controls on a Subform at: http://allenbrowne.com/casu-04.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, r...

Unable to set authentication methods using Exchange Systems Manager
Hello I am trying to set the authentication methods (default domain for basic auth.) for my OWA virtual directories through Exchange System Manager. However, when I access the property sheet, everything is greyed and I cannot change anything. I am able to do the settings using IIS manager, but they will be overwritten by Exchange. I am using a single Exchange Server 2003 with Service Pack 1 running on Windows Server 2003. Any help will be appreciated. Regards, thomas This is normal, since you have only one server it's a back-end server and back-end server needs SSPI (integrated...

The difference between putting textbox on page and subform
Hello, On the Access sample for Address database, I see on the form, tabcontrol, on page1 is directlly put textbox on page and on the other page the form is put. What is the difference of these 2 ways. What is the purpose Thanks in advance -- H. Frank Situmorang Putting a textbox directly on the tab control, the text box can use the recordsource of the form the tab control is on and be bound to a field in that recordsource. To use a SECOND (or more) recordsource (i.e. different tables) you can use a subform to be able to do that. -- Bob Larson Access World Forums Super Moderator ...

Resetting subform filters
Hi all. I have a form with two unbound subforms. The main form has a combobox whose selection I use to filter *both* subforms. Code called on after update: Private Sub cmbMyCombo_AfterUpdate() If IsNull(cmbMyCombo) then _ cmbMyCombo=-1 if cmbMyCombo=-1 then me.frmSubForm1.Form.FilterOn = False me.frmSubForm2.Form.FilterOn = False else me.frmSubForm1.Form.Filter="myComboId=" & cmbMyCombo me.frmSubForm1.Form.FilterOn = true me.frmSubForm2.Form.Filter="myComboId=" & cmbMyCombo me.f...

Access 2007 DLOOKUP
Can someone please help me with this expression? I am trying to use an ID to bring up a Name in a form. The form is named ISSUE. The table that has the ID's and Name is DEALER. The form and table fields are named exactly the same. Is this where I am making my mistake? Do I put the Dlookup in the ID or Name control source? I am new to this, so if someone could tell me what fields and table names to put in the expression and where that would be wonderful. Thank you in advance for your assistance in this matter! This is the expression I am trying to use:=DLookUp("[Name]&quo...

Set value of a field based on a value in a subform
Hello - I have a form to add a new record to a record set. I would like to create an AfterUpdate procedure that will set the value of one of the fields in the main form to the value of the related field in the sub form. The forms are linked by the BrdSN field. I have: Private Sub BrdSN_AfterUpdate() ' set the value of the BGA# field in the main form ' to the value in the BGA# field in the sub form Me![BGA#] = Me![MySubForm].Form![BGA#] End Sub but nothing is updating. Thanks for your help! sandy In which form's module is this co...

Setting subform to add mode while main form is in edit mode
Is it possible to have the subform on a main form open in add mode while the main form is in edit mode? I have a main form that shows the first and last name of my drivers while the subform is for entering a date and number of hours on duty for that date. The subform is beginning to have a large number of entries and I have to scroll down to the last entry to begin entering data. Thanks for your help Justin Yes, just set the subform's Data entry property to Yes. This sort of thing you can just try for yourself by changing the setting to the one you want. Then test the for...

Dynamic form, columns of subform based on a table that changes with each application
Hello! I want to make a form/subform that can be used across clinical trials. The form container for the subform won't change. However, the columns of the sub-form will change based on records in a table. I have a table of subjects with unique identifiers. I have a table of vital signs. The vitals collected for each study will be different. The vitals are collected at multiple time points. I want the subform to show the unique identifiers for the subjects as the rows and the columns to be the vital signs. Once collected on the form, I'd like to transpose the vitals into one vi...

Form and Subform for single ID
Hi I am in the process of creating a Case Management database. Every week, a coach meets with the students for Case Management purposes. When the youth becomes involved in Case Management, they complete surveys at 6 months intervals. I also have several tables (forms) which are based on survey questions which must be completed at six months intervals. The main youth form is in the Participant table (there is YouthID is the PK). I have created a One-to-Many relationship between this table and the survey tables but there is no referential integrity since I have more youths...

A good method for searching a table with an entry from a form?
I am trying to figure out a good method for allowing a user to enter student data into a form to determine if the student already exists in a table. I have a table: STUDENTSTABLE with the fields Last_Name, First_Name, and DOB. I have a form: frmSEARCH with the text boxes lname, fname, and dob. The form also contains a button: search_students. I have not specified a record source for the form. After the user enters he student information into the form and clicks search, a message box will popup stating whether the student is or is not already in the table. I know how to pop...

Restricted number of records in form and subform
Hi everybody, I have a form which updates a table that contains among other fields: "Month" and "Employee Name". For each month and each employee there is ONLY one record allowed. How can I check if the user of my DB is inserting a second record for the same month and employee and prevent him from saving? Somebody suggested I make a condition in the BeforeUpdating but I am a beginner with Access and can't really write CODE. Could somebody help me with this? (show me the procedure in SQL). In the same form I also have a subform in which I am only allowed to insert 5 r...

Info in subform showing up multiple times..
I have created a report that will summarize all of my invoices that are in the Invoice form. My problem is that in the report it is showing all of the seperate charges on the same invoice. Say if there is 3 charges on the one invoice, that invoice comes up 3 times on the report. I can get the report to show the invoices only one time, but I need the netamount in the report also. In order for the netamount to be shown I need the quantity and unitprice in the report, causing the problem of the charges coming up more than once... Can someone please help me? -- Message posted via AccessMonster.c...

problem with WriteXml method
Hi, I used WriteXml() to write my data from DataSet to the file. The output in the file is fine. However, there is one problem with my Column names in the table. If my column name has more than two words seperated with space, the output use "_x0020_" to connect those words. For example, if I have column names, such as "Inbound Vehicle", "Arrived At Holding", those names would be written as "Inbound_x0020_Vehicle","Arrived_x0020_At_x0020_Holding". Anyone idea to solve this problem is greatly appreciated. *** Sent via Developersdex http://...

Cursor flicker on subform
I have a form in an Access ADP app that exhibits weird behavior on a few PCs in the office. The app was originally coded in Access 2000 and win2k, and now runs under Access 2003 and WinXP. It's a single form with a tab control coded with 6 tabs and mostly bound sub forms beyond the first tab. The last tab contains a bound tablular subform, linked to a form on another tab.. When the user navigates to the last and creates a new record, the cursor starts flickering between the arrow and hourglass. The first column on the form is a drop down list that's bound to a small table for lookup,...

Make subform visible when a field has a particular value
Hi, I have a field in my form that is a text field, but will only be entered as Y or N. If the field is Y, I want a subform to be displayed, if it's N the subform can stay hidden. I can't work out exactly how / what to have the subform bound to to make this work. thanks JJ Use the link master field and link child field to link the subform to the main form. Use the after update event of the textbox to show or hide the subform. Use the current event to show or hide the subform when opening an existing record for editing. Jeanette Cunningham MS Access MVP -- Melb...

Setting the focus to a field on a subform within a tab control
I have a form called frmMain with a tab control called TabCtl0 and a subform within the tab control called frmScreening. I would like to set the focus to a field called txtserial when a new record is added. I have tried the following 2 lines, but neither work: [Forms]![frmMain]![frmScreening].Form![txtserial].SetFocus and Forms!frmMain!TabCtl0.Pages(1).txtserial.SetFocus Would you know the proper syntax. Thanks. gaugust wrote: >I have a form called frmMain with a tab control called TabCtl0 and a subform >within the tab control called frmScreening. I would like to set the ...

The Webpage Fetch Method I've Been Using Has Quit Working
I have used the following function to fetch webpages for a long time and in several applications. hOpenUrl = InternetOpenUrl(hOpen, sUrl, vbNullString, 0, _ INTERNET_FLAG_RELOAD, 0) bDoLoop = True While bDoLoop sReadBuffer = vbNullString bRet = InternetReadFile(hOpenUrl, sReadBuffer, _ Len(sReadBuffer), lNumberOfBytesRead) sBuffer = sBuffer & Left$(sReadBuffer, _ lNumberOfBytesRead) If Not CBool(lNumberOfBytesRead) Then bDoLoop = False Wend If hOpenUrl <> 0 Then InternetCloseHandle (hO...

CopyHere Method Cancel
I am using an adaptation of the code I found at http://www.rondebruin.nl/windowsxpzip.htm to zip some files in VBA (Access). It works fine, but in adding error handling, etc, I can't seem to find a way to determine if the user pressed the cancel button of the UI. The CopyHere method http://msdn2.microsoft.com/en-us/library/bb787866.aspx specifies some parameters, one of which is 'do not display a progress dialog', but they seem to be ignored. Can anyone tell me how to detect if the cancel button was clicked, or the process failed for some other reason. There are no errors gen...

What kind of serialization method should be used
Hello! Assume there are two applications developed in .NET running in two different networks that should use serialize/deserialized data between each other. What kind of method should be used.. There are two alternatives 1. BinaryFormatter 2. Soapformatter According to the docs it says. You should choose BinaryFormatter only when you know that all clients opening the serialized data will be .NET Framework applications. Use SoapFormatter when other applications might read your serialized data and when sending data across a network. //Tony Tony Johansson wrote: > ...

Shared Web Method
I'm using Ajax Callbacks which require a webmethod attribute and require that the method be declared as public shared. In order to access session variable values I have to use httpcontext.cusrrent.session... Isn't this a bad practice in a web application? Isn't there a possiblility of session crossup? What is the correct way to handle this scenerio? Thanks, Ron Hi, > Isn't this a bad practice in a web application? Isn't there a possiblility > of session crossup? HttpContext.Current is the context for the current request that is processed ...

why crash in the second method of one interface
Hi, Using one class generated from *.tlb to call 2 methods of one COM. The strange thing is that I got one error of the "First-chance exception in : 0xC0000005: Access Violation" in the second method. I also use ASP web page to call both methods of this COM and both methods seems is OK. ITemplateManage mytemplate; if (!mytemplate.CreateDispatch("comobj.TemplateManage")) { IDE_Error::Report (ComError|1,"ComObj.TemplateManage in FUN_Template::Add()"); return false; } result = mytemplate.Check (templatefilename); decodeparameter=mytemplate.GetInfo (templ...

does not contain a definition for 'PolicyValidate' and no extension method 'PolicyValidate'
in the .ASPX i have <td> <asp:Panel ID="Panel1" DefaultButton="btnNewPolicy" runat="server"> <asp:TextBox ID="textPolicyNumber" runat="server" MaxLength="10" /><br /> <asp:Button ID="btnNewPolicy" runat="server" Text="Get Forms" OnClick="lnkNewPolicy_Click" Visible="true" /><br/> <asp:CustomValidator ID="PolicyNumb...