Modules & VBA :: Referring To Calculated Field In A Recordset
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
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
A main form - named [formMain] On [formMain] I have a tab control - named [tabMain] [tabMain] has 3 pages named [pageOne], [pageTwo] and [pageThree]. On [pageThree] I have created a subform named [formSub]. On [formSub] there is a tab control named [tabSub].
Now what I would like is to make [tabSub] (in)visible if the user switches from [pageOne] or [pageTwo] to [pageThree].
Code: Sub tabMain_Change()
How do I refer to [tabSub] from within this subroutine? I am trying something like:
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 have a simple date stamp that works great in a private sub within a form. (error handling removed for clarity)
Code:
Private Sub btnDateStamp_Click() ' UserInit is global variable Me!Notes.SetFocus Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & UserInit & _ " -" & vbCrLf & Me!Notes
[code]...
I am rewriting it as Module function that is Called from various forms, to save space. The function receives the parameters varFormName and varControlName. I wish to write the results of the function back to a memo field on the form.I am stumped at the get go by the need to refer to the Forms controls with a full reference instead of the Me command.
Code:
Function DateStamp(varFormName As String, varMemoName As String) 'varInitials, varFormName, varMemoName are global variables Forms!varFormName.Controls!varMemoName.SetFocus ' Error here 'Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & varInitials & _
[code]...
how to refer to the forms control's with their full reference, from within the Module's function, the rest will fall into place.
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.
The [ItemsID] is actually on the form as a txt box. but i dont really need it there for any other purpose other than the vba above.
In the form i have a field that is in the form as a txt box that has a relationship to the ItemsID. i have attached a image of the relationship.
The field that is in the form is tblOrdersItems.OrdersItemsID and i also have tblItems.Items. so rather than adding another meaningless txt box to the form(ItemsID) i would like to be able to use the relationships to get the correct ItemID ...
I have a form which contains fields from my main table and also has a subform containing a query based on a filtered list of my main table.
I have a combobox on the form to select a name and pass it to the query to filter on.
The list of names is in a table called tblnames which has 2 fields, name and ID. I have linked the ID field on this table to a field called nameid on my main table which is a numeric field (and that allows be to select a name from a list when I enter data into my main table.
The xox is unbound, control source empty and row source set to tblname.
The combobox is only showing a list of numbers (I assume they are the id field from tblname). Yesterday I had the list of names showing and I checked a backup and the only difference I can see is the row source property refers to the name field in tblname but I don't know how I got it there. When I click on the list box for row source I only get offered a list of my objects.
I have a query[view] where I have six employee numbers, I left join these to an Employee table to retreive the names.
I've created a report that uses this query/view as its source. For some reason my report keeps making all six name fields refer to the same control source Employee.Name and not Employee_1.Name, Employee_2.Name, Employee_3.Name,.... respectively.
In design view everything looks fine but when I switch to report view the switch happens, it displays the same name in all fields. When I switch back to design view all of the fields have changed to the same control source.
why this is happening? My tables are in a SQL server.
I have a part table summary with various quantities 1-1000 and want to create a new table where part number repeats with quantity of 1 corresponding back to the sum. If part 123456=20 then this would repeat 20 x and each record Qty=1
Start with summary --================ Part Qty 111000 2 222000 3
End result all Qty=1 --================ Part Qty 111000 1 111000 1 222000 1 222000 1 222000 1 --================
I stared with a loop and was able to get an append query to work referring to the quantity value (3) for one record from tbl_temp to tbl_main, but not really sure how to advance through many records.
For n = 1 To [Forms]![MainScreen]![Text7] DoCmd.OpenQuery "qry_Update_Qty" 'DoCmd.GoToRecord , , acNewRec Next n
I'm trying to run an if statment, on the records in a field (called "Current_Month"), in an existing table called ""Current_Months_Lag1_Data".
The IF statements work fine - and simply perform a different action for different data in "Current_Month".
I am having problems referring to "Current_Month", where I keep getting 424 (Object required) and 3420 (Object invalid) error messages, on the last line of code.
I have defined & identified the table in which the field is located, yet somehow cannot identify the field within that table. (I've already tried searching the web for similar problems under error messages 424 & 3420).
Function LAG_Forecast_03() Dim dbs As Database Set dbs = CurrentDb Dim Tbl As TableDef Set Tbl = CurrentDb.TableDefs("Current_Months_Lag1_Data") Dim FLD As Field Set FLD = Tbl![Current_Month]
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 believe I did this before awhile ago but for some reason I keep getting an error.
I have a Mainform (frmMain) that has a Subform (frmSub). On frmSub I have 2 comboxes (strCom1 & strCom2) one is set to invisible (strCom2.Visible = No).
So using the "On Open Event" of frmMain I want make strCom2 visible if strCom1 = "Read Only". Ofcourse I will also need to place the vba on the On After Update event. Below is what I have so far but doesnt work.
I get Run-time Error 2427 "You enetered an experssion that has no value"
Code:
Private Sub Form_Open(Cancel As Integer) If Me!frmSub.Form!strCom1.Value = "Read Only" Then Me!frmSub.Form!strCom2.Visible = True Else Me!frmSub.Form!strCom2.Visible = False End If End Sub
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.
the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder
I have a form with a query set up as the record source. If there are records in this query, the form will display. When the user clicks a button, if there is another record in this query, the form displays the next record. When it gets to the EOF, it should close this form, but EOF is not being recognized (I run debug and it says it's false when the button is clicked)? I get run-time error '3201' after I click the button twice.
I know for a fact that there are 2 records in this query. So once I click the button on the 2nd record, the form should close.
Code: Private Sub SelectTblMyMedButton_Click() Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs = db.OpenRecordset("qryMedDataSelect", dbOpenDynaset)
I have learned the basic looping technique for going through a record set from the following link. I need to know if my logic is on the right track. URL...
My question is a followup to a thread that was opened on this forum: URL....I want to do the following:
1) Use record set looping technique to fix a variety of incorrect naming conventions to a standard format 2) Update the table (or create a new table) from the updated record set values. (Is my logic going to update the table selected in the code I used to dimension the record set or will I need to do something else to make the changes available for other tasks after record set is closed ? After the naming conventions are fixed this data will be available for excel automation that I am working on and posting questions on another thread in this forum. HAHA I'm going code BANANAS) 3) rs.fields![fleetlocation] is used so many times, how can I make this a variable (what do I dimension the variable as?) 4) Use an AND statment with an if statment (how to do this with correct syntax)
Code: sub loopandfix() on error goto errorhandler: strSQL = "tblUnionQueryResults" 'table was created from a union query but has inconsistant naming conventions for the fleet location name
I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:
10Jul15: Sales and then adding Criteria like: EndDate = #10/07/2015# It doesnt work.
I have a Suppliers table with 3 fields (code,name,addr). I have a Form where I need to list all records in this table + a checkbox against each record on which the user will tick for only some suppliers he wishes to select. How can I do this without this??
I dont want to create a table just for this. Is there a way I can populate these records into a temporary Recordset & Add a field to it, and then assign this recordset to the Form???
