K
KJGinNC
Hello,
I'm creating a tool in Access 2000 that will massage data (mainly adjusting
dates for weekends and holidays) from our corporate Oracle database so it can
be manually entered into someone else's website. I have no control over the
Oracle database. Here's the setup. User enters number on form in unbound
control and clicks command button. Data is pulled from Oracle into a
recordset. The recordset data is displayed in more unbound controls on the
form only if certain criteria are met (and after a date is massaged).
I think the problem is the recordset is making the memo field a "adVarWChar"
instead of a "adLongVarWChar". This the part of the SQL that refers to the
memo field: First(VRSC_MCS_SVC_REQ.MSR_COMMENTS) AS Comments.
I have no idea how to change the data type of a recordset. I've searched
and searched! Please help!
Here's the offending code with some parts removed for an attempt a brevity
and clarity!
Thanks in advance!
Karrie
----
Private Sub cmdSSInfo_Click()
On Error GoTo Err_cmdSSInfo_Click
Dim rst As ADODB.Recordset, frm As Form
Dim cnn As ADODB.Connection
Dim strSR As String, dtSMSDate As Date
Dim sqlSR As String,
Set cnn = CurrentProject.Connection
Set frm = [Forms]![frmMain]
Set rst = New ADODB.Recordset
'Code here for Getting SR from form
strSR = Nz(frm![txtSR], "")
If strSR = "" Then
MsgBox "Please enter SR number and try again", vbOKOnly
Exit Sub
Else
sqlSR = "(((VRSC_MCS_SVC_REQ.MSR_SR_NUM)=" & strSR & ")"
'Get the SR data from SMS as a record set
rst.Open "SELECT VRSC_MCS_SVC_REQ.MSR_SR_NUM AS SR,
VRSC_MCS_SVC_REQ.MSR_RESP AS Response, " _
& "Max(VRSC_MCS_SVC_REQ_ACTION.MSRA_ACTION_NUM) AS Trip,
VRSC_MCS_SVC_REQ.MSR_TICKET_NUM AS Ticket, " _
& "VRSC_MCS_SVC_REQ.MSR_OPEN_DATE_TIME AS [Open Date],
Min(VRSC_MCS_SVC_REQ_ACTION.MSRA_ARRIVE_TIME) " _
& "AS [Work Start], Max(VRSC_MCS_SVC_REQ_ACTION.MSRA_DEPART_TIME) AS [Work
End], " _
& "First(VRSC_MCS_SVC_REQ.MSR_COMMENTS) AS Comments, " _
&
"(Max([VRSC_MCS_SVC_REQ_ACTION].[MSRA_DEPART_TIME])-Max([VRSC_MCS_SVC_REQ].[MSR_OPEN_DATE_TIME]))*24 AS KPI " _
& "FROM VRSC_MCS_SVC_REQ INNER JOIN VRSC_MCS_SVC_REQ_ACTION ON
VRSC_MCS_SVC_REQ.MSR_SR_NUM = " _
& "VRSC_MCS_SVC_REQ_ACTION.MSRA_SR_NUM GROUP BY
VRSC_MCS_SVC_REQ.MSR_SR_NUM, " _
& "VRSC_MCS_SVC_REQ.MSR_RESP, VRSC_MCS_SVC_REQ.MSR_TICKET_NUM, " _
& "VRSC_MCS_SVC_REQ.MSR_OPEN_DATE_TIME, VRSC_MCS_SVC_REQ.MSR_CUST_ID,
VRSC_MCS_SVC_REQ.MSR_SR_STATUS " _
& "HAVING " & sqlSR & " AND ((VRSC_MCS_SVC_REQ.MSR_CUST_ID)='SG02222'))", _
cnn, adOpenForwardOnly, adLockReadOnly
End If
'Check to make the recordset has data otherwise give error message
If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
dtSMSDate = rst.Fields("Open Date")
' check business day and time
If BusinessDay(dtSMSDate) = True Then
--snip---
Else
'get next business day
--snip--
End If
' Check number of trips and display data as needed.
If rst.Fields("Trip") > 1 Then
' If multiple trip display message
frm![txtInfo] = "There are multiple trips. Do not enter data before
checking with supervisor."
Else
' If single trip check for pass fail and display data
frm![txtInfo] = "Use information below to update."
frm![txtTicket] = rst.Fields("Ticket")
frm![txtWorkStart] = Format(rst.Fields("Work Start"), "Short Date")
frm![txtWorkEnd] = Format(rst.Fields("Work End"), "Short Date")
frm![txtComments] = rst.Fields("Comments")
End If
Else
MsgBox "There is no data for this SR number. Please try again", vbOKOnly
Exit Sub
End If
Exit_cmdSSInfo_Click:
Exit Sub
Err_cmdSSInfo_Click:
MsgBox Err.Description
Resume Exit_cmdSSInfo_Click
End Sub
I'm creating a tool in Access 2000 that will massage data (mainly adjusting
dates for weekends and holidays) from our corporate Oracle database so it can
be manually entered into someone else's website. I have no control over the
Oracle database. Here's the setup. User enters number on form in unbound
control and clicks command button. Data is pulled from Oracle into a
recordset. The recordset data is displayed in more unbound controls on the
form only if certain criteria are met (and after a date is massaged).
I think the problem is the recordset is making the memo field a "adVarWChar"
instead of a "adLongVarWChar". This the part of the SQL that refers to the
memo field: First(VRSC_MCS_SVC_REQ.MSR_COMMENTS) AS Comments.
I have no idea how to change the data type of a recordset. I've searched
and searched! Please help!
Here's the offending code with some parts removed for an attempt a brevity
and clarity!
Thanks in advance!
Karrie
----
Private Sub cmdSSInfo_Click()
On Error GoTo Err_cmdSSInfo_Click
Dim rst As ADODB.Recordset, frm As Form
Dim cnn As ADODB.Connection
Dim strSR As String, dtSMSDate As Date
Dim sqlSR As String,
Set cnn = CurrentProject.Connection
Set frm = [Forms]![frmMain]
Set rst = New ADODB.Recordset
'Code here for Getting SR from form
strSR = Nz(frm![txtSR], "")
If strSR = "" Then
MsgBox "Please enter SR number and try again", vbOKOnly
Exit Sub
Else
sqlSR = "(((VRSC_MCS_SVC_REQ.MSR_SR_NUM)=" & strSR & ")"
'Get the SR data from SMS as a record set
rst.Open "SELECT VRSC_MCS_SVC_REQ.MSR_SR_NUM AS SR,
VRSC_MCS_SVC_REQ.MSR_RESP AS Response, " _
& "Max(VRSC_MCS_SVC_REQ_ACTION.MSRA_ACTION_NUM) AS Trip,
VRSC_MCS_SVC_REQ.MSR_TICKET_NUM AS Ticket, " _
& "VRSC_MCS_SVC_REQ.MSR_OPEN_DATE_TIME AS [Open Date],
Min(VRSC_MCS_SVC_REQ_ACTION.MSRA_ARRIVE_TIME) " _
& "AS [Work Start], Max(VRSC_MCS_SVC_REQ_ACTION.MSRA_DEPART_TIME) AS [Work
End], " _
& "First(VRSC_MCS_SVC_REQ.MSR_COMMENTS) AS Comments, " _
&
"(Max([VRSC_MCS_SVC_REQ_ACTION].[MSRA_DEPART_TIME])-Max([VRSC_MCS_SVC_REQ].[MSR_OPEN_DATE_TIME]))*24 AS KPI " _
& "FROM VRSC_MCS_SVC_REQ INNER JOIN VRSC_MCS_SVC_REQ_ACTION ON
VRSC_MCS_SVC_REQ.MSR_SR_NUM = " _
& "VRSC_MCS_SVC_REQ_ACTION.MSRA_SR_NUM GROUP BY
VRSC_MCS_SVC_REQ.MSR_SR_NUM, " _
& "VRSC_MCS_SVC_REQ.MSR_RESP, VRSC_MCS_SVC_REQ.MSR_TICKET_NUM, " _
& "VRSC_MCS_SVC_REQ.MSR_OPEN_DATE_TIME, VRSC_MCS_SVC_REQ.MSR_CUST_ID,
VRSC_MCS_SVC_REQ.MSR_SR_STATUS " _
& "HAVING " & sqlSR & " AND ((VRSC_MCS_SVC_REQ.MSR_CUST_ID)='SG02222'))", _
cnn, adOpenForwardOnly, adLockReadOnly
End If
'Check to make the recordset has data otherwise give error message
If Not rst.BOF And Not rst.EOF Then
rst.MoveFirst
dtSMSDate = rst.Fields("Open Date")
' check business day and time
If BusinessDay(dtSMSDate) = True Then
--snip---
Else
'get next business day
--snip--
End If
' Check number of trips and display data as needed.
If rst.Fields("Trip") > 1 Then
' If multiple trip display message
frm![txtInfo] = "There are multiple trips. Do not enter data before
checking with supervisor."
Else
' If single trip check for pass fail and display data
frm![txtInfo] = "Use information below to update."
frm![txtTicket] = rst.Fields("Ticket")
frm![txtWorkStart] = Format(rst.Fields("Work Start"), "Short Date")
frm![txtWorkEnd] = Format(rst.Fields("Work End"), "Short Date")
frm![txtComments] = rst.Fields("Comments")
End If
Else
MsgBox "There is no data for this SR number. Please try again", vbOKOnly
Exit Sub
End If
Exit_cmdSSInfo_Click:
Exit Sub
Err_cmdSSInfo_Click:
MsgBox Err.Description
Resume Exit_cmdSSInfo_Click
End Sub