Access 2000 Project with SQL backend

[yakdogs]

Help:
I am really a novice working with SQL. I have an Access 2000 Project form that has three combo boxes, and the information needs to be held in the prospect fields in the table, as I report heavily off this information. The combo boxes need to cascade from Combo Box 1 to Combo Box 2 then to Combo Box three.

I have been having great difficulty getting the SQL right to do this. I have even tried building it in an Access DB, and was able to get it to work. Then put in to SQL view (in ACCESS), but it doesn't like the SQL.

Sample:

SELECT DISTINCTROW tblSubCategory.PKSCID, tblSubCategory.strSubCat, tblSubCategory.FKCategoryID
FROM tblSubCategory
WHERE (((tblSubCategory.FKCategoryID)=[Forms]![frmSyncComboBoxes]![cboCategories]))
ORDER BY tblSubCategory.PKSCID;

I get errors in a View, and Store Procedures.
If feel that I am close, but haven't a clue what to do to fix the problem.

Can someone point me the right direction here.

 

[cmmrfrds]

Can you elaborate on your sample.

SELECT DISTINCTROW tblSubCategory.PKSCID, tblSubCategory.strSubCat, tblSubCategory.FKCategoryID
FROM tblSubCategory
WHERE (((tblSubCategory.FKCategoryID)=[Forms]![frmSyncComboBoxes]![cboCategories]))
ORDER BY tblSubCategory.PKSCID;

Is this the Row Source for a combobox or Form?
How are you building the Select Statement?
How does your example relate to a View or Stored Procedure?
Need a little more information on what you are doing with the SQL.

 

[yakdogs]

Let me tell you what I am trying to do then maybe you can tell me how to approach it. Maybe, I am approaching this totally wrong.

I need three Combo Boxes, 1 Category, 2 Sub-Category, 3 Reason. The user will pick a Category which will drive the items in Sub-Category, what they pick will drive what is in Reason. Keeping in mind that I need to have this information retained in the table as these are items that I report heavily off.

All the samples I have read about here, were for Access mdb. I need to do this with a Access 2000 Project, front end and SQL backend.

So I was taking their examples and building them in Access MDB, then in the Query switch it to SQL view to give me an idea of how to approach this in SQL.

The SQL view that Access mdb would give me would be like what you see above, but I received errors when putting in a View or Stored Procedure.

Is this something that can be done in a View or Stored Procedure or do I have to look at working with just the boxes themselves?

 

[cmmrfrds]

If you want to follow an example that was done for Access, then take the Access tables and put them in SQL Server. You can use DTS in SQL Server or whatever you are comfortable with. Do NOT do the example in an MDB since it will be harder to emulate the example. Instead do the example in an ADP (Access Project). An adp allows you to treat sql server tables etc. directly in the data source of a Form, Report, Combobox, etc.... Get you examples working first this way it will be easier.

In an adp you can design View and Stored Procedure which can be used as a data source. Start out simple and build from there.

 

[yakdogs]

I tried to find an example with Project was unable to locate one, do you know of one that you can point me to?

 

[cmmrfrds]

Have you set up an Access Project yet? This is the first thing you must be comfortable with.

 

[yakdogs]

I have one, but it is strickly for reporting purposes. I have been working with views and simple stored procedures in that one.

This DB is for data entry. I have my tables for the combo boxes set up, and are linked to SQL. Those look good. The form is built off of a view, only thing not working are the boxes. I currently have them just pulling from the tables, and saving the data in the appropriate field in the table, but not cascading.

So what do I do next?

 

[cmmrfrds]

I digressed.

Here is an example of how I handled the first level out of 3 like in your example - this comes from an app I have. I used global variables to hold the keys and have an additional field on the Form to display the selected key.

Private Sub CBCustomer_Click() '1st level
glbCustomerId = CBCustomer
Debug.Print "Customer Id = "; glbCustomerId
Me.txtCustomer.Requery
'--- refresh Shop Order
glbShopOrderNumber = " "
Me.CBShopOrder.Requery
'--- refresh Equipment
glbEquipmentKey = 0
Me.txtEquipment = " "
Me.CBEquipment.Requery
End Sub

Private Sub CBShopOrder_Click() '-2nd level
glbShopOrderNumber = CBShopOrder
Me.txtShopOrder.Requery
CBShopOrder.Requery
Debug.Print "glbShopOrderNumber = "; glbShopOrderNumber
'--- refresh Equipment
glbEquipmentKey = 0
Me.txtEquipment = " "
Me.CBEquipment.Requery
End Sub

Private Sub CBEquipment_Click() '-3rd level
glbEquipmentKey = CBEquipment
txtEquipment = Me.CBEquipment.Column(1)
Me.txtEquipment.Requery
Debug.Print "glbEquipmentKey = "; glbEquipmentKey
End Sub

Does this help? If you are doing this in a project then the row source can be directly out of sql server for each of the comboboxes.

My row source uses a funtion to return the key (global var).
SELECT [ShopOrders].[shopOrderNumber], [Customer].[CustomerName], [ShopOrders].[customerID] FROM ShopOrders INNER JOIN Customer ON [ShopOrders].[customerID]=[Customer].[CustomerID] WHERE ((([ShopOrders].[customerID])=ReturnGlbCustomerID()));

 

[yakdogs]

I will give it a try, and get back to you.

If I have not mentioned it, thanks for your help.