Forms :: DLookup As Source For Text Box
Jun 2, 2014
Database has a form on which the user is to select a PP Type. This is a choice of up to 8 different kinds of types. There is a table in the database that keeps track of what each of these types are, either a pallet or a case. I need to create an unbound text box on the form that will display wether the chosen Type is a pallet or case. I understand DLookUp is the way to go on this but I am having a problem with the where clause part of the code.
What I am trying to write here is display in the text box the "Pallet Case" field in the "PP Type TBL", where the "PP Type" field of the "PP Type TBL" is the same as the selection in the combo box name "Type". PP FRM is the name of the form I am working on and where the text box is as well as the combo box.
DLookup("[Pallet Case]", "[PP Type TBL]", "[PP Type] = " & Forms![PP FRM]![Type])
I get #Error in the text box. My confusion is how do I refer to the PP Type as it is int he PP Type TBL in the criteria?
View Replies
ADVERTISEMENT
Feb 7, 2014
I currently have a DLookUp formula in a Text Box Control Source. It returns the value I am looking for, however, the value will not add to the record. I have tried putting the DLookUp formula in the Default Property, but it is not working.It is a multi-criteria DLookUp, where the criteria is entered into the form.
=DLookUp("[TBA CUSIPs]","[TBA CUSIPs]","[Settlement Month]=" & [Forms]![Enter Trade]![Settlement Month Text Box] & " AND [Coupon]=" & [Forms]![Enter Trade]![Coupon Text Box] & " AND [Instrument] = '" & [Forms]![Enter Trade]![Instrument Combo Box] & "'")
View 3 Replies
View Related
Apr 23, 2015
I have 3 text boxes on a form each of which gets their values from different queries using DLookup this is functional but only when I click on the text box its self, I have tried using the "On load" & "On current" to requery the textbox (Text38 as control name) but no luck,, another strange thing i've noticed, when I have the form open in access and i click on my taskbar, thus switching windows from access to the desktop but keeping access in view, all text boxes are calculated immediately.
View 2 Replies
View Related
Jul 23, 2015
I'm trying to create an Expression as Control Source for a Text Box.An example which can be found is:
Code:
=DLookup("[ProductName]", "Products", "[ProductID] =" & Forms![Order Details]!ProductID)
In above example the criteria is based on [ProductID] on a form.Is it possible to change it, so criteria is based on a value from a specific query, i.e. similar as above?I have tried something like this, but it doesn't work:
Code:
..... & Query![QRYORDER]!ProductID)
View 2 Replies
View Related
Jul 7, 2014
I have an unbound form which has 2 unbound combo boxes on it, both based on the same table, but I have used a query for each one to created the sort order, one alphabetically by item name (cmbA) and the other numerically by the item number (cmbN) . I also have a text field (ID) upon which is based a report when the user clicks a button. Currently the text box (ID) has a control source of column 1 of cmbA and I have the report working perfectly if one selects from the name combo box (cmbA) What I want to do is allow the user to select from cmbN and have the control source of the ID text box equal the number the selected so the report button will work then also.
I have tried to set the control source after update of cmbN but all that does is blank out the ID text box. I have not done this before so I am sure I have done it incorrectly. I tried again this morning using me.ID.ControlSource = Me.cmbN and stepping through the code shows that the cmbN ControlSource is equal to ID but I can't make the code that follows use the results.
View 8 Replies
View Related
Mar 19, 2013
My form is based on a table (SOP TBL) and has several text boxes with control sources of the records in the table. I need to add a criteria to one field (SOP Number) so that only current versions of the number show up. I have the SQL for a query that I use in another form for a combo box as shown below. What this criteria does is find the version number which is the last 2 digits of the SOP Number and displays only that record for the SOP Number in the combo box. How can I apply this code to the text box with the control source directly from the table so only the current records are displayed on the form?
Code:
In (select max([SOP Number]) from [SOP TBL] group by left([SOP Number], len([SOP Number]) - 2))
View 1 Replies
View Related
May 30, 2013
I am working with textbox [DispCD] (a date field) on form [Contract]. There is a dropdown on combobox [Searchbox] for selecting the desired record. I am writing VBA in the [Searchbox] OnChange event. The control source for [DispCD] would normally be "=Searchbox.column(11)". If "column(11)" has a date, I want [DispCD] to show that date and then be locked. If "column(11)" does NOT have a date, I want [DispCD] to allow entry of a date with the date picker showing. I've tried to assign the control source to [DispCD] with this:
CSVal = Format(Forms![Contract]!SearchBox.Column(11), "ddd m/d/yyyy") (this works)
If CSVal = "" Then _
Forms![Contract]!DispCD.countrolsource = "=SearchBox.Column(11)"
I get "Object doesn't support this property or method" error.
View 4 Replies
View Related
Jun 7, 2013
Can a query be used as a control source for a text box on a form?
=[QueryLibrary]![Kit] is the control source do I need to change anything else?
View 3 Replies
View Related
Jul 16, 2015
is it possible to use a sum select query in the control source field of a text box. I have a query that works fine in a combo text box but I keep getting a 'syntax error with subquery parenthesis' when I try and build on in a normal text box.
I am building the query using query design view and the query works and then I'm copying the SQL code behind the query into the text box field and access reformats it s a bit so not sure it's that's the reason.
R_P_Data_P = Table
approvalNosys = Field in the table
status = Field in the table
score = Field in the table
cmrOverview = Form
[approvalNoSys] = field in form
1 is the criteria
Code:
SELECT R_P_Data_P.approvalNosys, R_P_Data_P.status, Sum(R_P_Data_P.score) AS SumOfscore
FROM R_P_Data_P
GROUP BY R_P_Data_P.approvalNosys, R_P_Data_P.status
HAVING (((R_P_Data_P.approvalNosys)=[forms]![cmrOverview]![approvalNoSys]) AND ((R_P_Data_P.status)="1"));
View 7 Replies
View Related
Sep 26, 2014
I have a 2 unbound text boxes.1 populates the id number from a table.I want the other to display the first name where this id occurs. I am currently putting this in the control source of the seconded textbox
This is what I have
=dlookup("[FirstName]","[tblUser]","[ID]='[Textbox1]")
View 1 Replies
View Related
Oct 17, 2014
I have a form based on query. On form i am retrieving data from another table using DLookup in a unbound text box. So I want to save the result of DLookup function in another field/table on same form.
View 8 Replies
View Related
Jun 13, 2013
I am trying to create a form with multiple combo boxes where users can select fields from my main database and click a button to generate a report based on their selections. I think I am supposed to create a generic report with perhaps 4 fields where I would link the four combo boxes to. The issue is I cant seem to get the selection of the combo box to change the field that the report should pull from the database. I think I am supposed to use dlookup in the reports control source for each field, but I cant seem to get it to work.
View 14 Replies
View Related
May 29, 2015
DLookup function. (this is for a stamp collection database).
On my form ("InventoryInput") I have a text box called "Catalog" for a numeric entry and a text combo box for selecting a "Country" in drop down list.
I want to query a table called "CatNameList" to get the "StampName" of the item (based on the entries of Catalog and Country) and populate that name in the text box. The fields in this table are called "StampName", "CatNumb" & "CName" respectively.
I have successfully placed the following expression in the control source of that textbox and able to populate the StampName I need based solely on the catalog number alone.
That express is :
=DLookUp("StampName", "CatNameList", "CatNumb = Form![Catalog]")
So it will populate the "StampName" data to match the "Catalog" number entry just fine.
However, I need to add a second layer to incorporate the Country.
Example : There is a catalog "1" for "USA", and a catalog "1" for "Canada" but both have different "StampName".
I have been attempting to get that second piece added with no success. Here is the expression I have been trying to get to work :
=DLookUp("StampName", "CatNameList", "[CatNumb] = " & [Catalog] & " And CName = '" & [Country] & "'")
Right now, the text box is just blank with the above expression. I thought it may be because there was no match found, but I have triple checked to ensure I have the spelling correct on the country name in both places.
Basically, I just need the dlookup to take the "catalog" and "country" off the form and match it to the "CatNameList" table fields of "CatNumb" and "CName" to give me "StampName" field back on the form.
View 4 Replies
View Related
Oct 15, 2015
I'm working on my first report and am trying to get my textbox, tboJob to show a field value and not the ID. I have tried the following Dlookup in my control source with no luck.
Code:
=DLookUp("[JobName]","Jobs","ID=" & [tboJob])
JobName is the field I want to show from the table Jobs.
View 7 Replies
View Related
Apr 15, 2014
I'm trying to pull a date from a table into a text box on a form tab control using DLookup and I just can't figure out what is wrong with my DLookup expression:
=DLookUp("DateOrdered","tDateOrdered","PrNumber=" & [PrNumber]) ----- (DateOrdered is short date, PrNumber is text, db is split Access 2013)
Whats missing in this expression? I've tried every criteria variant I could find but to no avail.
View 9 Replies
View Related
May 13, 2005
I have a combo box which has a table/query row source type. For the row source, I want to use a query that contains some data from text boxes on the form. I'm currently trying:
SELECT [LAB],[COAT] FROM [University] WHERE [COLOR]=tbColor.value AND [TYPE]=tbType.value;
Although this is a valid query, I don't get any results when used as a row source. No errors, just no results.
Thanks in advance...
View 5 Replies
View Related
Jun 20, 2006
Hi everyone,
This is a very simple problem, and it seems to me like it should work but it doesn't. On my form, I have a field called [mindate], which looks up the earliest date from a table. I also have a field called [maxdate] which looks up the latest date in my table. These both work great. My third text box [difference] is supposed to find the difference between the two above dates in days, but it doesn't work.:eek: The control source of this [difference] text box is this:
=DateDiff('d',[mindate],[maxdate])
Any suggestions?
Chris
View 4 Replies
View Related
Apr 8, 2007
hi,
im trying to add a text box for entering a date into the table database for my form but i cannot seem to connect the control source to the text box, the name of the box/script is not appearing in the control source list. I have already made a column in my table for this box on the form but just cannot connect the two properly?
Any quick thoughts on this?
Thanks.
Trevor.
View 2 Replies
View Related
Oct 26, 2012
I have the following tables and am trying to populate a text box on a form, and store that value in a field
TotalSegLengths
tbl_Segments
LineNum SegLength
1 3
2 9
2 10
tbl_Lines
LineNum TotalSegLengths
1
2
3
So when I create a new record in tbl_Lines from the form (Form is bound to tbl_Lines) it should query the tbl_Segments, add up the SegLengths for the corresponding LineNum and store that value in TotalSegLengths.
So for example, with the data as per above, from the form, create a new record with LineNum = 2 and it should display 19 in a text box and store 19 in the TotalSegLengths field.
I was trying to use DSum in the text box Control Source but was just returning an #Error in the text box.
View 1 Replies
View Related
Feb 2, 2014
I have two queries called "Query_match_AND" and "Query_match_OR". I want to show the total number of records for either of these queries in a form called "form_candidates_result" depending upon the combo box value (either "AND" or "OR") in a form called "form_match".If I put
Code:
=DCount("*","Query_match_AND")
in the control source of [candidatecount] in "form_candidates_result", the value is shown in the form and it works fine.If I use the following code in either of the "on load" or "on open" events in "form_candidates_result"...
Code:
Select Case [Forms]![form_match]![ANDOR]
Case "AND"
Me.CandidateCount.ControlSource = DCount("*", "Query_matching_AND")
Case "OR"
Me.CandidateCount.ControlSource = DCount("*", "Query_matching_OR")
End Select
it doesn't work and I get
Code:
#NAME?
View 7 Replies
View Related
Dec 24, 2013
I built an expression for the control source in Access 2007 report as follows:
=Sum([Weighted Value])/(Sum([CourseInfo.Credit])-Sum(IIf([StudentClassRecord].[Grade]="P",[CourseInfo].[Credit],0)))
This is to calculate the GPA. However, sometimes the denominator can become 0 and an error us returned. I would like to get "NA" in return instead.
All the field names are derived from table or query. I tried to aggregate the IIF function but ran into error message.
View 3 Replies
View Related
Mar 27, 2013
I have a report based on a parameter query. One of the parameters is TM Name. This parameter value is selected from a list box. The table on which the query is based also has TM Name as a look-up from a list. So ofcourse the ID is showing in the TM Name field on the report...not the actual name.
I am attempting to use a Dlookup to show the actual name than I will hide the field with the ID. Here's what I have as the control source:
=DLookUp("Name","TM_Names","[TM]=" & [Reports]![Report_TM_Records]![TM])
It is pulling in the value of the first row in the "Name" column of the "TM_Names" table. Doesn't seem to be looking at the value in the TM field on the reports.
My gut feeling is the report needs to be "saved" or "refreshed". I have tried a few macros to do this but I get errors.
View 1 Replies
View Related
Oct 7, 2005
Hello,
I've got a text box on my form header with a calculation in the control source that adds values from other text boxes on my form.
My problem is that since the calculation is in the control source of my text box I can not save the sum of the calculation to a field on the forms bound table.
Is there a way to move this calculation out of the control source and still have the calculation populate this text box with the sum of the calculation, so I can choice a field in which to save this calculations sum.
Calculation in the text box control source=
[Tot40yrcomp]+[Totfelt1536]+[Totfelt3036]+ there will be many more other text boxes added.
Thanks--Any help will be greatly appreciated.
View 1 Replies
View Related
Apr 19, 2013
I have a Dlookup which returns the correct value but returns the text of the instead of a numeric value. I have triple checked that the fields are all numeric. I suspect there is something wrong with my Dlookup syntax as it is my biggest weakness. In the following syntax what could be wrong?
DLookUp("SSER","TABSSS",[grosspay] & " Between [TABSSS].[Minrange] And [TABSSS].[Maxrange]")
View 4 Replies
View Related
Aug 3, 2015
I have a report based on a query. I want to populate 6 Text Boxes with Dates from fields in another query. The date fields I want to add will be headings for columns that represent weeks (they change all the time so can�t be hard figures). The two queries are not really related by any common field. I am not able to get this working because the fields I want are not part of the query that is the Record Source for the Report.
Is there any way that I can do this? Can I change the record source of just the text boxes?
View 5 Replies
View Related
May 29, 2015
Having problems getting dlookup to work in the control source field of a text box.
My form has fields : Catalog # (numeric value) and Country (drop down text selection).
I would like to query a table CatNameList for a name (text) if the catalog # and country find a match on the table.
My field names on the CatNameList table are : Name, Number (to validate against the Catalog # entered on the form) and CName (to validate against the Country drop down on the form).
I am successfully able to populate the name from the CatNameList table on my form using lookup of the catalog # using this :
=DLookUp("Name","CatNameList","Number = Form![Catalog #]")
However, I will eventually have several catalog numbers that will be identical in the table CatNameList, thus why the country is important as the second criteria to be added into the dlookup.
I have tried for a few hours unsuccessfully to add the second portion to my dlookup.
This is what I have currently (not working) that I have been playing with, I'm sure I'm missing a quote mark, & or something simple.
=DLookUp("Name", "CatNameList", "Number = Form![Catalog #] And CName = ‘”& Form![Country] & ”’”)
View 14 Replies
View Related
