Hi all
I am a newbie when it comes to access. My company requires that all Databases run on the company SQL back-end as a result i have a Access project that connects to the server.
I have two tables, table 1 called tblProjectDetai ls, table 2 called tblLearners
The fields in tblProjectDetai ls are - ProjectPK numeric (Its also the Primary Key)]
-
Name char (50)
-
Description char (50) There are other field but they dont play a role in the comboboxes
-
-
The fields in tblLearners are
-
LeanerPk numeric (Primary Key)
-
Surname char (50)
-
FirstName char (50)
-
ProjectNo numeric
I have created two bound combo boxes and the idea is that one of my staff members can select the project first(called cboProject), once that is done then the second combo box would show the appropriate learners(called cboLearner).
I have the following code as an after update event for cboProject - cboLearner.RowSource = "SELECT * from tblLearners WHERE ProjectNo = " & [Forms]![frmProjectMangerTracking]![subfrmlinkin]![cboProject]
frmProjectMange rTracking - is the main form that i am using
subfrmlinkin - is the subform that i am using. the name is temporary until i solve my problem
When i execute the cascading combo box cboLearner is blank. Can somebody please show where i am going wrong?
Thanks in advance
12 1711
Hi. It would appear that you are referring to a control on a subform as part of your WHERE clause. The syntax for this is slightly different to what you have written, as the subform is treated as a control when it is on the main form. To access a control within the subform you need to refer to its form property: - WHERE ProjectNo = " & [Forms]![frmProjectMangerTracking]![subfrmlinkin].Form![cboProject]
See the following HowTo article on Referring to Items on a Subform for further examples.
-Stewart
FishVal 2,653
Recognized Expert Specialist - cboLearner.RowSource = "SELECT * from tblLearners WHERE ProjectNo = " & [Forms]![frmProjectMangerTracking]![subfrmlinkin]![cboProject]
Hello, there.
I almost sure Access project gets Recordset to fill combobox passing SQL expression directly to SQL server. If so, then, I guess, you couldn't include any VBA and/or any object's property/method calling in the SQL expression.
Regards,
Fish
Thanks guys
It seems now I'm still stuck 'cos i either get a blank combo box or an error message which reads
The record source 'SELECT * from tblLearners where ProjectNo = 39' specified on this form or report does not exist
Any ideas?
Thanks again
Hi. Well, this is an advance - the SQL quoted in your error message is at least showing that the project number is being passed correctly from the subform control as part of the row source statement. One error resolved.
As a temporary measure to make sure the combo can actually access tblLearners, set the row source of the combo in design view to tblLearners then switch to form view and see if the combo shows the rows from the table when selected (the fields displayed will depend on how many columns you have set the combo to show). If it can, try replacing the row source manually with the SQL statement but without its WHERE clause, again to see that the combo can access the table's rows but this time via the SQL statement. As a last check, add back the WHERE clause to the rowsource manually and see if that works. In each case you are narrowing the potential causes of the error.
Let us know how you get on.
-Stewart
Hi Guys
I really do appreciate the help. at least i have found where the problem is, but my knowledge is a bit limited so i'm not sure what the next step is to resolve it.
Stewart i followed your steps ans as soon as i added the WHERE clause it all fell apart and the combo box was blank again
Thanks
FishVal 2,653
Recognized Expert Specialist
Could you please post your code as it looks right now?
P.S. G..d save us. Add please semicolon to the end of SQL statement. I had a situation in the past when only absense of that semicolon prevented SQL server to execute command correctly.
OK, so now check that you really have a value of 39 in the projectno field of tblLearners - as we know that the table itself is accessible, and that the WHERE clause is not matching the records.
Please also check carefully when you open the table whether the projectno field is displayed left-aligned or right-aligned. If it is left-aligned it is not a number but a string value - and the where clause needed to filter your records would be slightly different if it is, as you would need to include single quotes before and after the value you are comparing.
-Stewart
Thanks Guys i solved it.... but i certainly would not have got there with out your help
Well done for solving this; as a matter of interest, what was it that was wrong?
-Stewart
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: cognoscento |
last post by:
I'm currently putting together a database for my work (not an expert by
any stretch, so muddling through as best as I can... you know the
story...) and I could use some advice and hand-holding
I've got a subform with a series of cascading combo boxes (thanks to
the Access tutorials on fontstuff.com) that let the user assign
categories to items, in this case photos. This is being done to help
constrain user selections and keep the...
|
by: Edwinah63 |
last post by:
Hi everyone,
Please let there be someone out there who can help.
I have two BOUND combo boxes on a continuous form, the second being
dependent on the first. I have no problem getting the second combo to
change depending on what values the user selects in the first box, it's
just that every time the user changes the first combobox, the second
combobox FOR EVERY RECORD goes blank.
|
by: Rabbit |
last post by:
Cascading Combo/List Boxes
This tutorial is to guide you in the creation of Cascading combo/list boxes. That is when you have multiple combo/list boxes where the selection of an option in one determines the available options in the other.
TERMINOLOGY
Row Source: The table/query from which the Combo Box or List Box gets its values. Note: There are other types of row sources that can be used but for simplicity we will stick with Tables...
|
by: klarae99 |
last post by:
Hello,
I am working on an Access 2003 Database. The tables that pertain to this issue are tblOrg, tblState, tblCity, and tblZip. I have posted the table structure with only the pertinant fields below.
tblOrg
OrgID, AutoNumber, PK
ZipID, Number, FK
tblState
StateID, AutoNumber, PK
|
by: kcdoell |
last post by:
Good Morning:
I have a form where I am trying to create cascading combo boxes. I have done this before but I am getting the following error that is throwing me off:
Procedure declaration does not match description of event or procedure having the same name.
Basically have three tables:
One for the Division location:
| |
by: kcdoell |
last post by:
I have 5 cascading combo boxes on a form. Below is a sample of my vb in the first combo box:
Private Sub CboDivision_AfterUpdate()
'When the Division is selected, the appropriate Segment list will
'display in the drop down list of CboSegment
With Me!
If IsNull(Me!cboDivision) Then
|
by: Toireasa |
last post by:
Hi,
Newbie Access developer here, and my first post on this forum, so I might not get everything right - thanks in advance for your help and your patience!
I'm using Access 2007, in XP. I'm currently trying to set up a whole pile of cascading combo boxes of different levels of complexity, so I started with the easiest set - and can't even get that to work (even using the tutorial on this site at...
|
by: bluclouds9 |
last post by:
I am new to Access and have been charged with creating a database for our course alumni.
I currently have a "Contacts" form and am trying to create a subform to hold the course alumni information. I would like the first combo box to contain the course and the second to show the approriate years. Everything seems to be working except the years are not showing in the second combo box.
My tables are set up as:
CourseNames
CourseID...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |