General :: No Current Recordset / Canceled By Associated Object
Feb 6, 2013
My form respectively subform has a couple of problems related to the recordset as it is available in VBA.
The mainform contains material data, the subform contains the components of that material and a quantity, while the components are materials themselves. The subform's control source is an SQL statement created by the query builder.
Everything worked fine before i replaced the material-selecting combobox in the continuous subform by a textbox and a button. That button leads to another (dialog) form with some filtering options, which in turn returns the number of the selected material. This material gets inserted into the textbox. To this point it works fine.
But when i enter a quantity before i selected a Material, i get an error message after selecting the Material: This Action was Cancelled by an Associated Object. This happens while executing the following VBA Code on click of the material selection button (exact position commented in code):
Code:
Private Sub cbuSelectComponent_Click()
' Select component
Dim SQL As String
Dim rs As Recordset
DoCmd.OpenForm "Material Selector Dialog", , , , , acDialog, "Dialog"
If GLB_selected_mat = -1 Then 'cancel
[Code] ....
I've found the following Microsoft KB Article: [URL] ..... In their example code they use:
' Restore text box value to the original record contents
' in this case, that is NULL
datDataCtl.UpdateControls
I assume this is the relevant part, but i have no DataControl (what's that?) and neither found an UpdateControls method in the subform object.
The second error, "no current recordset", occures when i edit an existing component line in the subform that has been added right before (also using the same event and code as mentioned above). If i close the form after adding the component and open it again, it's no problem.
View Replies
ADVERTISEMENT
Aug 14, 2015
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
View 4 Replies
View Related
Aug 15, 2006
Hi all,
I've been banging my head against this problem for a while now and making no progress and am hoping someone cleverer than me can provide some assistance in solving it, or point me in the right direction.
Here goes....
I am working solely in the VBA environment and would like to perform an automated process that returns a recordset based on the results of a previously created recordset (assuming of course it is possible in the first instance.)
The catch is I would like to perform an INNER JOIN using the results held within a recordset.
I have already achieved this by creating a temporary table (using a SELECT INTO statement) and then removing the temporary table when I am finished but find this is a slower process than I would like.
I have three tables:
Table A - Fields: GROUP, NAME, DATA1, DATA2
Table B - Fields: GROUP, NAME, , CITY, DATA3
Table C - Fields: CITY, DATA3
I have the following declarations:
Dim db As Database
Dim rs1 As Recordset, rs2 As Recordset, rs3 As Recordset
Dim strSql As String
Set db = CurrentDb
Next, I select a specific range I am interested in. For the purpose of this example, I am only selecting GROUP values of 1,2 and 3.
strSql = "SELECT * FROM A WHERE A.GROUP IN (1,2,3)"
Set rs1 = db.OpenRecordset(strSql)
rs1.MoveFirst
Now, for each record returned in the query above, I would like to process as follows:
' // Build SQL statement
strSql = ""
strSql = strSql & "SELECT B.* "
strSql = strSql & "AS jc "
strSql = strSql & "FROM A "
strSql = strSql & "INNER JOIN B ON (A.Id = B.Id) "
strSql = strSql & "WHERE B.Name = " & rs1.Fields("Name")
strSql = strSql & " And A.Name = B.Name"
Set rs2 = db.OpenRecordset(strSql)
rs2.MoveFirst
The rs2 query returns the results of that query into what I though was a memory resident table referred to as "jc". (This is most likely where I am going wrong!)
Using this reference, I perform another query...
' // Build SQL statement
strSql = ""
strSql = strSql & "SELECT * FROM jc "
strSql = strSql & "INNER JOIN [C] ON (jc.City = C.City) "
strSql = strSql & "WHERE (([jc]![City] In ([jc]![City]) ))"
Set rs3 = db.OpenRecordset(strSql)
rs3.MoveFirst
And from this point I would be able to process the results (if any) that I need to....
...if it wasn't for the error:
"The Microsoft Jet database engine cannot find the input table or query 'jc'. Make sure it exists and that its name is spelled correctly."
I know the table doesn't 'truly' exist as a physical object or entity within the database, so how do I reference this 'phantom' table in a query?
I am happy to provide more details if it would help solve this problem and would really appreciate any feedback that anyone has to offer.
Thanks,
JC
View 5 Replies
View Related
Oct 14, 2013
I am in the middle of creating a function that populates two unbound text boxes on a form and then uses those unbound textboxes in a query (Total of 3 unbound text boxes - 2 are populated from this function). The saved query object is working fine when I manually execute it (after the unbound text boxes have been populated). However, when I go to set the same query to a recordset I am getting the "Too Few Parameters. Expected 3." error message.
In the saved query I used the build function to use the unbound text boxes as part of the where clause. Below is the code I am trying to execute:
PHP Code:
Public Function Test()Dim db As DAO.DatabaseSet db = CurrentDbDim rst As DAO.Recordset
Dim DtBegin As String''FInd the Begin dtstBegin = DateAdd("q", -1, DateSerial(Year(Date), (DatePart("q", Date) - 1) * 3 + 1, 1))
Dim DtEnd As String''find the end dateDtEnd = DateAdd("Q", DateDiff("Q", 0, Date) - 1, 0)
[Code] ......
The unbound text boxes are populated before the query is set to the recordset.
View 5 Replies
View Related
Jul 8, 2015
Which reference to be added to use Recordset object in Excel VBA. I added "Microsoft DAO 3.6 Object Library" reference but still its not working.
View 8 Replies
View Related
Dec 4, 2013
I put a button on a form for printing the object but it prints every record. How can I print the object with the current record?
View 3 Replies
View Related
Jan 7, 2008
i'm getting this message when i try and change a record in a table via a recordset... but i am using CursorType = adLockOptimistic which i thought let you make changes to the table
here is my code so far
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.CursorType = adLockOptimistic
rst.Open "TBL_TmpSubmission", CurrentProject.Connection
If rst.RecordCount > 0 Then
Do While Not rst.EOF
MsgBox rst!PropertyType, vbOKOnly, "debug"
If DCount("[PropertyType]", "[TBL_PropertyType]", "PropertyType = '" & rst!PropertyType & "'") <> 1 Then
rst!PropertyType = DLookup("[PropertyType]", "[TBL_PropertyType]", "IDPropertyType = " & rst!PropertyType)
MsgBox "property changed", vbOKOnly, "debug"
Else
MsgBox "good property", vbOKOnly, "debug"
End If
rst.MoveNext
Loop
End If
rst.Close
am i using the wrong combination or cursor and lock type here? reading the help it seems i should be able to make changes to the table.
View 2 Replies
View Related
Sep 2, 2005
ADODB.Recordset (0x800A0BCD)
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.
Code:
<%
function combomaker(elSQL, elFieldNameID, elFieldName, elSelected, elConn)
' This function creates a generic combo box with values from a table
dim elRS
set elRS = server.CreateObject("ADODB.Recordset")
set elRS = elConn.Execute(elSQL)
elRS.MoveFirst
do while not elRS.eof
response.Write "<option value='" & elRS.fields(elFieldNameID) & "'"
if elSelected <> "" then
if cstr(elRS.Fields(elFieldNameID)) = elSelected then
response.Write " selected "
end if
else
if elRS.BOF then
response.Write " selected "
end if
end if
response.Write ">" & elRS.fields(elFieldName) & "</option>" & vbCrLf
elRS.MoveNext
loop
elRS.close
set elRS = nothing
end function
%>
View 1 Replies
View Related
Aug 4, 2013
I have a DAO.recordset called "rsSQLIn". This comes from a csv file by:
Code:
strSql = "SELECT * " _
& "FROM [Text;Database=" _
& strFolder _
[Code]....
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"
View 3 Replies
View Related
Aug 7, 2015
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.
View 4 Replies
View Related
Apr 8, 2015
Code:
Function Write_rstADO_to_CurrdB_Table()
'Assumes you have already setup a DSN to your Server
'Assumes YOURDESTINATIONTABLE is the same structure as your SERVER.TABLE
Dim cnnADO As ADODB.Connection
Dim wkspDAO As DAO.Workspace
[Code] ....
View 1 Replies
View Related
Aug 25, 2006
Hi, I've been progamming Access for a couple of years so am not a complete novice but I can't seem to work out this problem.
I downloaded some code from "databasedev.co.uk" from the query section called "Using a Microsoft Access listbox to pass criteria to a query". It works fine. However when I import my own table and change the code to SELECT the imported table and change the WHERE statement to my new string within that table , I get an error. When I select from the list box and click the command button I get the message "You canceled the previous operation".
However if I select the "ALL" selection from the list box it does return all the records.
Any help would be gratefully received!
JeffT
View 1 Replies
View Related
Apr 4, 2015
I have a form that opens when you initially open the MS Access file...
This first form posts session and user ID data to a sessions table, then closes itself and opens an end user form (i.e. the main form in the application)
It's work just fine for many weeks, up until a few minutes ago. Now, when I open the MS Access file I get this error message:
Run-time error '2501':
The OpenForm action was canceled.
When I choose "debug" from the error message, it's showing me that the error happens here:
Code:
Public Sub CloseMeAndOpenMain(frmMe As Form)
DoCmd.Close acForm, frmMe.Name
DoCmd.OpenForm "0100_0000_STRAT_AND_REQ_ASSEMBLY_ECs_LISTING"
End Sub
The thing is, I can then walk through the code with no issues (i.e. when I hit F8, it runs...)
Also when I open the first "sessions" form from inside MS Access (as opposed the file open feature) it runs just fine and opens the main end user file without any issue.
I only get the error when I initially open the first form from the file...
View 1 Replies
View Related
Sep 13, 2006
Hi all,
Recently I am getting this message in a database that would allow users to add new rows to tables, even when I look into the tables themselves I can't seem to enter data
Would anyone know what could be causing this error?
Error (no. 2501): The save action was canceled
Thanks
Polo
:)
View 3 Replies
View Related
Feb 26, 2008
Code:Private Sub iProduct_BeforeUpdate(Cancel As Integer) If DCount("*", "Products", "Product = " & Me.iProduct) > 0 Then MsgBox ("Code Executed Successfully") End IfEnd Sub
I keep getting the error 2001: you canceled the previous operation and I don't know why.
'Products' is the table name, 'Product' is the field name and 'iProduct' is the name of the comboBox on the form.
View 7 Replies
View Related
May 19, 2006
Hi,
I have the following code:
Private Sub PLPREMCAL_Click()
Dim ColumnNumber As Integer
ColumnNumber = [NoWorkingDirectors] + [NoManualEmployees] + [NoPriciples]
If [LOI] = "2.6m" Then
PLPremium = DLookup("[" & ColumnNumber & "Emp]", "2_6m", "[Business]=Forms![Form1]![Business]")
ElseIf [LOI] = "3.9m" Then
PLPremium = DLookup("[" & ColumnNumber & "Emp]", "3_9m", "[Business]=Forms![Form1]![Business]")
ElseIf [LOI] = "6.5m" Then
PLPremium = DLookup("[" & ColumnNumber & "Emp]", "6_5m", "[Business]=Forms![Form1]![Business]")
ElseIf [LOI] = "10m" Then
PLPremium = DLookup("[" & ColumnNumber & "Emp]", "10m", "[Business]=Forms![Form1]![Business]")
End If
End Sub
LOI is coming from a Combobox on my form. When I change the LOI I want the PLPremium value on my form to up tho the value in that table.
It's so frustrating - I keep getting this run time error
Would REALLY appreciate any help.
View 1 Replies
View Related
Nov 10, 2014
I use the following code in the On No Data event of a report:
Private Sub Report_NoData(Cancel As Integer)
MsgBox "No data exists for the date range entered.", vbInformation, "No Data Alert"
Cancel = True
End Sub
This works fine to cancel the opening of the report however Ms Access annoyingly then pop-ups another alert that reads as follows:
"The OpenReport action was cancelled". There is no error number.
I've tried putting in a DoCmd.Close after Cancel = True however this doesn't do the job.
Any way to prevent the Ms Access pop-up from appearing?
View 4 Replies
View Related
Aug 8, 2012
I'm relatively new to access. I probably use about one tenth of one percent of what it has to offer because I have trouble understanding it at this time. I need to know how to update a yes/no object.
View 2 Replies
View Related
Aug 25, 2012
Been using XP all this time, just got a new computer with Windows 7. Still using old Access 2000, not my call to update as this is the decision of the guy who owns the database.
Anyways, I opened Access for the first time, just a blank database so I can set up the user account. When I go to Tools > Security > User and Group Accounts and try to assign a password to Admin, I get the error message that it cannot update because the database is read only.
Where did this come from? I have never seen this message before, and the person who owns the database has a couple computers with Windows 7 that I have set this up on and did not run into this issue.
I realize that there can be some compatibility issues with Windows 7 and Access 2000 but it has worked on other Windows 7 computers, and not using the virtual XP mode.
View 13 Replies
View Related
Nov 23, 2012
I am using a command button to email an invoice to a customer using the sendobject function, whilst this almost works correctly it is adding an additional mailto:address along with the correct email address.
Example: CorrectEmailAddress and then#mailto:CorrectEmailAddress
The code I am using is:
On Error Resume Next
Dim Cusemail As String
Cusemail = Nz(DLookup("Email", "invoiceemailQ", "orderID=" & OrderID), "")
If Cusemail = "" Then
MsgBox "No email Address"
Exit Sub
[code]....
View 6 Replies
View Related
Aug 11, 2015
I have adopted someone elses database. It is a front end with about 100 linked tables/forms/reports/modules. The problem is that the database is really large. I would think by looking at the objects that it should be around 5mb tops, however after compacting it is still 63mb.
Is there a way (vba or otherwise) to look at each object and get its size in KB?
Once I can figure out which object is taking up too much space I can work on making them smaller.
View 6 Replies
View Related
Sep 4, 2012
Is it possible to declare a reference as an object, this way we could avoid our issues with broken references? I would just try it, but it seems like there is a quite a lot of testing it would require.
View 11 Replies
View Related
Mar 3, 2014
I am cleaning up a database that has been around for some time and I wish to be able to check for references within REPORTS/VBA Module code/ Form Designs etc. anywhere there is likely to be a reference to either an Access TABLE or QUERY.... I know there is the 'Object Dependencies' function within Access that will go some way to telling you what relies on what, but this is simply not comprehensive enough in that it doesn't go down to Form design level where further references can hide...
Countless times I have checked and subsequently deleted tables that I considered redundant... only to find later on there was some SQL Source reference to a table or query buried in the design of a form that I had overlooked....
Any way to definitively check and to ensure that all traces of a specific table or query are accounted for...?
View 2 Replies
View Related
Feb 25, 2014
I have the following VBA code to select certain records from table to recordset, however, sometimes no records existed and movefirst should give error message. Just want to know how to know there got no records in the TRANS2 in case no criteria is matched??
Set TRANS2 = db.OpenRecordset("SELECT * FROM TRANSACTION WHERE tcode = 't12345'", dbOpenDynaset)
TRANS2.MoveFirst
View 3 Replies
View Related
Jul 6, 2013
How could I save a PDF report into a table (OLE object).
I want to click on a button, then that button should save the file into a table which formatted as OLE object ??
View 3 Replies
View Related
Aug 14, 2015
I have Access 2007 database.
I and trying to link to Outlook 2007 using the "External Data/More/Outlook Folder option and keep getting the "Object Class does not Support the Set of Events" error message.
I can link to a DBF and Paradox file without any problems.
I am running Office 2007 Professional and Access 2007 and Windows 10.
I have also done the following:
1. Removed Office 365
2. Decompiled the Access VBA code and corrected any issues
3. Compacted/Repaired the database
I have attached a screen shot of the references that I have selected.
This is a new setup as I recently purchased a new PC and Windows environment. I still have the old PC running Windows XP with the same configuration and it also experiences exactly the same error message.
View 6 Replies
View Related