VBA Coding Errors

  • Thread starter Thread starter Lee-Anne
  • Start date Start date
L

Lee-Anne

Hi

We have a database that is based mainly on VBA coding.
The person who programmed this has now left to move to
Australia and unfortunately the database is now displaying
an error msg. Basically we've not a clue how to fix this,
so I was wondering if someone could help us out. Thanks

The error msg is:

Microsoft Access: Run-Time error '2176'

The setting for this property is too long

You can enter up to either 255 or 2048 characters for this
property, depending on the data type.

The code which is prompting this error msg is:

'Builds a string to fill a date listbox
Sub fillDateList()
Dim rsValue As Recordset
Dim strSQL As String
Dim strList As String

strSQL = "SELECT DISTINCT tbl_CCHolding.[Date
Reported] FROM tbl_CCHolding ORDER BY tbl_CCHolding.[Date
Reported] DESC"
Set rsValue = getRecordset(strSQL)
If rsValue.RecordCount > 0 Then
rsValue.MoveFirst
Do While Not rsValue.EOF
If rsValue![Date Reported] <> "" Then
strList = strList & ";" & rsValue![Date
Reported]
End If
rsValue.MoveNext
Loop
strList = Mid(strList, 1)
Me.lstDate.RowSource = strList
Call closeRecordset(rsValue)
End If
End Sub
 
Lee-Anne said:
Hi

We have a database that is based mainly on VBA coding.
The person who programmed this has now left to move to
Australia and unfortunately the database is now displaying
an error msg. Basically we've not a clue how to fix this,
so I was wondering if someone could help us out. Thanks

The error msg is:

Microsoft Access: Run-Time error '2176'

The setting for this property is too long

You can enter up to either 255 or 2048 characters for this
property, depending on the data type.

The code which is prompting this error msg is:

'Builds a string to fill a date listbox
Sub fillDateList()
Dim rsValue As Recordset
Dim strSQL As String
Dim strList As String

strSQL = "SELECT DISTINCT tbl_CCHolding.[Date
Reported] FROM tbl_CCHolding ORDER BY tbl_CCHolding.[Date
Reported] DESC"
Set rsValue = getRecordset(strSQL)
If rsValue.RecordCount > 0 Then
rsValue.MoveFirst
Do While Not rsValue.EOF
If rsValue![Date Reported] <> "" Then
strList = strList & ";" & rsValue![Date
Reported]
End If
rsValue.MoveNext
Loop
strList = Mid(strList, 1)
Me.lstDate.RowSource = strList
Call closeRecordset(rsValue)
End If
End Sub
Click to expand...


ListBoxes and ComboBoxes can use either a query or a ValueList to provide the
options in the rows. Normally one would only use a ValueList if the list were
relatively short and unchanging. Your developer created the routine above to
make use of the ValueList while still making it dynamic by filling the ValueList
property on-the-fly. The problem is that the ValueList property does have a
maximum length which his routine is now exceeding.

My suggestion would be to eliminate the routine and just use the SQL statement
in his routine as the RowSource *query* for the ListBox. Then the problem goes
away. In looking at it I can see no reason why it wasn't done that way in the
first place.
 
It looks like this listbox is based on a value list
(there's probably a limit to the characters and that's
why you're getting the error). If you know anything
about coding - comment out the code below and try running
this code in its place (use the ' character to comment
out a line of code):
------------------------------
Dim strSQL As String

strSQL = "SELECT DISTINCT tbl_CCHolding.[Date
Reported] FROM tbl_CCHolding WHERE [Date Reported] <> ""
ORDER BY tbl_CCHolding.[Date Reported] DESC;"

If rsValue.RecordCount > 0 Then
Me.lstDate.RowSource = strSQL
End If

---------------------
Also, the lstDate (this is the listbox on the form where
you're getting the error) 'row source type' property
should be set to Table/Query - IMPORTANT to change this.
This way it's not a value list and there won't be a limit
(none that you'd have to be concerned with anyway) on the
# of recs returned to the listbox.
If for some reason this doesn't work, then you'll at
least have the old code to go back to (just remove the '
characters)

Regards,
Jen
 
Thanks for your help,

Unfortunately this didn't work, though it maybe just me.
Was I suppose to comment out ALL the existing code?

There new error message I'm getting is:

Run-Time Error '91': Object Variable or with block
variable not set.

-----Original Message-----
It looks like this listbox is based on a value list
(there's probably a limit to the characters and that's
why you're getting the error). If you know anything
about coding - comment out the code below and try running
this code in its place (use the ' character to comment
out a line of code):
------------------------------
Dim strSQL As String

strSQL = "SELECT DISTINCT tbl_CCHolding.[Date
Reported] FROM tbl_CCHolding WHERE [Date Reported] <> ""
ORDER BY tbl_CCHolding.[Date Reported] DESC;"

If rsValue.RecordCount > 0 Then
Me.lstDate.RowSource = strSQL
End If

---------------------
Also, the lstDate (this is the listbox on the form where
you're getting the error) 'row source type' property
should be set to Table/Query - IMPORTANT to change this.
This way it's not a value list and there won't be a limit
(none that you'd have to be concerned with anyway) on the
# of recs returned to the listbox.
If for some reason this doesn't work, then you'll at
least have the old code to go back to (just remove the '
characters)

Regards,
Jen
-----Original Message-----
Hi

We have a database that is based mainly on VBA coding.
The person who programmed this has now left to move to
Australia and unfortunately the database is now displaying
an error msg. Basically we've not a clue how to fix this,
so I was wondering if someone could help us out. Thanks

The error msg is:

Microsoft Access: Run-Time error '2176'

The setting for this property is too long

You can enter up to either 255 or 2048 characters for this
property, depending on the data type.

The code which is prompting this error msg is:

'Builds a string to fill a date listbox
Sub fillDateList()
Dim rsValue As Recordset
Dim strSQL As String
Dim strList As String

strSQL = "SELECT DISTINCT tbl_CCHolding.[Date
Reported] FROM tbl_CCHolding ORDER BY tbl_CCHolding. [Date
Reported] DESC"
Set rsValue = getRecordset(strSQL)
If rsValue.RecordCount > 0 Then
rsValue.MoveFirst
Do While Not rsValue.EOF
If rsValue![Date Reported] <> "" Then
strList = strList & ";" & rsValue![Date
Reported]
End If
rsValue.MoveNext
Loop
strList = Mid(strList, 1)
Me.lstDate.RowSource = strList
Call closeRecordset(rsValue)
End If
End Sub

.
Click to expand...
.
Click to expand...
 
Sorry, it should read as follows:
Dim strSQL As String

strSQL = "SELECT DISTINCT tbl_CCHolding.[Date
Reported] FROM tbl_CCHolding WHERE [Date Reported] <> ""
ORDER BY tbl_CCHolding.[Date Reported] DESC;"

Me.lstDate.RowSource = strSQL

Yes, comment out all of the code that was in there
before (Between the Sub and End Sub statement) and put
this code in there.

Regards,
Jen

-----Original Message-----
Thanks for your help,

Unfortunately this didn't work, though it maybe just me.
Was I suppose to comment out ALL the existing code?

There new error message I'm getting is:

Run-Time Error '91': Object Variable or with block
variable not set.

-----Original Message-----
It looks like this listbox is based on a value list
(there's probably a limit to the characters and that's
why you're getting the error). If you know anything
about coding - comment out the code below and try running
this code in its place (use the ' character to comment
out a line of code):
------------------------------
Dim strSQL As String

strSQL = "SELECT DISTINCT tbl_CCHolding.[Date
Reported] FROM tbl_CCHolding WHERE [Date Reported]
Click to expand...
ORDER BY tbl_CCHolding.[Date Reported] DESC;"

If rsValue.RecordCount > 0 Then
Me.lstDate.RowSource = strSQL
End If

---------------------
Also, the lstDate (this is the listbox on the form where
you're getting the error) 'row source type' property
should be set to Table/Query - IMPORTANT to change this.
This way it's not a value list and there won't be a limit
(none that you'd have to be concerned with anyway) on the
# of recs returned to the listbox.
If for some reason this doesn't work, then you'll at
least have the old code to go back to (just remove the '
characters)

Regards,
Jen
-----Original Message-----
Hi

We have a database that is based mainly on VBA coding.
The person who programmed this has now left to move to
Australia and unfortunately the database is now displaying
an error msg. Basically we've not a clue how to fix this,
so I was wondering if someone could help us out. Thanks

The error msg is:

Microsoft Access: Run-Time error '2176'

The setting for this property is too long

You can enter up to either 255 or 2048 characters for this
property, depending on the data type.

The code which is prompting this error msg is:

'Builds a string to fill a date listbox
Sub fillDateList()
Dim rsValue As Recordset
Dim strSQL As String
Dim strList As String

strSQL = "SELECT DISTINCT tbl_CCHolding.[Date
Reported] FROM tbl_CCHolding ORDER BY tbl_CCHolding. [Date
Reported] DESC"
Set rsValue = getRecordset(strSQL)
If rsValue.RecordCount > 0 Then
rsValue.MoveFirst
Do While Not rsValue.EOF
If rsValue![Date Reported] <> "" Then
strList = strList & ";" & rsValue! [Date
Reported]
End If
rsValue.MoveNext
Loop
strList = Mid(strList, 1)
Me.lstDate.RowSource = strList
Call closeRecordset(rsValue)
End If
End Sub

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top