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")
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
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: 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
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)
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
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"
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!
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?
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
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!
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];
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
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?
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
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.
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
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.
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?
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.
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.
