SQL statement not working

M

Michelle

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
 
M

Michelle

FSt1,

Thanks for your response. Its happening on the Open statement OR when it
actually tries to run the sql.

FSt1 said:
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 said:
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
Click to expand...
Click to expand...
 
M

Michelle

The WHERE statement is where it messing up!

WHERE tblDSRCommodity.BWProjectNumberID =
BWProjectNumberID = numeric
cboBWContractNumber = Alphanumeric

How can I convert cboBWContractNumber = numeric?

Michelle said:
FSt1,

Thanks for your response. Its happening on the Open statement OR when it
actually tries to run the sql.

FSt1 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Getting Automation error 4
waiting for user response 6
SQL not importing records from ACCDB 2
ADO retrieval from close workbook-data type issue 2
Excel/SQL Problem 3
Operation is not allowed when the object is closed 1
VBA SQL Statement not working 1
Recordset Object 0

Top