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.
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]")
Hi,
I've created a db I plan on using for pricing out parts, the issue I'm having is that I'm getting a circular reference error.
The way my db is setup is like this Partstbl contains the information for all parts made. Some of these parts can have subassemblies and these parts will also be in the "partstbl". In another table called "Subassemblytbl" I choice the parts from the "parttbl". Parttbl has a one to many relationship with "subassemblytbl" The error happens when I created a query that was to calculate the part cost. This cost would then be displayed in the subassembly subform on my main form. I'm having some difficulty explaining this
if anyone can help I'd really appreciate it
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] & "'")
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 RelatedI 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 RelatedI created a query that pulls information from 2 other queries and everything was going fine until I saved the query. I now get a circular reference error
SELECT [CashValue Link Query1].Facility,
[CashValue Link Query1].[Financial Class],
[CashValue Link Query1].Date,
[CashValue Link Query1].Date,
[CashValue Link Query1].[Total A/R],
[CashValue Link Query1].Current,
[code]....
I have an unbound form with three tabs. On each tab there is a sub form. Each tab is a search form and each sub is a results returned. I have made everything unbound and set the sub form recordsource and its controls control souce on afterupdate of the main form search criteria. Works fine except for after some use the db decides the sub forms are not unbound and sets the record source and control sources.
I'm trying to do a
Me.PollingPlacesResults.Form.RecordSource = ""
Me!PollingPlacesResults!PollID.ControlSource = ""
but this does not seem to work in actualy removeing the record source and control source.
Here is my VBA Dlookup Code:
Code:
Public Sub test()
Dim frm As Form
Set frm = Forms!StationLevelSummary
[code]...
At the bottom im printing the content of the controls which are on my form. These should return 1 number, but for some reason it does not. Ive used this code many times but I cant figure out why nothing is being returned.
I'm new to access and need to modify an existing database to get a report I want to create. I'm an Excel user and could accomplish what I want to do in seconds using Excel's Lookup functions, so I'm trying to do the same with Access Dlookup.
I have two tables in my database that tracks components in a hardware product. The first table is just a list of hardware units shipped. The second table is a list of various components (and versions thereof) used in each unit. The two tables are linked by a UNIT_ID field. I need to create a report showing each unit and what the various versions of the components are. My difficulty is that the component table is does not have a dedicated field for each component and each unit can be comprised of a different quantity of components. So the tables look something like this:
UNITS TABLE:
ID Customer
1 Joe
2 Bob
3 Bill
COMPONENTS TABLE:
ID Component Revision
1 Main Board A
1 Blue Board B
2 Main Board B
2 Blue Board B
3 Main Board A
3 Red Board C
I want to produce a report that looks like
ID Customer Main Board Blue Board Red Board
1 Joe A B --
2 Bob B B --
3 Bill A -- C
I setup queries to get revisions of each Board and am trying to compile into the full report, but can't figure it out. Right now I'm trying to use Dlookup to lookup data from one of the queries and insert into a larger table. I can lookup a single value fine, but can't figure out how to get Dlookup to give me the Blue Board & Red Board values for each of the system ID numbers in a master query (which would be a snap in Excel).
I want the user to be able to see the source/reference of (many of) the different data points in my database, so I need to store sources/references in the database itself. I want to have all references in one single/common table (list of references). Each reference may be the source of many data points, in different columns in a given table, and in different tables. Is this possible, and can it be done in an elegant way?
View 2 Replies View RelatedI 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]")
I'm using a Dlookup in expression builder and it's only returning the value of the first record rather than the criteria I specify.
View 6 Replies View RelatedI 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 query on a form that is providing all of the information I need for everything on my form.
The problem is I don't know how to refer to a field's value in the query in VBA without having a hidden text field on the form.
I know I can reference a combo box's query to include criteria such as [Forms]![cboSelectStudent] if the control is actually on the form, but how would I refer to a value in the form's query that doesn't have a control on the form.
I've been searching for everything I can think to call this but haven't been able to come up with anything.
Thanks.
I added a new field to one of my tables and query, but when I try to add a control for that field into my form it is not recognising it and the field isn't appearing in the sources for the form, even though the table is sourced to the form? Or am I going to have to do the usual and redo the entire form because I made a minor adjustment?
View 1 Replies View RelatedI have table that I had to add a new field to which we update with a form. I tried to add a control for the new field but the field does not show up in the list for the control source. I am trying to add a list box to the form with a blank and 5 options.
I have attached screen shots of the table design and the form. The table has the field in datasheet view and I have manually entered a few entries in it but it still will not show in the control source for the form control. The top section of the form is where we enter and select the data for the new records. The bottom section (circled in red) autofills the matching record, from separate tables, for updating with the new entries.
I have added form controls for modified fields in the past so I am confused about why this is happening.
I made a custom function to look certain value from table based on couple of criteria that it gets from query where I want to use it. Function's code is below:
Code:
Public Function PotteryWeights(strLocusID As Long, nrPotSubID As Long) As Variant
Dim priSubID As Long
Dim priLocusID As Long
Dim priResult As Variant
priSubID = nrPotSubID
[Code] ...
However, when I use it in query it only returns Case else - option and everything else is empty.
I am having a problem with a Dlookup query. I want to achieve the following - I have 2 different tables
Demography Table - consisting of 2 fields, City and Region
For example
City Region
NYC NAM
The other Table totalflow consists of several field, one of the is flow from.
For example
Flow from
NYC
WAS
SEA
I want to do the following. Lookup the Flow from filed in table totalflow, and compare it to City in demography Table. If City = Flow from, return the value in Region. All are text fields.
I have tried following Query, but it only returns the same value for all rows. (from SQL view)
SELECT DLookUp("[Region]","Demography Table","[City] = '" &[Flow from]& "'") AS test
FROM [Demography Table], flowsize;
OK. I have searched and searched and every thread dances around similar situations but none seem to address this particular one. I am trying to make a control on a sub form visible/not visible depending on the condition of a control on a main form. The catch is that the control I'm trying to change the state of is on a subform located in a tab control. I've tried a thousand combinations and none seem to work. How do I reference the control on the subform in a tab control?
Main Form: frmMemberMain
Tab Control: TabCtl12
Tab Control Page: 2
Subform: frmChildren
Control on subform: txtRelationship
Can someone please help before my brain explodes!! Thanks! :eek:
I have a list box on a form which uses the following SQL to pull the list items from a table :
SELECT DISTINCT tblMyTable.MyField FROM tblMyTable ORDER BY tblMyTable.MyField;
For some reason, during testing, the first two items on the list are blanks. I am quite happy for one blank to be returned at the top of the list (as this would easily identify for the user any records for which this field has not yet been completed, which is a good thing) but I can't understand why it would appear twice?
Why doesn't the DISTINCT command ensure any blank entries only appear once?
Incidentally, I have a subform which populates on the back of selections from the list boxes, so I can quickly see the corresponding records which generate these blanks. What's puzzling me is that the same records show for both blanks on the list - suggesting it is the same value repeating itself (and not, say, "" vs " ", for example, which was what I originally suspected...)
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?
is it possible to refer to a control dynamically?
i need to refer to one textbox if the value in a related and similarly named textbox meets a certain condition.
for example:
txt_MMC_Fail_Rate and txt_MMC_Fail_Count
if the (calculated) value in txt_MMC_Fail_Rate exceeds 3%, i need the backcolor of each of these controls to change to hilight the failure to the user.
i'm iterating through the controls collection, but can't figure out how to refer to the fail rate when i'm looking at txt_MMC_Fail_Count. what i hope to be able to do is strip off "Count" from the current control's name and append "Rate", then use that value to refer to the txt_MMC_Fail_Rate control.
is this possible? if so, could someone educate me, because all my attempts have failed...
thanks,
john
(edit: i am aware of the conditional formatting available directly on the control, but prefer not to go that route if possible. due to the number of controls involved, and the fact that documenter does not pick up conditional formats, i prefer to do this in code.)
Hi,
I'm trying to reference a control in a subform from a module but I keep getting errors!
Here's what I've tried.. and what error I've been getting:
First try:
GetCboEntries = Forms.frmMain!frmSub.Form![cboEntries].Column(1)
(where frmSub is the name of the actual form in the subform control)
Error: Object doesn't support this property or method
Second try:
GetCboActivities = Forms.frmMain!chdMain.Form![cboEntries].Column(1)
(where chdMain is the name of the subform control)
Error: Object doesn't support this property or method
Third try:
GetCboEntries=Forms!NameOfMainForm!NameOfSubform![cboEntries].Column(1)
Error: Run-time error '2450':
Microsoft Access can't find the form 'frmMain' referred to in a macro expression or Visual Basic code.
Any help would be much appreciated!
Thanks in advance!
I have a main form "KZ_SEARCH" and within this I have a subform which is loaded when a button is pressed and is populated with query results (based on text entered in a text box). The subform is called "KZ NOTICES Query subform". That all works fine and the query modifies based on the text entered every time the button is pressed. The next stage is that when the subform is loaded I want the user to be able to select a line from the subform and to jump to that record in another form (which also needs to be opened from that selection). The reference for the record to select in the newly opened form would be the first column of the selected line highlighted in the subform (control name "KZ NUMBER").
View 10 Replies View RelatedOk, this is what I have:
I have setup my main form with 2 subforms to mimic a split form; this works fine.
To search for a record, I am using a cbo on the main form which I would like the user to select from. Once selected, then this will populate the first subform.
This is the code I have for the cbo:
Dim intAnswer As Integer
If IsNull(Me!cboCaseNoCFDWit) Then Exit Sub
With Me!sfFocus.Form.RecordsetClone
.FindFirst "Me!sfFocus.Form!CaseNumber = """ & Me!cboCaseNoCFDWit & """"
If Not .NoMatch Then
If Me.Dirty Then Me.Dirty = False
[Code] ....
I get an error at the .findfirst; states that the Microsoft Engine does not recogize Me!sfFocus.Form!CaseNumber
And the reason I am not using a regular split form is because I cannot, get the form to the size that I want. The splitform works great with the code above (a few changes to it of course), but the bottom of the splitform (datasheet) is too long and I cannot shorten it.