Modules & VBA :: FileCopy Command For Each Record In Recordset
Nov 5, 2013
I have a problem with trying to execute a command for each record in a recordset. What I'm trying to build is a file distribution system. I have a form with the path where the source file is and a subform with a couple of records where the destination path is defined. I use the code you will find below, but it will only copy the file to the destination from the first record.So the code will do the filecopy command for every destination.
Code:
Dim Sourcepath, Destinationpath
Sourcepath = Forms![Item distribution]![Item source path]
Destinationpath = Me.Destination_path
Dim rs As DAO.Recordset
Set rs = Me.Form.Recordset
While the validation runs a boolean keeps track of validated input and errored input.
After validation the validated input is dumped in the table.
Now what I want is de saving the errored record from "rsSQLIn" to be copied to a new .csv file.
The problem I have is that I cant seem to get the current record from the recordset "rsSQLIn". How do I reference this? I need the complete set of 24 fields being the same within "rsSQLIn"
I am attempting to use 2 fields from a query to supply the Top and Left Properties of a Collection of Rectangle Controls on my form. The purpose of this is to display the locations on a map of "Spots" in a haunted house. The query that I am using shows the spots that have been pre-tagged with the location of where they belong on the map (currently the query has only 24 tagged spots). On the actual form I have rectangle controls (control type acRectangle) with their visible property set to False by default, named box1 through box25 (there will be more eventually, as I am just working with this test group).
I started with the following code, yet it stops after (correctly) placing the first spot on the map (please see the attached jpg):
Code: Private Sub Form_Open(Cancel As Integer) Dim ctl As Control Dim db As DAO.Database Dim rst As DAO.Recordset
[Code]....
I'm sure I need to have 'Loop' in there somewhere, but I am not sure exactly where to place it, or if another line is also needed.
Is is possible to generate a workbook for each record in a recordset, and title it using the unique identifier for that record?
I created the following code, but it does not seem to work. First of all it doesnt like the string and secondly it does not like the declaration of wb as Excel.Application
Code: Private Sub generate_wkbk() Dim rsID As DAO.Recordset Set rsID = CurrentDb.OpenRecordset("Select * FROM tblMeeeting;", dbOpenDynaset) With rsID rsID.MoveFirst
Is there a way to print the current record from a dao.recordset?
This is an exercise to compare data content.
I have a table with 30 fields and thousands of rows (rs1).
I'm comparing it with a copy of the same table (rs2) that has the same number of fields and the same rows and almost the same content.
I can loop through the recordsets and get the cursor to stop on a field whose values don't match, lets say on row #x
and the programmed message will say something like:
"ROW: 699 Field: [RequestStatus] rs1.VALUE: Closed, DOES NOT MATCH rs2.Value: VOID, in the comparison recordset"Then I'd like to print the entire Row, Row #699.
I thought I could use rs1.getrows but I'm not sure how to make that work.
I found this code and have substituted parameters to suit my own needs however the loop is not working. Only the first record in my recordset (which is a test recordset of only 3 records) is being updated.
Also, for testing only, the edit or update being applied is trivial: Description = "WHITE RESIN". If i can get the loop to work I want to substitute higher functionality to the module.
Private Sub Update_Click() Dim dbs As DAO.Database Dim rsQuery As DAO.Recordset
Set dbs = CurrentDb Set rsQuery = dbs.OpenRecordset("qryRmResin", dbOpenDynaset)
I have the listbox named "payment_date", which shows recordset specified by following code:
Code: Private sub Form_Current() Dim que As String que = "SELECT [date] FROM payments WHERE id Like '" & Me!myid & "*' ORDER BY [date] desc;" Me.payment_date.RowSource = que End Sub
I need my listbox to show recordset with record count like this:
My question is which method of finding the last record is best, QueryDef or Recordset? Here is my data:
Table: tbl_module_repairs
Field: aps_rma
Textbox to insert last record RMA into: txt_test
Here is some code I tried but get an invalid argument msgbox:
''''''''''''''''''''''''''''''''' 'Opens last RMA into textbox (For opening tag sheet) Dim dbs As DAO.Database Dim rst As DAO.Recordset 'Get the database and Recordset Set dbs = CurrentDb Set rst = dbs.OpenRecordset("tbl_module_repairs")
In order to arrive at the required solution I have to perform a calculation, using data from each successive table record (in chronological order). The result of each calculation must then be used to arrive at the result for the next calculation.
In other words I have to update a variable based on data from each record, sorted in chronological order, and use the final result to populate a field on a form.
Is it time to break out my copy of "VBA For Dummies" and start learning how to use Recordsets?
I have a table in Access that I have a form saving new records to. Before this save occurs, I would like Access to check if the account number already exists and if the account does exist if it is outstanding. If both of those conditions are met I would like a message box to display and cancel the save as it is a duplicate. I can't seem to get it to work though.
I was thinking to use a filtered recordset based on one of the conditions and then perform a find on that recordset to see if it is null.
Code:
dim acct as long dim rstfiltered as DAO.Recordset Set rstfiltered = CurrentDb.OpenRecordset("SELECT * FROM tblclstrack WHERE [Request Status] <> 'Completed'") acct = Me.cd_number.Value
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 am adding new record into subform via recordsetclone method. The problem is that record is added but on save it does not appear in the table. If add this record manual using subform everything works. When record added manually update of the record works fine.
C 'Add Wastage value to flooring area section Private Sub Wastage_AfterUpdate() Dim rsFlArea As DAO.Recordset Dim Wastage As Double Dim Item As String Set rsFlArea = Me.OrderFloorAreaEdit.Form.RecordsetClone
I have created a form and need to be able to delete employees from a table. I built a command button using the wizard and this is the current vb code-
Code: Private Sub cmdDeleteEmployee_Click() On Error GoTo Err_cmdDeleteEmployee_Click
DoCmd.RunCommand acCmdSelectRecord DoCmd.RunCommand acCmdDeleteRecord Exit_cmdDeleteEmployee_Click: Exit Sub Err_cmdDeleteEmployee_Click: MsgBox Err.Description Resume Exit_cmdDeleteEmployee_Click End Sub
But when I click on the button I get a message saying it would create duplicate data in the table.
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
Ok I have a Continuous form that in one of it's fields contains Hyperlinks. The form is named [Song Info] and the field is [Play]. I want this field to be hidden and have a command button open up the hyperlink in that recordset. I think that all the info need but if not let me know.
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
I need to return a folders directory to a text box on my forms record called Files_Directory when i click the Browse command button... The folder will have more folders within it along with documents all relivant to the folder selected, hense the need for just the folder directory rather than a file.
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?
I have a form with a record source already defined (used so the user can browse through records and edit them) I have the form store requirement ids and not the name however I would like to present the requirement name rather then the id to the user So it tried with a sql query and a recordset but I keep getting the error data type mismatch in criteria expression on this line Set rsm = dbm.OpenRecordset(sRequirementName1)
I think the problem is having the record source already defined and then having a new Recordset not sure how to work around it
here is the code ---------------------------------------------------------------------------------------------------------------------------------------------------------- Private Sub Next_Click()
Dim sRequirementName1 As String Dim queryResult As String Dim wsm As workspace Dim dbm As Database Dim rsm As Recordset
I have also attached the DB if anyone thinks they can help, if you are confused about what i am trying to do with the modify form i made one with students that works it is the modify hours1 form and the code above is the on click event for the next button
First I will open a form and choose a salesman. Then based on the salesman selected I want to open a recordset and start searching for the last record for the salesman selected. When the record is found some of the information from that record is pulled on the form.
I am trying to go to a specific record in a recordset (on a form).
In the past I as using the typical open the form and use the filter to go to the specific record:
strLinkCriteria = "Where ID = 5"
Docmd.openform "frmName", , strLinkCriteria
This takes me to the correct record, but filters the form down to that one specific record. The users instead would like to go to the specific record, but be able to see all the other records for the form correctly.
I have 2 forms. On form1, the record source is a query. From form2, I need to iterate through the recordset from form1 and perform some action. How do I access the recordset from form1?
