Modules & VBA :: Result From A Query To Specify String To Use
Jun 7, 2015
I have a query that returns the string to use but I am not sure how I can actually use the returned value to do what I want.
e.g.
my table:
Partner ID Backup Field
5023949 UPC
501354 GTIN
my query will return 1 [backup field] value from the table above, and I then want to use that value to get another value that was previously declared in my code.
eg
GTIN = "ABC123"
UPC = "123ABC"
if my query returns the value "GTIN" then need to use the value of GTIN (ABC123) in my next part of the code.
msgbox GTIN would result in a message box ABC123
But I am not sure how to get the result value. I can say msgbox [backup field] but that returns "GTIN" not "ABC123"
View Replies
ADVERTISEMENT
Sep 19, 2005
I have a dinamic SQL string built by a function, and i want to assign it to a query to show it in a subform.
Here are the names.
Form FrmServiciosAfectados
Subform subFrmcnsServiciosAfectados
Query cnsServiciosAfectados
Built SQL String strSQLPuertosCanales
Please help me with code.
View 2 Replies
View Related
Sep 30, 2013
For some reason i've had to make an update on some old VBA projects of mine. In this update i switch all references of DAO to ADO. I know there are some limits to what i can accomplish with ADO (where i would need to use DAO). Anyways in some of these old projects i use an SQL statement to provide me with a limited number of records which i then modify by iterating through the recordset.
I can't get this to work with ADO. It's as if it always expects me to provide a table in the argument.
In short my question is. Is it possible to change the values returned from an SQL statement using ADO
Code:
'This is the DAO version which is exactly what i'm looking to using ADO
strSqlCommandText = "SELECT tblMain.TaskID, tblMain.Heading FROM tblMain WHERE (((tblMain.TaskID)=" & ProcessForm.txtIDValue.Text & "))"
Set objRecordset = pubObjDatabase.OpenRecordset(strSqlCommandText, dbOpenDynaset)
objRecordset.Edit
objRcsToDbTable.Fields("Heading") = ProcessForm.txtBoxHeading.Text
objRecordset.Update
This is what i've done and it doesn't work.
Code:
strSqlCommandText = "SELECT tblMain.TaskID, tblMain.Heading FROM tblMain WHERE (((tblMain.TaskID)=" & ProcessForm.txtIDValue.Text & "))"
Set objRecordset = New ADODB.Recordset
objRecordset.Open strSqlCommandText, pubObjDatabase, adOpenDynamic, adLockOptimistic
objRecordset.Edit
objRcsToDbTable.Fields("Heading") = ProcessForm.txtBoxHeading.Text
objRecordset.Update
I can think of some work around like, creating a temp table and insert/update all the records from there or creating an UPDATE SQL statement.
View 6 Replies
View Related
Jan 2, 2015
In the following code, I debug a problem with the CurrentDb.OpenRecordset
Code:
' ---
' ENVOYER UN MAIL DEPUIS ACCESS
' ---
' Entree : strEmail <- Adresse e-mail du destinataire
' strObj <- Objet du courrier
' strMsg <- Corps du message
[Code] ....
View 7 Replies
View Related
Jun 25, 2013
I have query that creates table with 2 records each with 2 columns (2x2) and they do not have indexed ID,and query is related to combo box in other form so results are not always same but it is always (2x2) and value types are always same,
So how to show those results in text box in form,lets say 4 text boxes ,every value in one text box, i assume that i need to use DLookup() but i was able only to show first record,did not know what criteria put to go to second record.
When i select that query and create report i get what i want but i cant copy those text boxes to form that i want.
View 10 Replies
View Related
Aug 22, 2013
I already success to run this dynamic query where the parameters taken from the main form.Now the problem is the query result doesn't show in the subform.But the status bar below tell me that it have 2 records in the subform, but there is no data in the subform, it just Blank.I already apllied the Requery or Refresh to the subform (in the Command Button), but it have no result too.This is the code:
Forms!MsDataWarga.QueryDataWarga.Form.RecordSource = "MyQuery"
Forms!MsDataWarga.QueryDataWarga.Requery
Forms!MsDataWarga.QueryDataWarga.Refresh
View 4 Replies
View Related
Jun 7, 2013
MS-Access VBA code to separate numbers and string from an alphanumeric string.
Example:
Source: 598790abcdef2T
Output Required: 598790
Source: 5789065432abcdefghijklT
Output Required: 5789065432
View 13 Replies
View Related
Aug 8, 2013
I am trying to count rows in the result of one of the queries and I am having a bit of trouble getting it going.
The current code - this is executed as on-click event when clicked on List Box feed with query below.
What I want to add is simple if that when number of rows produced by the querry is 1 it will enable a picture item in the different part of the form, however it does not want to count the rows for me.
Code:
Private Sub search_items_Click()
Me.OBSFullFilledOrdersHolder.Enabled = True
mysql = "SELECT orders.[order id] , STUDENTS.[first name]& ' ' & students.[surname] AS Name, students.[contact name] AS ContactName , ORDERS.[Online Bookshelf order] AS OBS , STUDENTS.[Delivery Address 1], STUDENTS.[Delivery Address 2], STUDENTS.[Delivery Address 3], STUDENTS.[Delivery Address 4]"
[Code] ....
The query itself works when tested but when used in code with DCount function will return error: Run-Time 2471 the expression you entered as query parameter prouced this error
'[Forms]![FULLFILL ORDERS]![search items].[Column(0)]'.
View 5 Replies
View Related
Mar 5, 2014
I am working with Access 2010, on vista. What I have is a query made up of two tables, one product the other inventory. (see below) query.jpg
In the product table i have a field called "minimum reorder level". In the inventory table i have two fields one called "number in stock" and "number on order". What i want to happen is "number on order" to be filtered by the result, if the "number in stock", is less than "minimum reorder level", if it is, have the result placed in the "number on order" field. EG. if the "number in stock" = 2 and the "minimum reorder level" = 5 then 3 would be placed in the field "number on order" and only the second record from the query would be visible (see below) Query result.jpg The result of this would mean that the field "number on order" would be populated with the result and the and query would also use this to filter the record.
View 1 Replies
View Related
Aug 18, 2013
I want to add a number to my results within a query depending on the month and how many results. For example I have 10 results in my query 3 from January, 5 from March and the rest from April. The 3 from January would be 1,2,3. The five in March would be 1,2,3,4,5 and so on. Is it possible to do?
I'm using access 2003.
View 4 Replies
View Related
Jan 30, 2015
My access is 2013.
Suppose there's a simple query which has two fields, "year" and "graduates", where "year" can be grouped by "2012", "2013", etc and "graduates" are individual names.
How can I export the list of graduates to multiple excel files, with the filename based on "year"?
I have tried to set the output file in macro as "c:desktop" & query.year & ".xls"
But it's not working and the output filename is exactly "&query.year&.xls "and the file contains all year and all names.
View 14 Replies
View Related
Jul 22, 2015
I am trying to create a form button that will automatically email each row of a query result to myself. At first the VBA code worked fine with a standard query. However when I use it with a query that contains a reference to a combobox form such as "<=[Forms]![Reminder]![Monthsleft].[Value] And >=0" I get the 3061 run-time error and "Too few parameters. Expected 1." I have included the VBA code below.
Private Sub Command9_Click()
Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
[code]....
If you intend to renew the lease, terms and conditions will need to be submitted for ECC for approval (regardless of changes or not in lease rates). If the terms have yet to be confirmed, it is important to begin the negotiation process as soon as possible with a target to provide the ECC submission at least two months prior to the commencement date of the renewed lease. To ensure sufficient time for ECC approval before the contract expiry date, please prepare the ECC paper and obtain necessary endorsements. Submission details can be found here. The ECC submission template and PSD Questionnaire could be found from this link
DoCmd.SendObject acSendNoObject, , , _
sToName, , , sSubject, sMessageBody, False, False
.MoveNext
Loop
End With
Set MyDb = Nothing
Set rsEmail = Nothing
End Sub
View 2 Replies
View Related
Jul 22, 2013
I am currently trying to create an update query (building a SQL String in VBA for a command button click event) to update a variable's value into a table.
Basically, there are 2 tables, displayed in 2 sub froms within the same main form. (OldTable and NewTable for arguments sake)
The basic method I want to implement is that a user highlights a record in the subform of "NewTable". (This value is stored as a variable "NewJPNUM" This value is then to be inserted into the highlighted row (or rows) of table OldTable on command button click.
So the basic idea is a user highlights a row in one table and this value is stored as a variable "NewJPNUM" . The user then highlights a row or rows in "OldTable" and the value from variable "NewJPNUM" is then written to field "NewJPNUM" in "NewTable" on command button click.
I am not experienced with Access but have decent experience in Excel / VBA so not really sure of best practice methods etc.
View 3 Replies
View Related
May 2, 2014
Why is strCreditCheck returning the actual query string and not the result? Of course, this is not working.
Code:
Private Sub b_PrintWorkOrder_Click()
Me.Requery
Dim strCreditCheck As String
Dim strCompany_id As Integer
Dim stlinkCriteria As String
[code]....
View 6 Replies
View Related
Sep 10, 2013
How do I use a update sql query to update a field based on a string from an input box. Heres what i have been trying:
Code:
Dim NETWORKBOX As String
NETWORKBOX = InputBox("NETWORK TO IMPORT" & Chr(10) & "EXAMPLE: PRIMARY", "NETOWRK TYPE")
sql = "UPDATE " & TABLENAME & " SET NETWORK = " & NETWORKBOX & " ;"
DoCmd.RunSQL sql
If i run the code and input 'PRIMARY' in the NETWORKBOX the sql query will return an 'enter parameter value dialog box' with the word 'PRIMARY' above the input field.
View 1 Replies
View Related
Sep 27, 2013
I have a simple select query , and I want to add a field that will take the value from another field and increment it based on a condtion.
i know i need a case statement but not sure how to go about this ..
column 4 is my desired result
i want to increment the text string from column 3 based on column 3 and restart the increment based on column 1
values of A would increment in the following manner 01,11,21,31,41 etc
values of O would increment 02,12,22,32,42,52,62
i'd also like the option that if a new value were introduced to increment it
values of x would increment 80,81,82,83,84
Example data .. apples and oranges ..
column1 column2(name) column3(type) column4
mary braeburn A 01A
mary blood O 02A
tom cortland A 01A
tom fuji A 11A
tom navel O 02O
tom mcintosh A 21A
[Code]...
View 2 Replies
View Related
Oct 12, 2013
I there is no result in query, I need the default result zero in my form field. I only use query wizard to create queries.
View 5 Replies
View Related
Feb 26, 2014
I am using a public function to feed a variable string to a query. So far I have got:
Code:
Public Function ClientStreetModule(firstLVar As Variant, streetVar As Variant, newFL As Variant) As String
Dim cslStr1 As String, newStreet As String
newStreet = Right(streetVar, Len(streetVar) - Len(newFL))
[code]....
However, I only need to use newStreet as the true part of iif, in which instance all are longer. At least I think this is the problem. I realise I might need to use NZ but am not sure how. Why it is evaluating and giving errors for all records and not just when the iif criteria is true as I want it to?
View 7 Replies
View Related
Jun 18, 2013
I'm using Access 2007.
So far I have a Multi Select enabled list on which the user selects the serial numbers they want. They then click the "Report" button which will trigger a query based on the selected serial numbers to create a report on those serial numbers.
I have the code for the multi-select list working already. It creates a string of comma separated values that are the serial numbers which are selected in the list. Somehow I need to pass this string to my query so it can use it as a filter.
Here is some of my code:
Code:
Option Compare Database
Option Explicit
Private Sub Form_Current()
Dim oItem As Variant
Dim bFound As Boolean
[Code] ....
Here's my current query in SQL:
Then finally how to I get the query to execute and create a report based on all of this?
View 11 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
Jan 15, 2015
When I run the following sql (in a query) I enter information once for [enter cty id]. and it all works fine.
What I would like to do now, is write the vba code so that the sql runs multiple times looping through a list of items.
For instance instead of the popup window asking "enter cty id", I'd like to automatically have the code loop through a list. For example a list of 1 through 10.
Then, in the loop vba code:
after item 1 is run, do an export of the results.
after item 2, do an export.
etc.
Code:
select a.south_index_id, c.cnty_cd, a.southwest_name_source_cd, sp.south_ssn, sp.estimated_birth_dt,
[a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME],
a.BIRTH_DT, a.GENDER_CD, sp.ms_pmi, cnty_person_id
FROM SW_alias_name AS a, SW_south_person AS sp, SW_county_pid AS c
WHERE EXISTS (select * from SW_alias_name a2 where ucase(a2.last_name) = ucase(a.last_name)
[Code] .....
View 1 Replies
View Related
Oct 1, 2014
I have a form with a search box, Its UNBOUND called ID . Everytime I search for an ID number it shows no results, However I know there are entries in the table. This is the code I am using :
Code:
Private Sub ID_AfterUpdate()
If (ID & vbNullString) = vbNullString Then Exit Sub
Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[Id] LIKE ' " & ID & " ' "
[Code] ....
I am assuming I need to add something to search for auto numbers but i don't know what!
View 2 Replies
View Related
May 1, 2014
I'm trying to get a value from a spreadsheet to import into my MS Access database. Currently I am trimming the spaces/carriage returns from it but need to strip some more data from the value.
Here is my code.
trimmed_department = Trim(Replace(new_department, vbCrLf, ""))
Example value being "123 Point 5 Finance and Accounting"
I want to use Mid (I think?) to remove the "123 Point 5" (it is always the same with no exceptions) but don't know how to use it as I don't know how to use multiple parameters within a string.
View 8 Replies
View Related
Sep 2, 2014
I want VBA code that will perform the following steps. The Table1 has following fields:
ScanDate,NewBatchNo,BatchNo,PolicyNo
1. First of all look into Table1 the scandate<=29082014 and then check if there is No NewBatchNo of the corresponding record then capture its corresponding BatchNo of each record whose scandate<=29082014
2.Then check for the batchno that we have captured in another table Table2 and if its present then return corresponding PolicyNo. Now keep on searching that policy no in Table2 as it could be present 50 times in the table and then return corresponding batch numbers in excel sheet of that Policy No as below:
BatchNo - Policy No - Batch No1 - Batch No2 - Batch No3 ---------------
View 1 Replies
View Related
Feb 27, 2014
I want to add a string as year to a date.
Somehow it doesn't work out. It should extract all records with Valid_from = 01.01.2013 and valid_to = 31.12.2013. The Year assignment works.
Code:
Public Sub BEN()
Dim strSQL As String
Dim t As Date, s As Date
DoCmd.SetWarnings False
Year = Right(pricedate, 4)
t = 1 / 1 / " & Year & "
s = 12 / 31 / " & Year & "
strSQL = "SELECT TRANSFER_PRICES.* INTO [TEMP] " & _
" FROM TRANSFER_PRICES " & _
" Where [Valid_from] = " & Format(t, "#mm/dd/yyyy#") & _
" AND [Valid_to] = " & Format(s, "#mm/dd/yyyy#")
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
View 3 Replies
View Related
May 19, 2015
I have set calendar control 12.0 up and everything works how I want it to (click date and peoples names in a table to the left to show scheduled meetings on that day). What I want is to add a string on top of the calendar. For example, this monday I would like for it to say "Memorial Day" on the physical calendar itself.
View 3 Replies
View Related
