ComboBox to update TextBoxes on Form

[ryguy7272]

My code is below:

Private Sub cboCombo_AfterUpdate(Cancel As Integer)

Me.txtTextBox1 = Me.cboCombo.Column(1)
Me.txtTextBox3 = Me.cboCombo.Column(3)
Me.txtTextBox4 = Me.cboCombo.Column(4)
Me.txtTextBox5 = Me.cboCombo.Column(5)
Me.txtTextBox6 = Me.cboCombo.Column(6)
Me.txtTextBox7 = Me.cboCombo.Column(7)
Me.txtTextBox8 = Me.cboCombo.Column(8)
Me.txtTextBox9 = Me.cboCombo.Column(9)
Me.txtTextBox11 = Me.cboCombo.Column(11)
Me.txtTextBox12 = Me.cboCombo.Column(12)

Me.Query1.Requery
End Sub

The Error message reads: ‘The expression After Update you entered as the
event property setting produced the following error: Procedure declaration
does not match description of event or procedure having the same name.’

I have 10 TextBoxes on a Form. I’m trying to update values in each textbox,
by means of a ComboBox AfterUpdate event. What am I doing wrong?

Thanks,
Ryan---

 

[Dirk Goldgar]

My code is below:

Private Sub cboCombo_AfterUpdate(Cancel As Integer)

Me.txtTextBox1 = Me.cboCombo.Column(1)
Me.txtTextBox3 = Me.cboCombo.Column(3)
Me.txtTextBox4 = Me.cboCombo.Column(4)
Me.txtTextBox5 = Me.cboCombo.Column(5)
Me.txtTextBox6 = Me.cboCombo.Column(6)
Me.txtTextBox7 = Me.cboCombo.Column(7)
Me.txtTextBox8 = Me.cboCombo.Column(8)
Me.txtTextBox9 = Me.cboCombo.Column(9)
Me.txtTextBox11 = Me.cboCombo.Column(11)
Me.txtTextBox12 = Me.cboCombo.Column(12)

Me.Query1.Requery
End Sub

The Error message reads: ‘The expression After Update you entered as the
event property setting produced the following error: Procedure declaration
does not match description of event or procedure having the same name.’

I have 10 TextBoxes on a Form. I’m trying to update values in each
textbox,
by means of a ComboBox AfterUpdate event. What am I doing wrong?

The AfterUdpate event has no Cancel argument. Just write:

Private Sub cboCombo_AfterUpdate()

 

[ryguy7272]

Thanks Dirk! I removed 'Cancel As Integer' and now I get a message that
reads 'Compile Error, Method or data member not found.'

This line is in blue:
Query1

I have this as the Row Source for the ComboBox:
SELECT Query1.F1, Query1.F2, Query1.F3, Query1.F4, Query1.F5, Query1.F6,
Query1.F7, Query1.F8, Query1.F9, Query1.F10, Query1.F11, Query1.F12,
Query1.F13 FROM Query1 GROUP BY Query1.F1, Query1.F2, Query1.F3, Query1.F4,
Query1.F5, Query1.F6, Query1.F7, Query1.F8, Query1.F9, Query1.F10,
Query1.F11, Query1.F12, Query1.F13, Query1.F1, Query1.F1, Query1.F1,
Query1.F1 HAVING (((Query1.F1) Not

I can't tell what the problem is now. Can anyone tell what is preventing me
from running this Query?

Thanks,
Ryan---

 

[Douglas J. Steele]

The RowSource isn't valid. It's missing a FROM clause, and "HAVING
(((Query1.F1) Not" isn't valid.

 

[Dirk Goldgar]

Thanks Dirk! I removed 'Cancel As Integer' and now I get a message that
reads 'Compile Error, Method or data member not found.'

This line is in blue:
Query1

I have this as the Row Source for the ComboBox:
SELECT Query1.F1, Query1.F2, Query1.F3, Query1.F4, Query1.F5, Query1.F6,
Query1.F7, Query1.F8, Query1.F9, Query1.F10, Query1.F11, Query1.F12,
Query1.F13 FROM Query1 GROUP BY Query1.F1, Query1.F2, Query1.F3,
Query1.F4,
Query1.F5, Query1.F6, Query1.F7, Query1.F8, Query1.F9, Query1.F10,
Query1.F11, Query1.F12, Query1.F13, Query1.F1, Query1.F1, Query1.F1,
Query1.F1 HAVING (((Query1.F1) Not

I can't tell what the problem is now. Can anyone tell what is preventing
me
from running this Query?

Ryan -

Doug has pointed out the problem in the above SQL statement. Aside from
that, in your previous post, you had this code:

Me.Query1.Requery

Is "Query1" the name of an object on your form? If so, what is it? A
subform, a list box ... ?

 

[ryguy7272]

Sorry, this is the actual SQL:
SELECT Query1.F1, Query1.F2, Query1.F3, Query1.F4, Query1.F5, Query1.F6,
Query1.F7, Query1.F8, Query1.F9, Query1.F10, Query1.F11, Query1.F12,
Query1.F13
FROM Query1
GROUP BY Query1.F1, Query1.F2, Query1.F3, Query1.F4, Query1.F5, Query1.F6,
Query1.F7, Query1.F8, Query1.F9, Query1.F10, Query1.F11, Query1.F12,
Query1.F13, Query1.F1, Query1.F1, Query1.F1, Query1.F1
HAVING (((Query1.F1) Not Like ("*Top*") And (Query1.F1) Not Like
("*Remaining*") And (Query1.F1) Not Like ("*Total*") And (Query1.F1) Not Like
("*Quoted*") And (Query1.F1) Not Like ("*Solar*")))
ORDER BY Query1.F1;


I don't know why it didn't copy last time, and obviously I wasn't paying
attention or I would have caught that.

Query1 is linked to a Table with some data on the 50 states. The SQL for
the Query is:
SELECT State.*
FROM State;

So, the query is identical to the Table.

When I run the Query I get:
'Compile Error, Method or data member not found.'
This line is blue:
Query1

All I did was set up a Form with 10 TextBoxes pointing to the Query1, and
the I want to have the 10 TextBoxes update with the appropriate records when
I select a State from the ComboBox. This shouldn't be a hard thing to do. I
must be missing something simple here. Any additional help would be greatly
appreciated.

Thanks,
Ryan---

 

[Dirk Goldgar]

(Please see my questions inliine with the quoted text.)

Sorry, this is the actual SQL:
SELECT Query1.F1, Query1.F2, Query1.F3, Query1.F4, Query1.F5, Query1.F6,
Query1.F7, Query1.F8, Query1.F9, Query1.F10, Query1.F11, Query1.F12,
Query1.F13
FROM Query1
GROUP BY Query1.F1, Query1.F2, Query1.F3, Query1.F4, Query1.F5, Query1.F6,
Query1.F7, Query1.F8, Query1.F9, Query1.F10, Query1.F11, Query1.F12,
Query1.F13, Query1.F1, Query1.F1, Query1.F1, Query1.F1
HAVING (((Query1.F1) Not Like ("*Top*") And (Query1.F1) Not Like
("*Remaining*") And (Query1.F1) Not Like ("*Total*") And (Query1.F1) Not
Like
("*Quoted*") And (Query1.F1) Not Like ("*Solar*")))
ORDER BY Query1.F1;


I don't know why it didn't copy last time, and obviously I wasn't paying
attention or I would have caught that.

Query1 is linked to a Table with some data on the 50 states. The SQL for
the Query is:
SELECT State.*
FROM State;

Does the State table have fields name "F1", "F2", "F3", etc.? Those are
very uninformative field names.

So, the query is identical to the Table.

So why does Query1 exist?

When I run the Query I get:
'Compile Error, Method or data member not found.'

What do you mean, "when I run the query"? How do you run the query? That
is a compile error, and cannot be generated by a query.

This line is blue:
Query1

Where is that line? What is the code surrounding that line?

All I did was set up a Form with 10 TextBoxes pointing to the Query1, and

What is the RecordSource property of the form? Is the form unbound (no
recordsource)?

the I want to have the 10 TextBoxes update with the appropriate records
when
I select a State from the ComboBox. This shouldn't be a hard thing to do.
I
must be missing something simple here. Any additional help would be
greatly
appreciated.

I think you aren't describing the situation in terms that are well-defined.
As soon as we can pin down some of the details, it will be simple.

Please answer this question, which I asked in my previous post:
If "Query1" is not the name of a subform or other control on your form, then
the line "Me.Query1.Requery" is meaningless, will not compile, and could be
the source of the error you report.

 

[ryguy7272]

Ah! Me.Query1.Requery really is meaningless. I don't know why I stuck that
in there (well, I worked on a project before where I needed to requery so I
probably picked it up there).

Ok, this is the scenario. When I click on the ComboBox, and choose a State,
txtTextBox1 is updated with the appropriate information.

How do I set this up for the other variables? My code looks like this:

Me.txtTextBox1 = Me.cboCombo.Column(1)
Me.txtTextBox3 = Me.cboCombo.Column(3)
Me.txtTextBox4 = Me.cboCombo.Column(4)
Me.txtTextBox5 = Me.cboCombo.Column(5)

In my Table:
Column1 = State
Column3 = State Governor
Column4 = State Revenue
Column5 = Tax Rate

TIA!
Ryan--

 

[Dirk Goldgar]

Ah! Me.Query1.Requery really is meaningless. I don't know why I stuck
that
in there (well, I worked on a project before where I needed to requery so
I
probably picked it up there).

Ok, this is the scenario. When I click on the ComboBox, and choose a
State,
txtTextBox1 is updated with the appropriate information.

Does that mean that you removed the "Me.Query1.Requery" line, and that fixed
the compile error? So the code is working, so far as getting the correct
info into txtTextBox1?

How do I set this up for the other variables? My code looks like this:

Me.txtTextBox1 = Me.cboCombo.Column(1)
Me.txtTextBox3 = Me.cboCombo.Column(3)
Me.txtTextBox4 = Me.cboCombo.Column(4)
Me.txtTextBox5 = Me.cboCombo.Column(5)

In my Table:
Column1 = State
Column3 = State Governor
Column4 = State Revenue
Column5 = Tax Rate

And is the code not working?
If it is not working, what is happening?
Are you getting an error message?
What is the value of the Column Count property of the combo box?

Ryan, so far in our discussion I have asked you a number of questions, and
you have answered very few of them. In some cases, your replies have hinted
at the answers, but not explicitly answered them. This is making it very
difficult to help you. Please answer the above questions explicitly.

 

[ryguy7272]

Hello, again, Dirk. I am trying to respond to all your questions now. Hope
this makes sense; I hope to resolve this issue soon…


Does the State table have fields name "F1", "F2", "F3", etc.? Those are
very uninformative field names.

Yes, I was planning to assign a much more meaningful name to each field; I
wanted to get the ComboBox thing working first though.


So why does Query1 exist?

I created a Query1 to prepare for future additions of Tables and eventually
more Queries. Again, I wanted to get the ComboBox thing working.


What do you mean, "when I run the query"? How do you run the query? That
is a compile error, and cannot be generated by a query.

The Query is fine; just this for now:
SELECT State.*
FROM State;

I was going to add more in later, after I got the ComboBox working.


What is the RecordSource property of the form? Is the form unbound (no
recordsource)?

I used the Wizard to create the Form; I believe it is Bound.


Where is that line? What is the code surrounding that line?

I did away with Query 1; not using it for the time being in the VBA code…the
Query is there, but the code doesn’t pull in any Queries.

VBA is now:
Private Sub cboCombo_AfterUpdate()
Me.txtTextBox1 = Me.cboCombo.Column(1)
Me.txtTextBox3 = Me.cboCombo.Column(3)
Me.txtTextBox4 = Me.cboCombo.Column(4)
'etc.
End Sub


Does that mean that you removed the "Me.Query1.Requery" line, and that fixed
the compile error? So the code is working, so far as getting the correct
info into txtTextBox1?

Yes, TextBox1 works fine, but none of the other TextBoxes update with
appropriate values.


What is the value of the Column Count property of the combo box?

The ComboBox is named cboCombo and it is set to Bound Columns 1.


Does all of this make sense? I’d really like to learn how this works so I
can do it for myself, and eventually help others, soon.

Thanks,
Ryan--

 

[Dirk Goldgar]

Hello, again, Dirk. I am trying to respond to all your questions now.

Thanks, I appreciate it.

What is the RecordSource property of the form? Is the form unbound (no
recordsource)?

I used the Wizard to create the Form; I believe it is Bound.

Do you know how to find the RecordSource property? It's on the Data tab of
the form's property sheet in design view. I'd like to know what that
property says.

Are the text boxes (txtTextBox1, etc.) also bound? That is, do they have
fields named in their Control Source properties? If so, your code for the
combo box will be setting values of fields in a record that will be saved.
Is that what you had in mind?

Yes, TextBox1 works fine, but none of the other TextBoxes update with
appropriate values.

Hmm. I suspect the Column Count property is not set right.

What is the value of the Column Count property of the combo box?

The ComboBox is named cboCombo and it is set to Bound Columns 1.

That's not the property I wanted to know about. The Column Count property
is on the Format tab of the combo box's property sheet in design view. It
should be set to the number of fields that the combo's RowSource query
returns. I think that was 13, but you should make sure.

I'm going to bed now, so I won't be able to reply to any further messages
until morning.

 

[ryguy7272]

Ah! Finally got to the bottom of it. You were totally right, Dirk, the
Column Count property was not set right. I had the dang thing set to 1!! I
put 13 in there, and adjusted the VBA a bit (after I realized it's zero
based). I even figured out how to set the ColumnWidth so I can see just
state names and I zeroed out the rest. I’m in business!!

Thanks so much!!
Ryan--