Whats wrong with my Cascading Combo Code

[azalea45]

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 tblProjectDetails, table 2 called tblLearners
The fields in tblProjectDetails 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] 

frmProjectMangerTracking - 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

[Stewart Ross]

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]

 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

[azalea45]

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

[Stewart Ross]

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

[azalea45]

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]

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.

[Stewart Ross]

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

[azalea45]

Thanks Guys i solved it.... but i certainly would not have got there with out your help

[Stewart Ross]

Well done for solving this; as a matter of interest, what was it that was wrong?

-Stewart

[azalea45]

Well done for solving this; as a matter of interest, what was it that was wrong?

-Stewart

I'm not totally sure but as i rebult the statement from the Where clause it worked. There must have been an error there

I have distributed the project to my staff and when they try to access the form on cboLearner they get the following error message

 SELECT permission denied on 'tblLearners',  database 'assessment informationtracking', owner 'dbo' 

i have checked the permissions on the server and the users have the following permissions on the table
SELECT
INSERT
UPDATE

any ideas?

[Stewart Ross]

Hi. Unfortunately, I have no experience in this area (permissions in SQL server). We have a SQL Server forum which may be able to help you better than I can.

A quick Google search on SELECT PERMISSION DENIED shows many hits on this topic - too many to list here, but an interesting thread from MSDN which provides some insight into the issues is linked here. The permission problems could apparently have been resolved by dropping and rebuilding the database in this case, but the poster concerned actually did an over-the-top reinstall of everything. I hope you don't end up taking such an approach to resolve your issues!

-Stewart

[azalea45]

Hi

Thanks for the advice

The solution was a simple one and when you look at a problem for too long you sometimes don't see the wood for the trees.

All my users are in two different groups. The one had permissions to the table and the other had deny access to the table, the net result is that there global permission status was deny and as a result they were not allowed access to the table.

I am glad to say that it was simple and no drastic measures had to be taken