syntax error- Rrequire help

[sunilvedula]

Can anyone please help me with the error in this code : there is something missing a double quote which is required or something. I am trying to compare three fields and pull the data and place it on a form. The error is: "Format of the initilization string does not confirm to the OLE DB Specification"


Private Sub CmdSearch_Click()
On Error GoTo Err_CmdSearch_Click
Dim rs As New ADODB.Recordset
Dim Cnxn As New ADODB.Connection

rs.Open "SELECT TblO4.Empid, TblO4.Month, TblO4.Year, TblO4.Core Standards, TblO4.Productivity, TblO4.Quality Standard, TblO4.Time Sheets, TblO4.Teamwork, TblO4. Audit Adherence, TblO4.ContinousImprovement, TblO4. Action Plan, TblO4.No of SL, TblO4.No of Time sick, Tblo4. No of Upl, TblO4.No of times unplanned, TblO4.No of PL, TblO4.No of Late Comming, TblO4.Balance Leave " _

& " FROM TblO4", " WHERE TblO4.Empid = '" & CboxHbiid.Value & ", TblO4.Month = " & CboxMonth.Value & ", TblO4.Year = " & CboxYear.Value & ", Cnxn, adOpenDynamic"

 

[boblarson]

For one, you can't use spaces in field names. If you have spaces in field (or table) names you have to put square brackets around them.

Second, you are using reserved key words for field names and you are going to run into trouble with those. You have fields named Month and Year and those are reserved key words.

Third (not related to your problem but an FYI) - you don't need to put .Value for the combo box as that is the default. All you need is CBoxYear

Fourth - Get rid of the & " after the CBoxYear part and get rid of the " mark at the end of the code as the end of the string is CBoxYear then the code just wants the comma and the connection and then comma and the adOpenDynamic part.

 

[sunilvedula]

Thank you and i will keep in mind regarding the reserved words. I tried what you asked me to and this time it was giving the following error "13-type mismatch"
this is what i idid:

Private Sub CmdSearch_Click()
On Error GoTo Err_CmdSearch_Click
Dim rs As New ADODB.Recordset
Dim Cnxn As New ADODB.Connection

Cnxn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = J:\Disputes_PE\CORE\O4\O4.mdb"
Cnxn.Open
rs.Open "SELECT TblO4.Empid, TblO4.Month, TblO4.Year, TblO4.Core Standards, TblO4.Productivity, TblO4.Quality Standard, TblO4.Time Sheets, TblO4.Teamwork, TblO4. Audit Adherence, TblO4.ContinousImprovement, TblO4. Action Plan, TblO4.No of SL, TblO4.No of Time sick, Tblo4. No of Upl, TblO4.No of times unplanned, TblO4.No of PL, TblO4.No of Late Comming, TblO4.Balance Leave " _
& " FROM TblO4", " WHERE TblO4.Empid = '" & CboxHbiid & ", TblO4.Month = " & CboxMonth & ", TblO4.Year = " & CboxYear, Cnxn, adOpenDynamic

If rs.EOF Then
MsgBox "No Records Found try again"
Else
Me.CboxHbiid = rs.Fields("Empid").Value
Me.CboxMonth = rs.Fields("Month").Value
Me.CboxYear = rs.Fields("Year").Value
Me.TxtCoreStandard = rs.Fields("Core Standard").Value

End If

Me.CboxHbiid = ""
Me.CboxMonth = ""
Me.CboxYear = ""
Me.TxtCoreStandard = ""
Me.TxtProd = ""
Me.TxtQualitySt = ""
Me.TxtTimesheets = ""
Me.TxtTeamwork = ""
Me.TxtAuditadh = ""
Me.TxtCi = ""
Me.TxtAp = ""

DoCmd.GoToRecord , , acNewRec

Exit_CmdSearch_Click:
Exit Sub

Err_CmdSearch_Click:
Select Case Err.Number
Case 3058
Resume Next
Case Else
MsgBox Err.Number & "-" & Err.Description
End Select
Resume Exit_CmdSearch_Click

 

[modest]

WHERE TblO4.Empid = '" & CboxHbiid & ",
you're missing your close single quote (before the comma)


Should be:
WHERE TblO4.Empid = '" & CboxHbiid & "',

 

[boblarson]

You still also have spaces in the table names like this one (there are others too):
TblO4.Core Standards

Should be
Tbl04.[Core Standards]

 

[sunilvedula]

changed code but the error remains

This the changed code which i have done with ur suggestions. But it still gives the following error:"Run time error 13 type mismatch"


rs.Open "SELECT TblO4.Empid, TblO4.Month, TblO4.Year, TblO4.CoreStandards, TblO4.Productivity, TblO4.QualityStandard, TblO4.TimeSheets, TblO4.Teamwork, TblO4. AuditAdherence, TblO4.ContinousImprovement, TblO4.ActionPlan, TblO4.NoofSL, TblO4.NoofTimesick, Tblo4.NoofUpl, TblO4.Nooftimesunplanned, TblO4.NoofPL, TblO4.NoofLateComming, TblO4.BalanceLeave " _
& " FROM TblO4 ", " WHERE TblO4.Empid = '" & CboxHbiid & "' AND TblO4.Mth = " & CboxMth & " AND TblO4.Yr = " & CboxYr & "", Cnxn, adOpenDynamic

 

[Moniker]

I have a feeling the field EmpId is a numeric field, and you're referencing it like it's text. You also have extra quotes at the end and a misplaced comma between FROM and WHERE. Change to:

rs.Open "SELECT TblO4.Empid, TblO4.Month, TblO4.Year, TblO4.CoreStandards, TblO4.Productivity, TblO4.QualityStandard, TblO4.TimeSheets, TblO4.Teamwork, TblO4.AuditAdherence, TblO4.ContinousImprovement, TblO4.ActionPlan, TblO4.NoofSL, TblO4.NoofTimesick, Tblo4.NoofUpl, TblO4.Nooftimesunplanned, TblO4.NoofPL, TblO4.NoofLateComming, TblO4.BalanceLeave " _
& " FROM TblO4 WHERE TblO4.Empid = " & CboxHbiid & " AND TblO4.Mth = " & CboxMth & " AND TblO4.Yr = " & CboxYr, Cnxn, adOpenDynamic

I removed the single quotes when referring to the EmpID field and cleaned up the formatting.

A type mismatch means you're trying to access a numeric field as a text field or vice-versa. When you're writing to or assigning a value to a variable, you have to use the same data type that the variable was designed to take. Without it, you have a (data) type mismatch. If TblO4.Mth or TblO4.Yr are text values, you will have to put single quotes around them. Cleaned up SQL (easier to read):

SELECT 
    TblO4.Empid
    ,TblO4.Month
    ,TblO4.Year
    ,TblO4.CoreStandards
    ,TblO4.Productivity
    ,TblO4.QualityStandard
    ,TblO4.TimeSheets
    ,TblO4.Teamwork
    ,TblO4.AuditAdherence
    ,TblO4.ContinousImprovement
    ,TblO4.ActionPlan
    ,TblO4.NoofSL
    ,TblO4.NoofTimesick
    ,Tblo4.NoofUpl
    ,TblO4.Nooftimesunplanned
    ,TblO4.NoofPL
    ,TblO4.NoofLateComming
    ,TblO4.BalanceLeave 
FROM 
    TblO4 
WHERE 
    TblO4.Empid = " & CboxHbiid & " 
    AND TblO4.Mth = " & CboxMth & "
    AND TblO4.Yr = " & CboxYr
;

Posting it like that (or something similar) makes it a lot easier for people to help.

 

[sunilvedula]

Hi Now the errror is " No value given for one or more req parameters" when i try the following code with ur suggestion:
Dim rs As New ADODB.Recordset
Dim Cnxn As New ADODB.Connection

Cnxn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = J:\Disputes_PE\CORE\O4\O4.mdb"
Cnxn.Open
rs.Open "SELECT TblO4.Empid, TblO4.Month, TblO4.Year, TblO4.CoreStandards, TblO4.Productivity, " _
& "TblO4.QualityStandard, TblO4.TimeSheets, TblO4.Teamwork, TblO4.AuditAdherence, " _
& "TblO4.ContinousImprovement, TblO4.ActionPlan, TblO4.NoofSL, TblO4.NoofTimesick, " _
& "Tblo4.NoofUpl, TblO4.Nooftimesunplanned, TblO4.NoofPL, TblO4.NoofLateComming, " _
& "TblO4.BalanceLeave " _
& " FROM TblO4 " _
& " WHERE TblO4.Empid = '" & CboxHbiid _
& "' AND TblO4.Mth = " & CboxMth _
& " AND TblO4.Yr = " & CboxYr _
, Cnxn, adOpenDynamic

If rs.EOF Then
MsgBox "No Records Found try again"
Else
Me.CboxHbiid = rs.Fields("Empid").Value
Me.CboxMth = rs.Fields("Mth").Value
Me.CboxYr = rs.Fields("Yr").Value
Me.TxtCoreStandard = rs.Fields("CoreStandard").Value

 

[Moniker]

The parameters are your WHERE statement. One of these is NULL:

CboxHbiid
CboxMth
CboxYr

 

[sunilvedula]

Could u explain what it means. sorry i could not understand.

 

[Moniker]

At the end of your sequel statement, you have this:

& " WHERE TblO4.Empid = '" & CboxHbiid _
& "' AND TblO4.Mth = " & CboxMth _
& " AND TblO4.Yr = " & CboxYr _

One of those ComboBoxes is empty (assuming that's what CBox is) and those are required values.

 

[sunilvedula]

Thanq all and the issue is resolved

Thanq all the issue has been resolved and it is working fine.