Forms :: Loop Through Continuous Form Recordset Crash
Jun 27, 2014
My application crashes when trying to change the value of a text box in a continuous form. Here is the code:
Code:
Private Sub cboPoCurrency_AfterUpdate()
On Error GoTo ErrHandler
Dim rst As Recordset
[Code].....
If I replace .txtUnitCost by MsgBox .txtUnitCost, it loops correctly through each record and returns the value. But if I try to change the value as shown in above code, MS Access crashes! (This is a desktop application with tables linked to SP lists - not a web app)
In my database, I have a continuous form with a Name, a Date and a Yes/No field.
When the form opens, I want to look at the date of every record on the form and show a message box if it is before the current day.
The code I have is this;
Private Sub Form_Load() With Me.RecordsetClone While Not .EOF If Me.Date1 < Date Then MsgBox "" & Me.Person & "" End If If Not .EOF Then .MoveNext Wend End With End Sub
However, it loops just the first record the amount of times there is of records (i.e., it will only show the first person's name in the message box, and will show 3 times if there are 3 records).
I have a following problem: I have a form in continuous mode. Users are supposed to filter data in it to their liking using inbuild filters in Access. After that there is a button that should calculate total weights in filtered records (at this point, I add more calculations once I have solved this issue). Code of button is as follows:
Code: Private Sub btnCalculateWeight_Click()
Dim rst As Recordset Dim weight As Long Dim material As Long Dim subtype As Long Dim locus As Long
[Code] ....
Loop goes through recordset correct amount of times but for unknown reason takes value only from the first record and sums it N times where N is number of records in recordset. Recordset seems to be correctly assigned according to messageboxes I added to pinpoint problem. PotteryWeights is a custom function I made and it works properly.
I'm trying to populate a listbox from a recordset. I will explain what I'm trying to achieve.
I have a (continuous) form with some data from a table. On the form header I have a listbox, showing all (distinct) customers from that form.
The user is able to do some filtering. That all works great. But I want to populate the listbox with ONLY the customers on the form AFTER filtering.
One way I thought might me the solution was using RecordClone. But I'm stuck there. It must be something simple for an expert. I'm not entirely a newbie, at least not to VBA. Is there an easy way to refer the listbox' recordset to the actual recordset on the form?
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 have attached an image of what i am trying to achieve so here goes:
I need to add data from one subform to another - problem is they are both continuous forms and both subforms.
The part in green is where it needs to go, and the white is where the data is held. The links have already been created, i just need to get the info from subform 1 to subform 2.
I have attached code but it only moves line selected / first line.
Dim dst As DAO.Recordset Set dst = Me.frmAS9102_Material_LINK.Form.RecordsetClone With dst .MoveFirst Do While Not .EOF
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 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
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 there a way of looping through a form record set, while adding new records to a different form record set? using some data from the 1st record set in the new records?
So I have a table "zztblArticles." Some fields should go to "tblArticles" and values in Tag_ID should go to a lookup table "tblTag" and a junction table "tblArticles_Tags." I'll explain the code I've written below:
Code: Private Sub cmdSubmit_Click()
Dim db As Database Dim strINSERT As String Dim strVALUES As String Dim rszztblArticles As DAO.Recordset
[Code] ....
The first loop through works fine, I get the records uploaded to all tables. The second loop through fails at
Because it can't decide which ID value to use. This is because the value has been duplicated in tblArticles after the code acts on the same record again. It has completely failed to move to the next record in the recordset, despite the .MoveNext before the Loop!
I have 2 identical tables of asset information. Table 1 has 251 records while table 2 has 84 records. All 84 records are in table 1 right now. My end goal is to be able to click a button, have vba script run, and table 2 updates table 1 with any changes from different fields. I know there are merging options with query and what not but the exact structure of my tables doesnt play well with it.
Right now, my code is able to loop through the values of each computername and display it. For some reason, when it gets to the 130th record of table one, the loop skips it and returns the 131st record, it stays one ahead for the rest of the loop and then prints the 130th record finally.
Since I am trying to compare field values, this sudden shift throws everything off. why it skips?
Code:
Option Compare Database Private Sub UpdateAssetsBTN_Click() On Error GoTo Err_Proc Dim rs1 As DAO.Recordset Dim rs2 As DAO.Recordset
managed to get some code up and running but when trying to enhance it I have hit a rut.
I have a function that is looking up a query called Optimisation - Auto Optimise with operational data in it. I only need 2 key fields; consolcode and volume. There are 106 records with different consolcodes each with different volume. e.g consolcode: Chittagong to Rotterdam201452 (Chittagong to Rotterdam by year "2014" by week "52") and volume 161 (cbm)
I then run a code that allocates the volume into specific sea freight containers and returns the values into a different output table.
The allocation code works fine but when I run the loop function for the recordset rsttradelane it runs for the correct amount of records (106) but always returns the first record of Chittagong to Rotterdam201452 and 161 cbm and not the other 105 consolcodes with the different cbm. Giving me an output table with Chittagong to Rotterdam201452 and 161 cbm repeated 105 times!!
Code: Set rsttradelane = dbsEPIC.OpenRecordset("Optimisation - Auto Optimise") consollane = rsttradelane!consolcode ConsolVol = rsttradelane!Volume Do Until rsttradelane.EOF 'Optimisation code' then Code: rsttradelane.MoveNext Loop rsttradelane.Close
How do I ensure that each consolcode and its associated cbm is recognised individually and flushed through the optimisation code?
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 some code that loops the clone recordset of my subform and generates a email with attachments. I have mainform and continuous subform within the subform I have field called address this holds paths to files and another field called send and this is a yes/no field
Now what I'm trying to do is loop through the subform if send field is true then attach file from the address path but if send field is false then do not attach file
Code: Dim olApp As Outlook.Application Dim olMail As Outlook.MailItem Dim olAttach As Outlook.Attachment Dim rstAttach As DAO.Recordset
Set olApp = New Outlook.Application Set olMail = olApp.CreateItem(olMailItem)
I have this code here that sends an email out with all the needed info. The problem i ran into was that for my field "item" it is in a table called "test" and there can be more than one record in there. I just can't get it to loop and show me all of the records. Right now it shows me only the first record. Also it is showing the name of the table before the result so i am not sure what is wrong there...
Code: Private Sub Command4_Click() On Error GoTo Err_SendInfo_Click Dim varTo As Variant Dim varCC As Variant Dim stSubject As String
I am trying to get a form to loop through after a click the next. I have looked around in this site, and ive not found the answers im looking for. What im trying to do is I have navigation records for my form, but when I reach the last record, I would like it to loop.
I am trying loop through a datasheet so that I can copy the contents of one field into another form.
I have tried using the bellow code to do this on a button click event. However when i run it I get an error telling me that the object doesn't support this property or method. Im not quite sure what this means.
Dim rst As DAO.Recordset Set rst = Forms![Roll Out - Site Form]![Roll Out - Sign items pick list].RecordsetClone Do Until rst.EOF [Roll Out - Sign items added].Form![Code] = [Roll Out - Sign items pick list].[Form]![Item Category]
I have a bound continuous tabular form,However, based on data content in one field of a record, I want a checkbox in the same record enabled, so the user can check it if necessary. I have created a record set using the form as shown below, and I am looping through each record. To show that my code is referring to the field with required data content, I display it as a message box and it works, yet my checkbox does not enable.
I have the code in the form_load event, however, for testing purposes I have it behind a button.If I am seeing this properly, the code behind the button enables the checkbox for ALL records once the criteria in the required field is true, and based on the last record, which has no data content, it disables the checkbox in ALL records. I also have the PK ID for each record hidden in the form. Can I utilize that to target the checkbox of each individual record??
Form Detail -Form does not allow additions or deletions. Edits allowed -All fields are disabled and locked -I only want the check box to unlock if data is found in the "RequiredField" as referred to below. I have also tried if not isNull(requiredfield.value) then -enable checkbox, which yields the same results
Here is my code
Code:
Dim rstMyForm As DAO.Recordset Set rstMyForm = Forms!MyForm.Form.Recordset rstMyForm.MoveFirst Do While Not rstMyForm.EOF If Not RequiredField.Value = "" Then
I want to use buttons on a form to change the sort order on a continuous form. In the buttons click event I am using a public function (named Sort_1) to change the sort order. The first element of the event call is the name of a generic query (named Sort_1_Query1) and the query field to sort (LAST_NAME OR FRIST_NAME, depending on the button.)This is the Click Statement.
=Sort_1("Sort_1_Query1","LAST_NAME")
This is the Public Function Public Function Sort_1(SortName As String, FieldName1 As String) DoCmd.ApplyFilter SortName, FieldName1 & "between 'A' and 'Z'" End Function
I think the problem is in the use of quotation marks or trying to pass the query field name to the Do Command or the use of an ampersand.
I have a continuous form in which I put a command button for each record called "detail". I would like to click on the "detail" button and make it open another form containing all (and only) the info on this record.
At first I refused to use an "id" to link both forms, but finally I added the "id" in the table... however still does not work.
continuous form: "04 - GASTOS_BUSQUEDA" id field on continuous form: "Gastid"
pop-up (details) form: "GASTOS_EDITAR" id on pop-up (details) form: "editar_id"
This is what I have tried on the "click" properties of the "details" button field (called "btn_editgs"):
4) Private Sub btn_editgs_Click() On Error GoTo btn_editgs_Click_Err Dim strWhere As String strWhere = "[editar_id] = " & Me.Gastid DoCmd.OpenForm "GASTOS_EDITAR", , , strWhere btn_editgs_Click_Exit: Exit Sub btn_editgs_Click_Err: MsgBox Error$ Resume btn_editgs_Click_Exit End Sub
I have a continuous form sfrmAddress which is based on my table tblAddressList. What I am trying to achieve concerns the date fields DateFrom and DateTo.
DateFrom is a bound text box txtDateFrom
I am trying to put an IIf function in the Control Source of unbound text box txtDateTo
qryAddressHistory1 returns the LatestFromDate , so the most recent address.
The idea being if it is their most recent address, they have lived there until today, if it is not their most recent address it will display the date contained in the table (when they moved out or DateTo).
I have tried this a few different ways, double and triple checked my references and I'm usually OK with IIf so I don't understand where it's going wrong!
I should add I'm getting the #Name? error all the way down the column when I open the form.
