I am runnig a query via VBA where I have a variable defined for the criteria value.
However I do want to run this query several times for several values for the the variable.
These values are stored in a table.
Is it now possible to make sort of loop function in VBA that picks the first value in the table as criteria value, runs the query and then picks the second value in the table as criteria value, runs the query, etc, until all values have been picked?
I hopeing that someone could help me with a Loop problem. I am trying to create a directory on each name change. My code works for the first name, however, when I am trying to Loop through the table, it isn't working.
Here is my code
'MkDir "H:NewPrivateFolder"
On Error GoTo Err_cmbMkDir_Click
Dim DirName As String Dim response As String Dim folder As String
If MsgBox("OK to create folder!", vbOKCancel) = vbOK Then
MkDir DirName
Else MsgBox "Create folder cancelled. Folder not created." Exit Sub End If Else MsgBox "The folder already exists..." & Chr(10) & "Please check the directories using Windows Explorer.", vbOKOnly Exit Sub End If 'response = MsgBox(DirName, vbOKOnly)
Let's say I have 10 variables (ID1, ID2, .........,ID10) In java, I can use a while loop as follows to go through each variable
For example: i=1 j=1 while (j<=10) { ID+i = 0 //this is some type of initialization of each var to 0 i = i + 1 j = J + 1 } What would be the equivalent code in VBA, especially for "ID+i" ?? I use the '&' symbol but it concatenates everything meaning in the first loop goes to ID1 (which is correct), the second loop goes to ID12 (which is NOT correct), the third loop goes to ID13 (which is NOT correct either), and so on.
I have a form that has a command button that requeries to the next days events. I want to skip days where there are no events. I created the Do Loop below but need it to stop after 10 days and return a MsgBox that says there are no further events.
I tried Do Until EOF but since the query is empty is fails. I have searched and read some other sources and did not find anything on limiting a loop the way that I need it. Or at least the way I understand it.
Any Ideas?
Forms!frmmain!txtSortdate = Forms!frmmain!txtSortdate + 1 If DCount("*", "qryflypages_old") <= 0 Then Do Forms!frmmain!txtSortdate = Forms!frmmain!txtSortdate + 1 Requery Repaint Loop Else Requery Repaint End If
I'm having a problem with my form where everytime I try importing a file into my database, I received an "overflow" message. It appears the problem is somewhere in my Do loop. The following shows what my code looks like right now:
Open txtCSVFileToUse For Input As #1 lvRandNum = Int((1000000 * Rnd) + 1) lvINTERNAL_ID = lvRandNum lpCntr = 1 Do While Not (EOF(1))
I have created a recordset and what i want to do is check the field value "SiteRAG" to see if it matches some criteria and if so do some action where the field "SiteID" = the same as a label on a form.
Please see CAPS in middle of code:
Dim db As Object Dim rs As Object Dim intCount As Integer, intRecordCount As Integer, intID As Integer Dim strSQL As String Dim strRAG As Long Dim fldItem As Field
strSQL = "SELECT tblSite.SiteID, tblSite.SiteRAG, tblSite.Active " _ & " FROM tblSite " _ & " WHERE (((tblSite.Active)=Yes));"
Set db = CurrentDb Set rs = db.OpenRecordset(strSQL)
intRecordCount = 0 rs.MoveFirst
CHECK THE FIRST ROW FIELD SiteRAG & IF = TO CRITERIA THEN DO SOMETHING BASED ON A LABEL MATCHING THE SiteID ELSE MOVE TO NEXT ROW
I am very inexperienced in databases and I am currently attempting to design a database and there is one part of the schema where I am unsure how to proceed.
I need to model schools, pupils and teams. Schools have a number of pupils as do teams.
I rushed in with the following design but it just seems wrong to me. I don't know much about database design but I don't like the idea of having a kind of loop/triangle in the schema like this.
I have another problem. I am waiting for a barcode scanner to give me info about a projectnummer. I do this with a do loop untill. When this loop is open and I close the form I get an error. I have to close the loop. Any one have a sugestion.
Thanks
Snakebite
Here is the code
Do DoEvents Buffer = Buffer & Me.MSComm1.Input Loop Until InStr(Buffer, Chr$(10))
I want to have a label "Flash" when a command button is clicked, but I'm confused about the loop statement that is required in the code. Here's what I have so far:
Code: Private Sub Command11_Click()Do[Label13].Visible = TrueTimerInterval = 500[Label13].Visible = FalseLoopEnd Sub
Could someone please tell me what's missing. Thanks.
In this I have come up with a loop function that should loop until there is a blank record, but it dosnt The problem im having is that the code goes in an unterminating loop on the 1st record and dosnt move on to the next one.
the fault lies in the line, it keep's saying "the field 'item stock quantity can not contain a null value as the requiered property is set to true'"
the thing is the form goes blank when the code is ran, but returns when the error is terminated
DoCmd.GoToRecord acDataForm, "purchase", acNext
here is all of the code, command 10 is a button on the main form
Private Sub Command10_Click()
Dim Counter As Integer Counter = DCount("*", "Qry check amo")
If Counter <> 0 Then If [Forms]![Purchase].[Form]![item purchase subform]![Purchase Quantity] > [Forms]![Purchase].[Form]![item purchase subform]![Stock Quantity] Or [Forms]![Purchase].[Form]![ammunition purchase subform]![Purchase Quantity] > [Forms]![Purchase].[Form]![ammunition purchase subform]![Stock Quantity] Then MsgBox "Not enough stock" Else
Below is all the code I have entered so far and for some reason the sub routine TripGT_Exit is staying in a loop until another field is selected. The problem is this is the last field in the record before a new one. Tab control involves only 3 fields: cmbICAO, TripETE, TripGT.
I comment out both fields populating TripDepartTZ and TripDepartDZ and it works fine but of course the fields aren't populated.
Code:Option Compare Database Public datArr As Date Public datDepart As Date Option ExplicitPrivate Sub cmbICAO_Click() Dim datDepartHomeTime As Date 'Variable from MSN Plan input form Dim datDepartHomeDate As Date 'Variable from MSN Plan input form Dim datFullDateTime As Date 'Sets initial date and times of the trip, used for testing only datDepartHomeTime = #10:00:00 PM# datDepartHomeDate = #1/2/2008# 'Takes the time and date and puts them together datFullDateTime = datDepartHomeTime & datDepartHomeDate datDepart = datFullDateTime If Me.cmbICAO = "ICAO" Or Me.LegNo <> 0 Then 'No new leg number needed if a leg is inserted into 'an exisiting trip Else Me.LegNo = NewControlID() 'Gets a new leg number for a new record of the trip End If If Me.LegNo = 0 Then 'For first leg of trip and assigns departure values Me.TripDepartTZ = datDepart Me.TripDepartDZ = (DatePart("d", datDepart)) End IfEnd SubPrivate Sub TripETE_Exit(Cancel As Integer)'Used for calculating arrival times Dim sinETE As Single Dim intAir As Integer If Me.LegNo <> 0 Then 'Calculates the air time after the initial leg (0) and updates "datArr" which is then 'used in calculating the ground departure time of the next leg sinETE = Me.TripETE intAir = sinETE * 60 datArr = DateAdd("n", intAir, datDepart) Me.TripArrvTZ = datArr Me.TripArrvDZ = (DatePart("d", datArr)) End IfEnd SubPrivate Sub TripGT_Exit(Cancel As Integer) 'Used for calculating departure times Dim sinGT As Single Dim intGround As Integer If Me.LegNo <> 0 Then 'Calculates the ground time after the initial leg (0) and updates "datDepart" whic is then 'used in calculting the air time of the next leg sinGT = Me.TripGT intGround = sinGT * 60 datDepart = DateAdd("n", intGround, datArr) Me.TripDepartTZ = datDepart Me.TripDepartDZ = (DatePart("d", datDepart)) End IfEnd Sub
I understand for normalization I should not be putting calculated fields into a table but use a quey instead; however, I'm not sure how to make this into a query whose calculations continue throughout as new legs added.
I am using MS Access 2010. I have a query that will return 3 values (ZZZ070113,ZZZ070213,ZZZ090713) here is the query (SELECT DISTINCT RUN_ID FROM CUSTOMER where customerID = 1567). These variables will change from time to time. Some times there will be 2 or 4 ID's returned...I want to take the results and loop through a series of commands to extract records from another table and write the results to a new table.So based on some research I have done I figure I need to use a For Each loop statement
First is how do I assign the values from the query to a variable?Then how do I pass these results into a loop so I can see the results?The code would take ZZZ070113 and query table Customer_Details to retrieve ProcessDate and ProcessCount. These value would be written to a new table called Customer Details...Then the code would take ZZZ070213 and do the same etc.
Need to loop .wav file three times. (See "Sound") (Not tagged in origional code.)
Private Sub Address_Exit(Cancel As Integer) Address.BackColor = 16777215 On Error Resume Next
'If there is no matching record in communications do nothing. If IsNull(DLookup("[address]", "communications", "[address]='" & replacequote(Me![Address]) & "'")) Then Exit Sub End If
'Make button visable and activate alert beep. Command1796.Visible = True
PlaySound "H:GeneraldmediaBEEP_FM.wav", ByVal 0&, SND_FILENAME Or SND_ASYNC
Is it possible to loop through all forms within a project and look at its controls? I was requested to change some object names to a more generic name, but I would like to find all references of the form within the Access application. Is there an easy way to do this?
I know that the MSysObjects contains the listing of all forms, but I don't know how to generically type cast a local variable to the form name listed in the table.
Does this make sense? There has to be a way to do this, but I haven't found anything in searching this forum.
Hi I am creating a stock database and I have come across this problem:
I have a stock table, and I have successfully creted a query to insert a record with values selected in a form:
EG. In the form you can input a part no, comments and the quantity you want to add. I have created the stock system so that each individual item of stock is one record in the stock table.
What I can't seem to do is use the quantity field in the form to dictate how many times the form details is entered into the stock table.??
I've got 20 checkboxes which i need to do the same thing but individually.
If IsNull(DLookup("[question 1]", "qryQuestions")) Then chkQuestion1.Visible = False Else chkQuestion1.Visible = True End If
I tried this:
Dim loopy As Integer Dim tmpquest As String
For loopy = 1 To 20 tmpquest = "question " & loopy tmpCheck = "chkQuestion" & loopy If IsNull(DLookup(tmpquest, "qryQuestions")) Then tmpCheck.Visible = False Else tmpCheck.Visible = True End If Next loopy
tmpquest works. tmpcheck doesn't.
I tried dimming tmpcheck as lots of things but no luck.
hi guys i was wondering if you can help me please, in my main form i have a text box which the user enter an item number , and a combo box with the main contracts., then a continuous subform which has a combo box with the subcontracts that belong to that main contracts, in the subform i also have a txtPriceA, and txtPriceB. my problem is that with the code i have the priceA and priceB get added in different rows and all the other fields get added correctly but with the columns priceA and priceB empty.
this is what i tried
Private Sub Command94_Click()
Dim db As DAO.Database Dim rst As DAO.Recordset Dim varItem As Variant
Dim strSQL As String
Set rst = CurrentDb.OpenRecordset("tblMain")
For varItem = 0 To Me!frmAddSubform.Form!cmbSubContracts.ListCount - 1 'combo box from subform rst.AddNew 'add new row for each subContract in combo box
rst!PriceA = Me!frmAddSubform.Form!txtPriceA.Value (this is supposed to be added in the columm PriceA in the same row as the 1st row added from rst!subContract_ID. )
rst!Priceb = Me!frmAddSubform.Form!txtPriceB.Value (this is supposed to be added in the columm PriceB in the same row as the 2nd row added from rst!subContract_ID. )
rst.Update Next varItem
rst.Close
But I get this:
ID (autonumber) Item# Main Contract Subcontract PriceA PriceB 1 123 12345 12345-1 2 123 12345 12345-2
3 $15.00 $ 15.01 4 $15.50 $ 16.01
I should only have two rows added instead of four....like this
I want some VBA code that can loop through the table above one by one returning the value. The table data will change so i would like the code to handle change as well.
I want the returning value to be returned has a string i.e. TblValue = <table data value>
Because then i want to use the value to be put in this sql query- DoCmd.RunSQL "INSERT INTO TEST_DOC SELECT * FROM " & [color:red] TblValue [/color]
Ok, say I have a table with the following fields, custid, mainnum, subnum whereas the data would look like this: Code:Cust Main SubA101 1 2A101 2 1A101 3 4A101 4 2A101 5 3B202 1 2B202 2 4B202 3 1C303 1 2C303 2 1
The main number always counts up from 1 on each new customer, I already have code to do this. I am running a for next loop based on the highest main number (I already have this part), for instance, for Cust A101, the for next loop would essentially be: for x = 1 to 5.... like I said, I have this part.
What I want to do when x is counting, to pull up the mainnum value that equals the value in the for/next (in other words equals X), and run a for next loop based on the sub number based for that record. For example using C303's data: Code:For X = 1 to MaxOfMainNum '<---I already have this 'code to pull up subnum where mainnum=X For Y=1 to SubNum_For_Selected_Mainnum 'I will be doing an insert to table here custid,x,y Next YNext X The parts in Bold in the above code is what I'm needing an answer to.
I tried to be as concise as possible, but I know it might be confusing.
I have created this simple SQL/Query that only show a table of students who passed the exams, but I have to do it manully, like I have to change the range or coordination for every class I have, I want the Query/SQL to loop by itself and goes to the next class/school and gives me the number of all the students who entered the exams and the one's who passed only sorted by school name and class, no more info is required like Student Name or something.this is a very very basic and simple code:
Quote:
SELECT studt.schoolnm, studt.class, studt.fdor, studt.freslt FROM studt WHERE (((studt.schoolnm) Like "8") AND ((studt.class) Like "005") AND ((studt.fdor) Like "02") AND ((studt.freslt) Like "0100"));
in other words, I want the SQL/Query to loop by school ID (numbered from 1 to 198 schools) and classes (from 4th to 12th grade entered as 004/005/006/010/012 and so on), the "fdor/freslt" are standers so they must not be changed or looped (if this even possible). and I want it to be printed like:
School Name | Class | Total Number of Students | Number of who Passed
I have a query that includes fields from T_employee and fields from T_courses, which is courses taught by each employee per semester and T_ Additional, which is additional hours added to employees for each semester. The query groups by employee and has a calculation of the teachers total hours per course based on teaching hours and factors for marking and preparation and adds the additional hours from T_additional.
Each teacher cannot have more than 44 hours, so there are additional calculations that calculate the consequence of subtracting one course from the total hours. The record that is closest to 44 without going over is optimal. In some cases, subtracting one course is not enough to get the hours below 44 and an additional course will need to be subtracted and perhaps a 3rd an so on.
I would like to evaluate which course is the most optimal to subtract and if it is not enough, then to loop through and subtract the next course until the hours are below 44, but as close to 44 as possible.Would a Do loop be what I need? I need to output the total number of hours that were subtracted and the number of courses.
I tried creating a query based on this one, grouping by employee and under the NetHours in the totals row, the max nethours <44. This works but there are still a few records where the nethours is >44. I would like to use a Where in the Totals row and in the Criteria somehow indicate that I want the largest number that is less than 44.
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
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.
