Data truncated in text box on form

[Mark]

I have researched the various issues as it relates to truncation of memo
fields. I read Allen Browne's fine treatise on the subject. However I am
experiencing something I can't quite understand or correct. Here goes.

I have a form with a text box control on it. There is a query that runs that
populates this text box. The table the query runs against has a field
formatted as memo and the data looks fine in there. When I run the query on
its own the memo fields appear in their entirety. As a note, within this
query I do not use the "Group By" sorting. However, when I look at the
contents of that field in the text box within the form, it's truncating at
255 characters. I have no formatting options assigned to the text box, or
within the query, or the table containing this memo field. I have Can Grow
set to yes within the text box. Also, the field within the table that is
being updated via this form has a Data Type set to memo as well. And of
course the data is truncated in here despite that. I think if we can resolve
the truncation in the text box on the form this field should contain all the
data. I hope.

Can someone please resolve this for me?

Thanks

Mark

 

[Mark]

After further research it appears its the VBA code that is populating the
text box that might be causing the problem. How do I allow the VBA code to
allow all characters within a memo field? Here is what I have:

Dim DB As Database
Dim rs As Recordset
Set DB = CurrentDb
Set rs = DB.OpenRecordset("tblSoxSummaryData")
Forms!frmSoxSummaryData!Cntrl_Desc = cboCntrl_Num.Column(1)
Forms!frmSoxSummaryData!Process = cboCntrl_Num.Column(2)
Set rs = Nothing
Set DB = Nothing
End Sub

 

[Bob Quintal]

After further research it appears its the VBA code that is
populating the text box that might be causing the problem. How do
I allow the VBA code to allow all characters within a memo field?
Here is what I have:

Dim DB As Database
Dim rs As Recordset
Set DB = CurrentDb
Set rs = DB.OpenRecordset("tblSoxSummaryData")
Forms!frmSoxSummaryData!Cntrl_Desc = cboCntrl_Num.Column(1)
Forms!frmSoxSummaryData!Process = cboCntrl_Num.Column(2)
Set rs = Nothing
Set DB = Nothing
End Sub

The source of the truncation is the combobox. You want to organize
the code differently, because as it is, it's not doing what you
think it does, or you cut out some important parts of it.


Dim DB As Database
Dim rs As Recordset
Dim strSQL as string
Set DB = CurrentDb
strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num = " _
& cboCntrl_Num
Set rs = DB.OpenRecordset(strSQL)
Forms!frmSoxSummaryData!Cntrl_Desc = rs!Cntrl_Desc
Forms!frmSoxSummaryData!Process = Process
Set rs = Nothing
Set DB = Nothing
End Sub

 

[Mark]

This is the code in its entirety. Not all fields are memo fields however.
Any further coding changes based on this? I am simply populating different
text boxes/fields based on the query called in the cboCntrl_Num. I will try
your code changes in the meantime. Thanks

Mark

Private Sub cboCntrl_Num_AfterUpdate()
'assigning form field values based on combo box selection for Control data

Dim DB As Database
Dim rs As Recordset
Set DB = CurrentDb
Set rs = DB.OpenRecordset("tblSoxSummaryData")
Forms!frmSoxSummaryData!Cntrl_Desc = cboCntrl_Num.Column(1)
Forms!frmSoxSummaryData!Process = cboCntrl_Num.Column(2)
Forms!frmSoxSummaryData!Sub_Process = cboCntrl_Num.Column(3)
Forms!frmSoxSummaryData!PD = cboCntrl_Num.Column(4)
Forms!frmSoxSummaryData!Cntrl_Environ = cboCntrl_Num.Column(5)
Forms!frmSoxSummaryData!Info_Comm = cboCntrl_Num.Column(6)
Forms!frmSoxSummaryData!Monitoring = cboCntrl_Num.Column(7)
Forms!frmSoxSummaryData!Risk_Assess = cboCntrl_Num.Column(8)
Forms!frmSoxSummaryData!Completeness = cboCntrl_Num.Column(9)
Forms!frmSoxSummaryData!Val_Alloc = cboCntrl_Num.Column(10)
Forms!frmSoxSummaryData!Ext_Occur = cboCntrl_Num.Column(11)
Forms!frmSoxSummaryData!Rights_Obl = cboCntrl_Num.Column(12)
Forms!frmSoxSummaryData!Present_Discl = cboCntrl_Num.Column(13)
Forms!frmSoxSummaryData!Res_Access = cboCntrl_Num.Column(14)
Forms!frmSoxSummaryData!SOD = cboCntrl_Num.Column(15)
Forms!frmSoxSummaryData!Safe_Assets = cboCntrl_Num.Column(16)
Forms!frmSoxSummaryData!Anti_Fraud = cboCntrl_Num.Column(17)
Forms!frmSoxSummaryData!Cntrl_Type = cboCntrl_Num.Column(18)

Set rs = Nothing
Set DB = Nothing
End Sub

 

[Bob Quintal]

This is the code in its entirety. Not all fields are memo fields
however. Any further coding changes based on this? I am simply
populating different text boxes/fields based on the query called
in the cboCntrl_Num. I will try your code changes in the meantime.
Thanks

Mark

The code below opens a recordset, then populates all the rows from
the combobox, closes the recordset and exits without using the
recordset.

I'd just use the combo box to indicate the selected record, and then
open the recordset already filtered to return the one row.
If your combobox columns are in the same order as the fields in the
table, you could just change the cboCntrl_Num.Column(n) to refer to
the recordset as rs.fields(n)

a 30 second cut and paste jobbie, if it's the case.

Q

Private Sub cboCntrl_Num_AfterUpdate()
'assigning form field values based on combo box selection for
Control data

Dim DB As Database
Dim rs As Recordset
Set DB = CurrentDb
Set rs = DB.OpenRecordset("tblSoxSummaryData")
Forms!frmSoxSummaryData!Cntrl_Desc = cboCntrl_Num.Column(1)
Forms!frmSoxSummaryData!Process = cboCntrl_Num.Column(2)
Forms!frmSoxSummaryData!Sub_Process = cboCntrl_Num.Column(3)
Forms!frmSoxSummaryData!PD = cboCntrl_Num.Column(4)
Forms!frmSoxSummaryData!Cntrl_Environ =
cboCntrl_Num.Column(5)

Forms!frmSoxSummaryData!Info_Comm =

cboCntrl_Num.Column(6)

Forms!frmSoxSummaryData!Monitoring =

cboCntrl_Num.Column(7)

Forms!frmSoxSummaryData!Risk_Assess =

cboCntrl_Num.Column(8)

Forms!frmSoxSummaryData!Completeness =

cboCntrl_Num.Column(9)

Forms!frmSoxSummaryData!Val_Alloc =

cboCntrl_Num.Column(10)

Forms!frmSoxSummaryData!Ext_Occur =

cboCntrl_Num.Column(11)

Forms!frmSoxSummaryData!Rights_Obl =

cboCntrl_Num.Column(12) Forms!frmSoxSummaryData!Present_Discl
= cboCntrl_Num.Column(13) Forms!frmSoxSummaryData!Res_Access
= cboCntrl_Num.Column(14) Forms!frmSoxSummaryData!SOD =
cboCntrl_Num.Column(15)

Forms!frmSoxSummaryData!Safe_Assets =

cboCntrl_Num.Column(16)

Forms!frmSoxSummaryData!Anti_Fraud =

cboCntrl_Num.Column(17)

Forms!frmSoxSummaryData!Cntrl_Type =

cboCntrl_Num.Column(18)

Set rs = Nothing
Set DB = Nothing
End Sub

Bob Q

 

[Mark]

Bob, thanks for your response. Bear with me as I am not yet quite an ace
programmer but I hope to be. Trust me I love the cut and paste jobs but I
want to understand as well. Just to confirm, using the code you provided me.
Would it look like this basically. My previous code was working alright
except of course when it came to processing my memo fields. Your code will
work around the 255 char limit I presume? Thank you!

Mark

would the new code look like this and basically replace what I have:

Dim DB As Database
Dim rs As Recordset
Dim strSQL as string
Set DB = CurrentDb
strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num = " _
& cboCntrl_Num
Set rs = DB.OpenRecordset(strSQL)
Forms!frmSoxSummaryData!Cntrl_Desc = rs!Cntrl_Desc
Forms!frmSoxSummaryData!Process = rs!Process
Forms!frmSoxSummaryData!Process = rs!PD
Forms!frmSoxSummaryData!Process = rs!Cntrl_Environ
etc.
Set rs = Nothing
Set DB = Nothing
End Sub

 

[Bob Quintal]

Bob, thanks for your response. Bear with me as I am not yet quite
an ace programmer but I hope to be. Trust me I love the cut and
paste jobs but I want to understand as well. Just to confirm,
using the code you provided me. Would it look like this basically.
My previous code was working alright except of course when it came
to processing my memo fields. Your code will work around the 255
char limit I presume? Thank you!

Mark

yes the code works around the 255 character limit. The code looks
good.

Good luck

Q

would the new code look like this and basically replace what I
have:

Dim DB As Database
Dim rs As Recordset
Dim strSQL as string
Set DB = CurrentDb
strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num = " _
& cboCntrl_Num
Set rs = DB.OpenRecordset(strSQL)
Forms!frmSoxSummaryData!Cntrl_Desc = rs!Cntrl_Desc
Forms!frmSoxSummaryData!Process = rs!Process
Forms!frmSoxSummaryData!Process = rs!PD
Forms!frmSoxSummaryData!Process = rs!Cntrl_Environ
etc.
Set rs = Nothing
Set DB = Nothing
End Sub

The code below opens a recordset, then populates all the rows
from the combobox, closes the recordset and exits without using
the recordset.

I'd just use the combo box to indicate the selected record, and
then open the recordset already filtered to return the one row.
If your combobox columns are in the same order as the fields in
the table, you could just change the cboCntrl_Num.Column(n) to
refer to the recordset as rs.fields(n)

a 30 second cut and paste jobbie, if it's the case.

Q
Forms!frmSoxSummaryData!Info_Comm =
Forms!frmSoxSummaryData!Monitoring =
Forms!frmSoxSummaryData!Risk_Assess =
Forms!frmSoxSummaryData!Completeness =
Forms!frmSoxSummaryData!Val_Alloc =
Forms!frmSoxSummaryData!Ext_Occur =
Forms!frmSoxSummaryData!Rights_Obl =
Forms!frmSoxSummaryData!Safe_Assets =
Forms!frmSoxSummaryData!Anti_Fraud =
Forms!frmSoxSummaryData!Cntrl_Type =


Bob Q

 

[Mark]

Bob, here is my new code. I am getting the following error however: Run-time
error '3061' too few parameters. Expected 2. This is happening on this line
of code:
Set rs = DB.OpenRecordset(strSQL)

This is the code in its entirety:

Dim DB As Database
Dim rs As Recordset
Dim strSQL As String
Set DB = CurrentDb
strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num = " _
& cboCntrl_Num
Set rs = DB.OpenRecordset(strSQL)
Forms!frmSoxSummaryData!Cntrl_Desc = rs!Cntrl_Desc
Forms!frmSoxSummaryData!Process = rs!Process
Forms!frmSoxSummaryData!Sub_Process = rs!Sub_Process
Forms!frmSoxSummaryData!PD = rs!PD
Forms!frmSoxSummaryData!Cntrl_Environ = rs!Cntrl_Environ
Forms!frmSoxSummaryData!Info_Comm = rs!Info_Comm
Forms!frmSoxSummaryData!Monitoring = rs!Monitoring
Forms!frmSoxSummaryData!Risk_Assess = rs!Risk_Assess
Forms!frmSoxSummaryData!Completeness = rs!Completness
Forms!frmSoxSummaryData!Val_Alloc = rs!Val_Alloc
Forms!frmSoxSummaryData!Ext_Occur = rs!Ext_Occur
Forms!frmSoxSummaryData!Rights_Obl = rs!Rights_Obl
Forms!frmSoxSummaryData!Present_Discl = rs!Present_Discl
Forms!frmSoxSummaryData!Res_Access = rs!Res_Access
Forms!frmSoxSummaryData!SOD = rs!SOD
Forms!frmSoxSummaryData!Safe_Assets = rs!Safe_Assets
Forms!frmSoxSummaryData!Anti_Fraud = rs!Anti_Fraud
Forms!frmSoxSummaryData!Cntrl_Type = rs!Cntrl_Type
Set rs = Nothing
Set DB = Nothing
End Sub

Thanks

Bob, thanks for your response. Bear with me as I am not yet quite
an ace programmer but I hope to be. Trust me I love the cut and
paste jobs but I want to understand as well. Just to confirm,
using the code you provided me. Would it look like this basically.
My previous code was working alright except of course when it came
to processing my memo fields. Your code will work around the 255
char limit I presume? Thank you!

Mark

yes the code works around the 255 character limit. The code looks
good.

Good luck

Q

 

[Bob Quintal]

Bob, here is my new code. I am getting the following error
however: Run-time error '3061' too few parameters. Expected 2.
This is happening on this line of code:
Set rs = DB.OpenRecordset(strSQL)

A fer possibilities:

Is Cntrl_num actually a text field, not a number?
If it is, then
strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num = """ _
& cboCntrl_Num & """"

There may be a conflict between adoDb and dao in the type of
recordset created in Access 2000, explicitly declare

Dim rs As dao.Recordset

This is the code in its entirety:

Dim DB As Database
Dim rs As Recordset
Dim strSQL As String
Set DB = CurrentDb
strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num = "
_
& cboCntrl_Num
Set rs = DB.OpenRecordset(strSQL)
Forms!frmSoxSummaryData!Cntrl_Desc = rs!Cntrl_Desc
Forms!frmSoxSummaryData!Process = rs!Process
Forms!frmSoxSummaryData!Sub_Process = rs!Sub_Process
Forms!frmSoxSummaryData!PD = rs!PD
Forms!frmSoxSummaryData!Cntrl_Environ = rs!Cntrl_Environ
Forms!frmSoxSummaryData!Info_Comm = rs!Info_Comm
Forms!frmSoxSummaryData!Monitoring = rs!Monitoring
Forms!frmSoxSummaryData!Risk_Assess = rs!Risk_Assess
Forms!frmSoxSummaryData!Completeness = rs!Completness
Forms!frmSoxSummaryData!Val_Alloc = rs!Val_Alloc
Forms!frmSoxSummaryData!Ext_Occur = rs!Ext_Occur
Forms!frmSoxSummaryData!Rights_Obl = rs!Rights_Obl
Forms!frmSoxSummaryData!Present_Discl = rs!Present_Discl
Forms!frmSoxSummaryData!Res_Access = rs!Res_Access
Forms!frmSoxSummaryData!SOD = rs!SOD
Forms!frmSoxSummaryData!Safe_Assets = rs!Safe_Assets
Forms!frmSoxSummaryData!Anti_Fraud = rs!Anti_Fraud
Forms!frmSoxSummaryData!Cntrl_Type = rs!Cntrl_Type
Set rs = Nothing
Set DB = Nothing
End Sub

Thanks

yes the code works around the 255 character limit. The code looks
good.

Good luck

Q

 

[Mark]

Bob, sorry for the confusion. Cntrl_num is a text field. I should have
stipulated that. I'll try the new changes.

Thanks

Bob, here is my new code. I am getting the following error
however: Run-time error '3061' too few parameters. Expected 2.
This is happening on this line of code:
Set rs = DB.OpenRecordset(strSQL)

A fer possibilities:

Is Cntrl_num actually a text field, not a number?
If it is, then
strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num = """ _
& cboCntrl_Num & """"

There may be a conflict between adoDb and dao in the type of
recordset created in Access 2000, explicitly declare

Dim rs As dao.Recordset

 

[Mark]

We're getting closer. Still getting Run-time error '3061' but this time it
says Expected 1 instead of 2. So we made some progress there. I made all the
changes you suggested. I appreciate your assistance.

Mark

Bob, here is my new code. I am getting the following error
however: Run-time error '3061' too few parameters. Expected 2.
This is happening on this line of code:
Set rs = DB.OpenRecordset(strSQL)

A fer possibilities:

Is Cntrl_num actually a text field, not a number?
If it is, then
strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num = """ _
& cboCntrl_Num & """"

There may be a conflict between adoDb and dao in the type of
recordset created in Access 2000, explicitly declare

Dim rs As dao.Recordset

 

[Bob Quintal]

We're getting closer. Still getting Run-time error '3061' but this
time it says Expected 1 instead of 2. So we made some progress
there. I made all the changes you suggested. I appreciate your
assistance.

Mark

I really don't know, but let's try a few things to debug this.

Temporarily change the strSQL to
"SELECT * from tblSoxSummaryData " _
& "WHERE Cntrl_num = 'ABC'"

I doubt it will return anything, but see if the error message goes
away. If that gives no error message, it would indicate that
cboCntrl_Num is returning a Null and not a real value.

if it still gives an error message, let's add the supposedly
optional parameter to the OpenRecordset method
Set rs = DB.OpenRecordset(strSQL,dbForwardOnly)

else if it works, change ABC to a real control number and see if
that works.


hth,
Q

A fer possibilities:

Is Cntrl_num actually a text field, not a number?
If it is, then
strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num = """
_
& cboCntrl_Num & """"

There may be a conflict between adoDb and dao in the type of
recordset created in Access 2000, explicitly declare

Dim rs As dao.Recordset

 

[Mark]

Bob, you just might be a candidate for sainthood.

At any rate I managed to make the errors go away. The code is running
without error but the memo field (Cntrl_Desc) is still getting truncated.
That's the key issue here as I need those memo fields in their entirety.
Unless of course I made a change that you don't agree with. Here is the code
now. All I want is for the text boxes on my form to populate correctly and
then of course update the tblSoxSummaryData table.

As an FYI the query below runs as a row source within the cboCntrl_Num combo
box. This query shows the memo fields just fine. Let me know if it's easier
to just send you the database at this point.

SELECT tblSOXControls.FY08CntrlNum, tblSOXControls.FY08CntrlDescr,
tblSOXControls.Process, tblSOXControls.SubProcess, tblSOXControls.PD,
tblSOXControls.CntrlEnviron, tblSOXControls.InfoComm,
tblSOXControls.Monitoring, tblSOXControls.RiskAssess,
tblSOXControls.Completeness, tblSOXControls.ValAlloc,
tblSOXControls.ExtOccur, tblSOXControls.RightsObl,
tblSOXControls.PresentDiscl, tblSOXControls.ResAccess, tblSOXControls.SOD,
tblSOXControls.SafeAssets, tblSOXControls.AntiFraud,
tblSOXControls.CntrlType FROM tblSOXControls;

Here is the new and improved VBA that is still truncating the memo fields.

Dim DB As dao.Database
Dim rs As dao.Recordset
Dim strSQL As String
Set DB = CurrentDb
strSQL = "SELECT * FROM [tblSoxSummaryData] WHERE [Cntrlnum] ='" _
& Me.cboCntrl_Num & "'"

Set rs = DB.OpenRecordset(strSQL)
Forms!frmSoxSummaryData!Cntrl_Desc = rs!CntrlDesc
Forms!frmSoxSummaryData!Process = rs!Process
Forms!frmSoxSummaryData!Sub_Process = rs!SubProcess
Forms!frmSoxSummaryData!PD = rs!PD
Forms!frmSoxSummaryData!Cntrl_Environ = rs!CntrlEnviron
Forms!frmSoxSummaryData!Info_Comm = rs!InfoComm
Forms!frmSoxSummaryData!Monitoring = rs!Monitoring
Forms!frmSoxSummaryData!Risk_Assess = rs!RiskAssess
Forms!frmSoxSummaryData!Completeness = rs!Completeness
Forms!frmSoxSummaryData!Val_Alloc = rs!ValAlloc
Forms!frmSoxSummaryData!Ext_Occur = rs!ExtOccur
Forms!frmSoxSummaryData!Rights_Obl = rs!RightsObl
Forms!frmSoxSummaryData!Present_Discl = rs!PresentDiscl
Forms!frmSoxSummaryData!Res_Access = rs!ResAccess
Forms!frmSoxSummaryData!SOD = rs!SOD
Forms!frmSoxSummaryData!Safe_Assets = rs!SafeAssets
Forms!frmSoxSummaryData!Anti_Fraud = rs!AntiFraud
Forms!frmSoxSummaryData!Cntrl_Type = rs!CntrlType
Set rs = Nothing
Set DB = Nothing
End Sub

Thanks!

MD

We're getting closer. Still getting Run-time error '3061' but this
time it says Expected 1 instead of 2. So we made some progress
there. I made all the changes you suggested. I appreciate your
assistance.

Mark

I really don't know, but let's try a few things to debug this.

Temporarily change the strSQL to
"SELECT * from tblSoxSummaryData " _
& "WHERE Cntrl_num = 'ABC'"

I doubt it will return anything, but see if the error message goes
away. If that gives no error message, it would indicate that
cboCntrl_Num is returning a Null and not a real value.

if it still gives an error message, let's add the supposedly
optional parameter to the OpenRecordset method
Set rs = DB.OpenRecordset(strSQL,dbForwardOnly)

else if it works, change ABC to a real control number and see if
that works.


hth,
Q

 

[Bob Quintal]

Bob, you just might be a candidate for sainthood.

At any rate I managed to make the errors go away. The code is
running without error but the memo field (Cntrl_Desc) is still
getting truncated. That's the key issue here as I need those memo
fields in their entirety. Unless of course I made a change that
you don't agree with. Here is the code now. All I want is for the
text boxes on my form to populate correctly and then of course
update the tblSoxSummaryData table.

As an FYI the query below runs as a row source within the
cboCntrl_Num combo box. This query shows the memo fields just
fine. Let me know if it's easier to just send you the database at
this point.

yeah, bob quintal has an Gmail account. Just change the space
between first and last names to a dot and send it there because the
Sympatico account doesn't like big files.

SELECT tblSOXControls.FY08CntrlNum, tblSOXControls.FY08CntrlDescr,
tblSOXControls.Process, tblSOXControls.SubProcess,
tblSOXControls.PD, tblSOXControls.CntrlEnviron,
tblSOXControls.InfoComm, tblSOXControls.Monitoring,
tblSOXControls.RiskAssess, tblSOXControls.Completeness,
tblSOXControls.ValAlloc, tblSOXControls.ExtOccur,
tblSOXControls.RightsObl, tblSOXControls.PresentDiscl,
tblSOXControls.ResAccess, tblSOXControls.SOD,
tblSOXControls.SafeAssets, tblSOXControls.AntiFraud,
tblSOXControls.CntrlType FROM tblSOXControls;

Here is the new and improved VBA that is still truncating the memo
fields.

Dim DB As dao.Database
Dim rs As dao.Recordset
Dim strSQL As String
Set DB = CurrentDb
strSQL = "SELECT * FROM [tblSoxSummaryData] WHERE
[Cntrlnum] ='" _
& Me.cboCntrl_Num & "'"

Set rs = DB.OpenRecordset(strSQL)
Forms!frmSoxSummaryData!Cntrl_Desc = rs!CntrlDesc
Forms!frmSoxSummaryData!Process = rs!Process
Forms!frmSoxSummaryData!Sub_Process = rs!SubProcess
Forms!frmSoxSummaryData!PD = rs!PD
Forms!frmSoxSummaryData!Cntrl_Environ = rs!CntrlEnviron
Forms!frmSoxSummaryData!Info_Comm = rs!InfoComm
Forms!frmSoxSummaryData!Monitoring = rs!Monitoring
Forms!frmSoxSummaryData!Risk_Assess = rs!RiskAssess
Forms!frmSoxSummaryData!Completeness = rs!Completeness
Forms!frmSoxSummaryData!Val_Alloc = rs!ValAlloc
Forms!frmSoxSummaryData!Ext_Occur = rs!ExtOccur
Forms!frmSoxSummaryData!Rights_Obl = rs!RightsObl
Forms!frmSoxSummaryData!Present_Discl = rs!PresentDiscl
Forms!frmSoxSummaryData!Res_Access = rs!ResAccess
Forms!frmSoxSummaryData!SOD = rs!SOD
Forms!frmSoxSummaryData!Safe_Assets = rs!SafeAssets
Forms!frmSoxSummaryData!Anti_Fraud = rs!AntiFraud
Forms!frmSoxSummaryData!Cntrl_Type = rs!CntrlType
Set rs = Nothing
Set DB = Nothing
End Sub

Thanks!

MD

I really don't know, but let's try a few things to debug this.

Temporarily change the strSQL to
"SELECT * from tblSoxSummaryData " _
& "WHERE Cntrl_num = 'ABC'"

I doubt it will return anything, but see if the error message
goes away. If that gives no error message, it would indicate that
cboCntrl_Num is returning a Null and not a real value.

if it still gives an error message, let's add the supposedly
optional parameter to the OpenRecordset method
Set rs = DB.OpenRecordset(strSQL,dbForwardOnly)

else if it works, change ABC to a real control number and see if
that works.


hth,
Q

 

[Mark]

Bob, you're off the hook. I substituted the code you suggested with the
DLOOKUP command and that solved the problem. Will wonders ever cease?

Thanks for being patient and hanging in there on this one. I will file your
info away for future reference.

Take care. Oh, and I'm pretty certain I will post another issue here in this
newsgroup in the near future.

Mark

Bob, you just might be a candidate for sainthood.

At any rate I managed to make the errors go away. The code is
running without error but the memo field (Cntrl_Desc) is still
getting truncated. That's the key issue here as I need those memo
fields in their entirety. Unless of course I made a change that
you don't agree with. Here is the code now. All I want is for the
text boxes on my form to populate correctly and then of course
update the tblSoxSummaryData table.

As an FYI the query below runs as a row source within the
cboCntrl_Num combo box. This query shows the memo fields just
fine. Let me know if it's easier to just send you the database at
this point.

yeah, bob quintal has an Gmail account. Just change the space
between first and last names to a dot and send it there because the
Sympatico account doesn't like big files.

SELECT tblSOXControls.FY08CntrlNum, tblSOXControls.FY08CntrlDescr,
tblSOXControls.Process, tblSOXControls.SubProcess,
tblSOXControls.PD, tblSOXControls.CntrlEnviron,
tblSOXControls.InfoComm, tblSOXControls.Monitoring,
tblSOXControls.RiskAssess, tblSOXControls.Completeness,
tblSOXControls.ValAlloc, tblSOXControls.ExtOccur,
tblSOXControls.RightsObl, tblSOXControls.PresentDiscl,
tblSOXControls.ResAccess, tblSOXControls.SOD,
tblSOXControls.SafeAssets, tblSOXControls.AntiFraud,
tblSOXControls.CntrlType FROM tblSOXControls;

Here is the new and improved VBA that is still truncating the memo
fields.

Dim DB As dao.Database
Dim rs As dao.Recordset
Dim strSQL As String
Set DB = CurrentDb
strSQL = "SELECT * FROM [tblSoxSummaryData] WHERE
[Cntrlnum] ='" _
& Me.cboCntrl_Num & "'"

Set rs = DB.OpenRecordset(strSQL)
Forms!frmSoxSummaryData!Cntrl_Desc = rs!CntrlDesc
Forms!frmSoxSummaryData!Process = rs!Process
Forms!frmSoxSummaryData!Sub_Process = rs!SubProcess
Forms!frmSoxSummaryData!PD = rs!PD
Forms!frmSoxSummaryData!Cntrl_Environ = rs!CntrlEnviron
Forms!frmSoxSummaryData!Info_Comm = rs!InfoComm
Forms!frmSoxSummaryData!Monitoring = rs!Monitoring
Forms!frmSoxSummaryData!Risk_Assess = rs!RiskAssess
Forms!frmSoxSummaryData!Completeness = rs!Completeness
Forms!frmSoxSummaryData!Val_Alloc = rs!ValAlloc
Forms!frmSoxSummaryData!Ext_Occur = rs!ExtOccur
Forms!frmSoxSummaryData!Rights_Obl = rs!RightsObl
Forms!frmSoxSummaryData!Present_Discl = rs!PresentDiscl
Forms!frmSoxSummaryData!Res_Access = rs!ResAccess
Forms!frmSoxSummaryData!SOD = rs!SOD
Forms!frmSoxSummaryData!Safe_Assets = rs!SafeAssets
Forms!frmSoxSummaryData!Anti_Fraud = rs!AntiFraud
Forms!frmSoxSummaryData!Cntrl_Type = rs!CntrlType
Set rs = Nothing
Set DB = Nothing
End Sub

Thanks!

MD

We're getting closer. Still getting Run-time error '3061' but
this time it says Expected 1 instead of 2. So we made some
progress there. I made all the changes you suggested. I
appreciate your assistance.

Mark

I really don't know, but let's try a few things to debug this.

Temporarily change the strSQL to
"SELECT * from tblSoxSummaryData " _
& "WHERE Cntrl_num = 'ABC'"

I doubt it will return anything, but see if the error message
goes away. If that gives no error message, it would indicate that
cboCntrl_Num is returning a Null and not a real value.

if it still gives an error message, let's add the supposedly
optional parameter to the OpenRecordset method
Set rs = DB.OpenRecordset(strSQL,dbForwardOnly)

else if it works, change ABC to a real control number and see if
that works.


hth,
Q



Bob, here is my new code. I am getting the following error
however: Run-time error '3061' too few parameters. Expected 2.
This is happening on this line of code:
Set rs = DB.OpenRecordset(strSQL)

A fer possibilities:

Is Cntrl_num actually a text field, not a number?
If it is, then
strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num =
""" _
& cboCntrl_Num & """"

There may be a conflict between adoDb and dao in the type of
recordset created in Access 2000, explicitly declare

Dim rs As dao.Recordset

This is the code in its entirety:

Dim DB As Database
Dim rs As Recordset
Dim strSQL As String
Set DB = CurrentDb
strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num
= " _
& cboCntrl_Num
Set rs = DB.OpenRecordset(strSQL)
Forms!frmSoxSummaryData!Cntrl_Desc = rs!Cntrl_Desc
Forms!frmSoxSummaryData!Process = rs!Process
Forms!frmSoxSummaryData!Sub_Process = rs!Sub_Process
Forms!frmSoxSummaryData!PD = rs!PD
Forms!frmSoxSummaryData!Cntrl_Environ = rs!Cntrl_Environ
Forms!frmSoxSummaryData!Info_Comm = rs!Info_Comm
Forms!frmSoxSummaryData!Monitoring = rs!Monitoring
Forms!frmSoxSummaryData!Risk_Assess = rs!Risk_Assess
Forms!frmSoxSummaryData!Completeness = rs!Completness
Forms!frmSoxSummaryData!Val_Alloc = rs!Val_Alloc
Forms!frmSoxSummaryData!Ext_Occur = rs!Ext_Occur
Forms!frmSoxSummaryData!Rights_Obl = rs!Rights_Obl
Forms!frmSoxSummaryData!Present_Discl = rs!Present_Discl
Forms!frmSoxSummaryData!Res_Access = rs!Res_Access
Forms!frmSoxSummaryData!SOD = rs!SOD
Forms!frmSoxSummaryData!Safe_Assets = rs!Safe_Assets
Forms!frmSoxSummaryData!Anti_Fraud = rs!Anti_Fraud
Forms!frmSoxSummaryData!Cntrl_Type = rs!Cntrl_Type
Set rs = Nothing
Set DB = Nothing
End Sub

Thanks

Bob, thanks for your response. Bear with me as I am not yet
quite an ace programmer but I hope to be. Trust me I love
the cut and paste jobs but I want to understand as well.
Just to confirm, using the code you provided me. Would it
look like this basically. My previous code was working
alright except of course when it came to processing my memo
fields. Your code will work around the 255 char limit I
presume? Thank you!

Mark

yes the code works around the 255 character limit. The code
looks good.

Good luck

Q

would the new code look like this and basically replace what
I have:

Dim DB As Database
Dim rs As Recordset
Dim strSQL as string
Set DB = CurrentDb
strSQL = "SELECT * from tblSoxSummaryData WHERE Cntrl_num
= " _
& cboCntrl_Num
Set rs = DB.OpenRecordset(strSQL)
Forms!frmSoxSummaryData!Cntrl_Desc = rs!Cntrl_Desc
Forms!frmSoxSummaryData!Process = rs!Process
Forms!frmSoxSummaryData!Process = rs!PD
Forms!frmSoxSummaryData!Process = rs!Cntrl_Environ
etc.
Set rs = Nothing
Set DB = Nothing
End Sub


This is the code in its entirety. Not all fields are memo
fields however. Any further coding changes based on this?
I am simply populating different text boxes/fields based
on the query called in the cboCntrl_Num. I will try your
code changes in the meantime. Thanks

Mark

The code below opens a recordset, then populates all the
rows from the combobox, closes the recordset and exits
without using the recordset.

I'd just use the combo box to indicate the selected record,
and then open the recordset already filtered to return the
one row. If your combobox columns are in the same order as
the fields in the table, you could just change the
cboCntrl_Num.Column(n) to refer to the recordset as
rs.fields(n)

a 30 second cut and paste jobbie, if it's the case.

Q

Private Sub cboCntrl_Num_AfterUpdate()
'assigning form field values based on combo box selection
for Control data

Dim DB As Database
Dim rs As Recordset
Set DB = CurrentDb
Set rs = DB.OpenRecordset("tblSoxSummaryData")
Forms!frmSoxSummaryData!Cntrl_Desc =
cboCntrl_Num.Column(1)
Forms!frmSoxSummaryData!Process =
cboCntrl_Num.Column(2)
Forms!frmSoxSummaryData!Sub_Process =
cboCntrl_Num.Column(3) Forms!frmSoxSummaryData!PD =
cboCntrl_Num.Column(4)
Forms!frmSoxSummaryData!Cntrl_Environ =
cboCntrl_Num.Column(5)
Forms!frmSoxSummaryData!Info_Comm =
cboCntrl_Num.Column(6)
Forms!frmSoxSummaryData!Monitoring =
cboCntrl_Num.Column(7)
Forms!frmSoxSummaryData!Risk_Assess =
cboCntrl_Num.Column(8)
Forms!frmSoxSummaryData!Completeness =
cboCntrl_Num.Column(9)
Forms!frmSoxSummaryData!Val_Alloc =
cboCntrl_Num.Column(10)
Forms!frmSoxSummaryData!Ext_Occur =
cboCntrl_Num.Column(11)
Forms!frmSoxSummaryData!Rights_Obl =
cboCntrl_Num.Column(12)
Forms!frmSoxSummaryData!Present_Discl
= cboCntrl_Num.Column(13)
Forms!frmSoxSummaryData!Res_Access
= cboCntrl_Num.Column(14)
Forms!frmSoxSummaryData!SOD =
cboCntrl_Num.Column(15)
Forms!frmSoxSummaryData!Safe_Assets =
cboCntrl_Num.Column(16)
Forms!frmSoxSummaryData!Anti_Fraud =
cboCntrl_Num.Column(17)
Forms!frmSoxSummaryData!Cntrl_Type =
cboCntrl_Num.Column(18)

Set rs = Nothing
Set DB = Nothing
End Sub


Bob Q

--
Posted via a free Usenet account from
http://www.teranews.com







--
Bob Quintal

PA is y I've altered my email address.