Queries :: DLookup In Reports Control Source For Each Field
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.
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
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 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.
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.
I 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 have a form that has five subforms on it. On Open all the subforms are unbound (so i can prevent the queries from running before the On Open event to speed up). Then i can enter my criteria for all five subforms in txtbox and click run. After I establish the source object for the subforms, i cannot establish the controlsource for several text boxes that pull the data from the subform, i get #Name?. her is a sample from the on click event... the top links the unbound subform and the bottom should then link a control in the subform to a control on the main form.
Me.Child167.SourceObject = "query.RP Sum Fuelman F1" Me.Text71.ControlSource = "=[RP Sum Fuelman F1 Subform]![cntRecords]"
i have a list box with 6 columns. in column 6 there is a time field. on selecting this list box i want to set the value of a combo box to the time field.
This has no effect. I am not sure if i have done this correctly. but the combo box has manual values in it. the reason i want to have values in it is so if the treatment is 45 minutes at default. But i want to change it to 30 minutes just for a single occasion then i thought that this would be the way to do it.
I want to create a table of all fields in a form, their datatype and their controlsource.
I have done the first 2 parts easily enough using the forms.formname.controls.name property and TypeName(forms.formname.controls)
But I am unable to pragmatically read the controlSource. EG forms.formname.controls.txtname.controlsource works
but when In a for loop
I cannot use the name of the control to return the control source.
Code: Sub PopTab Set frm = forms.FrmCases for each CTl in frm.controls MyName = ctl.name MyType = TypeName(ctl) MySource = ctl.name.controlsource end if next ctl end sub
When the user enters the invoice number it populates the ship, consignee, billing address information. I have the list box to display line items for that invoice.
Currently I just have them enter the invoice number twice.
Is there a way I can take the user input and apply it to my other query?
I'm preparing a query as the control source for an unbound listbox. The following code gives the desired results:
Code: SELECT DISTINCT tblCameras.CameraNum, QrySbfShotList.CamerasFK FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID WHERE (((QrySbfShotList.shootsFK)=[my].[control])) ORDER BY QrySbfShotList.CamerasFK
(My.control will be a control on the form. For the time being, I let the query prompt me for a value.)
It produces two columns like so:
CameraNum Camera ID 1 2 2 3 3 4 4 5 5 6 6 7 8 9 11 12
CameraNum is text; CameraID is numeric.
Now, I'm trying to use a trick I read about that should add a single textual entry to the top of the list like so:
Code: SELECT DISTINCT tblCameras.CameraNum, QrySbfShotList.CamerasFK FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID WHERE (((QrySbfShotList.shootsFK)=[my].[control])) ORDER BY QrySbfShotList.CamerasFK
union
SELECT "(ALL)", "Dummy" FROM QrySbfShotList INNER JOIN tblCameras ON QrySbfShotList.CamerasFK = tblCameras.CamerasID WHERE (((QrySbfShotList.shootsFK)=[my].[control]));
This produces
CameraNum Camera ID ALL Dummy 1 2 11 12 2 3 3 4 4 5 5 6 6 7 8 9
The second (numeric) column is now out of order. This is reproducible for other values of my.control. If there are double digit entries they get inserted at the third row.
Why? What am I not understanding about how UNION works?
(BTW, I know I could put the "ALL" entry into tblCameras, thereby avoiding the need for a union, but I'd still like to know why the unexpected result.)
I am relatively new to Access and i am wondering why i cannot find a specific field from a table on the control source when i am and attached it to a check box.
I have created the field on the table, and now i want to attached it to the check box on a form however when i go down the list of control sources i cannot see it.
I have an access database where I have added a new field, but I cannot find the name of that field in the Control Source list to link it from where the data will be entered. It IS in the table, too. Why in the world is it not working.
A user enters a New Job Opening into the form for New Job Opening. Later, a user enters a New Interview in the New Interview form.... when they enter the job opening number into the job opening number field, it auto-populates the other information previously entered into the New Job Opening form into an area within the New Interview form. I have made sure the new field is in the table for the New Job Openings table.
I have a search from that has an option group, text fields, and a checkbox where the users selects a variety of option that generates a query. If the query return at least on record a split form (form on top/datasheet on bottom) is displayed and the form has an Edit and Close button. If the query returns no records the form is still displayed except the buttons are not visible.
If I knew the result of the query and then made a decision whether to open the form or release control back to the search box that would be great. To open the form I am using VBA with the DoCmd.OpenForm(,,"MyQuery","criteria") command.I thought about using the DLookup command and evaluate the return value for null to be used to control the program flow.
I have a database that reports activities by region.
Each week, my regional volunteers report statistics on a number of club activities. This is in the form of zero to theoretically infinite activity reports that they enter on a website. I download the .csv from the website, add the activity reports to the activity table and send them a totals summary every now and then.
The summary report shows figures for every club in the region, even if no activity reports have been entered for that club that week or ever.
This works fine, including forcing the query to return zeros when no reports have been submitted for that club.
What I want to do is have the report also show (in brackets next to each figure) the position as it was X number of days previously.
I can make the query and report to show the figures now.
I can make the query and report to show the figures X days ago.
What I cannot work out is how to combine the two queries into one report source so that I can get
Club 1 100(50) 75(0) 45(45) Club 2 0(0) 0(0) 0(0) Club 3 20(19) 0(0) 200(50)
etc
If I try and make a third query that gets the sums from qryNow and the sums from qryXdaysago for each record in qryClubsByRegion, I get two lines for each club.
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 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:
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.
I have been all over the internet and trying different things for hours to no solution. I have created a form (Code) which I am using to auto fill two categories in a form using dlookup. Both categories fill perfectly but I cannot get the information to transfer to a query in order to capture the information in a report.
These are the dlookups I've been using and the categories I'm attempting to capture are "Description" and "Category"
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?
I have a textbox on a report that I wish to populate based on the value that is in another textbox/field on the report, and I thought DLookup was the way to go - however, I cannot seem to get it to work.
I have a table (ComplaintsResponses) that has two fields, both text
[ShortDescription] [ResponseText]
The text from [ShortDescription] is saved in a field on another table that contains all the other relevant information that is used in the report, and whilst this short description is mostly fine, I have one report where I need the data from the larger [ResponseText] field.
I am in the process of creating a training database that includes levels of proficiency with certain tasks for employees.
In one of my reports I would like to appropriately display with tasks the employee "Cannot Perform";"Can Perform with Assistance";"Can Perform Alone";"Trainer" (straight from the field list of the task). But I can't seem to get the hierarchy correct. Tried it in a PivotTable too as I thought similar to PTs in Excel you could get some kind of "count" of values. Couldn't make that happen either.
Each employee has a proficiency rating on about 20 different tasks. Proficiency input is controlled by a field list. I would like to structure this part of the report like so:
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?
