473,326 Members | 2,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Code for combo boxes returns error message!!!

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
15 1639
nico5038
3,080 Expert 2GB
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
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
32,556 Expert Mod 16PB
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
32,556 Expert Mod 16PB
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
1,384 Expert 1GB
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
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
1,384 Expert 1GB
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
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
1,384 Expert 1GB
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
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
32,556 Expert Mod 16PB
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
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
1,384 Expert 1GB
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
1,384 Expert 1GB
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
32,556 Expert Mod 16PB
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

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

Similar topics

1
by: Rodrigo-Dublin | last post by:
Hello there I wrote a web site in c# and I'm using forms authentication for some of the pages in the root folder. The site is fully accessible from the web no problem, but when I request crawler...
3
by: RodrigoIreland | last post by:
Hello there I wrote a web site in csharp and I'm using forms authentication for some of the pages in the root folder. The site is fully accessible from the web no problem, but when I request...
1
by: Jeremy Ames | last post by:
I have a datagrid that updates the table using a stored procedure. The stored procedure is confirmed to complete correctly, yet the sql data adapter is returning an error that my application is...
2
by: 3Dfelix | last post by:
Hello, I'm starting with Visual Web Developper and ASP.NET 2.0. In order to test my remote server, I created an "hello word" aplication, that is working on local server. I have copied all...
6
gregerly
by: gregerly | last post by:
Hello all, I appologize if this is the wrong place for this post. Not sure where else it would go. I am building a shopping cart with PHP. I know that on a page where you would collect the user...
2
by: Bruno Rafael Moreira de Barros | last post by:
index.php --- inlcude 'application.php'; functions.php --- function test1() { trigger_error('My error'); return FALSE;
1
by: sanghamitra das | last post by:
We have developed a web application in which we are using login user(as a array structure) in session variable using HttpContext for some purpose. The session is availabe in the same project...
6
by: MNNovice | last post by:
Hello! I am trying to use a search button to find : 1) VendorName and/or 2)InvoiceNo. One is working while the other is not. When I look for an invoice number, it works but when I try to find a...
20
by: cowboyrocks2009 | last post by:
Hi, I need help to automate my code to take data from input file. Also I need to create it as a function so that I can pass it to some other program. I am new to Java so having a bit limitation to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.