Modules & VBA :: Edit Recordset Querydef That Has Calculated Field
Jan 19, 2014
Is it possible to add/edit a record from a DAO querydef that has a calculated field in.
Code:
Set qdf = db.QueryDefs("qryOutput_" & strDept)
qdf.Parameters("Enter Date") = dDate
Set rs = qdf.OpenRecordset()
Using the rs.Edit or rs.AddNew worked fine until I put a calculated field in the query.
The calculated field is just a total of some fields in the table.
I thought this was something to do with dbOpenDynaset but it just keeps having the same error
Cannot update. Database or object is read-only.
View Replies
ADVERTISEMENT
Feb 11, 2014
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")
[Code] .....
View 14 Replies
View Related
Nov 22, 2014
I am trying to use a calculated field in a recordset but I am having problem with the script trying to compile.
my record set is
Set rstPrice = db.OpenRecordset("SELECT Period_desc, Price, Round(Price/7,2) AS Daily_rate FROM qryPropertyPriceList WHERE [Our ref] = '" & rstProp![Our Ref] & "' And [Year] = " & rstProp![Next year price base] & " Order By Sequence", dbOpenSnapshot)
The calculated filed I have added in is Round(Price/7,2) AS Daily_rate
If I let it run just with this it runs fine.
and then I try to use this calculated field:
Do Until rstPrice.EOF
Temp = Temp & rstHTML!html35 & rstPrice!Period_desc & rstHTML!html36 & rstPrice![Price] & "-" & rstPrice![Daily_rate] & rstHTML!html37
rstPrice.MoveNext
Loop
rstPrice.Close ' Tidy up
Set rstPrice = Nothing
They it stops running and it all seems to be because of the Daily_rate field
View 11 Replies
View Related
Sep 4, 2013
I can't make edits with ADODB recordset bound to my form.
Access 2010 linking to SQL Server 2008.
Simple form bound to a single table.
Connection string works fine.
Code is as below (cursor etc is set using enums btw).
Private Sub Form_Open(Cancel As Integer)
Dim rst As ADODB.Recordset
If g1OpenRecordset(rst, "tblName", rrOpenKeyset, rrLockOptimistic, False) = False Then
Cancel = True
Exit Sub
[Code] ...
View 3 Replies
View Related
Dec 1, 2014
I have a TRANSFORMED query:
Code:
TRANSFORM nz(count(T_qa.qaQAPK),0) AS SumOfQAs
SELECT month(qaDate) AS QAmonth, Count(T_qa.qaQAPK) AS QAs
FROM Q_ALL_qa
GROUP BY Month(qaDate)
PIVOT month([qaDate])
IN (1,2,3,4,5,6,7,8,9,10,11,12);
This query is record source for a report, then this report show all calculations in a form.
I have 7 of each (query + report) all showing on the same form.
All those queries calculate data for all departments.
In the form, I have placed a combobox.
What I want is to create a vba code which will add clause WHERE to all queries at the same time and then run it.
Code:
WHERE qaDeptFK=Forms!F_CompLvl.cboDeptStats
However, if nothing has been selected in the combo, I want the queries to calculate data as normal, for all departments.
Where do I place the vba statement? Is it under combobox AfterUpdate event?
I am planning to use this: (As I never done it before)
[URL] ....
Modifying SQL On-The-Fly section
View 5 Replies
View Related
Jun 13, 2014
So I had this code working and then I cleaned it up a little and it no longer works. It should export data from a created query using criteria selected by the user on a form and put it into an excel file that exists. I get no errors but it does not export anymore. After pouring over it for a while checking for mistakes with my form control references and variables I have yet to find anything. I did change my form name and edited the code accordingly after I already had it working, and changed a few form settings but changing them back did not fix the issue. I am not very experienced and stumped since I am not getting error messages.
Code:
'First set variables for the SQL string and CreateQueryDef command
Dim strExport As String
Dim qdf As dao.QueryDef
'Then define the SQL to be exported (Static Response Info by ItemID)
[Code] ......
View 6 Replies
View Related
Aug 2, 2006
Hi all,
I get an Invalid or Unqualified Reference error with this code, highlighting !Comments. Comments is a Memo field in the tblSOftware table. The code is being run from a button on a form. Anyone got any ideas what's up??
Cheers,
Matt
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strComments As String
'Setup Objects
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSoftware", dbOpenTable)
'Set Index and Seek for record
rs.Index = "Software Code"
rs.Seek "=", Me.SoftwareCodeNew
'Get Text String
strComments = Me.CommentsNew.Text
'Edit Record in Comments field
rs.Edit
!Comments = strComments
'Update and close recordset
rs.Update
rs.Close
db.Close 'Do i need this line of code since I'm using the current DB?
Set rs = Nothing
Set db = Nothing
View 1 Replies
View Related
May 7, 2015
I have a form with 3 combo boxes that filter another form/report. The first combo (cboByCategory) contains options from 2 different fields within the same table. Before I added this add'l piece of code, all 3 combos worked fine. I am not getting error messages, it just does not filter on the other 2 combo boxes - cboDiv and cboGender.
Code:
Private Sub cmdModifyRecords_Click()
On Error GoTo Err_cmdModifyRecords_Click
Dim stDocName As String
Dim strFilter As String
Dim stLinkCriteria As String
stDocName = "Modify_OpenItems"
[Code] .....
View 5 Replies
View Related
Aug 27, 2013
I have a query that searches for records that are between two dates using the WHERE clause. The two dates are referenced to two respective text boxes on a form. On the same form I have a button that will launch the query in VBA using querydefs. I get the error 3061 saying I need to input the parameters. Therefore I am a bit unsure how to set the parameters in VBA. So far I have done this:
Code:
Dim Db As DAO.Database
Set Db = CurrentDb
Dim QDef As DAO.QueryDef
Dim rst As DAO.Recordset
Set QDef = Db.QueryDefs("Rqt_F_BrokerageMandate_MF3_TEST")
[Code] ....
Where Date_VL is the field to be filtered. I know this is wrong but all examples I have seen have equated the parameter to a fixed value i.e 30/12/2012 for example, but I want this to be at the users discression. The only way I know of to get around this at the moment would be to write a temp query in VBA with PARAMETERS in the SQL code instead using the method above/
After this I'm going to assign the recordset to a matrix but that's a different story!
View 10 Replies
View Related
Oct 30, 2013
I have a form and a VBA event in which I would like to update a field in the current record. However, the field name is stored as a variable so I can't just use Me!FieldName = __
I've tried simply the following, but get a "No current record" error. why I'm getting the error, but how do I select the record currently visible in the form to edit?
Code:
foo = "bar"
Me.Recordset.Edit.Fields(foo) = True
View 7 Replies
View Related
Mar 4, 2015
I am attempting to adjust the font color of a date field on a report based on the value of two other fields. I have the below code set in the "On Format" property of my detail section - however it does not work when I open the report to view.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Frequency = "Annually" Then
If Me.ClassDate < (DateAdd("yyyy", -1, Date)) Then
Me.ClassDate.ForeColor = vbRed
Me.ClassDate.FontBold
End If
End If
End Sub
View 1 Replies
View Related
Mar 30, 2007
Table A has a field for Full Name (Bob Jones)
Table B has two separate fields for first and last (Bob and Jones). Table B's contents are actually based on an XML import so I can not just change the table structure.
I want to join the content from these two tables together based on full name.
So first I created a query on table B with a new field created with the expression =[First] & " " & [Last].
I then created a query with table A and query B where I joined the two full-name columns together.
This works perfectly. However, I can't edit the values in this query and I believe from the help files that it's because there's a calculated column in one of the tables.
Is there a way to get around this?
I thought maybe inside my ImportXML VB function once I imported the XML data I could populate an empty column with First + Last but I've read about 50 articles and they're all over my head and I can't figure out how to just concatenate the two columns together into one using VB. It seems if I did this I would avoid having to use a calculated column in a query and this would fix my problems.
Any help would be greatly appreciated. Thanks so much!
View 8 Replies
View Related
Jan 7, 2008
Hi!
I have done a query which has calculated fields, but I can't edit the stored fields directly from the query, instead I have to go to the table so as to edit it. Is this the way it works or is something wrong?
SELECT [INSTRUCCIONES DE EMBARQUE].N_OP, [INSTRUCCIONES DE EMBARQUE].[N° FAC EXP], [INSTRUCCIONES DE EMBARQUE].[FECHA CUMP], [INSTRUCCIONES DE EMBARQUE].[DERECHO PGC], [INSTRUCCIONES DE EMBARQUE].[VTO DER EXP], [INSTRUCCIONES DE EMBARQUE].[IMPORTE REINTEGRO], [DETALLE CONTENEDOR].CANTIDAD, [DETALLE CONTENEDOR].[VALOR FOB], [INSTRUCCIONES DE EMBARQUE].[TIPO DE CAMBIO], [TIPO DE CAMBIO]*[VALOR FOB] AS [PRECIO VTA (ARS)], [PRECIO VTA (ARS)]*[CANTIDAD] AS [TOTAL(ARS)], [TOTAL(ARS)]/[TIPO DE CAMBIO] AS [TOTAL(USD)]
FROM [INSTRUCCIONES DE EMBARQUE], [DETALLE CONTENEDOR];
View 2 Replies
View Related
Nov 11, 2014
Is there a possibility to use a shorter code for extracting one field from one record, than opening a recordset, getting the field, closing it and setting the recset to "Nothing" again? I'm doing this multiple times in my code and it seems a bit too much for what it's supposed to do.
Code:
sql = "SELECT tblAdFlgDaten.* FROM tblAdFlgDaten WHERE (((tblAdFlgDaten.AuftrID)=" & ABAuftrID & _
") And ((tblAdFlgDaten.Schritt)=2))"
Set RSnap = dbase.OpenRecordset(sql, dbOpenSnapshot)
If IsNull(RSnap.Fields(2)) Then
...
...
End If
RSnap.Close
Set RSnap = Nothing
View 4 Replies
View Related
Jan 25, 2014
Is it possible to retrieve the format of a field when looping through recordset.
e.g you can get the field type by using
Quote:
rs.Fields(y).Type
But the type for decimal or percentage is the same. It is the format that is different
I need to be able to see what is formatted as a percentage and what is formatted as fixed.
View 7 Replies
View Related
Mar 12, 2014
I am using Access 2013 under Windows 7. In my database I have a table, tblStock, with field names Module, Component_1, Component_2, etc. up to Component_50. I also have fields Qty_1, Qty_2, etc. up to Qty_50. These field names are not easily changed as they are constantly updated from another database. I want to create a table, tblTempBOM, using VBA by selecting a particular value of Module selected from a combo box on a form, from tblStock and creating a record with the Fields "Module", "Component" and "Qty" for each Component and Qty from 1 to 50. I am trying to use a From... Next... loop to cycle through the Component and Qty fields and store the data in the new table.
Dim strModule As String
Dim strSQL As String
Dim strQty As String
Dim strEye As String
Dim strTest As String
[code]....
This runs to the point where I try to set the value of rcd![StockCode], but the item rcdStock![strTest] has no value.Can I reference the value of the field in the rcdStock Recordset in some other way that would work?
View 5 Replies
View Related
Nov 25, 2014
I am storing values of pictures and the location of them in a table, this works fine!... using OpenRecordset. The problem is that when the function is called to store the information, it just keeps adding the same values of each file in the folder over and over again in a word "Duplicating" the information.
I have tried various methods using the OpenRecordset, but cannot seem to find the correct manor of applying the code.
Below is the function I have for storing the data...
Code:
Public Sub GetFilesNamesFromFolder(strFolderPath As String)
On Error GoTo ErrorHandler
Dim objFSO As Scripting.FileSystemObject
Dim objFolder As Scripting.folder
Dim objFile As Scripting.File
[Code] .....
View 12 Replies
View Related
Mar 19, 2014
I am trying to update a field with the result of what is in the field plus the contents of a unbound form field.
So if crm_order_det.Qty contained the number 5 and the form field (labelled qty) contained 7 I would like the field crm_order_det.Qty to end up with 12.
The code I am using
sql = " Update crm_order_det SET [Qty] = (" & _
frm!Qty + crm_order_det.Qty & ") where crm_order_det.part = '" & frm![SearchResults] & _
"' and order_id = " & frm![order_id] & ";"
DoCmd.RunSQL sql
However I get the following error
Run-time error '424': Object required.
View 3 Replies
View Related
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
Nov 12, 2014
I am looking for a way to add a calculated field to the end of an existing query using VBA. Is there an easy way to do this?
The data I receive from an external supplier shows monthly data split by column with a new column added in each month. I then need to reflect this by adding a new column to the end of the query. It is currently a manual tweak, but I want to automate this with code.
View 6 Replies
View Related
Jul 7, 2013
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?
View 1 Replies
View Related
Apr 12, 2006
My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.
I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.
Does anyone know how to correct this? Thanks in advance.
:confused:
View 1 Replies
View Related
May 10, 2005
why wont my DB reconise this script
View 3 Replies
View Related
Sep 30, 2005
Does anyone know what a QueryDef Type of 3 is?
If I run the following code, as well as returning all queries in the db, it also returns a few that are of Type=3.
For Each qd In db.QueryDefs
Debug.Print qd.Name & " - " & qd.Type
Next
For the Type 3 ones, the qd.Name returns something like
~sq_rREPORTNAME
where REPORTNAME is the name of a report in the database .
However, it's only a couple of reports that appear this way.
Where or what is a Type 3 - even if I view Hidden & System objects I can't see them!
View 1 Replies
View Related
Sep 26, 2005
Hello everybody,
I'm looking for solution to this. I've already searched in this forum a lot of healines for this problem but none of them really helps me.
I know that We're not suposed to change static Querys and bla bla bla because they static and bla bla bla bla BUT....
the thing is...
I have this form with a lot of filters for a report. I Want to change the join fields and parameters according to what the user chose in the form and then run the report.
The Report Record source property needs to be populated with a query object.
to do that I need to some how load the query and change its sql.
Dim Q as QueryDef
set q = CurrentDB.QueryDef("MYQUERY")
I don't know what property I can use, MS HELP doesn't help actually
when I type "Q." after the dot the list of properties doesn't show.
Thank you !
View 1 Replies
View Related
Aug 3, 2013
Is there a way for a SQL QueryDef object in Access to look up the name of itself in the SQL and return that as a value in the result set? Ex:
Code:
SELECT [foo],
[bar],
[something]
@NameOfQueryDef
FROM [table]
View 3 Replies
View Related
