error 2176

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

OTWarrior - 04 Jun 2007 13:00 GMT

I have looked on this forum for this error, and the solutions haven't worked
for me (guessing my code is a bit different).

Basically, i want to load the values of a table into a combo box for
selection, which did work before, but now i have exceeded the 2000 character
limit on the string (i have taken over from someone else who worked on this
form before, so did not forsee this problem until it happened)

Here is the code to populate the combobox i use:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSource As String

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_DAY_Providers", dbOpenDynaset)
strSource = "0;All"

rs.MoveFirst
Do Until rs.EOF
strSource = strSource & ";" & rs.Fields(0) & ";" & rs.Fields(1)
rs.MoveNext
Loop
rs.Close
Me.Combo65.RowSource = strSource

The last line is the problem (or at least where debug highlights) as the
string is quite large here (over 3000 characters). I did try the LEFT()
command as another thread suggested, but that failed.

Any ideas on how to fix this would be GREATLY appreciated, as this went live
today, and I could really use the help.

Reply to this Message

Klatuu - 04 Jun 2007 13:43 GMT

The approach you are taking will not work. It appears the table you are
using is one record with some number of fields. If this is correct, the
solution is to change your table so it has one field and some number of
records. The change the row source type to Table/Query and use the table as
the row source.

Signature

Dave Hargis, Microsoft Access MVP

> I have looked on this forum for this error, and the solutions haven't worked
> for me (guessing my code is a bit different).
[quoted text clipped - 28 lines]
> Any ideas on how to fix this would be GREATLY appreciated, as this went live
> today, and I could really use the help.

Reply to this Message

OTWarrior - 04 Jun 2007 15:21 GMT

the table uses 2 fields and a number of records, and i have tried using it as
a row source:
SELECT tbl_DAY_Providers.anDayProviderID, tbl_DAY_Providers.txtProviderName
FROM tbl_DAY_Providers;

the tables does have 7 fields to it (along with LOADS of records, not one),
however I am only passing 2 of the fields to it., so would assume that would
be ok.

But the problem is down to the limit of the data being passed to the string
as 2000 characters, not the ammount of fields, as I understand it. there are
over 150 records on this table, and an average of 15 characters per record,
which need to be passsed to the string to form a list.

>The approach you are taking will not work. It appears the table you are
>using is one record with some number of fields. If this is correct, the
[quoted text clipped - 6 lines]
>> Any ideas on how to fix this would be GREATLY appreciated, as this went live
>> today, and I could really use the help.

Reply to this Message

Baz - 04 Jun 2007 16:52 GMT

Creat the following query and save it. Let's suppose that you call it
qryComboBox:

(SELECT 0 AS Field1, "All" AS Field2 FROM tbl_DAY_Providers) UNION (SELECT *
FROM tbl_DAY_Providers)

Change the combo box's Row Source Type property to Table/Query, and set the
Row Source to this:

SELECT * FROM qryComboBox ORDER BY Field1

Reply to this Message

Baz - 04 Jun 2007 17:02 GMT

Oh, and lose the code...

> Creat the following query and save it. Let's suppose that you call it
> qryComboBox:
[quoted text clipped - 43 lines]
> live
> > today, and I could really use the help.

Reply to this Message

OTWarrior - 04 Jun 2007 17:07 GMT

You are a legend, It works perfectly, Thank you :)

>Creat the following query and save it. Let's suppose that you call it
>qryComboBox:
[quoted text clipped - 12 lines]
>> Any ideas on how to fix this would be GREATLY appreciated, as this went live
>> today, and I could really use the help.

Reply to this Message