All; using 2010. How can I get a subform to grow or shrink based on num of records returned? Its a continuous subform. Tried changing Vertical and Horizontal Anchors as well as some VBA suggestions but havent been able to make any of them work.
View RepliesI have a complicated form I am working on. The problem is that there is A LOT of data on this form, and to evaluate the data, the end user really needs to see as much of it as possible. What was typed in text box 5, may affect how he or she will interpret what was typed in text box 1.
In a report, I can make the text boxes grow or shrink so that you can see all the text in the box.
Can the same thing be done in a form?
Can I make the size a sub-form expand to show all the rows of data in its datasheet?
The end user needs to be able to add notes to the end and you can't do that with a report.
Thanks,
David
I reported this a day or two ago and the post is now gone.I have a report with grow and shrink = yes everyplace - I have three fields aligned top and they do not grow or shrink correctly - if i move the fields so they are not aligned horizontally they all work, or seem to work, fine. Is this a bug or am i doing something wrong. Tested it on two computers.
View 3 Replies View RelatedHi,
I would like to count the number of records returned from a query, but am unsure how to do this using VBA.
Any help appreciated.
Thanks
Hello all. I need some help calculating the total number of records returned in a query anytime i run the query.
I have a form that creates an SQL statement and changes the querydef at runtime and displays the results in a listbox. I want to include on my form a percentage of the total records from the table based on the records returned in the query Ex. (5/10) so I can use this information on a report.
What I am trying to ultimately acheive is to calculate an attrition rate based on the number of (clients that leave in a given period / total new clients added in the given period )
thanks for your help!
tony-t
I am trying to use Dcount to find the number of records in a query and put it on a text box in the header of a report. The query is a parameter query. The button opens a form which asks for start and end dates, the query reads those dates from the form and the report displays the correct number of records that fall within the date range. The query is based on another query, which is based on the table. I tried =dcount("*","qryName") but I get #error. Maybe becauase of dates? or parameters?
Access 2010, Windows 7
I have a form [IUDATA]
I have a add record button.
I have a date field [DATEIN]
I have a text field [DRPNO]
If the [DPRNO] field is empty, I would like the user to have the [DPRNO] field be automatically populated after the user enters a date.
I'd like the format of [DPRNO] to be "dpr YY-XXX"
Where:
YY is the year of the [DATEIN] field and
XXX is number of records in that year.
So for example, if it was the 4th record with a 2013 date the [DPRNO] would be dpr 13-004.
I have a main form [frmZone] and a sub form (single form) [fromZoneSub] linked master/child by [ZoneID]
If there are, say, 5 related sub form records I'm trying to get a label [LabelCount] on the sub form to say "1 of 5" and as you click through the sub form for the label to change "2 of 5", then "3 of 5" etc....basically letting the user know how many records there are and as they click to the next record know which record they're on.
Is it possible to limit a subform to 3 records?
View 12 Replies View RelatedI have a continuous subform. Because the records are often many, the user needs to scroll down the page.
How do I have a text box in each row that shows Record x of Total Records?
I tried setting a text box control source = [currentrecord] in the Detail area of the subform but it didn't work . It did weird things because I suspect the control is trying to show the record that has the focus or something
I am trying to count the number of records in a subform where the text box (txtFinal) = RPR-RPR & RTN. The subform is called PartNumbers. The table name the subform is bound to is named Completed.
I tried using =DCount("[Part Number]","Completed","[Final] = 'RPR-RPR & RTN'"). That is counting the total number of records in the table, not the total number of records in the subform. How do I get it to count the total number of records in the subform?
I'm trying to create a database for a construction company, as an experiment more than anything, and my problem is this:
I need to create a set of identical records in the table "buildings" based on the information entered into a text box by a user on the form for registering a new project. I want each record to be identical except for the "plot number" field, which I want to start at one and increase to the number of buildings defined in the text box.
"Projects" is one table and "Plots" is in the other.
I have a form with 22 subforms (in about 11 tabs). Each one of these subforms has a controlling field in the main form (table) that dictates if the associated subform needs to comply with data entry rules or not.
For example:
Field in main form cboMed (Yes; No)
Subform: frmSubMeds has a list of medications
I want to be able to loop through all the records of the subform and determine if they are complete.
This check is run from a command button; that should also consider the value in the combo box. i.e. Check only if the combo has a value of "Yes"
Since i have 22 subforms and have to run the check on everyone of them, i have used the tag property to check if the record is blank or not. This is what i have so far
Code:
For Each ctl In Me.Controls
Select Case ctl.Properties("ControlType")
Case acSubform ' only look for subforms
If ctl.Enabled Then
If Not (IsNull(ctl.Properties("SourceObject"))) Then 'if it is a subform type
[Code] ....
The problem with this is that it only looks for the selected record in the subform and doesnt look for ALL the records.
I have a parent form and connected to it is a subform. On the parent form I have a checkbox which enables and disables fields on the parent form and also hides the subform.
What I want to do is when the user unchecks the checkbox, this action also deletes the associated subform records, if there are any.I'm sure that this can be done with an SQL Delete query in VBA.
Hi,
I have made a query from different tables; however, my query is returning about 5 rows for each person because some fields in the query return more than 1 row.
Is there anyway to fix this problem?
Thank you,
B
I have a simple subform that shows an address block.Created as a form, the can shrink property does not kick in. Created as a report, it works fine.All controls and properties and query they are based on are identical.The report pasted into Access 2010 works fine and missing fields shrink.The form pasted into Access 2010 does not.The problem I have is in how do I reference the "subreport" in the form so as to be able to requery?
View 1 Replies View RelatedHi all, I've searched this with no success.
I have a ODBC (Firebird) table linked using a DSN connection which has >300 fields. In Access, the linked table only show 256 fields (max for Access). Otherwise the link works fine. I have no control over the source Firebird table.
What I'd like to do is limit which fields from the Firebird table are shown in Access. I don't need all the fields, but I need some that are at the end and are not linked in the current setup.
I'm very much a novice at using ODBC, but can I somehow use SQL or some other method to specify which fields to show in the linked table?
Thanks for any suggestions,
John
I have a strange 'bug'.
I wrote a query to return the TOP 32 items for a key. Works fine:-
SELECT TOP 32 [my Disks Coefficients sub].[thier Disk ID] FROM [my Disks Coefficients] AS [my Disks Coefficients sub] WHERE [my Disks Coefficients sub].[my Disk ID]=1 GROUP BY [my Disks Coefficients sub].[thier Disk ID], [my Disks Coefficients sub].coefficient ORDER BY [my Disks Coefficients sub].coefficient;
I then plugged this into a query in the WHERE clause using In (Select Top .....;); [changing the =1 condition to pick up the correct id from the emcompassing query]:-
SELECT [my Disks Coefficients].[my Disk ID], [my Disks Coefficients].[thier Disk ID], [my Disks Coefficients].coefficient FROM [my Disks Coefficients] WHERE [my Disks Coefficients].[thier Disk ID] In (SELECT TOP 32 [my Disks Coefficients sub].[thier Disk ID] FROM [my Disks Coefficients] AS [my Disks Coefficients sub] WHERE [my Disks Coefficients sub].[my Disk ID]=[my Disks Coefficients].[my Disk ID] GROUP BY [my Disks Coefficients sub].[thier Disk ID], [my Disks Coefficients sub].coefficient ORDER BY [my Disks Coefficients sub].coefficient;);
This runs fine but the the number of items returned is for each key is less than if I run the Top query by itself for each key seperately? So when I run it stand-alone for ID 1 I get 127 rows (there are many equal coefficients); when I run it as a sub-query I get only 121 rows for ID 1!
Anyone any ideas?
Martin
I'm trying to run a line of code after doing a Query Search that reports back the total number of results pulled from the search. I've dug around and found some useful code, but nothing that gives the results I'm needing. Most everything gives me the total number from the query. I'm also using a Split Form.
The process goes: Enter numeric search in Text10 and hit the search button to run the following macro: [studentID]=[Forms]![Query1]![Text10]
It then gives me the requested information.I have a second text box (Text19) that needs to display the number of pulled results. I've written VBA that only gives me the total number searched for studentID or for the entire Query.
Can I simply add an additional line to the Macro?
I can get the number of records to be returned but I want to run a condition if the records returned is zero. To do this, I need to put the number returned into a variable but I haven't been find a way.
For instance, this code does work:
SQL = "SELECT Count(*) AS Records FROM Table1 GROUP BY Table1.Quantity HAVING (((Table1.Quantity)=4));"
conDatabase.Execute SQL
But I want to store the count as a variable. What I want is something that look this (it doesn't work but I just want to give an idea what I'm looking for)
SQL = "SELECT Count(*) AS Records FROM Table1 GROUP BY Table1.Quantity HAVING (((Table1.Quantity)=4));"
Dim hold as Integer
hold = conDatabase.Execute SQL
If(hold = 0) Then
'tell them nothing returned
End If
So how can I go about this?
Update: I see that someone posted a solution a few threads down using queries which did work. Can I still still do the same thing with SQL strings?
scratch
I'm using a very basic query
SELECT DRAWING_desc
FROM Drawing
WHERE DRAWING_desc Like '%L%'
I've got a table called Drawing, and a column in that table called Drawing_desc
I want all records returned that have the letter "L" in them.
When I run this query, I get nothing
If I replace the '%L%' with "*" it returns all records like you'd think it would.
What gives?
I am trying to create a Membership report that shows member activity over several months.
My problem is that the software that gives me the Member Activity does not report on accounts that were not used during that month
and some members do not come in every month.
I have a Member's Details table as well as individual months activity and have queries set to show the results of each month individually (these work, obviously)
The Reports I am trying to get from Access only show the activity of Members that have some movement every month, but totally skips members that are not present for 1 or more.
The report uses these queries but fails to report any member that is not in the tables for all months.
Example:
September
Rank Member FirstName LastName Activity
1 100 Andy Todd 200
October
Rank Member FirstName LastName Activity
November
Rank Member FirstName LastName Activity
5 100 Andy Todd 50
The report needs to show: Member FirstName LastName Sept.Rank Sept.Activity Oct.Rank Oct.Activity Nov.Rank Nov.Activity
but will not because there are no values for that member in October.
I am teaching myself Access as I go, so I am sure there is an easy solution.
Any help greatly appreciated.
Andy
We want to send letters to clients on behalf of our salespeople on a weekly basis from a list of clients in a table. But some salespeople will have 5 letters go out and some will have 20 go out (depending on what amount the salesperson is able to followup on). I can store the weekly letter quantity number in the table no problem, but I am just not sure how to have the query show 5 letters for Bob and 20 letters for Sue.
I know about the "Top N" setting in query design, but not sure how to set it to read from the table.
Any ideas?
i have a query that returns anywhere between 0-20ish records, and i need an efficient way to count how many records are returned. any ideas?
i was also wondering if there is a more efficient way to do this:
Code: [Text127] = DLookup("[Comments]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text175] = DLookup("[Performed]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text177] = DLookup("[Model]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text179] = DLookup("[Serial]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text181] = DLookup("[System]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text183] = DLookup("[HComments]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)[Text201] = DLookup("[Labor]", "orderheaderz", "[OrderNumber] = " & GlOrderNumber)
mabey i could condense them into one Dlookup somehow? any ideas?
thnx for your help
Hello all,
I'm having some difficulty obtaining the answers I want from my query. I'd have been more comfortable using filters in excel to find the answers, but my dataset is too large in this instance.
I devised a query which I thought should achieve the results, but based on a subset analysed in Excel the answers are significantly different.
My table has about thirty fields, the query I've produced is this:
SELECT AH_orphans.Product, AH_orphans.Comments, AH_orphans.ESP, AH_orphans.[ESP Parts],
<snip, all other fields in table>
FROM AH_orphans
WHERE (((AH_orphans.ESP)="n") AND ((AH_orphans.FRU)="y") AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a")) OR (((AH_orphans.ESP) Is Null) AND ((AH_orphans.FRU) Is Null) AND ((AH_orphans.EntitlementName) Like "*uptime*") AND ((AH_orphans.Account) Like "nation*") AND ((AH_orphans.Type) Like "Primar*") AND ((AH_orphans.[Is a parent?])="#n/a"));
What I'm hoping for is the subset which match the following conditions:
- ESP field is not "Y". Possible values Y, N, null
- FRU field is not "N". Possible values Y, N, null
- Only those records from EntitlementName which include the substring 'uptime'
- Only those records from Account which start with the subtring nation
- Only those records from Type which include the substring primar
- Only those records from [Is a parent?] which equal #N/A.
Asking the same questions in Excel I get around 1.5k records. If I use the quoted query on the same dataset in Access I get precisely 11.
Thanks for reading this far! If anyone can spot the flaw/s in my query, or indeed advise that I'm going at it completely the wrong way I'd be very grateful of some advice.
Cheers,
Alex
I have a table with an empty column called ActionID. I need to generate a numerical number that begins with 5000 and goes up by 1 number on each saved record. I have a basic form that links all of my other field to the table except the one I need to generate (ActionID) but will need that number saved to the table once its created.
View 11 Replies View Related