By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,173 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Code for combo boxes returns error message!!!

P: 21
I thought I had it figured out yesterday. WRONG! I have 2 combo boxes, CBUnit and CBSub. When value is clicked within CBUnit, it should bring up corresponding sub-values in CBSub. These combo boxes are based on 2 tables; tbl_Unit and tbl_Sub.

CBUnit has the following:
RowSource: tbl_Unit
ColumnCount: 2
ColumnWidth: 0";1.5"
BoundColumn: 1

CBSub has the following:
RowSource: SELECT tbl_Sub.AUTONUMBER, tbl_Sub.Sub FROM tbl_Sub ORDER BY [Sub];
ColumnCount: 2
ColumnWidth: 0";1"
BoundColumn: 1

In theVisual Basic Editor, I put the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CBUnit_AfterUpdate()
  2.   With Me![Sub]
  3.       If IsNull(Me!Unit) Then
  4.         .RowSource = ""
  5.       Else
  6.         .RowSource = "SELECT [Sub] " & _
  7.                      "FROM tbl_Sub " & _
  8.                      "WHERE [UnitID]=" & Me!Unit
  9.       End If
  10.       Call .Requery
  11.     End With
  12. End Sub
I put nothing in CBSub.

Well, the values show in Unit once I return to datasheet view. However, when I click on a value, it comes up with the following error:

"Run-time error '2465':
Microsoft Office Access can't find the field 'Sub' referred to in your expression"

For the life of me I cannot figure out how it can't find it! Can someone please help me???
Oct 12 '07 #1
Share this Question
Share on Google+
15 Replies


nico5038
Expert 2.5K+
P: 3,072
Hmm, your combobox is named CBUnit, but not referred to in the WHERE:
"WHERE [UnitID]=" & Me!Unit
Did you try to use:
"WHERE [UnitID]=" & Me!CBUnit

Also make sure that the UnitID is numeric !

Nic;o)
Oct 12 '07 #2

P: 21
Hmm, your combobox is named CBUnit, but not referred to in the WHERE:
"WHERE [UnitID]=" & Me!Unit
Did you try to use:
"WHERE [UnitID]=" & Me!CBUnit

Also make sure that the UnitID is numeric !

Nic;o)
Crap, I thought that would work. But I replaced it and it displayed the same error message. And I did check to see if UnitID is numeric. It is. Autonumber and Long Integer.

Any other ideas?

But thanks for at least trying to help Nic. I appreciate it greatly!
Oct 12 '07 #3

NeoPa
Expert Mod 15k+
P: 31,494
Deanndra,

It's not just that bit of your code that referred to it wrongly.
However, from my reading of your error message, it is the field [Sub] in the table tbl_Sub that doesn't exist. Can you post your table meta-data for that table and we can check it over for you. Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Oct 12 '07 #4

NeoPa
Expert Mod 15k+
P: 31,494
BTW If that doesn't help you find the answer, post which line the debugger stopped on when it reported the error. That will help us to narrow it down a bit.
Good luck.
Oct 12 '07 #5

Scott Price
Expert 100+
P: 1,384
I thought I had it figured out yesterday. WRONG! I have 2 combo boxes, CBUnit and CBSub. When value is clicked within CBUnit, it should bring up corresponding sub-values in CBSub. These combo boxes are based on 2 tables; tbl_Unit and tbl_Sub.

CBUnit has the following:
RowSource: tbl_Unit
ColumnCount: 2
ColumnWidth: 0";1.5"
BoundColumn: 1

CBSub has the following:
RowSource: SELECT tbl_Sub.AUTONUMBER, tbl_Sub.Sub FROM tbl_Sub ORDER BY [Sub];
ColumnCount: 2
ColumnWidth: 0";1"
BoundColumn: 1

In theVisual Basic Editor, I put the following code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub CBUnit_AfterUpdate()
  2.   With Me![Sub]
  3.       If IsNull(Me!Unit) Then
  4.         .RowSource = ""
  5.       Else
  6.         .RowSource = "SELECT [Sub] " & _
  7.                      "FROM tbl_Sub " & _
  8.                      "WHERE [UnitID]=" & Me!Unit
  9.       End If
  10.       Call .Requery
  11.     End With
  12. End Sub
I put nothing in CBSub.

Well, the values show in Unit once I return to datasheet view. However, when I click on a value, it comes up with the following error:

"Run-time error '2465':
Microsoft Office Access can't find the field 'Sub' referred to in your expression"

For the life of me I cannot figure out how it can't find it! Can someone please help me???

Take a good look again at your line 2. Your combo box is named CBSub, is it not? This is the same idea as Nico had with the CBUnit...

Regards,
Scott
Oct 13 '07 #6

P: 21
Deanndra,

It's not just that bit of your code that referred to it wrongly.
However, from my reading of your error message, it is the field [Sub] in the table tbl_Sub that doesn't exist. Can you post your table meta-data for that table and we can check it over for you. Here is an example of how to post table MetaData :
Table Name=tblStudent
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
I hope I get this right as I didn't really know the term "metadata" but what you typed looked familiar to the table in design form so that's what I went with. I hop that's right.
Table Name=tbl_Unit
Expand|Select|Wrap|Line Numbers
  1. Field; type; IndexInfo
  2. UnitID; AutoNumber; PK
  3. Unit; Text; FK
Table Name=tbl_Sub
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. Sub; Text
  3. UnitID; Number; FK
  4. SubID; AutoNumber; PK
I did run Debug, and it stopped on "If IsNull(Me!Unit) Then"

When I click on CBUnit, it still come sup with the same error msg (sorry Scott, I did try what you said to). When I click on CBSub, it comes up with a box that says "Enter Parameter Value" and then "tbl_Sub.AUTONUMBER"

not too sure what that's about...does anyone have any more ideas? I am so stumped! I feel like I followed directions that I pulled from various sites, but I must not have if I ended up here!

Thanks again for the time you have taken to answer my question.
Oct 15 '07 #7

Scott Price
Expert 100+
P: 1,384
On your form, what are the exact names of your controls? Combo boxes, etc...

Me!Unit means that you have a control named Unit on your form. If there is no control with this name, VBA won't be able to find it... That's why I suggested CBUnit, or whatever the exact name of your control is. In design view of the form, right-click on the control, choose Properties. The top field on the All tab is the Name field. This name needs to correspond between the form and the VBA code.

Check very carefully in each instance.

One thing that will be very helpful for you as you learn VBA is to work through each line of code and find out exactly what you are trying to tell the computer to do... For example: If IsNull(Me!Unit) Then... This code tells VBA to look for a control named Unit on the current form (Me), If this control contains no value (Null), Then do something.

Regards,
Scott
Oct 15 '07 #8

P: 21
On your form, what are the exact names of your controls? Combo boxes, etc...

Me!Unit means that you have a control named Unit on your form. If there is no control with this name, VBA won't be able to find it... That's why I suggested CBUnit, or whatever the exact name of your control is. In design view of the form, right-click on the control, choose Properties. The top field on the All tab is the Name field. This name needs to correspond between the form and the VBA code.

Check very carefully in each instance.

One thing that will be very helpful for you as you learn VBA is to work through each line of code and find out exactly what you are trying to tell the computer to do... For example: If IsNull(Me!Unit) Then... This code tells VBA to look for a control named Unit on the current form (Me), If this control contains no value (Null), Then do something.

Regards,
Scott
Well the GOOD news is that this did correct part of the problem! So thanks! It got rid of the error message from CBUnit, as well as the "autonumber" message for CBSub. The code now looks as follows (because there is still a partial problem here):

Expand|Select|Wrap|Line Numbers
  1. Private Sub CBUnit_AfterUpdate()
  2.   With Me![CBSub]
  3.       If IsNull(Me!CBUnit) Then
  4.         .RowSource = ""
  5.       Else
  6.         .RowSource = "SELECT [CBSub] " & _
  7.                      "FROM tbl_Sub " & _
  8.                      "WHERE [UnitID]=" & Me!CBUnit
  9.       End If
  10.       Call .Requery
  11.     End With
  12. End Sub
Now what it is coming up with is when I click on a value in CBUnit that is supposed to autofill CBSub with befitting values (according to the value chosen), it is coming up with the CORRECT number of values in CBSub, they're just blank. So, for instance, a field name in CBUnit named "CSMS" is supposed to autopopulate a "1" and "2" in CBSub which can be chosen. Instead it is showing 2 blank spaces. So I've obviously got something coded wrong somewhere...?

At least one problem is solved!!! Thanks so much!!!
Oct 15 '07 #9

Scott Price
Expert 100+
P: 1,384
Well, let's take a look at what you are telling the computer in lines 6 - 8 of your code.

You are saying that the VBA should set the Row Source of your CBSub (combo-box) to the statement:
Expand|Select|Wrap|Line Numbers
  1. Select CBSub From tblSub Where, etc...
How can the row source select anything from the table if it doesn't exist in the table? Since CBSub is not a field in your table, it cannot be Selected. In your post #7 you indicate that the structure of tblSub is SubID, Sub. Don't you want to reflect this? (just trying to help you understand what's happening :-)

So your Row Source should more properly look like this, eh?:
Expand|Select|Wrap|Line Numbers
  1. Select SubID, Sub From tblSub Where [UnitID] =" & Me!CBUnit
Regards,
Scott
Oct 15 '07 #10

P: 21
Well, let's take a look at what you are telling the computer in lines 6 - 8 of your code.

You are saying that the VBA should set the Row Source of your CBSub (combo-box) to the statement:
Expand|Select|Wrap|Line Numbers
  1. Select CBSub From tblSub Where, etc...
How can the row source select anything from the table if it doesn't exist in the table? Since CBSub is not a field in your table, it cannot be Selected. In your post #7 you indicate that the structure of tblSub is SubID, Sub. Don't you want to reflect this? (just trying to help you understand what's happening :-)

So your Row Source should more properly look like this, eh?:
Expand|Select|Wrap|Line Numbers
  1. Select SubID, Sub From tblSub Where [UnitID] =" & Me!CBUnit
Regards,
Scott
Thanks for explaining that to me. It makes alot more sense now to me, definitely. I plugged the following into the Event Procedure:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Private Sub CBUnit_AfterUpdate()
  3.   With Me![CBSub]
  4.       If IsNull(Me!CBUnit) Then
  5.         .RowSource = ""
  6.       Else
  7.         .RowSource = "SELECT [SubID,Sub] " & _
  8.                      "FROM tbl_Sub " & _
  9.                      "WHERE [UnitID]=" & Me!CBUnit
  10.       End If
  11.       Call .Requery
  12.     End With
  13. End Sub
I'm not sure what the "Option Compare Database" is for but it's been there since last week. Regardless, now when I get into the CBSub combo is brings up a prompt saying "Enter Parameter Value" with SubID, Sub and a white box asking for input.

Please have patience. I am not asking someone to do this for me. I truly have tried EVERYTHING to get this to work, but to no avail. I have absolutely no idea what the heck could be wrong anymore, especially if the things you are coming up with don't work, and you're an expert!
Oct 15 '07 #11

NeoPa
Expert Mod 15k+
P: 31,494
The angle brackets ([ & ]) surround text, the whole of which is the name of a single entity. "[SubID,Sub]" is looking for a single field called "SubID,Sub". Try "[SubID], [Sub]" instead (Line #7).
Oct 15 '07 #12

P: 21
The angle brackets ([ & ]) surround text, the whole of which is the name of a single entity. "[SubID,Sub]" is looking for a single field called "SubID,Sub". Try "[SubID], [Sub]" instead (Line #7).
YAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAY!!!

It worked!

Sorry for my excitement! But YAAAAAAAY!

Thank you all SOOO much for all yoru help! There is NO way I would have figured that simple step out. :)

Thanks again!
Oct 15 '07 #13

Scott Price
Expert 100+
P: 1,384
As for the Option Compare Database, that is just telling VBA which database to look at when running the code.

As a good programming practice you should also have Option Explicit just below that line. This tells VBA that you must explicitly declare any variables that you use by the Dim or similar method. This avoids headaches down the road when you mis-type a variable name, as without the Option Explicit a mis-typed name will be assumed to be a new variable with Variant data type.

In the VBA editor window, go to Tools>Options>Edit Tab, make sure the Require Variable Declaration check box is checked. This will automatically add this Option Explicit line to every new module that is created.

Kind Regards,
Scott
Oct 15 '07 #14

Scott Price
Expert 100+
P: 1,384
YAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAY!!!

It worked!

Sorry for my excitement! But YAAAAAAAY!

Thank you all SOOO much for all yoru help! There is NO way I would have figured that simple step out. :)

Thanks again!
You are quite welcome! We are all glad to help, and especially glad when you reach that moment of understanding: the *click* 'Oh, now I get it!' moment. That's what we live for here :-)

Regards,
Scott
Oct 15 '07 #15

NeoPa
Expert Mod 15k+
P: 31,494
YAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAY!!!

It worked!

Sorry for my excitement! But YAAAAAAAY!

Thank you all SOOO much for all yoru help! There is NO way I would have figured that simple step out. :)

Thanks again!
Deanndra,

Please pay close attention to Scott's post (#14). It's very good advice.
YAAAAAY is good in my book. Excitement is good. We're both very pleased to have helped cause that excitement :)
Oct 16 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.