I have a subform that has the Expense Code from the Expense Codes Table and I want to look up the Description (yes I know its not correctly spelled in the formula but it is in the table)
=DLookUp("Expense Discription","Expense Codes","Expense Code=" & [ExpCombo])
But I keep getting "error" returned
I have double checked spelling field names etc...
I have a DLOOKUP which is used to auto populate a field on my Income Commitment Form based on a field on my Expenditure Commitment Form.It works perfectly when you enter a new commitment.Currently you enter a new Expenditure Commitment, save it but don't close it, then from the Expenditure Commitment Form there is is a button to enter a related Income Commitment. You save the Income commitment then close it, then close the expenditure form and you are on the Client screen with both these forms now showing as subforms, both have record selectors.
My issue is, that sometimes the Expenditure Subjective can change, so from the client form you use the record selector to open the Expenditure Commitment and select the correct Subjective, you then save it and close it. You should then click on the Income Record selector which opens the income commitment, but when you re-click on the Combo42 button, it doesn't reselect the Income subjective and I think it is because the Expenditure Commitment which is what it is looking up isn't open. My issue is how do I get the DLOOKUP to work and refer to the Commitment Form if it is a new commitment, or the Subform if I have gone back in through the record selector.Here is the code I have.
Private Sub Combo42_AfterUpdate()
If (Me.Combo42 = 1) Then
Me.IncomeSubjective = DLookup("[Subjective]", "tblIncomeSubjective", "[ExpSubjective]='" & Forms![frmCommitments]![cmbSubjective] & "'")
Me.OtherIncomeConfirmed.Enabled = False
Me.OtherConfirmedBy.Enabled = False
[code]....
I have a form "NewMetalF" that has three combo boxes. The first one is used to choose "Precious Metal" or "Base Metal". The second one shows all the metals based on the first combo box. When I open the form in form view alone the combo boxes work flawlessly. I run the form "NewMetalF" within another form "NewExternalPartF" by clicking a button "AddMetal". When the button is clicked "NewMetalF" opens. The first combo box is selected, and when the second combo box is clicked the error "Enter Paramater Value" "Forms!NewMetalF!cboPreciousOrBase".
View 13 Replies View RelatedI have an IIF function specified on a continuous sub-form as below - basically it checks to see if the value of two text boxes is equal, and if they are it displays 'And', if not it displays the value of the column EntitlementName. I'm trying to work around grouping not working in continuous forms.
When I view the form directly it works no problems but when I view the form as a sub-form instead of showing the correct output it shows #Name? Both the text boxes I refer to have the correct values in them.
=IIF([txtPrevValue]=[txtValue], Format("And"),Format([EntitlementName])
When I click on a record on the subform and right click to get the delete record option nothing happens in FORM view but works in DESIGN view.
I have checked all the record and form options and they are all correct.
Using Access 2010
I have a cascading combo box working fabulously in a form. However when I embed that form into another form, the cascading combo boxes quit working. After doing some searching around on the web I think my issue is either in the query that the dependent combo box references and/or in the "after update event procedure" but I just can't seem to figure out the exact changes to make to my database. Details:
Tables with data for combo boxes:LOOKUP TABLE - Veg Species - Species Type,
LOOKUP TABLE - Veg Species - Species Name.
Related together based on common field: SpeciesType
[code]...
When the form with the combo boxes is embedded as above, I can select the species type from the dropdown but when I try to select the species common name value I am asked to
"enter parameter value - Forms!SUBFORM2a - Enter Specific Veg Info!cmbSpecies_Type".
I have a "status update" subform on a form that the user can go in and put periodic updates for that record. I have one field on the subform called UpdateStatus that I want it to get its value from the current value on the main form (which changes periodically) called SDPPhase so that you can see the status at the time of the update and have a history.
I am trying to do this using DLookup with the following code in the UpdateStatus field:
=DLookUp("SDPPhase","BRD","BRD_Id = " & [BRD Id])
BRD is the main form's table and BRD_Id is on both forms and what links the subform.
The problem I am having is that although when I step through it...it picks up the correct BRD Id, the output is ALWAYS the value for the very first record in the BRD table...not the one for the BRD_Id on the current form.
I also tried putting the code in the form properties for the subform but that doesn't work either.
Any help would be GREATLY appreciated!!
I am using Dlookup to look up LastName, I have
AssignBy = DLookup("[LastName]", Employees, "POC =" & Me.Combo22)
I am getting an ERROR ""You entered an invalid argument in a domain aggregate function"
I need AssignBy = LastName from Employees Table where POC=what is selected in Form for Combo22.
ex. POC=3 go to Employees Table find POC that equals 3 and get the LastName.
Please Help!!
Jessie
I have two command buttons in a subform "sbfScoutRegDetails", one that launches a report and the other attaches the report pdf to an email, associated with the record ID when clicked.
Both of these buttons work fine when just the subform itself is open, but when viewing it in its main form, I get a window saying 'enter parameter value' for "Forms!sbfScoutRegDetails!ID" (this is the WHERE condition in the macro).The report then opens with all the record information blank.The full WHERE condition
Code:
[ID]=[Forms]![sbfScoutRegDetails]![ID]
Not sure if it's because of using a continuous form as a subform? I need the user to be able to view/print or email a contract to each group/contact that signs up.
Can't figure out why dlookup will not work for me in a query for certain dates?
Simple example to highlight the problem:
Table1 -
Read_Date----Read_Value
31/07/14-------10
01/08/14-------20
03/08/14-------30
20/08/14-------40
Query based on this table with the following calculated field:
Expr1: DLookUp("[Read_value]","Table1","Read_Date=#" &[Read_Date]& "#")
Query output -
Read_Date-----Expr1
31/07/14--------10
01/08/14
03/08/14
20/08/14--------40
Will not return a value for 01/08/14 or 03/08/14 (dd/mm/yy)
If you try it with many dates its skips several and I cannot see a pattern.
I am new to Access (using Access 2012) and All I am trying to achieve is to autofill the field [Frequency] from Table FullList based on the value of [Frequency] from Table Courses using a DLookup code used to update the FullList form. The code is not updating anything! It is frustrating! Frequency is a number (integer) and while Course is a text. The figures of my Access database are below.
View 2 Replies View RelatedI am trying to return a single value from a table and assign it to a string to be used later but Dlookup isnt working at all. below is the code im using and the error message im recieving is "wrong number of arguements or invalid property assignment"
Code:
Sub boo()
Dim result As Integer
result = dlookup("Definition", "Config", "Parameter = 'Mail Folder'")
End Sub
I have a small problem with dlookup multiple criteria. Vba code looks like this:
Code:
Label34.Caption = DLookup("[Spent_Hours]", "249_1_CHours", "[Date_Added]= " & Me.Text27 & " And [Shift] = '" & Me.Text29 & "'")
This gives following error:
Syntax error in number in query expression '[Date_Added]=4.02.2015 And [Shift] = '2'.
[Shift] column is a numeric field.
I have a form that have a calculated field which generate a series of numbers. Correct and usuable numbers. Now, all I want to do this is to total (Sum)that numbers up at the Form Footer.. I have a Text Box control on the form footer for this. And formula on the Text box control is this.. =Sum ([Dhrs]*[Davg]). I got error everywhere on my form when I open the form..I have been trying using NZ Sum etc..
View 14 Replies View RelatedContinuous forms error.
Hi,
I have a database which displays one form as a subform of another form. Originally the subform was displayed in datasheet view, but now due to changes I am trying to make to the system I want to display this subform as continuous forms. Unfortunately in datasheet view, when a record of the main form has several records (ie enough to require scrolling down the list) in the subform I get errors when scrolling through these subform records.
The error message that access is displaying is "The Value you entered isn't valid for this field. eg you have entered text in a numeric field or a number that is larger than the FieldSize setting permits."
I thought ok, maybe the subform design was duff (I am taking over a system designed by someone else) and so created a new subform, but the error still occurs with a totally fresh subform. The error occurs even when viewing the subform on its own. With a lot of records displayed on screen in the continous forms view I get numerous occurances of the abopve mentioned error message box and access eventually crashes. Yet in datasheet view everything is fine.
It's incredibly annoying as I can't fathom out what is causing the problem and how to fix it and to go about what I am doing in another way (ie without using continous forms) will mean scrapping a whole days work. I also don't like to do things a different way just because I can't get it to work the way I'd like to.
If anyone has some solutions to this problem I would be eternally grateful!
Thanks in advance!
John
How to use Dsum Function? Am using below following Dsum functions but they are not working and shown an error result.
=Nz(DSum("[Liters]","[SRB_SubformQry]","[Category]=MILK"),0)
=Nz(DSum("[Liters]","[SRB_SubformQry]","[Category]='MILK'"),0)
=Nz(DSum("[Liters]","[SRB_SubformQry]","[Category]<>MILK"),0)
How it works: You search for a User, and then you search for a Serial.Now, what if I want to edit the table? (inside the subform, the results). I added an openRecordSet, and Parameters, but I still get the error saying that Recordset cannot be updated.Why? I opened and sent parameters but still fails.
View 2 Replies View RelatedI have a main form with several subforms in tabs. From one of the subforms I list linked records to the main form (Clients) within that subform (Bank account details). I list the records and have a button to add new records.
Where the Client has one or more records in the subform the add button works perfectly.
When the subform has no records the add new records button produces the following error "Run-time error '2498' An expression you entered is the wrong data type for one of the arguments"
The add button has
Private Sub Command52_Click()
DoCmd.OpenForm "AddClientBankDetailsFrm", acNormal, , , , acFormAdd, OpenArgs:=Me!ClientId
End Sub
the "AddClientBankDetailsFrm" popup form has
Private Sub Form_Load()
If IsNull(Me.OpenArgs) = False Then
MsgBox "Form was opened with ClientID = " & Me.OpenArgs
Me!ClientId = Me.OpenArgs
Else
MsgBox "No ClientID was passed."
End If
End Sub
ClientId is the primary key of the main form and the secondary key in the new record.
Hello all,
I have been working on this problem for about the last hour, and have gotten absolutely no-where - I cannot figure out what is wrong with my DLOOKUP.
I am using it in a query, as part of an IIf statement, but to simplify things whilst testing, I have just bunged the dlookup into a text box on a form, but itself.
The code I am using is:
=DLookUp("[SHORTNAME]","CPL_NOMINEE","[FULLNAME]=" Like "*" & "[AccID]")
What I am trying to do is return the SHORTNAME, from a table called "CPL_NOMINEE", which is a linked table (to Oracle), where the field FULLNAME has the value of ACCID at the end.
An example of each field would be:
SHORTNAME: CNL
FULLNAME: H Noms Ltd 500222
ACCID: 500222
With the code above, I just get a blank box - as if it can't find anything in the dlookup, but I can SEE the data is there in the table - what's up?!?
Any help much appreciated!
Hi all
Am currently having trouble with a DLookup function as follows
Table
======
Products
ProductID
Description
Nett
I have a form (Purchase Order Detail Subform) where by I want to enter the Product ID manually and then for the Description and Nett fields to populate automatically
I've started on the description field:
=DLookUp("[Description]","Products","[ProductID] =" & [Forms]![Purchase Order Detail Subform]![ProductID])
but this isn't right, being a bit of a newbie to access its probably because I've done something really silly...
any idea?
Thanks
I built a form—frmDataEntry—whose Record Source is a query called qryEvent, which contains various fields from tblEvent. The primary key field is called EventID.
I also build a subform—sfmDataEntry—whose Record Source is a query called qryEventImages, which contains various fields from tblItem. One of those fields is ItemEventLink, which links records from tblItem to the EventID field in tblEvent. There is also an image box in this subform: filling in the field ImageFile with an image name (example: Logo.jpg) causes that image to display in the image box.
I’ve inserted the subform into the form using the Subform Wizard. Now I’m getting an error message that says "2450: Microsoft Access cannot find the reference form ‘sfmDataEntry’." Why this is popping up. The Link Master Fields and Link Child Fields sections of the property sheet are filled in correctly. Moreover, the subform records are still appearing, but the images linked to each record aren’t loading.
I am having a problem with my Contacts Database. I have attached it for your review.
My main form entitled "frmContact", is a form to enter a new contact. I have a Notes subform, and I want to be able to enter multiple notes for one person. I have a note for John Doe, and tried to enter a new note for him and got this error message:
"The LinkMasterFields property setting has produced this error: 'The object doesn't contain the Automation object 'tblcontact"."
Why is it doing this? I tihnk I have the relationships correct, and everything...
Hello,
I have a table called "ShrinkWrapBinding" where I am supposed to look up a "Price" from. As you see in the code below, I calculate a "totalPackages" number which comes from values in a form. My problem is that when I try to use this value (totalPackages) on the DLookUp function (as seen in this code) it gives me a syntax error exactly on the totalPackages variable. Please note that I added single quotes to the 'totalPackages' variable when I use it on the DLookUp function, and have tried every possible combination (single quotes, double quotes) but still doesn't work. What is it that I'm doing wrong?
Code:
Dim totalPackages As Integer
Dim tempPrice As Double
totalPackages = Me.QuantityBooks1 / Me.ShrinkWrapQty
tempPrice = DLookup("[ShrinkWrapBinding]! [Price]", "ShrinkWrapBinding", "'totalPackages' >= [ShrinkWrapBinding]![RangeField]")
Thank you
I made a form for a table which contains client questionnaire responses, "Dec4Docs". I wanted to include the names of these clients in the form, just to make everything easier for whoever is doing data entry, but client names are in a different table, "BasicInformation". Both the questionnaire table and the client information table use a common ID number, "WISnum". I found a number of online tutorials which instructed me to use the DLookUp function for what I'm doing.This site here, for example, suggests the following formula:
=DLookup("CustomerID", "Orders", "OrderID = " & Forms![Orders]!OrderID)
Which I adapted to:
=DLookUp("FirstName","BasicInformation","WISnum=" & [Forms]![Dec4DocsForm]![WISnumField])
^This was meant to find FirstName in the BasicInformation table where WISnum is equal to the WisnumField of the Dec4DocsForm form. Instead, all I got was a flashing error message that partially broke the program's GUI. I could not find a solution on Google, so I played around for awhile, bug checking by adding and removing things to the formula, until I finally got a little lucky and figured this out:
=DLookUp("FirstName","BasicInformation","WISnum=" & '[Forms]![Dec4DocsForm]![WISnumField]')
I wonder if you can help me. In my query, for this calculated field, i get an error message about a syntax error missing operator in query expression.
what did i do wrong? thanks
FormulaID is a text field.
UnitRM Combined: nz(DLookUp("UnitRM Combined","QryFormulaSummed","[QryFormulaSummed].[FormulaID]='" & tblCostJunction.FormulaID & "'"))
Can someone please help with the following:
Dim strX As Variant
strX = DLookup("State", "TblScheduleK12004", "[PartnerID] = '" & Forms!FrmPrintRpt!cboClient & "'" And "[SetNum] = " & Forms!frmPrintRpt!txtSet)
[PartnerID] is a text field
[SetNum] = is a number field
I can get it to work with the Partner ID OR the SetNum but keep getting the error when I add the "AND" to include both.