Hi, not sure looping is the correct word so i will try to explain what i am trying to do.
i have a tbl and need to mark the records in groups of 12. the first group would be in group 1. The next group of 12 would be 2. and so on. the highest number is unknown as the data will grow.
is this posible with a qry? or do i need a for next loop and run a qry each time? if i do that how do I incriment the to the next level.
Thanks.
I am trying to add another record to a table. Pid and nsn are the keys. the table has 70 unique pid's. I need to add another nsn to each of the pid's. I tried the following code but no go:
Set db = CurrentDb
Set rst1 = db.OpenRecordset("tblHandReceipt")
Set rst2 = db.OpenRecordset("tblHandReceipt")
rst1.MoveFirst
Do Until rst1.EOF
rst2.AddNew
rst2!PID = rst1!PID
rst2!NSN = Forms!frmparts!NSN
rst2!ISSUEDSOURCE = Forms!frmparts!subfrmEndItemParts.Form!cbxENDITEM
'Add like above line for each field
rst2.Update
rst2.MoveNext
rst1.MoveNext
Loop
rst1.Close
rst2.Close
Thanks for any help.
I have a pretty complex database that uses several queries that all lead to a final query with a parameter (order number). I have a report linked to that query so every time I open the report it asks me for an order number. Once I enter the order number the query is run and I print the report. I am curious if I can create a query that pulls out order numbers (possibly for a date range) and then have a macro or something loop through that query and print a report for each order number. So if the query returned 50 order numbers then the report with the parameter (called Final results) would be printed 50 times.
View 1 Replies View RelatedI have a database where part of it consists of an Item table and a Formula table. There is a many-to-many relationship between them so I created a junction table for this purpose.
Tables
tblItem
tblFormula
tblFormula_Item_JNT
There is a column in tblFormula called [Priority] that is a lookup field. It has the values of "High", "Medium", "Low".
So here's what I'm trying to do: I have a query that searches against tblItem for all Items that have a value of No/False for a field called Item_Status. That's simple enough. But I also want to show the Priority of the Formula that the Item belongs to. When I add that to my initial query I get multiple results of the same Item if it belongs to more than one Formula, which I understand. But I really want an Item to show up only once.
I want the query to go through each Item (based on its Item_ID), look through all the Formulas it's in, and if at least one of those Formulas has a Priority of "High" then it will display "High" in a field (could be a calculated field) in the query results. If there are no "High" Priority Formulas, then look for if there are any "Medium" Priority Formulas, etc.
need help with looping to get a total for a specified time block. It needs to look at a time block and count the number of entries for a hour before to an hour after 7 am, 11am and 3pm on given 12 shifts. The list of shifts come from another sheet with the date and the time of the end of the shift.
I have the following which gives me a total for the 12 shift but now need to loop for the times given:
For rowz = 13 To 20
endtime = Sheets(sheetname).Cells(rowz, 1).Value
begintime = endtime - 12 / 24
If IsDate(endtime) = False Or endtime > Now() Or endtime = Empty Then
GoTo Exit
End If
SQLstatement = "SELECT count(Field1), count(Field2),count(Field3), count(Field4), count(Field5), count(Field6)" & _
" FROM dbo.table WHERE [Date] Between '" & begintime & "' And '" & endtime & "' "
rs.Open SQLstatement, , adOpenDynamic
If rs.EOF = False And IsNull(rs(0)) = False Then
fieldcount= rs(0) + rs(1) + rs(2) + rs(3) + rs(4) + rs(5)
Else
fieldcount = "N/A"
GoTo Exit
End If
Thanks for all the help,
SLH
Hello all, thanks to Dennisk and Paul, I am able to move through my recordset and create a directory with each individual name. Now, I am attempting to "FileCopy" an excel template into each directory with the directories name.xls------got this fine.
Now I am attempting to copy and paste each individual's records by looping through the recordset RST1 and place that information into the appropriate directory/directory.xls file; close; and then save all workbooks.
Could someone point me in the right direction? Currenlty, the code is dumping all of the information into my first directory, and doing nothing for the rest of my recordsets.
Do I need another loop here? Example Do Until EOF RST1?
Here is the code:
Dim rst As DAO.RecordSet
Dim DIRName As String
Dim folder As String
Dim Dir As String
Dim strnewname As String
Dim stroldname As String
Dim Pause As Boolean
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim blnExcelOpen As Boolean
Dim strMacro As String
Dim mysheetpath As String
Dim rst1 As DAO.RecordSet
Dim objdb As Database
Dir = Me.Combo19
folder = "H:" & Dir & ""
'DoCmd.OpenForm "frmWait"
'DoCmd.RepaintObject acForm, "frmWait"
Set rst = CurrentDb.OpenRecordset("tblofficers")
Set rst1 = CurrentDb.OpenRecordset("tbl_Q1")
Do Until rst.EOF
DIRName = folder & rst!OFFICERS_NAME
stroldname = "H:Asset Verification Template.xls"
strnewname = folder & rst!OFFICERS_NAME & "" & rst!OFFICERS_NAME & ".xls"
' Processing each officer and creating a directory
MkDir DIRName
Sleep 10
FileCopy stroldname, strnewname
'Name stroldname As strnewname
Sleep 10
mysheetpath = strnewname
blnExcelOpen = IsExcelRunning()
If (blnExcelOpen) Then
Set xlApp = GetObject(, "Excel.Application")
Else
Set xlApp = CreateObject("Excel.Application")
End If
Set xlBook = xlApp.Workbooks.Open(mysheetpath, False, False)
Set xlSheet = xlBook.Worksheets("Sheet1")
'Transfer the data to Excel
xlSheet.Range("A2:r10000").ClearContents
xlSheet.Range("A2:r10000").CopyFromRecordset rst1
'rst1.Close
xlBook.Save
' back to the top...
xlApp.CutCopyMode = False
rst.MoveNext
Loop
' if we started Excel, then close it now…
If (Not blnExcelOpen) Then
xlApp.Quit
End If
' clean up...
Set rst1 = Nothing
Set objdb = Nothing
Set xlSheet = Nothing
Set xlBook = Nothing
Set xlApp = Nothing
Hello all, i am new so please bear with me. My question is: I have this access 97 database that opens with office 07 and I am TRYING to open the database and loop through it pulling data from columns in all tables that end with the word "Data". I am lost so your help is greatly appreciated!!
View 7 Replies View RelatedI have a series of fields on a form. Each field name is a number 1 thru 32. I use a loop to set field properties
Param = 1
For z = 1 To 32
If (Me(Me.ParamB & Me.Param).Visible = True) Then
Me(Me![ParamB] & Me!Param).Visible = False
Me(Me!ParamL & Me!Param).Visible = False
Me(Me!ParamE & Me!Param).Visible = False
Me(Me!ParamT & Me!Param) = Null
End If
Param = Param + 1
Next z
I also have on the same form a series of field whose names are alphabets "a" thur "t"
I would also like to loop thru these fields to do the same thing. Is there any way to do this? I have tried the following code but it doesn't work. I'm thinking I need to set up temp fields to reassign each of these fields a number then loop thru it. I havn't tried it yet. but I'm wondering if there is a way to loop through the alphabet.
Param = "A"
For z = "A" To "t"
If (Me(Me.ParamB & Me.Param).Visible = True) Then
Me(Me![ParamB] & Me!Param).Visible = False
Me(Me!ParamL & Me!Param).Visible = False
Me(Me!ParamE & Me!Param).Visible = False
Me(Me!ParamT & Me!Param) = Null
End If
Param = Param + 1
Next z
I have a form with an UPDATE button. There is a subfrm on this form that lists all my units. I want the update button to loop through all the records on the subfrm and update the WOSD based on the information below. Is this possible?
Private Sub cmdUpdateWOSD_Click()
Call UpdateWOSDChange
End Sub
Private Sub UpdateWOSDChange()
Dim AddColor As Boolean
Dim intNumDays As Integer
Select Case Forms!frmUpdateWOSD!subfrmWOSDupdate.Form.PreFin
Case "BR17", "BR28", "WH06"
AddColor = True
Case Else
AddColor = False
End Select
Select Case Forms!frmUpdateWOSD!subfrmWOSDupdate.Form.DrStyle
Case "DCREag", "DCRHWK", "DCRFAL", "RP-9", "RP-22", "RP-23"
If AddColor Then
intNumDays = 7
Else
intNumDays = 6
End If
Case Else
If AddColor Then
intNumDays = 7
Else
intNumDays = 4
End If
End Select
Forms!frmUpdateWOSD!subfrmWOSDupdate.Form.WOSD = MinusWorkdays(Forms!frmUpdateWOSD!subfrmWOSDupdate .Form.Deldate, intNumDays)
End Sub
Well, this should be easy... But I'm doing something wrong and can't find anything in the forum as an example.....I have a Form for creating an invoice billing for segments of time spent on a project. This form (frmInvoice) has a subform(frmHoursTimeInvoice) This subform has 2 checkboxes, one for "Billed" (HoursBilled) and one to include unbilled time into the invoice total (InvoiceHours)So... When you open the form to create an invoice the subform shows only unbilled hours.... as you ckeck the boxes (InvoiceHours) it adds the time to the invoice and calculates the total... So far so good........ Now the problem. I attached a "Close" command button to test this code....Private Sub cmdCloseSave_Click()On Error GoTo Err_cmdCloseSave_ClickIf [frmHoursTimeInvoice].Form![InvoiceHours] = True Then[frmHoursTimeInvoice].Form![HoursBilled] = TrueElseIf [frmHoursTimeInvoice].Form![InvoiceHours] = False Then[frmHoursTimeInvoice].Form![HoursBilled] = FalseEnd If End If DoCmd.CloseExit_cmdCloseSave_Click: Exit SubErr_cmdCloseSave_Click: MsgBox Err.Description Resume Exit_cmdCloseSave_Click End SubNow the problem is after it marks the first record (HoursBilled) True... it stops executing the code. I know it needs a loop here.... But I cannot find an example that works!! It needs to loop through all of the records in the subform and check the correct boxes True and leave the others False.So whats the best method? AND the correct syntax! Would a "For each... (something).... Next" work here??Thanks!PS.... Sorry for the lengthy explanation :)
View 6 Replies View RelatedI am trying to edit data in a table that has 30 fields.The fields names are W1,W2,- up to W30, and their value would correspond to a control on a form I have declared the controls in an array, so i would really like to use some sort of loop
Is There any way to use a loop to refer to fields to update them rather than
having to use
rst.edit
![W1]=Ctl(1)
![W2]=Ctl(2)
![W3]=Ctl(3)
etc
rst.update
What would the correct syntax be using a for next loop
for i = 1 to 30
rst.edit
!["W" & i]=Ctl(i) - this doesnt work
rst.update
next
thanks in advance
Bjackson :)
I've got a problem. The database-user can search through the database and the results are displayed in a subform. There's a button attachted to that subform and when that button is pressed, the files, which are linked to the found records and saved outside the database, have to be copied to an other folder. So how do I make I loop for copying those files?
I've got the following code behind the button (but it doesn't work):
Private Sub Command56_Click()
Dim rst As Recordset
Dim strBnaam As String
Set rst = CurrentDb.OpenRecordset("[Forms]![frmZoekVast]![qryZoekVast subform]")
With rst
Do While Not (rst.EOF)
.MoveFirst
strBnaam = rst!Tpon
FileCopy "\NLFS006A110154$Stage BidteamSysteemTeksten" & strBnaam & ".doc", "C:TempOfferteTraject" & strBnaam & ".doc"
.MoveNext
Loop
End With
End Sub
Can somebody help me plz :( ??? THNX!
Greetz
I’m in the process of developing a holiday property availability form for displaying the available and free dates for a particular property in a particular period (usually 35 days). The main form allows the selection of the start and end dates of the period using a Calendar control and then completes text boxes with the days of the week for each day of the period.
The next step for the user will be to click on a property button to run a select query to find the booking records that are present for the availability period being looked at. Each booking record has the start date and the number of days booked together with the property name and other fields.
I have the select query results as a subform and can access fields on the first record using
Mydate = Forms!Availability!CoiresgeirDates.Form!Revdate
MsgBox Mydate
However, I need to be able to access each record on the subform to be able to colour each text box for each day the property is booked so the user can see visually which days are booked for that period.
I’m struggling to find out how access the subform recordset to be able to loop through the records and pick off the days that are booked. I’ve tried defining a recordset but VB rejects the dim setting with Can't find project or library. Can somebody point me in the right direction?
Greetings
I am using the code below to try to update some tables and data. My problem is the code seem to only work on one line in the recordset the last line. I have put up to 10 records in the recordset and it still only does the last record.
Any help would be great as it is the last bit of the project and I am behind my deadline;
'============================
'Public Procedures
Public Sub UpdateQuestion()
Dim UpdateAnswer As Byte
Dim db1 As DAO.Database
Dim rst, R As DAO.Recordset
Dim volval As String
Dim rcount As String
UpdateAnswer = MsgBox("Is this " & _
"work note ready to update and complete Press" & vbCrLf & _
"YES to update and complete OR " & vbCrLf & "NO just " & _
"to update and close", vbQuestion + vbYesNo + _
vbDefaultButton1, "Update and Complete")
If UpdateAnswer = vbYes Then ' (Default)
If Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![StatusTransfer].Value = -1 Then 'do nothing
Else
Set db1 = CurrentDb
Set R = Me.Form.RecordsetClone 'create a recordset of the data
R.MoveFirst 'got to start somewhere
Do Until R.EOF ' start loop
Set rst = db1.OpenRecordset("tbl_Data_Vessels")
rst.Index = "PrimaryKey"
rst.Seek "=", Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![VesselID]
rst.Edit
volval = rst![Volume]
rst![Volume] = Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![FinalVolume]
If rst![Volume] < 0 Then
MsgBox ("Problem restoring original value"), vbCritical, gstrAppTitle
rst![Volume] = volval
rst.Update
rst.Close
Exit Sub
End If
rst![Status] = Forms![frm_Data_JobOrder_Note]![FrmDestination].Form![Status]
rst![Updated] = [Forms]![frm_Data_JobOrder_Note]![EnterDate]
rst![LastOp] = [Forms]![frm_Data_JobOrder_Note]![JobNumber]
rst![ContentsOwner] = Forms![frm_Data_JobOrder_Note]![CompanyID]
rst.Update
rst.Close
R.MoveNext
Loop
Set db1 = Nothing
Set rst = Nothing
End Sub
Thanks
rbinder
There has got to be a better way to do what I want to do. Here is the situation:
I have a weekly forecast (up to 14 weeks worth) that I want to apply to the inventory that I have on hand to generate figure out how many 'days' of forecast that I have on hand. Eg. I have 7 units on hand, next week's forecast is 7 units therefore I have 7 days of inventory.
Here is how I am doing this:
SQL = "SELECT SKU, "
SQL = SQL & "IIF([ON_HAND]<=[W1 FORECAST], [OVERSTK_POT]/([W1 FORECAST]/7), "
weeks = "[W1 FORECAST]"
days = 7
end_brackets = ")"
For n = 2 To 14
weeks = weeks & "+[W" & n & " FORECAST]"
days = days + 7
end_brackets = end_brackets & ")"
SQL = SQL & "IIF([ON_HAND]<=(" & weeks & "), [ON_HAND]/((" & weeks & ")/" & days & "), "
Next n
SQL = SQL & "105" & end_brackets & " AS OVERSTOCK_DAYS, "
I have 2 issues with this code. The first is that 12 nested iif statements is all that will be allowed in this loop before the query becomes too complex. The second is that this cannot possibly be the best method to accomplish this. I'm hoping someone might know a better method.
Thanks!
I am trying to build an email string and have been sucessful...although there are times when the Email Field in my table may not have a value....
Currently this creates an error....I am trying to test for Null and then SKIP that record if it is null and move on...
Any thoughts?
THe problem is that I can click the Msgbox that gives me the varialbe from strTo endlessly....I am stuck in the loop...it never gets to EOF???
It never even gets passed the first record it finds...
Something is messed up....
Anyone have any thoughts...
?????????????????????
' START CODE ==================================
Dim dbs As Database
Dim rs As DAO.Recordset
Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("Select * From tbl_Groups", dbOpenDynaset)
Dim strTO As String
Dim EmailString As String
strTO = ""
EmailString = ""
Do Until rs.EOF
strTO = rs!Email
If strTO = "" Then
MsgBox "NOTHING"
Else
MsgBox strTO
End If
Loop
DoCmd.SendObject acSendNoObject, , , EmailString, , , "Subject Line", , True
' END CODE ====================================
I am currently modifying a form on my access database. More specifically the on click event of a button on the form. The form is based on a Table that only has 3 fiels, ID, GoingToCal, and Location. ID is the autonumber. GoingToCal is a multiselect list box on my form. Location is just where the user is submitting the entry from. The table is simply a way to track what went out at what facility.
The issue that I am having is 2 part. The first error I have doesn't create a runtime error, it simply doens't work. What I am attempting to do is update a recordset of the items selected in the list box. The items are in another table, and I have based my recordset on a query. When I run my code I am expecting first for the specific record to be found(I think works), then update a yes no box to yes. The Yes No field is called StillOut. It is a part of the query. However it doesn't update the Yes No box to Yes, nor does it seem to update any of the boxes. So I am confused as to what I am doing wrong that the field for that record isn't updating.
My Second issue, which I know there can be a few possible ways to work around I just can't make some of them happen. I am using the FindFirst for how I narrow down my recordset to just the one record I want to update. It doesn't seem to like "," with it. I have tried many times when setting the OpenRecordset to the specific record using SQL and a mix of SQL and the Query but no matter how hard I try I can never return any results. It constantly says too few parameters. That is why I am using the FindFirst command.
how to make the record update the Yes No box and how to specifically change just the records that have been selected in the form and a way to select those records.
If Me.GoingToCal.ItemsSelected.Count <> 0 Then
For Each oItem In Me.GoingToCal.ItemsSelected
If iCount = 0 Then
[code]...
I have learned the basic looping technique for going through a record set from the following link. I need to know if my logic is on the right track. URL...
My question is a followup to a thread that was opened on this forum: URL....I want to do the following:
1) Use record set looping technique to fix a variety of incorrect naming conventions to a standard format
2) Update the table (or create a new table) from the updated record set values. (Is my logic going to update the table selected in the code I used to dimension the record set or will I need to do something else to make the changes available for other tasks after record set is closed ? After the naming conventions are fixed this data will be available for excel automation that I am working on and posting questions on another thread in this forum. HAHA I'm going code BANANAS)
3) rs.fields![fleetlocation] is used so many times, how can I make this a variable (what do I dimension the variable as?)
4) Use an AND statment with an if statment (how to do this with correct syntax)
Code:
sub loopandfix()
on error goto errorhandler:
strSQL = "tblUnionQueryResults" 'table was created from a union query but has inconsistant naming conventions for the fleet location name
[code]....
I have the following example of code I have previously used which loops through columns where a column is called "Page1", "Page2", Etc.This is fine when the number of columns is fairly small. But in a new case I have 585 250 columns which may or may not contain some data which is the key.If data exists in column do something and after that or of not move to the next column and do the same thing until there are no more columns?
Code:
Dim rst As DAO.Recordset ' recordset for category data
' open the recordset for categories
Set rst = CurrentDb.QueryDefs!QryRMA.OpenRecordset
' loop through the rows in the recordset
rst.MoveFirst
Do Until rst.EOF
[code]...
I have a main form, lets call it FrmMain. In addition, I have a subform on FrmMain that has a tab control on it (frmSubform). What I would like to do is on form load, reference the tab control that is on the subform, loop through the controls/textboxes, lock them and change their backcolor. The code I am attempting is listed below, however, I am getting a "type mismatch error" highlighting this line:
PHP Code:
Set Frm = Forms!frmConsultingMain!FrmConsultingFeeDataÂ
Below is my code:
PHP Code:
Dim ctl As ControlDim Frm As FormSet Frm = Forms!frmmain!FrmSubformFor Each ctl In Frm.Controls   Â
If (TypeName(ctl) = "Textbox" Or (TypeName(ctl) = "combobox") Or (TypeName(ctl) = "listbox")) Then       Â
ctl.Value = Null        ctl.BackColor = vbYellow        ctl.Locked = True    End IfNext ctlÂ
Im still struggling with working with recordsets.What i want to achieve is to loop through a continuous form recordset, Using the OrderDetailFK from the record set and the OrderDetailPK in the OrderDetail Table
Looking at the Status FK in the OrderDetail Table. IF the OrderDetail Status = 2 then i want to make the textbox enable property = False. I have made the bellow SQL string which is placing all the criteria i just need to link it into a recordset?
Code:
mySQL = "SELECT [StatusFK] FROM [tblOrderDetail] WHERE [OrderDetailPK] = " & rst!OrderDetailFK & " AND [StatusFK] = 2 "";"""
I have a looping error on this code. It all works fine until the very last row, on the last row it brings over the Claim_Number and the FacID, but leaves the Paid_Amount blank.
If I have 10 or 10,000 rows, its always leaves the Paid_Amount on the last row blank.
Code:
Function RunLWSPA()
Dim cnn As ADODB.Connection
Dim rstUplo As New ADODB.Recordset
Dim rstDiag As New ADODB.Recordset
Dim rstTemp As New ADODB.Recordset
[Code] ....
I have managed to amend records based on the user selecting multiple items in a list box by using the following code.
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Usage", dbOpenDynaset, dbAppendOnly)
Set ctl = [Forms]![frmsearch]![lstSelector]
[Code].....
what I need to do now, is to loop(I think) through and amend all the records in a populate listbox, with no selections, similar to above, but without selection.
arrLocations = Me!customerBranchLocations.Value
'MsgBox (arrLocations)
arrParams = Split(arrLocations, ";")
For i = 0 To UBound(arrParams)
MsgBox (arrParams(i))
strSql1 = "SELECT branchName FROM ustax_customerBranchLocationsTBL WHERE branchName = '" & arrParams(i) & "' AND branchCustomerParentID = " & Me!customerID & ""
'MsgBox (strSql1)
DoCmd.RunSQL strSql1
Set rs = db.OpenRecordset(strSql1)
Dim count
count = rs.RecordCount
Next
Above is my code, what i am trying to do is loop thru the array and check if the values are in a table. I am getting an error after the first loop of 'A RunSQL action requires an argument consisting of an SQL statement', why would I get this error if I am looping thru the array? Wouldn't the sql statement just be read with the new value each time?
Thx.
This query returns a running asset balance for 60 months. Each month is 1 column.
1) I need to create a query based on those results that can cycle through each column and determine if it is NEGATIVE. Then if it is negative, I need to perform some math on it to determine how to get it back up to being positive.
2) I need to loop through X columns based on the assets lead time. Each lead time varies and I have a master table that contains it. So if the assets lead time is 3 months, I need it to move over 3 columns and return that value. If it is 6 months then I need it to return the value in the 6th column.
I have 3 tables; tblProjects, tblTesting, and tblEmployees.
The issue I'm having is that I've created a form which collects testing information from testers that test a particular project. Each project can have multiple testers. tblProject and tblTesters have a one to many relationship and the form (frmTesting) consists of a main form for project info and a subfrom for testing info.
For each project tested there are 4 possible outcomes. 1st not all testers have tested to project and that case project status is "InTesting", 2nd all testers pass testing in that case project staus is "Waiting Final Approval", 3rd all testers fail testing in that case project status is "Maintenance" and last some testers pass and some fail in that case project status is "IPR".
Here is my code for a button I've placed on the sub form.
Private Sub BtnTest_Click()
Dim db As DAO.Database
Dim rsSQL As DAO.Recordset
Dim intMax As Integer
Dim strSQL As String
Dim csSQL As String
Set db = CurrentDb()
[Code] ....
My select statement for my recordset isn't working and I've tested it by putting a specific project number in the statement and I still only pulling one recordset.