ADO Recordset truncating memo field

G

Guest

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
 
G

Guest

I think the problem stemmed from grouping the recordset. I removed the memo
field from that sql and made a separate recordset for it with no grouping.
That was not truncated! And I was able to display it in the form with no
problems.

So I've worked around it, but I'd still like to know how to modify the field
data type if possible.

Thanks to anyone who read this problem.

Karrie

KJGinNC said:
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
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

ADO Open command fails on multiple SQL functions 1

Top