Store Value from combo box

[manguilla]

Hello All. I am trying to take a value from a combo box and store that value in an insert statement to insert data to a table. I have a combo box that shows a week ending date on a form. When the user selects the end date, I want to take that number from the combo box and store that as either a String or Date. I then want to take that value and use it in an INSERT statement to populate a table with data. Here is the code I have so far. Any help will be appreciated. Thanks in advance.

Manguilla




Private Sub Go_Date_Click()
On Error GoTo Err_Go_Date_Click

Dim conn As ADODB.Connection
Dim strDate As String

Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\Projects\IBM Project\Project\" & "IBM_StateFarm Project.mdb" & ";" & _
"Persist Security Info=False"
'\\sesfileshare\projects\IBM2004-AERO3\AERO3_DB 'D:\Projects\IBM Project\Project
conn.Open

strDate = Me.cmbDate

conn.Execute "Delete from UsedInventory"

conn.Execute "INSERT INTO UsedInventory ( VL400, T22, USBHub, CameraCbls, USBCbls, ReturnLbls, Tripplites ) " + _
" SELECT Sum(InventoryReorderFile.[VL400s used]) AS [SumOfVL400s used], " + _
" Sum(InventoryReorderFile.[T22 used]) AS [SumOfT22 used], Sum(InventoryReorderFile.[Hubs used]) AS [SumOfHubs used], " + _
" Sum(InventoryReorderFile.[Camera Cbls used]) AS [SumOfCamera Cbls used], " + _
" Sum(InventoryReorderFile.[USB Cbls used]) AS [SumOfUSB Cbls used], " + _
" Sum(InventoryReorderFile.[Return Lbls used]) AS [SumOfReturn Lbls used], " + _
" Sum(InventoryReorderFile.[TrippLites used]) AS [SumOfTrippLites used] " + _
" FROM InventoryReorderFile, UsedInventory WHERE (((InventoryReorderFile.Date)<#'" & strDate & "'#));"


'Open form with results from week ending
DoCmd.OpenForm "UsedInventory"

conn.Close
Set conn = Nothing

Exit_Go_Date_Click:
Exit Sub

Err_Go_Date_Click:
MsgBox Err.Description
Resume Exit_Go_Date_Click

End Sub

 

[PHV]

Replace this:
)<#'" & strDate & "'#));"
By this:
)<#" & strDate & "#));"
And be sure that strDate is formatted as m/dd/yyyy

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[manguilla]

Thanks PHV the replacement fixed the error but I am still having a problem with the date. I have the Date stored in a table as a Text. I changed their values to Date/Time and String but it seems to be the same problem. Here is an example, when I select 7/3/2004 from the combo box, it will give a value of 12/31/1899. Am I selecting the value from the combo box corectly with strDate = Me.cmbDate? Here is the code I have now. Thanks again.

Manguilla


Private Sub Go_Date_Click()

Dim conn As ADODB.Connection
'Dim strDate As Date
Dim strDate As String

Set conn = New ADODB.Connection
conn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\Projects\IBM Project\Project\" & "IBM_StateFarm Project.mdb" & ";" & _
"Persist Security Info=False"
'\\sesfileshare\projects\IBM2004-AERO3\AERO3_DB 'D:\Projects\IBM Project\Project
conn.Open

strDate = Me.cmbDate
strDate = Format(strDate, "mm/dd/yyyy")
MsgBox " " & strDate & " "

conn.Execute "Delete from UsedInventory"

conn.Execute "INSERT INTO UsedInventory ( VL400, T22, USBHub, CameraCbls, USBCbls, ReturnLbls, Tripplites ) " + _
" SELECT Sum(InventoryReorderFile.[VL400s used]) AS [SumOfVL400s used], " + _
" Sum(InventoryReorderFile.[T22 used]) AS [SumOfT22 used], Sum(InventoryReorderFile.[Hubs used]) AS [SumOfHubs used], " + _
" Sum(InventoryReorderFile.[Camera Cbls used]) AS [SumOfCamera Cbls used], " + _
" Sum(InventoryReorderFile.[USB Cbls used]) AS [SumOfUSB Cbls used], " + _
" Sum(InventoryReorderFile.[Return Lbls used]) AS [SumOfReturn Lbls used], " + _
" Sum(InventoryReorderFile.[TrippLites used]) AS [SumOfTrippLites used] " + _
" FROM InventoryReorderFile, UsedInventory WHERE (((InventoryReorderFile.Date)<#" & strDate & "#));"


End Sub