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!
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]....
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
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?
How to determine if a field within a table is a primary key or part of a compound key using ADO?
View 1 Replies View RelatedI'm trying to get a total based a combination of criteria. Here's my issue:
If ([Vendor Billed Amount] > 650 $ and $ [Excess Fee Approved] = "Yes") then
[Payment to Vendor] = [Vendor Billed Amount]
else
If ([Vendor Billed Amount] < 650 $ and $ [Excess Fee Approved] <> "Yes") then
[Payment to Vendor] = [Vendor Billed Amount]
else
endif
endif
I think I'm missing an argument with the nested If statement.
I am trying to have the following variables input:
Starting balance
APR (compounded daily)
Amount of monthly withdrawel.
Term
I want to compute the ending balance.
Can anyone help please?
Thanks in advance.
Is there a way to calculate accrued interest or an amortization schedule in MS Access? I know it's easy in Excel but I have a need to do it in Access.
View 1 Replies View RelatedIs 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 RelatedI 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!
So I have a form that allows me to view the information associated with a specific record. Within this form is also a subform that shows additional information about the record from a junction table. I want to create a button that allows me to edit the information on both forms.
View 4 Replies View RelatedI have been putting together code from bits I have found online that will create an appointment in Outlook from a date field in my form.
My form has a number of dates and I need to create appointments for each one. Is there any way I can incorporate that into the existing code? Or will I need to add command buttons for each date?
The date fields are:
[Date Template Made]
[Date of Top Cut]
[Date of Bowl Cut]
Code:
Private Sub CreateAppt_Click()
If Me.Dirty Then
Me.Dirty = False
End If
If Me.chkAddedtoOutlook = True Then
MsgBox "This appointment has already been added to Microsoft Outlook", vbCritical
[Code] .....
I would like to make clickable labels on my form, so if the user has any questions about the field, clicking on the label would bring up a message box that would give them more information. I know how to write onclick events one by one for every label, but there are a lot of them, and I feel like there has to be a better way.
I put the label names, and the text I'd like for the message box in a table, and I'd like to have a module that will allow me to click on a label, and have the right text come up.
I've researched ways to do this and have come up with nothing. The farthest I've gotten is an array tied to the form open event that just displays all the message boxes from first to last, one after the other. I believe that's on the right track, but is not a workable solution as is. I need to be able to tie the message box to the actual label the user clicks.
I have a subform in datasheet view for tracking calls from clients. The Client Name is a combination of the first and last name. I want to create a text box that allows me to start typing in the client name and it will filter for only those Clients that match. Currently, I have the following code (below) for the textbox, however when I start typing in a client name all the clients disappear from the table. The client name is in a combo box so I don't know if that is making a difference.
Private Sub Text3_Change()
Dim strFilter As String
Me.Refresh
[Code].....
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 ).
Howdy all,
I'm attempting to set up a junction table.
The help for junctions says to make a compound key in the junction table, but I don't understand the intructions for this. Do they mean make all three of ID's the junction table PK's or is there a literal "Make a Compound Key"
button somewhere?
Currently, the table has an AutoIncrementing field as the PK and the two FK's copied in from the Primaries (converted from Auto to Number). As a result, I can only make one-to-one relationships to the junction table.
Please let me know what it is I am missing here.
Thanks
creating a VBA that will allow me to search for a record in Table A, and once the record is found, allow me the option to add that record to Table B. Is this possible to do if Table A and Table B are not formatted the same (i.e. one has more fields than the other)? In summary, creating a VBA that will allow me to search in one table and once the correct record is found, allow me the ability to add it to another table.
View 7 Replies View RelatedIn these three formulas for the Ceil tutorial found here:
[URL]
one of the formulas is:
=IIf(IsNull([RoomNumber]),"",DLookUp("RoomType","Rooms","RoomNumber= '" & [RoomNumber] & "'"))
If one were interested in BedType or Rate then simply substitute that term for RoomType.
Now I am somewhat familiar with how DLookUp works. In other words get RoomType from a table Rooms where there is a given RoomNumber.
Where I get confused is the last clause:
"RoomNumber = '" & [RoomNumber] & ""
I realize it is a compound statement hence the &, but it uses two &s and more.
I am trying to enforce referential integrity on tables where a compound key is implemented.
I want to enforce integrity on the DETAIL table so that it can only use an SOR_ID from the SOR table that has a corresponding PhaseID in the HEADER table. Here is my current diagram:
http://i234.photobucket.com/albums/ee261/winshent/IT/Relationships2.jpg
The only solution i can think of at the moment is to build two queries. One which concatenates SOR.PhaseID & SOR.SOR_ID, and another which concatenates HEADER.PhaseID & DETAIL.SOR_ID... and then create a relationship between the two queries.
There must be a much nicer way of doing this though?
In a form named PRODUCTION there are 3 text box for invoice data: xtype, xserie, xnumber
I need valid this data from the INVOICE table, where its key is a compound key (TPINV+SERINV+NUMINV)
I tried with:
Private Sub xnumber_AfterUpdate()
Dim searchinv as string
searchinv=Dlookup("*","[Invoice]", [tpinv]="& Forms![production].[xtype] and [xserie]= &Forms![production].[xnumber] and "&Forms![xnumber]
If searchinv ="" then
MsgBox ("Invoice doesn't exist'")
Cancel = True
End If
End Sub
... but it doesnt work.
When you create a column chart and then change one of the series to a line, how can you then apply formatting to that line? I've attached a stripped down database showing what I'm trying to do. Series 1 shows individual monthly values (percentages) and series 2 shows the target which is 80% for each month.
The problem is that although I can apply formatting to the column series, I can't see any way of selecting the line series so I can set the formatting I want. I'm using Access 2003, on Win XP Professional, in case that's important.
I'm getting back into Access after retiring and about 10 years of isolation (from Access). I have 2010 version and know that one should avoid duplicate entries and a way to do this is make those fields unique key fields. I have a Customers table and have bounced back between CusID (AutoNumber) and Compound Keys (CusFName and CusLName) as the key fields. The compound keys prevent duplicates but become very hard to work with later in code and expressions. The CusID is preferable from that standpoint, but can't prevent inadvertent entry of duplicate names.
View 2 Replies View RelatedI have to create codes with a certain algorithm.
It consists of 6 digits (positions) and looks for example like this: 6D45F3
On each position the sequence is "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F", so it has 16 possibilities.
Beginning from the last position going up to the first position the positions change according to the above sequence and starts from "0" when it has reached "F".
In the example above the next codes would be
6D45F4
6D45F5
...
6D45FF
6D4500
6D4501
...
6D450F
6D4510
...
So after each 15th loop the next positions changes and the previosu set back to "0". I thought that this should be easy but I failed with this code.
var16Stelle = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", "D", "E", "F") '16 Elemente
strErsterBC = UCase(InputBox("Enter the first code.", "Barcode-Generator")) 'for example 6D45F3
intRow = InputBox("Entere the number of codes to create.", "Barcode-Generator")
intStart6 = ListIndex(Mid(strErsterBC, 6, 1), var16Stelle)
intStart5 = ListIndex(Mid(strErsterBC, 5, 1), var16Stelle)
[Code] .....
I am creating a combo box with a button. In the below vba code, I am getting the string from the combo box. Then, I am trying to use SQL to the corresponding distro lists in the table (same row). This VBA code below is getting a Run-time error '438' Object doesn't support this property or method and it points to the SQL statment in the debugging screen.
Code:
Private Sub Command11_Click()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SQL As String
Dim vFld As Variant
[code]...
I know it's a long shot, but, in A2010 I'm trying to convert a series of Word doc files into pdf, BUT then assemble them into one large PDF. It's for a sort of archiving system.
way I can do this, preferably just using the PDF capabilities within Access and without purchasing any Adobe Pro software.
I've been playing around with trying to make a scrolling marquee on an access form and it works. How I could get it to change. Here's what I've got....
Private Sub Form_Open(Cancel As Integer)
message = "my message"
End Sub
Private Sub Form_Timer()
Text0 = message
'Get first character
Dim FChar As String
FChar = Left(message, 1)
'Remove first character
message = Mid$(message, 2, Len(message) - 1)
'Put 1st character at the end of the message.
message = message + FChar
End Sub
and that works great. Now I'm trying to make it get the message value from a table so I change to this...
Private Sub Form_Open(Cancel As Integer)
message = Text0.Value
End Sub
Private Sub Form_Timer()
Text0 = message
[code]....
and set the control source for text0 to a field in a table. the problem is when I change the message in the table or add another record the marquee doesn't change unless I completely close and re-open the form. Is there a way to make it update without closing and restarting?