"Out of Memory" Problem

[mgarrett]

I am having a problem assembling a string value.

strCompleteString = strSubString1 & strSubString2

When I get to this line in my code the value returned to strCompleteString is "Out of Memory", instead of the result of strSubString1 & strSubString2.

I have never encountered this before. I am using Windows XP Pro (SP2) and Access XP (2002).

Any help would be greatly appreciated.

 

[Ed2020]

Can you post the whole of your procedure and also give some indication of the contents of strSubString1 and strSubString2 (if this is not obvious from the procedure itself).

Ed Metcalfe.

Please do not feed the trolls.....

 

[mgarrett]

Public Sub Reviewer_Notifications(strReview_Type As String)
'*******************************************************************************************
Dim intUser_ID As Integer

Dim strUser_Status As String
Dim strUser_EMail_Address As String

Dim strMessage_Title As String
Dim strMessage_Text As String

Dim strWork_String As String
Dim strSQL_Statement As String

Dim rstUser_Property As Recordset
Dim rstUser_Account As Recordset

Set gdbCurrent_Database = CurrentDb '<<<<<TEST
'*******************************************************************************************
' Build the message title and text
strMessage_Title = ""
strMessage_Text = ""
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*
Select Case strReview_Type

Case "APPS"
strMessage_Title = "Material Review: Applications"
strMessage_Text = "We have completed the scanning of a batch of applications. " _
& Format(Now(), "dddd, h:nn AMPM")

Case "DOCS"
strMessage_Title = "Material Review: Documents"
strMessage_Text = "We have completed the scanning of a batch of documents. " _
& Format(Now(), "dddd, h:nn AMPM")

Case "TRANS"
strMessage_Title = "Material Review: Transcripts"
strMessage_Text = "We have completed the scanning of a batch of transcripts. " _
& Format(Now(), "dddd, h:nn AMPM")

End Select
'------------------------------------------------------------------------------------------*
strSQL_Statement = "SELECT * FROM tblCSUP_User_Property_Control " _
& "WHERE ( CSUP_property_category = " _
& Chr(34) & "MATRV-PEND" & Chr(34) & ") " _
& "AND ( CSUP_property_item = " _
& Chr(34) & Trim(strReview_Type) & Chr(34) & ");"

Set rstUser_Property = gdbCurrent_Database.OpenRecordset(strSQL_Statement, _
dbOpenSnapshot, _
dbReadOnly, _
dbReadOnly)

Do While Not rstUser_Property.EOF
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*
intUser_ID = Nz(rstUser_Property!CSUP_user_id, 0)
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*
' Access the User Record
strUser_EMail_Address = ""

strSQL_Statement = "SELECT * FROM tblAUA_User_Administration " _
& "WHERE ( AUA_user_id = " & Str(intUser_ID) & ") " _
& "AND ( AUA_user_active_flag = " & Chr(34) & "A" & Chr(34) & ") " _
& "ORDER BY AUA_user_id ASC;"

Set rstUser_Account = gdbCurrent_Database.OpenRecordset(strSQL_Statement, _
dbOpenSnapshot, _
dbReadOnly, _
dbReadOnly)

If Not rstUser_Account Then
strUser_EMail_Address = Trim(Nz(rstUser_Account!AUA_email_address, ""))
End If

rstUser_Account.Close
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*
' If there is NO email Address, bypass the User Account
If Trim(strUser_EMail_Address) = "" Then
GoTo Next_User_Property_Record
End If
' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -*
' Send the message
strSQL_Statement = "INSERT INTO eforms_tblXMO_Email_OutBound (" _ <---- This is where the problem is
& "XMO_Email_Address, " _
& "XMO_Email_Subject, " _
& "XMO_Email_Content" _
& ") VALUES (" _
& Chr(34) & Trim(strUser_EMail_Address) & Chr(34) & ", " _
& Chr(34) & Trim(strMessage_Title) & Chr(34) & ", " _
& Chr(34) & Trim(strMessage_Text) & Chr(34) _
& ");"

MsgBox strMessage_Text
MsgBox strSQL_Statement
gdbCurrent_Database.Execute strSQL_Statement
'------------------------------------------------------------------------------------------*
Next_User_Property_Record:
'========================

rstUser_Property.MoveNext

Loop
'------------------------------------------------------------------------------------------*
rstUser_Property.Close
'*******************************************************************************************
End Sub

 

[Ed2020]

Odd, although I can't execute all of your code as I don't have the necessary tables, I can't see anything wrong with it.

Unless the variable strMessage_Text contains a *very* long string I can't see what would be causing this.

Is the error occurring on any particular record in

Could you post some example values of what would be stored in strUser_EMail_Address, strMessage_Title and strMessage_Text? Obviously the email address will need to be fudged so you're not posting a valid address...

Ed Metcalfe.

Please do not feed the trolls.....

 

[Ed2020]

Odd, although I can't execute all of your code as I don't have the necessary tables, I can't see anything wrong with it.

Unless the variable strMessage_Text contains a *very* long string I can't see what would be causing this.

Is the error occurring on any particular record?

Could you post some example values of what would be stored in strUser_EMail_Address, strMessage_Title and strMessage_Text? Obviously the email address will need to be fudged so you're not posting a valid address...

Ed Metcalfe.

Please do not feed the trolls.....

 

[xlbo]

only thing I can see is that you never set your recordset variables to nothing to releasse them from memory. Other than that, I'm with Ed - can't see much wrong unless you have a very long string...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JerryKlmns]

And if tblAUA_User_Administration has the AUA_user_id as a PK then a functiona to retrieve strMessage_Title & strMessage_Text along with an INSERT INTO sql statement should accomplish all the above in one step execute method!

 

[mgarrett]

Example e-mail address would be something like m.garrett@state.us

Message Title: "Material Review: Applications"

Message Text: "We have completed the scanning of a batch of applications. 08/29/07 8:40:00 AM"

When Access tries to build that string I will get random results. Sometimes it will be blank other times it will contain garbage characters and other times it will return a value of "Out of Memory"

It is like the memory leak problem or the memory pointer is getting confused. We use similar code to this in other places in our database and it works fine. For some reason this combination of code Access just seems to be having a problem with.

 

[mgarrett]

We have resolved this problem. It seems that there was an undisplayable character somewhere in there. We rekeyed everything and it worked fine.

Thanks for everyones help.