I did try that using the query to filter based on Chargenumber of open form.
Query works fine by itself, but when i try to create a recordset using it I
get the following error:
No value given for one or more required parameters
Here is code:
rst.Open "qryCalculateAmpHrs", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic, adCmdTable
Once create, I need to be able to update and edit data systemamtically.
:
Okay, we're getting there...
(You know, this whole thing would be easier to put the code into a
saved query and just open the query...)
You need to evaluate the value of the ChargeNumber control in VBA
before passing it into the SQL query:
If ChargeID is a number:
"Where tblChargeData.ChargeID = " & Forms!frmChargeEntry!CHargeNumber
If it's a text field:
"Where tblChargeData.ChargeID = '" & Forms!frmChargeEntry!
CHargeNumber & "'"
Chris
There was a problem with that line but it did not solve the issue.
It should have read
WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry]!(ChargeNumber)))
Fixed that. Code is resolving it properly but still says I am missing
parameter.
:
I'm thinking the problem probably lies with:
WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry]))
When you use that in a query, it runs through MS Access first. Access
knows what FORMS!frmChargeEntry means. When you execute it in a
recordset, it runs through the ADO or DAO engine, which has no idea
waht it is. Try:
"WHERE (((tblChargeData.ChargeID)=" & [Forms]![frmChargeEntry] & "))
"
Chris
On Feb 21, 12:28 pm, Tirelle <
[email protected]>
wrote:
Thanks Chris. Ok, I got past the previous error although I have not broken
it down by smaller pieces yet. Now I am getting this error:
-2147217904 No value given for one or more required parameters
Here is the code:
rst.open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) &
: & Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
tblChargeData.TotalAh, FormatDateTime([LogDate],2) &' '&[LogTime] AS
AhCalculationTime " & _
" FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
" WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
" ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2) & : &
Right([LogEntry],2));"
:
FIX:
1-TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2)) AS LogTime
2-Save the whole thing as a query, open the query:
3-Use a bunch of string variables to help sort out the concatenations
I would do this:
Dim SQL as String
SQL = "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)
& ':' & Right([LogEntry],2)) AS LogTime, "
SQL = SQL & " tblChargeData.ChargeID, tblChargeData.LogEntry,
tblChargeData.Amps, tblChargeData.AhComputed, "
SQL = SQL & " tblChargeData.TotalAh, FormatDateTime([LogDate],2) & ' '
& [LogTime] AS AhCalculationTime "
SQL = SQL & " FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT
JOIN tblChargeData "
SQL = SQL & " ON tblVentilationLineUps.VentilationLineUpID =
tblChargeData.VentLU) ON "
SQL = SQL & " tblCharges.ChargeNumber = TblChargeData.ChargeID "
SQL = SQL & " WHERE (((tblChargeData.ChargeID)=" & [Forms]!
[frmChargeEntry]))
SQL = SQL & " ORDER BY tblChargeData.LogEntryDate,
TimeValue(Left([LogEntry],2) & ':' & Right([LogEntry],2));"
'Debug.Print SQL
rst.Open SQL
This way you can copy/paste the SQL variable into a query to double
check the SQL.
Chris
i am trying to create a recordset in VBA. The current statement is:
rst.Open "SELECT tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" &
":" & "Right([LogEntry],2)) AS LogTime, tblChargeData.ChargeID,
tblChargeData.LogEntry, tblChargeData.Amps, tblChargeData.AhComputed,
tblChargeData.TotalAh, FormatDateTime([LogDate],2)" & " " & "[LogTime] AS
AhCalculationTime " & _
" FROM tblCharges INNER JOIN (tblVentilationLineUps RIGHT JOIN tblChargeData
ON tblVentilationLineUps.VentilationLineUpID = tblChargeData.VentLU) ON
tblCharges.ChargeNumber = TblChargeData.ChargeID " & _
" WHERE (((tblChargeData.ChargeID)=[Forms]![frmChargeEntry])) " & _
" ORDER BY tblChargeData.LogEntryDate, TimeValue(Left([LogEntry],2)" & ":" &
"Right([LogEntry],2));"
WHen I run the code, I get a Syntax Error(Missing Operator) from
TimeValue(Left([LogEntry],2):Right([LogEntry],2)) AS LogTime. This part of
query is supposed to format a time based on those two values but the ":"
needs to be joined to it and not just inserted
(eg...TimeValue(Left([LogEntry],2)&":"&Right([LogEntry],2)) AS LogTime). How
do I fix this?
Thanks in advance.
Tirelle Lee- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
- Show quoted text -- Hide quoted text -
