getting right string in table

  • Thread starter Thread starter vtj
  • Start date Start date
V

vtj

I am trying to copy a memo field from a table and paste it into a Notepad
document. The field is build in VBA to include commas and quotes. If I copy
it from the table I get "IN(""070"", ""100"", ""130"")". If I use the
Debug.Print line from the immediate window I get IN("070", "100", "130")
which is what I want to get from the table. What am I doing wrong? This is
in A2007 and the code is as follows;

Public Function MakeGFFundList()
Dim list1 As String
Dim db1 As DAO.Database
Set db1 = CurrentDb()
Dim rst1 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim rst9 As DAO.Recordset

Set rst1 = db1.OpenRecordset("FUNDS IN LLBC", dbOpenDynaset)
rst1.MoveFirst
list1 = "IN" & Chr(40) & Chr(34) & rst1![Fund_cd] & Chr(34)
detail1:
rst1.MoveNext
If rst1.EOF Then GoTo close1
list1 = list1 & ", " & Chr(34) & rst1![Fund_cd] & Chr(34)
GoTo detail1

close1:
list1 = list1 & Chr(41)
Debug.Print list1
rst1.MoveFirst
rst1.MoveNext
rst1.Edit
rst1!FUND_list = list1
rst1.Update
Set rst1 = Nothing
Set db1 = Nothing
End Function
 
I'm not sure (at first glance) but your code is NASTY. I suggest you clean up
your logic a bit, which would make it easier to debug and understand.

----------------------------------
OpenParen = chr$(40)
CloseParen = chr$(41)
Quotes = chr$(34)

list1 = ""

Set rst1 = db1.OpenRecordset("FUNDS IN LLBC", dbOpenDynaset)

for i = 1 to rst1.RecordCount
if lst1 = "" then
list1 = "IN" & OpenParen & Quote & rst1![Fund_cd] & Quote
else
list1 = list1 & ", " & Quote & rst1![Fund_cd] & Quote
end if

rst1.MoveNext
Next i

rst1.close
set rst1 = Nothing

if list1 <> "" then list1 = list1 & CloseParen
----------------------------

I have NO CLUE what your Close1 section is doing. It appears that you're
moving to the first record in the recordset, then advancing to the second
record. (Why you just don't poisition the pointer to AbsolutePosition = 2 is
beyond me.) Once at the second record, you are attempting to update the
FUND_list column. From what I see in that code, it should work properly.
However, I'd leave off the "rst1.EDIT", because changing a column's value
implies edit. The UPDATE command will post the new value to the record.
 
Dennis, Thanks for your response. I'll admit my code is nasty if you'll
admit that VB is not a very easy to learn language (illogical). The purpose
of the code was to fill a memo field with all of another field from several
records (130 to be exact) that could then easily be picked out of the table
and put in text documents. There are several different iterations that go
into the first record, the second record and so on. It was quick and dirty
to do MoveNext as I copied the code for different iterations. The Close1
happened when end of file was reached and put the closing parens on the
string and updated the appropriate record. I do appreciate the education in
defining ASCII characters to words. That will be very useful. However I
still would like to know why I'm getting double quotation marks around each
entry when I copy it from the table and single quotation marks when I copy it
from the immediate window.

Dennis said:
I'm not sure (at first glance) but your code is NASTY. I suggest you clean up
your logic a bit, which would make it easier to debug and understand.

----------------------------------
OpenParen = chr$(40)
CloseParen = chr$(41)
Quotes = chr$(34)

list1 = ""

Set rst1 = db1.OpenRecordset("FUNDS IN LLBC", dbOpenDynaset)

for i = 1 to rst1.RecordCount
if lst1 = "" then
list1 = "IN" & OpenParen & Quote & rst1![Fund_cd] & Quote
else
list1 = list1 & ", " & Quote & rst1![Fund_cd] & Quote
end if

rst1.MoveNext
Next i

rst1.close
set rst1 = Nothing

if list1 <> "" then list1 = list1 & CloseParen
----------------------------

I have NO CLUE what your Close1 section is doing. It appears that you're
moving to the first record in the recordset, then advancing to the second
record. (Why you just don't poisition the pointer to AbsolutePosition = 2 is
beyond me.) Once at the second record, you are attempting to update the
FUND_list column. From what I see in that code, it should work properly.
However, I'd leave off the "rst1.EDIT", because changing a column's value
implies edit. The UPDATE command will post the new value to the record.



vtj said:
I am trying to copy a memo field from a table and paste it into a Notepad
document. The field is build in VBA to include commas and quotes. If I copy
it from the table I get "IN(""070"", ""100"", ""130"")". If I use the
Debug.Print line from the immediate window I get IN("070", "100", "130")
which is what I want to get from the table. What am I doing wrong? This is
in A2007 and the code is as follows;

Public Function MakeGFFundList()
Dim list1 As String
Dim db1 As DAO.Database
Set db1 = CurrentDb()
Dim rst1 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim rst9 As DAO.Recordset

Set rst1 = db1.OpenRecordset("FUNDS IN LLBC", dbOpenDynaset)
rst1.MoveFirst
list1 = "IN" & Chr(40) & Chr(34) & rst1![Fund_cd] & Chr(34)
detail1:
rst1.MoveNext
If rst1.EOF Then GoTo close1
list1 = list1 & ", " & Chr(34) & rst1![Fund_cd] & Chr(34)
GoTo detail1

close1:
list1 = list1 & Chr(41)
Debug.Print list1
rst1.MoveFirst
rst1.MoveNext
rst1.Edit
rst1!FUND_list = list1
rst1.Update
Set rst1 = Nothing
Set db1 = Nothing
End Function
Click to expand...
Click to expand...
 
VB isn't hard to learn in-and-of itself. Object oriented languages IN GENERAL
are difficult to learn for those who've had little or no exposure to them.
(Ask me how I know this; I'm an old mainframe COBOL programmer who had to
re-learn my career.)

Getting to specifics - the code I re-wrote will provide you with a string
like you want. The immediate window shows the EXACT contents of the string.
If there are quotes around the string in the final destination, that's not
something the code did, but a function of either the field type or the tool
itself. So you cannot look to the code to provide a resolution to your
problem. Is this going into an Excel spreadsheet by chance? Or a text-file?
Or are you perhaps exporting it to an external file? Because I have
personally seen your symptoms under those conditions.



vtj said:
Dennis, Thanks for your response. I'll admit my code is nasty if you'll
admit that VB is not a very easy to learn language (illogical). The purpose
of the code was to fill a memo field with all of another field from several
records (130 to be exact) that could then easily be picked out of the table
and put in text documents. There are several different iterations that go
into the first record, the second record and so on. It was quick and dirty
to do MoveNext as I copied the code for different iterations. The Close1
happened when end of file was reached and put the closing parens on the
string and updated the appropriate record. I do appreciate the education in
defining ASCII characters to words. That will be very useful. However I
still would like to know why I'm getting double quotation marks around each
entry when I copy it from the table and single quotation marks when I copy it
from the immediate window.

Dennis said:
I'm not sure (at first glance) but your code is NASTY. I suggest you clean up
your logic a bit, which would make it easier to debug and understand.

----------------------------------
OpenParen = chr$(40)
CloseParen = chr$(41)
Quotes = chr$(34)

list1 = ""

Set rst1 = db1.OpenRecordset("FUNDS IN LLBC", dbOpenDynaset)

for i = 1 to rst1.RecordCount
if lst1 = "" then
list1 = "IN" & OpenParen & Quote & rst1![Fund_cd] & Quote
else
list1 = list1 & ", " & Quote & rst1![Fund_cd] & Quote
end if

rst1.MoveNext
Next i

rst1.close
set rst1 = Nothing

if list1 <> "" then list1 = list1 & CloseParen
----------------------------

I have NO CLUE what your Close1 section is doing. It appears that you're
moving to the first record in the recordset, then advancing to the second
record. (Why you just don't poisition the pointer to AbsolutePosition = 2 is
beyond me.) Once at the second record, you are attempting to update the
FUND_list column. From what I see in that code, it should work properly.
However, I'd leave off the "rst1.EDIT", because changing a column's value
implies edit. The UPDATE command will post the new value to the record.



vtj said:
I am trying to copy a memo field from a table and paste it into a Notepad
document. The field is build in VBA to include commas and quotes. If I copy
it from the table I get "IN(""070"", ""100"", ""130"")". If I use the
Debug.Print line from the immediate window I get IN("070", "100", "130")
which is what I want to get from the table. What am I doing wrong? This is
in A2007 and the code is as follows;

Public Function MakeGFFundList()
Dim list1 As String
Dim db1 As DAO.Database
Set db1 = CurrentDb()
Dim rst1 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim rst9 As DAO.Recordset

Set rst1 = db1.OpenRecordset("FUNDS IN LLBC", dbOpenDynaset)
rst1.MoveFirst
list1 = "IN" & Chr(40) & Chr(34) & rst1![Fund_cd] & Chr(34)
detail1:
rst1.MoveNext
If rst1.EOF Then GoTo close1
list1 = list1 & ", " & Chr(34) & rst1![Fund_cd] & Chr(34)
GoTo detail1

close1:
list1 = list1 & Chr(41)
Debug.Print list1
rst1.MoveFirst
rst1.MoveNext
rst1.Edit
rst1!FUND_list = list1
rst1.Update
Set rst1 = Nothing
Set db1 = Nothing
End Function
Click to expand...
Click to expand...
Click to expand...
 
One more thing:

it is generally accepted in the IT industry that coding with GOTOs is
considered an extremely poor methodology, and is generally used by people who
don't know programming well, including looping and loop control.

Not trying to be a dick here; I'm just sayin'.

FYI
 
Thank you very much! I didn't think about the process of copying and pasting
creating the problem. I am also an old COBOL programmer and generally use
GOTO in only two circumstances. One where I have to Jump over intervening
code when something unusal occurs or when I'm doing a 'quick and dirty'
because they are much easier for me personnally to follow. I do appreciate
the input - it is the only way I'll hopefully eventually get to do these
things reasonably well.
 
Back
Top