Code for combo boxes returns error message!!!

[deanndra]

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.AUTONUM BER, 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:

Private Sub CBUnit_AfterUpdate()
  With Me![Sub]
      If IsNull(Me!Unit) Then
        .RowSource = ""
      Else
        .RowSource = "SELECT [Sub] " & _
                     "FROM tbl_Sub " & _
                     "WHERE [UnitID]=" & Me!Unit
      End If
      Call .Requery
    End With
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???

[nico5038]

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)

[deanndra]

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!

[NeoPa]

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

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[NeoPa]

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.

[Scott Price]

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.AUTONUM BER, 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:

Private Sub CBUnit_AfterUpdate()
  With Me![Sub]
      If IsNull(Me!Unit) Then
        .RowSource = ""
      Else
        .RowSource = "SELECT [Sub] " & _
                     "FROM tbl_Sub " & _
                     "WHERE [UnitID]=" & Me!Unit
      End If
      Call .Requery
    End With
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

[deanndra]

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

[i]Field; Type; IndexInfo[/i]
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
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

[i]Field; type; IndexInfo[/i]
UnitID; AutoNumber; PK
Unit; Text; FK

Table Name=tbl_Sub

[i]Field; Type; IndexInfo[/i]
Sub; Text
UnitID; Number; FK
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.AUTONU MBER"

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.

[Scott Price]

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

[deanndra]

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):

Private Sub CBUnit_AfterUpdate()
  With Me![CBSub]
      If IsNull(Me!CBUnit) Then
        .RowSource = ""
      Else
        .RowSource = "SELECT [CBSub] " & _
                     "FROM tbl_Sub " & _
                     "WHERE [UnitID]=" & Me!CBUnit
      End If
      Call .Requery
    End With
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!!!

[Scott Price]

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:

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?:

Select SubID, Sub From tblSub Where [UnitID] =" & Me!CBUnit

Regards,
Scott

[deanndra]

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:

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?:

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:

Option Compare Database
Private Sub CBUnit_AfterUpdate()
  With Me![CBSub]
      If IsNull(Me!CBUnit) Then
        .RowSource = ""
      Else
        .RowSource = "SELECT [SubID,Sub] " & _
                     "FROM tbl_Sub " & _
                     "WHERE [UnitID]=" & Me!CBUnit
      End If
      Call .Requery
    End With
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!

[NeoPa]

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).

[deanndra]

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).

YAAAAAAAAAAAAAA AAAAAAAAAAAAAAA AAAY!!!

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!

[Scott Price]

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>E dit 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

[Scott Price]

YAAAAAAAAAAAAAA AAAAAAAAAAAAAAA AAAY!!!

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