Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL statement not working
Help!!!! I'm trying to load a combo box on my Excel form by getting the
records from an Access database thru an SQL statement . For some reason it doesn't like my SQL statement. I get "No value given for one or more required parameters. I have tried several different methods. What am I doing wrong and what works! Thanks for you help! Private Sub cboBWContractNumber_Click() Dim UsageTracking As ADODB.Connection Dim objCommand As ADODB.Command Dim recordset As ADODB.recordset Dim i As Integer Dim strSQLEquipCommodity As String 'At this point, cboContractNumber is populated with values _ When cboBWContractNumber is selected _ Display cboEquipmentCommodity for that contract number _ Filter records and fill combo box with selection _ Create new sub for Get Commodity DSR's ____________________________________________ 'Create Connection String Method 1 Set UsageTracking = New ADODB.Connection With UsageTracking .ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & "\\bsrvfp04\shared\PLM\database\UsageTracking. mdb" & "; " .Mode = adModeShareDenyNone .Open End With Application.EnableEvents = True If cboBWContractNumber.Value < "" Then frmDSRHeader.cboEquipmentCommodity.Visible = True 'Create Command Object for query Set objCommand = New ADODB.Command objCommand.ActiveConnection = UsageTracking 'Load SQL string into Command object objCommand.CommandText = "SELECT tblDSRCommodity.BWProjectNumberID, tblDSRCommodity.COA, [Code of Accounts].Description " & _ "FROM tblDSRCommodity INNER JOIN [Code of Accounts] ON tblDSRCommodity.COA=[Code of Accounts].COA " & _ "WHERE tblDSRCommodity.BWProjectNumberID = Me!cboBWContractNumber.value ORDER BY tblDSRCommodity.COA;" 'Execute the statement Set recordset = objCommand.Execute '(Options:=adCmdText) __________________________________________________ _________ Set recordset = New ADODB.recordset Method 2 With recordset .ActiveConnection = UsageTracking .Open "SELECT tblDSRCommodity.BWProjectNumberID, tblDSRCommodity.COA, [Code of Accounts].Description " & _ "FROM tblDSRCommodity INNER JOIN [Code of Accounts] ON tblDSRCommodity.COA=[Code of Accounts].COA " & _ "WHERE tblDSRCommodity.BWProjectNumberID = Me!cboBWContractNumber ORDER BY tblDSRCommodity.COA;" .Close End With Set recordset = New ADODB.recordset recordset.CursorType = adOpenStatic recordset.LockType = adLockReadOnly recordset.Options = adCmdText recordset.Open strSQLEquipCommodity, UsageTracking recordset.MoveFirst With Me.cboEquipmentCommodity .Clear .ColumnCount = 2 Do .AddItem .List(i, 0) = recordset![COA] .List(i, 1) = recordset![Description] i = i + 1 recordset.MoveNext Loop Until recordset.EOF End With End If Set recordset = Nothing UsageTracking.Close Set UsageTracking = Nothing End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL statement not working
FSt1,
Thanks for your response. Its happening on the Open statement OR when it actually tries to run the sql. "FSt1" wrote: hi i may be going out on a limb here but....... on what line in your code is the error occuring? Regards FSt1 p.s. i think you are making this WAY more compllicated than it needs to be. "Michelle" wrote: Help!!!! I'm trying to load a combo box on my Excel form by getting the records from an Access database thru an SQL statement . For some reason it doesn't like my SQL statement. I get "No value given for one or more required parameters. I have tried several different methods. What am I doing wrong and what works! Thanks for you help! Private Sub cboBWContractNumber_Click() Dim UsageTracking As ADODB.Connection Dim objCommand As ADODB.Command Dim recordset As ADODB.recordset Dim i As Integer Dim strSQLEquipCommodity As String 'At this point, cboContractNumber is populated with values _ When cboBWContractNumber is selected _ Display cboEquipmentCommodity for that contract number _ Filter records and fill combo box with selection _ Create new sub for Get Commodity DSR's ____________________________________________ 'Create Connection String Method 1 Set UsageTracking = New ADODB.Connection With UsageTracking .ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & "\\bsrvfp04\shared\PLM\database\UsageTracking. mdb" & "; " .Mode = adModeShareDenyNone .Open End With Application.EnableEvents = True If cboBWContractNumber.Value < "" Then frmDSRHeader.cboEquipmentCommodity.Visible = True 'Create Command Object for query Set objCommand = New ADODB.Command objCommand.ActiveConnection = UsageTracking 'Load SQL string into Command object objCommand.CommandText = "SELECT tblDSRCommodity.BWProjectNumberID, tblDSRCommodity.COA, [Code of Accounts].Description " & _ "FROM tblDSRCommodity INNER JOIN [Code of Accounts] ON tblDSRCommodity.COA=[Code of Accounts].COA " & _ "WHERE tblDSRCommodity.BWProjectNumberID = Me!cboBWContractNumber.value ORDER BY tblDSRCommodity.COA;" 'Execute the statement Set recordset = objCommand.Execute '(Options:=adCmdText) __________________________________________________ _________ Set recordset = New ADODB.recordset Method 2 With recordset .ActiveConnection = UsageTracking .Open "SELECT tblDSRCommodity.BWProjectNumberID, tblDSRCommodity.COA, [Code of Accounts].Description " & _ "FROM tblDSRCommodity INNER JOIN [Code of Accounts] ON tblDSRCommodity.COA=[Code of Accounts].COA " & _ "WHERE tblDSRCommodity.BWProjectNumberID = Me!cboBWContractNumber ORDER BY tblDSRCommodity.COA;" .Close End With Set recordset = New ADODB.recordset recordset.CursorType = adOpenStatic recordset.LockType = adLockReadOnly recordset.Options = adCmdText recordset.Open strSQLEquipCommodity, UsageTracking recordset.MoveFirst With Me.cboEquipmentCommodity .Clear .ColumnCount = 2 Do .AddItem .List(i, 0) = recordset![COA] .List(i, 1) = recordset![Description] i = i + 1 recordset.MoveNext Loop Until recordset.EOF End With End If Set recordset = Nothing UsageTracking.Close Set UsageTracking = Nothing End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SQL statement not working
The WHERE statement is where it messing up!
WHERE tblDSRCommodity.BWProjectNumberID = Me!cboBWContractNumber.value BWProjectNumberID = numeric cboBWContractNumber = Alphanumeric How can I convert cboBWContractNumber = numeric? "Michelle" wrote: FSt1, Thanks for your response. Its happening on the Open statement OR when it actually tries to run the sql. "FSt1" wrote: hi i may be going out on a limb here but....... on what line in your code is the error occuring? Regards FSt1 p.s. i think you are making this WAY more compllicated than it needs to be. "Michelle" wrote: Help!!!! I'm trying to load a combo box on my Excel form by getting the records from an Access database thru an SQL statement . For some reason it doesn't like my SQL statement. I get "No value given for one or more required parameters. I have tried several different methods. What am I doing wrong and what works! Thanks for you help! Private Sub cboBWContractNumber_Click() Dim UsageTracking As ADODB.Connection Dim objCommand As ADODB.Command Dim recordset As ADODB.recordset Dim i As Integer Dim strSQLEquipCommodity As String 'At this point, cboContractNumber is populated with values _ When cboBWContractNumber is selected _ Display cboEquipmentCommodity for that contract number _ Filter records and fill combo box with selection _ Create new sub for Get Commodity DSR's ____________________________________________ 'Create Connection String Method 1 Set UsageTracking = New ADODB.Connection With UsageTracking .ConnectionString = _ "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & "\\bsrvfp04\shared\PLM\database\UsageTracking. mdb" & "; " .Mode = adModeShareDenyNone .Open End With Application.EnableEvents = True If cboBWContractNumber.Value < "" Then frmDSRHeader.cboEquipmentCommodity.Visible = True 'Create Command Object for query Set objCommand = New ADODB.Command objCommand.ActiveConnection = UsageTracking 'Load SQL string into Command object objCommand.CommandText = "SELECT tblDSRCommodity.BWProjectNumberID, tblDSRCommodity.COA, [Code of Accounts].Description " & _ "FROM tblDSRCommodity INNER JOIN [Code of Accounts] ON tblDSRCommodity.COA=[Code of Accounts].COA " & _ "WHERE tblDSRCommodity.BWProjectNumberID = Me!cboBWContractNumber.value ORDER BY tblDSRCommodity.COA;" 'Execute the statement Set recordset = objCommand.Execute '(Options:=adCmdText) __________________________________________________ _________ Set recordset = New ADODB.recordset Method 2 With recordset .ActiveConnection = UsageTracking .Open "SELECT tblDSRCommodity.BWProjectNumberID, tblDSRCommodity.COA, [Code of Accounts].Description " & _ "FROM tblDSRCommodity INNER JOIN [Code of Accounts] ON tblDSRCommodity.COA=[Code of Accounts].COA " & _ "WHERE tblDSRCommodity.BWProjectNumberID = Me!cboBWContractNumber ORDER BY tblDSRCommodity.COA;" .Close End With Set recordset = New ADODB.recordset recordset.CursorType = adOpenStatic recordset.LockType = adLockReadOnly recordset.Options = adCmdText recordset.Open strSQLEquipCommodity, UsageTracking recordset.MoveFirst With Me.cboEquipmentCommodity .Clear .ColumnCount = 2 Do .AddItem .List(i, 0) = recordset![COA] .List(i, 1) = recordset![Description] i = i + 1 recordset.MoveNext Loop Until recordset.EOF End With End If Set recordset = Nothing UsageTracking.Close Set UsageTracking = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF Statement not working | Excel Worksheet Functions | |||
SQL statement not working | Excel Programming | |||
VBA SQL Statement not working | Excel Programming | |||
IF statement not working | Excel Discussion (Misc queries) | |||
If statement not working | Excel Programming |