I'm populating my listbox from access but it doesn't like my SQL statement, where am I going wrong
sSQL = "SELECT * from Process where ParentName = '[cboCategory]'"
Process is a table and cboCategory is the excel form control.
The error is "No given value for one or more required parameters.
1. I have created a parameter query in access 2010. This parameter is on only one field.
2. I have created a multi-select list box in a form so that users can select one of more items.
I want to pass the selected items as parameters to the save access query.
After researching this issue I have not found a satisfactory solution to this issue.
I currently have 1 Access query that is the basis for my VBA code_ Sql statement. both Query statements work when debugged. However, I am getting this error on execution of the sql statement in my vba.
Dies here: '<<<<<<<<<<<<<<<<
Public Sub ConnectCMIS(spar As String)
Dim sConn As String
Dim oConn As ADODB.Connection
Dim lCnt As Long
Dim sSql, strSQL As String
Dim rstOra As ADODB.Recordset
Dim rsAccess As New ADODB.Recordset
Dim fld As ADODB.Field
On Error GoTo ErrorHandler
DoCmd.SetWarnings False
sConn = _
"Driver={Microsoft ODBC for Oracle};Server=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS =(PROTOCOL=TCP)" _
& _.;......
strSQL = "SELECT " & _
"MEASNO, FTEMNOMENCLATURE, NOMENCLATUREMODEL, " & _
"EquipID As EQUIPMENT_ID, MULTIPLE_ID, JOB_GROUP, " & _
"PROJECT, PRIORITY, IIf(Len(Trim(COMPLETE_BY_DATE)) > 0, Mid(COMPLETE_BY_DATE, 3, 2) & ""/"" & Mid(COMPLETE_BY_DATE, 5, 2) & ""/"" & Mid(COMPLETE_BY_DATE, 1, 2), Null) AS COMPLETEBYDATE, " & _
"RequestorId As REQUESTOR_ID, " & _
"CALIBRATION, REPAIR, MODIFICATION, ACCEPTANCE, EVALUATION, " & _
"MAINTENANCE, SUPPORT, CMIS_LAB, SERVICE_LAB, WORK_CODE, " & _
"CHARGE_NUMBER, DISPOSITION, ReqComments as REQUESTORCOMMENTS, INPUT_RANGE_MIN, " & _
"INPUT_RANGE_MAX, INPUT_UNITS, OUTPUT_RANGE_MIN, OUTPUT_RANGE_MAX, " & _
"OUTPUT_UNITS, GAIN, CUTOFF_FREQ, INPUT_FREQ, REF_FREQ, REF_VOLTAGE, " & _
"EXCIT_VOLTAGE, EXCIT_ENABLED, FTIR_ACCURACY, OFFSET, OFFSET_ENABLED, " & _
"REQ_EMO1, REQ_EMO2, REQ_EMO3, REQ_EMO4, REQ_EMO5, REQ_EMO6, " & _
"SPARECODE, CALIBRATION_ID " & _
"FROM QS_SRUpdatetoCMISdrt " & _
"WHERE job_group = " & Chr(39) & spar & Chr(39) & ""
Set rsAccess.ActiveConnection = CurrentProject.Connection
rsAccess.CursorType = adOpenStatic
Debug.Print strSQL
rsAccess.Open strSQL'<<<<<<<<<<<<<<<<
If rsAccess.EOF = False Then
Set oConn = New ADODB.Connection
oConn.Open sConn
Set rstOra = New ADODB.Recordset
rstOra.ActiveConnection = oConn
rstOra.CursorType = adOpenKeyset
rstOra.LockType = adLockOptimistic
rstOra.CursorLocation = adUseServer 'default
rstOra.Open "CMIS.UDV_RFS_SR"
Do While rsAccess.EOF = False
rstOra.AddNew 'Then where you add the Oracle record instead of individual assignments you have
On Error Resume Next
For Each fld In rsAccess.Fields
rstOra(fld.Name).Value = fld.Value
Next
rstOra.Update
rsAccess.MoveNext
Loop
End If
strSQL = "UPDATE CMIS.UDV_RFS_SR SET PROCESSED_IND = 'S' WHERE job_group = '" & spar & "'"
oConn.Execute strSQL, lCnt
DoCmd.RunSQL _
("UPDATE TA_SR SET PROCESSED_IND = 'S' WHERE Job_Group='" & spar & "'")
rstOra.Close
Set rstOra = Nothing
oConn.Close
Set oConn = Nothing
Call MsgBox("Submittal to CMIS has been processed.", vbInformation, "Process Submittal Complete")
SubExit:
On Error Resume Next
If Not oConn Is Nothing Then
Set oConn = Nothing
End If
rsAccess.Close
Set rsAccess = Nothing
Exit Sub
ErrorHandler:
MsgBox "Error Number = " & Err.Number & "-> " & Err.Description, vbExclamation, "CMISStatus"
Resume SubExit
End Sub
1st query:"QS_SRUpdatetoCMISdrt"
SELECT QS_TT_GeneralInfo.BEMS AS RequestorId, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, tblEquipListingPerJobGroup.MeasNo, IIf([Primary]=True,[Equipment_ID],Null) AS EquipID, IIf([Additional]=True,[Equipment_ID],Null) AS Multiple_ID, TA_SR.Job_Group, TA_SR.Project, TA_SR.Priority, TA_SR.Complete_By_Date, TA_SR.Calibration, TA_SR.Repair, TA_SR.Modification, TA_SR.Acceptance, TA_SR.Evaluation, TA_SR.Maintenance, TA_SR.Support, TA_SR.Cmis_Lab, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.Input_Range_Min, TA_SR.Input_Range_Max, TA_SR.Input_Units, TA_SR.Output_Range_Min, TA_SR.Output_Range_Max, TA_SR.Output_Units, TA_SR.Gain, TA_SR.Cutoff_Freq, TA_SR.Input_Freq, TA_SR.Ref_Freq, TA_SR.Ref_Voltage, TA_SR.Excit_Voltage, TA_SR.Excit_Enabled, TA_SR.FTIR_Accuracy, TA_SR.Offset, TA_SR.Offset_Enabled, TA_SR.REQ_EMO1, TA_SR.REQ_EMO2, TA_SR.REQ_EMO3, TA_SR.REQ_EMO4, TA_SR.REQ_EMO5, TA_SR.REQ_EMO6, TA_SR.SpareCode, TA_SR.CALIBRATION_ID, First(TA_SR.RequestorComments) AS ReqComments, "S" AS PROCESSED_IND, Now() AS LAST_UPDATE_DATE
FROM (TA_SR LEFT JOIN tblEquipListingPerJobGroup ON TA_SR.Job_Group = tblEquipListingPerJobGroup.Job_Group) LEFT JOIN QS_TT_GeneralInfo ON TA_SR.Requestor_ID = QS_TT_GeneralInfo.RequestorId
WHERE (((TA_SR.Job_Group)=[Forms]![FE_SRForm]![JobGroup]) AND ((TA_SR.SubmittedSR)=0))
GROUP BY QS_TT_GeneralInfo.BEMS, TA_SR.FTEMNomenclature, TA_SR.NomenclatureModel, tblEquipListingPerJobGroup.MeasNo, IIf([Primary]=True,[Equipment_ID],Null), IIf([Additional]=True,[Equipment_ID],Null), TA_SR.Job_Group, TA_SR.Project, TA_SR.Priority, TA_SR.Complete_By_Date, TA_SR.Calibration, TA_SR.Repair, TA_SR.Modification, TA_SR.Acceptance, TA_SR.Evaluation, TA_SR.Maintenance, TA_SR.Support, TA_SR.Cmis_Lab, TA_SR.Service_Lab, TA_SR.Work_Code, TA_SR.Charge_Number, TA_SR.Disposition, TA_SR.Input_Range_Min, TA_SR.Input_Range_Max, TA_SR.Input_Units, TA_SR.Output_Range_Min, TA_SR.Output_Range_Max, TA_SR.Output_Units, TA_SR.Gain, TA_SR.Cutoff_Freq, TA_SR.Input_Freq, TA_SR.Ref_Freq, TA_SR.Ref_Voltage, TA_SR.Excit_Voltage, TA_SR.Excit_Enabled, TA_SR.FTIR_Accuracy, TA_SR.Offset, TA_SR.Offset_Enabled, TA_SR.REQ_EMO1, TA_SR.REQ_EMO2, TA_SR.REQ_EMO3, TA_SR.REQ_EMO4, TA_SR.REQ_EMO5, TA_SR.REQ_EMO6, TA_SR.SpareCode, TA_SR.CALIBRATION_ID, "S", Now();
I have the error 80040e10-No value given for one or more required parameter inside a SQL string and I know that the problem is some/one of my variables passed, however I cannot found the mistake. See down here my code. I got experience in VBA but no much in ACCESS so the query have some problems.
I got the following parameters used inside of the string (I can see before open the recordset that all of them have valid values);
TotalGj is a number result of a dsumTemp1 is a textMketer is a text
My code is down here
Sub Enterdata()
Dim Mketer As String, MonthBR As String
Dim HECAmount As Currency
Dim RsCustomers As ADODB.Recordset, RsFfeesMonth As ADODB.Recordset
Dim Cnx As ADODB.Connection
[Code] .....
I have a form with one listbox, two combo boxes and two text boxes that provide parameters for the query that feeds the listbox. The selection of parameters trough comboboxes works perfectly but the query does not pick up parameters(datefrom/dateto) form text boxes. When I remove references to combos, date parameters work just fine, but the dates are not selected when there is reference to combos. The code behind combos is as follows:
Private Sub CboStaff_AfterUpdate()
Dim vSearchString As String
vSearchString = Me.CboStaff.Text
Me.txtSearch2.Value = vSearchString
Me.MyList.Requery
What am I doing wrong?
I have a form with one listbox, two combo boxes and two text boxes that provide parameters for the query that feeds the listbox. The selection of parameters trough comboboxes works perfectly but the query does not pick up parameters(datefrom/dateto) form text boxes. When I remove references to combos, date parameters work just fine, but the dates are not selected when there is reference to combos. The code behind combos is as follows:
Private Sub CboStaff_AfterUpdate()
Dim vSearchString As String
vSearchString = Me.CboStaff.Text
Me.txtSearch2.Value = vSearchString
Me.MyList.Requery
What am I doing wrong?
I've got a code that allows me to select one or many names from a listbox on a form and return data relevant to the name(s) selected from a query. The following code is triggered by a button on the form...
Private Sub Toggle4_Click()
'Set it all up for CSM selection
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
[code]....
(Toggle 10 goes to the code for the second listbox which has the same code with different tables refered to giving two selections in the query.)
What I want to do is replace the "warning if nothing found" with a code to show data against all the names in the list box if nothing is selected in the listbox.
I have a form that the user can add Work Order numbers to a text box and pass them to a listbox to collect 1 or more values. Each of which need a separate report with the labour hours for each Work Order.
I am having issues figuring out how to get it to pass them to a query or filter the reports.
I have tried many different examples and nothing seems to work.
I have a list box that displays records. When i double click a record it opens the form needed.However I want it to open the form but default to a specific tab. But when it goes to the tab, the subform within it needs to match the ContractID of the record they double clicked on in the search form?I currently have this which opens the Client Form based on the ClientID of the listbox query search results and the client id of the main record.
Code:
Private Sub SearchResults_DblClick(Cancel As Integer)
If CurrentProject.AllForms("frm_Clients").IsLoaded Then
DoCmd.Close acForm, "frm_Clients"
End If
DoCmd.OpenForm "frm_Clients", acNormal, , "[ClientID] = " & Me.SearchResults.Column(0)
So in theory If doubleClick then open Client Form on Tab3 where subform Contract field ContractID is matches the ContractID in on the search results?
I have made a form based on related tables. it requires me to fill out every field, which I don't want. I didn't make them required. Why does it do that?
View 3 Replies View RelatedI use the following code to get the first and second instances of a "/" character. How to get the position of the third instance.
iUPC = "123-7754LF-(A/S red Top)-T19/97876564"
'get number of instances
xTimes = 0
xTimes = Len(iUPC) - Len(Replace(iUPC, "/", ""))
'get position of characters
xInstance1 = InStr(1, iUPC, "/")
xInstance2 = InStr(InStr(1, iUPC, "/") + 1, iUPC, "/")
I have a form with three text boxes and i am trying to figure out how to ensure atleast one of these boxes is required and would error if none of these are completed.
View 1 Replies View RelatedI have a subForm set to continuous forms. I need a field called txtStatusDetails required (cannot be null) if the field txtDutyStatus = "Not Available"
Here's what I have so far:
Private Sub Form_Current()
' This procedure runs after the record changes.
On Error GoTo Err_Handler
ResetAvailability
Exit_Proc:
On Error Resume Next
Exit Sub
[Code] ....
Basically I have a form where a user has selected a couple values from a dropdown element, and entered a few other text values into the form. I am then trying to grab the values and append them to a table.
Code:
Private Sub btnSubmitInputVendorPerformanceForm_Click() 'Button Is part of frmInputVendorPerformance
Dim ValueCheck As Integer 'Increments to make sure no errors in entry
Dim ErrorShow As String
Dim YesorNoAnswerToMessageBox As String
Dim QuestionToMessageBox As String
Dim DeliveryRate As String
[code]....
How do you make certain fields "required" to be completed before exiting?
View 3 Replies View Related One form. Several fields which are required using event 'On exit' - "If isnull" statements for each one.
Button on form to close said form...
Where on the form would I put the event for the button to override all other events?
How can I adjust my required field so that you cannot simply enter a space bar character to circumvent the requirement?
Here is part of the code:
If IsNull([txtRequiredReason]) Then
MsgBox "The Reason field is required"
DoCmd.GoToControl "txtRequiredReason"
Else
Access 2003
This statement works great.
Code:
db.Execute "INSERT INTO TBLFILESTEMP (TextFile) VALUES (""" & Replace$(strDelimiter & vbNewLine & var, """", """""") & """);"
I'd like to include FileID (number Long Integer) and use the value from MyForm RecordID (autonumber)
This gives me a Run-Time error '3061': Too few parameters. Expected 1.
Code:
db.Execute "INSERT INTO TBLFILESTEMP (TextFile, FileID) VALUES (""" & Replace$(strDelimiter & vbNewLine & var, """", """""") & """, me.RecordID);"
This is my first time writing a pass through query pinging sql tables using an input parameter from a form. I have gotten as far as executing the query but I can't seem to display the result to ensure it's pulling the right records. I also want to be able to append the records to a table. Below is the code I have written so far:
Sub GETRT()
Code:
Dim db As DAO.Database
Dim QDF As QueryDef
Dim STRSQL As String
Dim RS As Recordset
STRSQL = "SELECT * FROM LAB_MESR.ODM_RT_DAYS" & _
"WHERE LOCATION_ID=" & [Forms]![PARMS]![STR_NBR]
Set db = CurrentDb
Set QDF = db.QueryDefs("001:GET_LT")
QDF.SQL = STRSQL
End Sub
I have a sample database (attached) where I have a form called frmVoteHeadsOne. The voteheads that I have are swimming (Swim) and transport (Trans), which both appear for data in a table. On the form, from the combo list, I would wish that if I select swimming, for example, only Mark Tyler's name is returned since he has paid 2000; if I select Transport from the same list, only John clement's name is returned.
My purpose for doing this is because in the actual database, I have 21 different voteheads which I would like to get different results from based on what the user selects from the combo list.
I am trying to dynamically change the IN sql from within VBA using parameters. for some reason i have no luck, no errors shows up, but it's actually not picking up the criteria.
Code:
THE SQL IN STATEMENT
In (select RemID from [ReminderAssignees] Where RemDate between [Date1] and [Date2] And [sDismiss] )));
Code:
THE VBA CODE
qdf.Parameters("date1") = Date1
qdf.Parameters("date2") = Date2
Select Case iDismissed
Case 0, 1
qdf.Parameters("sDismiss") = "1=1"
Case 2
qdf.Parameters("sDismiss") = "(not isdate(Dismiss) or Dismiss > #" & Now & "#)"
Case 3
qdf.Parameters("sDismiss") = " isdate(Dismiss) and Dismiss < #" & Now & "#"
End Select
qdf.Execute
I am trying to create a txt file to import into our accounting software. I get the file (its blank), but it fails on the WriteLine and i get the run time error. I have a command button on a form that the user will click to export the file.
Private Sub cmdExport_Click()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim strPath As String
Dim strPathGB As String
[Code] ....
In order to arrive at the required solution I have to perform a calculation, using data from each successive table record (in chronological order). The result of each calculation must then be used to arrive at the result for the next calculation.
In other words I have to update a variable based on data from each record, sorted in chronological order, and use the final result to populate a field on a form.
Is it time to break out my copy of "VBA For Dummies" and start learning how to use Recordsets?
How to set parameters for the date being last monday to last sunday
So
Paramaters = Date
Then I need
ParamatersWeek = the last monday to the last sunday ...
Table: DailyExport
Field: FailureGrouping (actually is offices)
I want to export all the fields from DailyExport each of our 9 offices to Excel, whetjer or not they have date in the DailyExport table. So if not, the exported workbook would only have columns headings.
I need to loop through the nine offices and export each office report to Excel.
Dim StrQry As String
Dim strfullpath As String
Dim SOffice as String
strSQL = "SELECT * FROM DailyExport WHERE FailureGrouping = " & SOffice
strfullpath = "Y:" & SOffice &" "& Format(Date,"mm-dd-yy") & "_Failures.xlsx"
DoCmd.TransferSpreadsheet acExport, , FailureGrouping, strfullpath, False
I have code for calling stored procedure with parameters,which is as follows
Dim qdf As DAO.QueryDef, rst As DAO.Recordset
Dim IdValueToProcess As Long
IdValueToProcess = 221177 ' test data
Debug.Print (IdValueToProcess)
Set qdf = CurrentDb.CreateQueryDef("")
[Code] ....
And my stored procedure is
Code:
ALTER PROCEDURE [dbo].[spItemDesc]
@ItemNo varchar(200) ,
AS
BEGIN
set nocount on ;
select ProductDesc1,ProductDesc2 from ProductDatabase.dbo.tblProductInfo where ProductNumber = @ItemNo
END
The error comes on debug.print(rst!ProductDesc1)
And it says "Item not found in the collection"