Populate multiple textboxes from combobox selection

[ribbons]

Hi Guys,

Another question. I have a combobox named PPQOfficer whose source is a query:

SELECT tbl_lookup_FEDInfo.PPQ_Off, tbl_lookup_FEDInfo.FEDAgency, tbl_lookup_FEDInfo.FEDAddress, tbl_lookup_FEDInfo.FEDCitySTZip
FROM tbl_lookup_FEDInfo;

I want the selection from this combobox to fill three other textboxes. This is the code I've tried, based on searches here, and I can't get it to work except for the first text box. I'm sure I'm overlooking something obvious.

Private Sub PPQ_Officer_AfterUpdate()

Me.txtfedagency = Me.PPQ_Officer.Column(1)
Me.txtfedagency.Requery
Me.txtFEDAddress = Me.PPQ_Officer.Column(2)
Me.txtFEDAddress.Requery
Me.txtFEDCitySTZip = Me.PPQ_Officer.Column(3)
Me.txtFEDCitySTZip.Requery

End Sub

Any help is appreciated.

 

[Remou]

I am not sure why you are requerying. Columns start with zero.

Private Sub PPQ_Officer_AfterUpdate()

Me.txtfedagency = Me.PPQ_Officer.Column(1)
[s]Me.txtfedagency.Requery[/s]
Me.txtFEDAddress = Me.PPQ_Officer.Column(2)
[s]Me.txtfedagency.Requery[/s]
Me.txtFEDCitySTZip = Me.PPQ_Officer.Column(3)
[s]Me.txtfedagency.Requery[/s]

End Sub

 

[ribbons]

The Requery was a suggestion I found here, but it didn't make a difference.

The first column that I want to populate the textbox is column 1 of the query. Column 0 is other data that I'm not using in the population. Will this work?



ribbons

 

[Remou]

It should work. Have you tried?

 

[ribbons]

Yes, I tried this to start with:

Private Sub PPQ_Officer_AfterUpdate()

Me.txtfedagency = Me.PPQ_Officer.Column(1)
Me.txtFEDAddress = Me.PPQ_Officer.Column(2)
Me.txtFEDCitySTZip = Me.PPQ_Officer.Column(3)

End Sub

Column (0) of the query is the name of the office and it is not needed to populate the textboxes.

I just can't figure out why this won't work.

 

[RivetHed]

Is it recognising that there's data in the columns when you step through the code?

 

[Remou]

Have you set the column count property of the combobox?

 

[ribbons]

Yes, it fills the first txtbox (txtfedagency) with the appropriate data, but not the second or third. And if I run the query in Access, it produces data fine.

Am I correct in assuming that the first column of the query is the source for the combobox (PPQOfficer) and that column 1 through 3 can be used to fill the agency, address and city/state/zip textboxes below? And do I have the right syntax? It appears so, based on my searches, but I'm stumped.

ribbons

 

[Remou]

Did you see RivetHed's post?

 

[ribbons]

How do you do that in Access? I can do it in VB 6, but when I try to "run" an Access program, it pops up a window that asks for a Macro.

ribbons

 

[RivetHed]

It's the same as VB6, just stick a break point in and update the combobox then step through checking what values it has listed for the column contents.

 

[ribbons]

Ok, when I run this, I get "Null" for the second and third boxes. No clue. If my syntax is right, then by default I guess there is something "funky" with the database. The data is there, both on direct examination of the database and of the query. It's a very simple table with only two columns and only about 6 or 7 records. Any other ideas?

Kathy

 

[Remou]

Something contadictory there. Above, you say the table has only two columns but the query shows four columns? Hva you tried building a scratch form and using the wizard to add a combobox? You can add a textbox to such a form with the controlsource set to:

[tt]=Me.cboCombo.Column(2)[/tt]

Which is a useful means of checking the data.

 

[ribbons]

Sorry, it does have four. The two belonged to another problem I'm working on.

I'll try checking the data as you've suggested.

I can format the AfterUpdate as I've posted, right? With the Me.textbox = cbobox.Column(x) and so forth?? Even if there are several columns in the query?

ribbons

 

[Remou]

Yes. I was concerned that you had not set the column count property for the combobox, which can lead to problems, but you say that you have. Is it possible that it was set to 2, rather than 4?

 

[ribbons]

Well . . I figured it out and ,sure enough, I had done something stupid. Forgot to set the columns of the combobox to 4. I suspected as much.

thanks for all the help!!

ribbons