Limit of use of Field.column(#)?

[Eljefegeneo]

I have a combox field called ClientID. The row source for this field is

  SELECT tblCustomerMaintenance.ClientID, tblCustomerMaintenance.Contact, tblCustomerMaintenance.Organization, tblCustomerMaintenance.ProgramTitle, tblCustomerMaintenance.Address, tblCustomerMaintenance.CityState, tblCustomerMaintenance.ZipPC, tblCustomerMaintenance.Country1, tblCustomerMaintenance.Caution, tblCustomerMaintenance.Einv, tblCustomerMaintenance.ACH
  FROM tblCustomerMaintenance
  ORDER BY tblCustomerMaintenance.ClientID;

To enter a new record I merely type in the ClientID and the first eight fields populate the form with the
after update event:

  Client = ClientID.Column(1)
  Organization = ClientID.Column(2)
  ProgramTitle = ClientID.Column(3)
  Address = ClientID.Column(4)
  CityState = ClientID.Column(5)
  ZipPC = ClientID.Column(6)
  Country = ClientID.Column(7)
  Caution = ClientID.Column(8)
  Einv = ClientID.Column(9)
  ACH = ClientID.Column(10)

But I cannot get the ninth and tenth fields to populate. The last three are Y/N. If I switch field #9 for Field #8 in the above SQL statement, and then change the number of the column in the after update event, the field populates, that is, there is nothing wrong with the field and its data.

So my question is why won't the ninth and tenth columns populate the form? Is there some sort of limit?

 

[sneuberg]

We use column 11 in one of our forms, not this way but as a control source and it works fine. So it's not a limitation of the number of columns. I suggest just poking this with a stick. Start by adding some Debug.Print statements. Like

  Client = ClientID.Column(1)
  Organization = ClientID.Column(2)
  ProgramTitle = ClientID.Column(3)
  Address = ClientID.Column(4)
  CityState = ClientID.Column(5)
  ZipPC = ClientID.Column(6)
  Country = ClientID.Column(7)
  Caution = ClientID.Column(8)
  Debug.Print "ClientID.Column(8) = " & ClientID.Column(8)
  Einv = ClientID.Column(9)
 Debug.Print "ClientID.Column(9) = " & ClientID.Column(9)
  ACH = ClientID.Column(10)
 Debug.Print "ClientID.Column(10) = " & ClientID.Column(10)

Also add a another text box to let's say txtTest and then add something like

Me.txtTest = ClientID.Column(10)

and see what you get,

 

[Minty]

The column indexes start at (0) as well ? So are you referencing the correct column number?

 

[Eljefegeneo]

yes, I am referencing the correct column number. sneuberg; I will try your suggestion and see what happens. Thank you both.

 

[Eljefegeneo]

I tried the suggestion to debug.print and came up with the following:

ClientID.Column(8) = -1
ClientID.Column(9) = 
ClientID.Column(10) =

however the referenced fields 9 and 10 were marked as true.

So, I decided to switch the last three references, columns 8,9 and 10 to the number 1,2 and 3 position. The result is that they now populate correctly. However the new 9 and 10th column data does not. This is the result:

ClientID.Column(8) = San Jose  CA
ClientID.Column(9) = 
ClientID.Column(10) = 
ClientID.Column(1) = -1
ClientID.Column(2) = -1
ClientID.Column(3) = -1

So, the original data is correct, but the 9th and 10th column just won't populate.

:banghead:

 

[Eljefegeneo]

:banghead::banghead::banghead:

Well, I solved the mystery and quite frankly I am very embarrassed. But, any lesson learned cannot be that bad to go through.

I looked at the property of the combo box and originally I had eight columns in it. Then I added two more. I forgot to update this and thus it was not seeing the last two columns.

All is now well.

 

[The_Doc_Man]

Actually, given your statement of the solution, I'm more surprised that you didn't get any of several errors that would tell you "selected object" doesn't exist (not necessarily in those exact words.)

 

[Eljefegeneo]

Nope, no error messages. Just a lesson learned the hard way. But not to be forgotten either!

 

[sneuberg]

Yeah that's nasty. I just reduced the column count in the combo box I talked of before from 12 to 5 and no #Name errors as you'd expected. It just doesn't show the data.

 

[The_Doc_Man]

That invites the question of whether other collections, when referenced by numeric index rather than using a "for each memberobject in parentcolletion" construct, would also silently return nulls (which is what it appears he got).