Paging 2 Recordset Loops At The Same Time??
Oct 23, 2004
I was just wondering if anyone can help me?
I have successfully paged through a recordset on my page using the .recordCount, etc. methods, the only thing is I have 2 independent recordsets looping on the page, which when there was not paging involved this worked fine, however now I need to span the results of BOTH of these across pages, I just cannot seem to figure out how! I have one set of records paging fine, but the other either messes the page up, or shows on the first page of results and no more of the other recordset, etc.... so yeah that is basically the problem I am hitting, so I was just wondering is there any examples or ways you could tell me to do this??
Any help that can be offerered would be very much appreciated ).
View Replies
ADVERTISEMENT
Jan 31, 2005
Hi.
I hava Form where a subform exists. The subform is expands or stays the same depending on
the products on the subform.
Therefore the Form it may be one page or could be 2 pages depending the subform list. Also I
have some details at the Page Footer in order every page in my Form to include those details.
But i can tot manage MA to make 2 pages or 1 page depending the subform list, when i print out the record.
Normally i get data in top of the next record and i Do not know how to declare that i am still in the same record.
I would like automatically to set the borders for the pages if is 1 or 2 even 3 pages..
I hope u understand what i am trying to explain.Thank you.
View 1 Replies
View Related
Mar 2, 2007
Does anyone have any samples or links to places that can help me. I created a basic list of items in a Access database, but there is so many I need to break them up. Any help is appreciated.
View 1 Replies
View Related
Mar 5, 2013
I have an Inventory database with 2 tables. Here is a simplified version of both tables:
First Table is Inventory:
Fields are
ID
Description
PackSize
PackCost
Second table is PriceHistory:
Fields are
ID
InventoryID
Date
PackCost
Fields are joined, one to many (one inventory record can have many prices in its history). ID in the inventory table is joined to InventoryID in the PriceHistory table.
The problem comes when I am browsing records in FORM VIEW of the inventory table:
As I page through the inventory records, I often see the same record multiple times (once for each entry in the pricehistory table).
For example, if the item POTATOES in the Inventory table has had it's price updated 3 times (3 entries in the PriceHistory) table, then I see the Potatoes record 3 times as I am browsing.
I only want to see this record once (with the latest price) when I am browsing the Inventory table in form view.
View 1 Replies
View Related
Dec 19, 2014
I am trying to have the code perform these things but unable to do so. While the code would still work, it will not move to the next user with either no date or the earliest date.
Goal:
-Go through the query record by record according to the date/time field
-Find the record with no date or earliest date (in that order)
-change its date to today's date
Code:
Code:
Private Sub Command56_Click() Dim i As Long
With Screen.ActiveForm.[AMID]
Dim LResponse As Integer
LResponse = MsgBox("Do you wish to auto-assign to an Accreditation Manager?", vbYesNo, "Continue")
[code]....
View 7 Replies
View Related
Jan 15, 2005
Is it possible to have sequal loops of some kinda? The basic setup of my DB is a table named "EC Faculty 2005", field name "Status", and the status field can be either "Approved" "Disapproved" and "In Process". There is another field called "Term Start Date" which holds months. I have been trying to create a SQL or some kind of query that will go through each month Jan, Feb, Mar, and so on, and count the number of Approved, disapproved, and in process for each month. This is what I have so far. Code:SELECT (SELECT Count([Status]) FROM [EC Faculty 2005] WHERE Status='Approved') AS Appr, (SELECT Count([Status]) FROM [EC Faculty 2005] WHERE Status='Disapproved') AS Disappr, (SELECT Count([Status]) FROM [EC Faculty 2005] WHERE Status='In Process') AS In Proc FROM [EC Faculty 2005]; That gives me a total count of the 3 statuses. Is there a way to loop this to count each status for each month? I hope thats not too confusing.
View 13 Replies
View Related
Jul 18, 2005
I am attempting to create a form for the automated printing of a series of reports. The form is tied to a query that captures Territory ID, Sales Rep ID, # of Reps in that Territory, Total # of Territories. Once completed, the code behind the form should:
1)Print the cover page report (Salesrep Sales Analysis – Cover);
2)For each of the territories, print a report for each sales rep (Salesrep Sales Analysis - A Rep) and once all the reps for that territory have been printed, print a summary report for that territory (Salesrep Sales Analysis - A Territory;
3)Print a combined report for all sales reps (Salesrep Sales Analysis - ALL by Month); and finally
4)Print the final page report (Salesrep Sales Analysis - Territory Totals).
I created the following code to accomplish that, but the results are in error:
1)The first rep for the first territory is being repeated (doesn’t seem to move off that first record at the correct time);
2)The final rep for the first territory is being skipped, and the first summary report is printed;
3)The second territory ends 1 rep short and the code moves into the 3rd territory without printing the summary for the 2nd territory;
4)The 1st rep of the 3rd territory prints, then the territory summary prints, then the 3rd territory continues, again stopping short of the final rep before moving to the 4th territory;
5)The two reps of the 4th territory (only 2 for that one) print, then the summary report for the 5th territory prints without printing the 4th territory summary or the report for the single rep for the 5th territory;
6)All three of the reps for the 6th territory print, then the summary for the 7th territory prints;
7)Three of the four reps for the 7th territory print, then I get an error message “You can’t go to the specified record” which ends the routine before the summary for that 7th territory and the last two reports are printed.
Private Sub Form_Open(Cancel As Integer)
DoCmd.Maximize
' Set Variables for Rep Count, Territory Count, Page Number and Date/Time
TotalReps = Me.CountOfSO_Rep
RemainingReps = TotalReps
Me.RepCount = RemainingReps
TotalTerritories = Me.CountOfTerritory
RemainingTerritories = TotalTerritories
Me.TerritoryCount = RemainingTerritories
PageNumber = 2
Me.PageNumber = PageNumber
Me.myTime = Now()
' Turn off action/warning messages
DoCmd.SetWarnings False
' Print Report Cover Page
DoCmd.OpenReport "Salesrep Sales Analysis - Cover", acViewNormal
DoCmd.GoToRecord , , acNext
' Loop through all the Territories - 01, 02, 04, 05, 06, 08, 09
Do While RemainingTerritories >= 1
' Loop through all Reps for the Current Territory
Do While RemainingReps >= 1
DoCmd.OpenReport "Salesrep Sales Analysis - A Rep", acViewNormal
DoCmd.GoToRecord , , acNext
RemainingReps = RemainingReps - 1
Me.RepCount = RemainingReps
Me.PageNumber = PageNumber + 1
Me.Repaint
Loop
' End of Rep Loop
DoCmd.OpenReport "Salesrep Sales Analysis - A Territory", acViewNormal
DoCmd.GoToRecord , , acNext
TotalReps = Me.CountOfSO_Rep
RemainingReps = TotalReps
Me.RepCount = RemainingReps
RemainingTerritories = RemainingTerritories - 1
Me.TerritoryCount = RemainingTerritories
Me.PageNumber = PageNumber + 1
Me.Repaint
Loop
' End of Territory Loop
' Print Territory Totals Report for the Final Territory
DoCmd.OpenReport "Salesrep Sales Analysis - A Territory", acViewNormal
' Print Totals Report for All Reps Combined
DoCmd.OpenReport "Salesrep Sales Analysis - ALL by Month", acViewNormal
' Print Totals by Territory Report - Final Report Page
DoCmd.OpenReport "Salesrep Sales Analysis - Territory Totals", acViewNormal
' Notify User that All Reports Have Been Printed
Beep
MsgBox "Salesrep Sales Analysis Reports have been sent to the printer.", vbOKOnly, ""
End Sub
I’ve spent a couple of days looking at this, trying different things, and am not getting satisfactory results. Can anyone find what I’m doing wrong here? Thanks very much for any help you can provide!
View 5 Replies
View Related
Jun 16, 2014
I have a linked table to a DB2 database. this table contains key-pair values and has about 140k records.
I use a Sub to update the value of a specific record.
The sub starts by opening the needed DAO recordset
Then it uses the rs.Findfirst method
It checks if rs.Nomatch is not true (so the records exists!)
Then it starts updating the record with
rs.edit
rs!value1 = myvalue1,
rs!value2 = myvalue 2
rs.Update
There is where I get the '3021 No current record' error
I use the same sub on the same table to update to different parts. One part works the other gives me the error.
I have checked for typos.
View 5 Replies
View Related
Nov 19, 2014
Having problem with loops. The inner loop updates a table. The outer loop pulls the record number from the "tblChangeOrderTable_Edit_Count" and is assigned to strRecordID . The inner loop uses strRecordID to find the right record. I keep getting errors like (Object variable or With Block variable not set.)
Code:
Private Sub btnClose_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
Dim db As Database, rs As Recordset, rs1 As Recordset2
Dim Criteria As String
Dim strAns1 As String
Dim strAns2 As String
Dim strCount1 As String
Dim strAns3 As String
[code]....
View 12 Replies
View Related
Apr 17, 2014
The idea is that I have a table with products. It is joined to another table that has each products ID and then a series of fields that correspond to each month of this year (so 12 fields). These fields contain how many of each product sold in that month. However some products did not appear in inventory until a few months into the year, so they have zero's for those months in which they didn't exist yet.
What I need to do is find the first month that each product went on sale, and pass that field back to my main table to do calculations with.
First I tried to do with with a query, but I ran into a road block and realized that maybe a query wasn't best as I likely needed a loop. So I started writing a function at that point... but it is obviously non functional.
As an example, if I had a product like this:
Product: X
SalesID: 1111
Price: 9.99
Month 1: 0
Month 2: 0
Month 3: 1582
Month 4: 2790
Month 5: 4501
Month 6: 4210
Then the idea of this function would be to look at Month 1, see if it contained a zero. if it did, move to month 2. If it doesn't, then send whatever that value is to a new field in the database.
So in the new table, I would have:
Product: X
SalesID: 1111
Price: 9.99
Month 1: 1582
Month 2: 2790
Month 3: 4501
Month 4: 4210
View 3 Replies
View Related
Jul 8, 2015
I am running an export function from a module1 that contains a loop nested within a second loop. Each loop is running through items in a separate combobox on a single form1. The outside loop goes through combo1 items and the inner loop goes through combo2 items.
My issue is that the value of combo1 determines what items are available in combo2 (values are tied to tables). I can get the combo2 values to update when a user changes the values in combo1 (using requery in the afterupdate property of the combo1). However, I do not want a user to change the values, and the code module1 is ignoring the requery. How to force the combobox to requery through code in a module?
View 8 Replies
View Related
Nov 22, 2014
I'm trying to create a compound interest form using loops and the following formula:
TotalVariable = TotalVariable + (TotalVariable * (RateVariable / 100 / 12))
I'm not sure whats wrong with my code and it's not working!
View 14 Replies
View Related
Aug 14, 2015
Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.
The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.
The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.
Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode:
? me.fsubsrNavSHLBHL("NavSH_QQ")
NESE ' this is the correct value for the current recordsource
Set a breakpoint right after the line:
fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
Immediate Window:
? me.fsubsrNavSHLBHL.Form.RecordSource
Select * from vsrNavigatorSHLBHL where Well_ID =91229
View 4 Replies
View Related
Apr 27, 2007
Hi guys, I'm a bit confused: I know that opening a bound form will bring down the whole recordset. But if I create a parametered query as the recordsource, will it still bring down the whole recordset first and only afterwards cut down to the records matching the parameters?
Example: I have a clients table with 5000 clients on a backend mdb, and my client lookup form in my frontend mdb. If I open the form looking for just client "Jim Jones" (via the parameter query), will Access bring down all 5000 clients from the backend to my frontend first, and only then seek out "Jim Jones" before discarding the rest of the recordset?
For if this is true then this whole Access business is rather unsuited for networks, even a small one. I hope you guys prove me wrong.
Premy
View 14 Replies
View Related
Jul 7, 2013
I want to write a email where there are 2 or 3 different ordernumbers for same email, i want to include the email in the mail part as single column table. how to do it? also can i use result of one recordset for other recordset?
View 1 Replies
View Related
May 23, 2006
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still shorttime but when you read the time within the code it comes out in AM/PM format. Also, when the defaul tiem should be #20:00:00# but this changes to #8:00:00 PM#
Weirdly though, when you just open the table, the times are in the correct shorttime format.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
This screen shot may help:
View 2 Replies
View Related
May 23, 2006
I have an application with a backend db on PC 1, and the same application on PC 2 linked to the backend db on PC 1 via the network.
The link works fine but the time formats are different.
On PC 1 the time format is shortime and displays as it should i.e. in 24 hour clock format
However on PC 2, opening the same database via the network, the time format is still short time but the format is in AM/PM or medium time.
Guessing, it must be a setting within the main core of Access 2003 that is different between the 2 instances?
Any ideas?
View 1 Replies
View Related
Aug 17, 2007
from a table with fields userID and Date (in which any userID has multiple records with different Date values, and other fields) i have built a select query based on Date = one specific value. can anyone give an explanation (for beginners), through a sample code, that expands the query recordset so that the new recordset includes all records per userID that qualified in the prior recordset? below is an example. thank you!
Table with records:
userID / Date
u1 / d1
u1 / d2
u2 / d1
u2 / d2
u3 / d2
...
1st Select Query (where Date = d1):
u1 / d1
u2 / d1
Desired 2nd Query based off 1st Query (where ?):
u1 / d1
u1 / d2
u2 / d1
u2 / d2
View 3 Replies
View Related
Jan 21, 2015
I have two tables that have the exact same fields. In table1 I have records that need to be UPDATED into table2. I tried an Update query and out of 600 records only half of those got updated. In my update query I joined tbl1 and tbl2 by Location (LOC) I checked for Nulls, Blanks, spaces, you name it. I can't figure why they all didn't get updated. I created a SQL query and go the exact same results. Ideally, I would like to create something in VBA to do this. I do not have any forms linked to these tables. In all I have about 600 records and 15 different fields that need to be updated. My example is only for one field.
sql example below. Which is the Access Sql in the Query
sql = "UPDATE tbl2 INNER JOIN tbl1 ON tbl2.LOC = tbl2.LOC" & _
"SET tbl2.Name = tbl1!Name" & _
"WHERE (((tbl2.Name) Is Null));"
Example.
Table1
Field1.Names = John
Table2
Field1.Names = "Need to UPDATE the name 'John' here"
I tried the Recordset .EDIT but I couldn't get it to work using two tables.
View 4 Replies
View Related
Nov 8, 2013
I have a database that stores information for lab testing. Each time a tech does a "step" in the test process he logs it in the table, using an input form. There are different categories, for example preparation, testing, analysis, etc, and each of those steps take time. I have the form autopopulate the date and time with NOW() evertime the form is updated. What i want to do is calculate the time it takes to do each in days. I can easily get how many days it was from now since they logged the test, =NOW()-TestDateTime. What I want to do is get the number of days it took to do each step, ie the number of days between each event. Is there a way to do this?
View 3 Replies
View Related
May 6, 2015
how to be able to enter time in access form the same way as if would be an access table (1p = 1:00 PM; 1.25 = 1:25 AM etc)
View 1 Replies
View Related
Apr 3, 2014
I have an attendance database and I connect the time attendance machine db to my access db, what i am trying to do is to generate a report that shows the time in and time out for specific date. the type of attendance db is date/time.
Please see the attached screenshot db from attendance machine.
an also some time there is duplicate entry, I need to get the first and the last entry only for specific date.
View 4 Replies
View Related
Dec 3, 2013
Formatting issue regarding elapsed time calculated using DateDiff().
I understand that you can specify the output value for DateDiff(). In my case I have chosen "n" for minutes. Each result in my query shows the correct calculation in terms of minutes.
[PunchIn] = 11/23/2013 8:11:28 AM
[PunchOut] = 11/23/2013 5:43:30 PM
[ShiftLength] =DateDiff("n", [PunchIn],[PunchOut]) = 572 minutes.
Now when I try to format the result in terms of H:MM (be it in a form or a report) I get varied results. I'll illustrate an example below:
=Format(([ShiftLength]/60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 10.32 Not correct
=Format(([ShiftLength]60),"0") & "." & Format(([ShiftLength] Mod 60),"00") Returns 9.32 This is correct but I need my result to be in the form of a decimal such as my next example
=([ShiftLength]/60) Returns 9.53333333. Getting there but how do I have this result only show two decimal points 9.53?
View 6 Replies
View Related
Aug 29, 2013
I have a date/time text field on a form with the General Date format and a combo box next to it that has sequencial times as the row source (IE. 12:45 AM, 1:00 AM, 1:15 AM, 1:30 AM, ETC.) When the user chooses a time in the combo box, I want the time portion of the text box to be updated with the chosen time in the combo. I have tried a few things but cant seem to get it right.
View 4 Replies
View Related
Apr 10, 2015
so i created a system to have events booked, and i am trying to check time availability of the event room available, but i dont know what wrong. it either my query or vba code. i have attached the attachment,
View 5 Replies
View Related
Dec 1, 2013
I have a working dB which can calculate a shift duration and sum total all shifts worked within a period for the purpose of producing a labor report for payroll. I have successfully used the DateDiff function and converted the minutes to HH:MM on my form and reports. Now I want to calculate elapsed time for a specific period within a shift, I'll call it OtherHours and I am aiming to calculate a portion of time that meet the following conditions below. I am using field names of [PunchIn] and [PunchOut] and both are of type General Date.
IF [PunchOut] ISNOT Saturday,Sunday
EXIT FUNCTION
ELSE
IF [PunchOut] ISNOT Between Midnight and 0559 hours
EXIT FUNCTION
ELSE
DATEDIFF ("n", <MIDNIGHT>, [PunchOut])
My thoughts are to solve the DateDiff portion and then figure out how to apply the conditions within the IF statements.
View 4 Replies
View Related
