Private Sub Form_Load()
Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("select * from tblpatient, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do While Not rs.EOF
' do stuff
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End If
End Sub
I have watched it step through on debug and it does everything right for the first record, but it seems to come back to the same record.
I have to loop thru 2 recordsets.I need to first determine the quarter , then retrieve a value from each of the 6 fields for that quarter and compare that value against a previous years value that is also stored in the table for each quarter.the following is the field names not the field value.
So I need to be able to set the value of PrYR = .385 and compare against the value of the 1st qtr for each tier for Qtr1.Then go to Qtr2 and repeat the process but grap the Q2 PRYR value = 1.25 and compare against all Tiers for Qtr2.As so forth for each quarter.then I need to compare the value of the PrYr and if it is the following then
If PctYrlyIncrease< Tier1 Pct (Q1T1) Payout = 0 elseIf PctYrlyIncrease> Tier1 Pct (Q1T1) and < Q1T2 then Sum(TotalNetUSExp * T1E) ElseIf PctYrlyIncrease> Tier2 Pct (Q1T2) and < Q1T3 then Sum( TotalNetUSExp * T2E)
and repeat for each Tier per Each Qtr.Here is my code so far:
Code: Public Function BkOvrCalc(ByVal gContractID As String) As Long Dim curDB As DAO.Database Dim strSQL As String, strSQL1 As String
I have a form where a textbox has shows the NameNo from the NameNo column in a db. At the moment I am using a dlookup to set the textbox = to the NameNo, but obviously this is only returning the 1st value in the column. How to get this form to automatically display each NameNo, creating a pdf of the form and then moving on tto the next nameno. I have the code to make the pdf working so far, all I need now is to figure out how to use the textbox to loop through all the NameNo's in the table.
I have a very generic form I use to track all my To Do things on a daily basis. The form has the following fields: fldComplete, fldDueDate, and fldSubject.
The fldComplete is a check box and the fldDueDate is of course a date data type.
What I would like to create is a query that once you click the fldComplete signifying that the task is complete:
1. The record is removed from the form.
2. The fldDueDate advances either annually, monthly, quarterly etc. which automatically goes to the end of the form in sequential order.
I have been struggling with this one a while now and need to throw this open as im Stuck!
i have a database of domain names that I currently own however i need to be able to report on the TLD (eg .co.uk .com. me.uk) part of the domain. in the database the field domain is held in the format "imstuck.co.uk" i need a query that will strip the domain part and just return the TLD in a seprate field.
I have used numvers of functions but cant seem to get the right format for it to work
i have the following code to get file list from specified directory:
Code: Dim db As DAO.Database Dim TB1 As DAO.Recordset Dim p As String, x As Variant
[Code].....
the field "File_Name " is not set to accept duplicate the above code work fine , but when it find the file name exist on the TB1 it return an error i need the code if find any error continue to retrive other files on the folder not exit loop and also i don't want to make check if file exist it will lower the speed of code
Basically what I am trying to accomplish is uploading multiples file automatically into server. The code I am trying to use works great if the user wants to upload a single file manually because the code prompts you to choose the file and I am wondering if there is a way to tweak the code. here is the code I am using
Code: Private Sub Form_Load() Dim objFTP As FTP Dim strfile As String
I work for an insurance company where various (and multiple) discounts or loads can be applied to a quoted premium for one reason or another.
These discounts/loads are stored in a table with a corresponding customer ID, where each row represents an individual discount/load (labelled and ordered-by a 'Step').
Unfortunately, the resulting premium from these discounts/loads is not stored in the database and are calculated on the fly by the front-end.
I have a requirement however, to store the 'new' premiums based on the stored discounts/loads for a report.
My problem is that the calculations must occur incrementally one after another, where the discount/load at each 'Step' applies to the resulting premium from the previous calculation.
The attached spreadsheet is demonstrative of the existing table where two additional columns have been added to show you what I need to calculate. 'Price_Amount' represents the discount/load in monetry terms relative to the calculation and 'New_Prem' is the premium resulting from the calculation step.
Notice that each discount/load applies to the previously calculated premium rather than the original one (Original_Prem = the starting point from which all further calculations should apply).
The full table would include many more rows for different customer ID's, where the number of 'steps' could be as few as 1 or as many as 7 per customer ID (ordered by 'Step').
Having toiled for many hours in Access to achieve the above, I am now resigned to the fact that only some kind of VBA function will achieve my requirements.
This function should take the Original Premium for each CustomerID and loop through each 'Step' applying the relative discount/load based on the value in 'Pricing' and the number type in 'Price_Type'. Once the function has calculated each step per CustomerID and has reached the maximum 'Step', it should move on to the next CustomerID.
such can only articulate the requirements without being able to convert this into actual coded logic. Nonetheless, I am in a bit of a pickle with this one and am under some pressure to create the report.
I am using to select and append data to an output table. Everything is working fine until the last step. The last step appends all the data to an output table from the initial table. The intermediate steps select the data to output.
"AddExpenseToOutput()" is stopping about halfway through the list of criteria. Initially I thought there might be a null in the list, but it doesn't look like there is after dropping everything into Excel to manipulate.
Code: Option Explicit Dim rstAccount As DAO.Recordset Dim rstAccounts As DAO.Recordset Dim CAccounts As String Dim Item As String Dim AccountKey As String
I have a sample database (attached) where I have a form called frmVoteHeadsOne. The voteheads that I have are swimming (Swim) and transport (Trans), which both appear for data in a table. On the form, from the combo list, I would wish that if I select swimming, for example, only Mark Tyler's name is returned since he has paid 2000; if I select Transport from the same list, only John clement's name is returned.
My purpose for doing this is because in the actual database, I have 21 different voteheads which I would like to get different results from based on what the user selects from the combo list.
I've got a problem with a loop for producing worksheets (reports). When everything works there's no problem but when one of the reports namely rptChemoOralWorksheet doesn't have data available to produce the report the loop finishes and then all remaining worksheets don't print.I've put in a standard error handler with MsgBox, but it's never triggered. I've also tried
Code: On Error Resume Next
but again rather than skip the DoCmd.OpenReport "rptChemoOralWorksheet" (when the error is present) and move to the next one to continue printing through the list the loop stops and the subsequent code starts to run.
Code: Dim mydatabase As DAO.Database Dim rst As DAO.Recordset Set mydatabase = CurrentDb Set rst = mydatabase.OpenRecordset("atblChemoWSLoop", dbOpenDynaset) Dim rsBN As String Dim rsDose As Double
Im trying to create a record set that compares a quantity value in the recordset to a Value in a temporary table that holds the most recent remaining quantity. I have the following code in the after update of a text box, but it does not trigger. I want it to run after a value is entered into the text box. Is the code wrong or the location im putting it incorrect?
Code: Private Sub txtQty_AfterUpdate() Dim rs As DAO.Recordset DoCmd.OpenQuery "qryQuantitySoFar" Set rs = Forms!frmReceive!sfrmReceiveDetailEntry.Form.RecordsetClone
I have succeeded with retrieving subTable data from a One to many relationship, however, I'm stuck in an infinite loop processing the first record of the subtable and not moving to the next one.
Dim db As DAO.Database Dim rstLandData As DAO.Recordset Set db = CurrentDb Set rstLandData = db.OpenRecordset("qryLandData", dbOpenDynaset) Dim strLandType As String
I am relatively new to VBA and have this loop (see below) was wondering if it was possible to build it into a variable, so I could call upon it within an IF statement.
Set ctl = Me.listSeeAllAssets For Each varItem In ctl.ItemsSelected rs1.AddNew rs1!AssetNo = ctl.ItemData(varItem) rs1!DateOfService = Me.DateOfService
Hello everyone, if anybody could help me I'd deeply appreciate it.
Booking ID = the time when it is booked over the phone Hiring ID = the time when the event is to be held
Basically I have to make some booking dates, and use January and February 2006 as test data. My only dilemma is it says that the booking dates must not be booked more than 8 weeks before the hiring, and at least 1 week before it. So if someone wanted to the hiring for 01/01/06, then they wouldn't be able to book before 01/11/05, but before 25/12/05. Can anybody help me? Thanks. I need it so that whatever the hiring date is, the booking date has to be no more than 8 weeks in advance, but 1 week before the hiring.
I have a problem with figuring out how to implement what I call “Paid In Advance”. If a customer pays 4, 5, 6, or 12 months in advance, I for some reason can’t think of how to set this up with tables, queries, forms, and reports. The example that closely matches my db is “Order Entry” from access sample db’s.
Tables are as follows: tblCustomerDetail tblCustomerHistory tblPayments
For every customer (tblCustomerDetail) there are multiple service records (tblCustomerHistory) and for every service record there are or can be multiple payments (tblPayments) for that one service record.
Just like in “Order Entry” where Customers have multiple Orders and then one or many Payments for that one Order. (Pretend that Order Details is in the Orders table.)
The problem that I’m having is trying to come up with a way to assign the “Paid In Advance” money to the customers account without assigning it to a service record or payment. I can’t assign it to a service record because it hasn’t been serviced yet. And I can’t assign it to a payment because the service record generates a new record in the tblPayments when a new service record is entered, and besides that wouldn’t work because the “Paid In Advance” money would only be applied to that one service record.
I hope everyone is following what I’m saying. If anyone has any clue as to what I am trying to do, please feel free to help me. As always any help would be greatly appreciated.
Thanks
P.S. If anyone knows where there is a sample db of what I am trying to do, I would be grateful if you could post the link in your reply.
creating a small loop to run code.For each record from AREA with PLANTPROGRAMID = VARIABLE Then...In this example, my table is called Area, my field is called plantprogramid, and my variable is defined earlier in code.
For Each Shape In pptslide.Shapes If Shape.Top > pptshape.Top Then 'MsgBox Shape.Name Shape.Cut newslide.Shapes.Paste End If Next Shape
If I keep only the message Box inside the loop, it iterates all the shapes in the slide. But when I cut and paste the shape, it comes out of the loop after cutting and pasting.
I have this code (below) that loops through a recordset and sends appointments. It executes the queries correctly and sends all appointments in the table, but sends them only to the contacts listed in the first record of the query. How do I get it to loop the contact details?
Code: Private Sub SchedFollowUp() Dim rsFollow As DAO.Recordset Set rsFollow = CurrentDb.OpenRecordset("SELECT * FROM Follow_Up WHERE HR_Approved = True AND Added_to_Outlook = False AND Cancelled = False;", dbOpenDynaset) Dim rsEmployee As DAO.Recordset
VBA creating and have spent about 4 hours trying to figure out the error with my simple loop to update a few fields. As you can see from the attached picture, the "Recalc" sub simply stops updating partway through the loop.
I would like Access to open an Excel file count the worksheets and then give each tab the name of the column heading in cell A1 of each worksheet. I had no problem with the code in Excel but when I tried to add it to Access I am getting an error on the WS_Count = obj.ActiveWorkbook.Worksheets.Count line. The error message is "Object variable or With block variable not set".
Here is the code:
Code: 'This block of code will cycle through each tab and name them Dim WS_Count As Integer Dim I As Integer
