What's wrong with this code? (1 Viewer)

[SurajAR]

Private Sub Form_Load()

Dim Sql As String

If cn.State = adStateOpen Then cn.Close

Set cn = CurrentProject.AccessConnection

If rs.State= adStateOpen Then rs.Close

With rs

Sql = “SELECT tblSubCategory.SubCategoryID,tblSubCategory.SubCategoryName as [SubCategoryName],tblCategory.CategoryID,tblCategory.CategoryName as [CategoryName] FROM tblCategory INNER JOIN tblSubCategory ON tblCategory.CategoryID = tblSubCategory.CategoryID order by tblCategory.CategoryID”

.CursorType = adOpenDynamic

.CursorLocation = adOpenDynamic

.LockType = adLockOptimistic

.Open Sql, cn, , , adCmdText

End With

Set lstCategory.Recordset = rs

End Sub

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[GPGeorge]

Private Sub Form_Load()

Dim Sql As String

If cn.State = adStateOpen Then cn.Close

Set cn = CurrentProject.AccessConnection

If rs.State= adStateOpen Then rs.Close

With rs

Sql = “SELECT tblSubCategory.SubCategoryID,tblSubCategory.SubCategoryName as [SubCategoryName],tblCategory.CategoryID,tblCategory.CategoryName as [CategoryName] FROM tblCategory INNER JOIN tblSubCategory ON tblCategory.CategoryID = tblSubCategory.CategoryID order by tblCategory.CategoryID”

.CursorType = adOpenDynamic

.CursorLocation = adOpenDynamic

.LockType = adLockOptimistic

.Open Sql, cn, , , adCmdText

End With

Set lstCategory.Recordset = rs

End Sub

Does the code fail to run at all? Does the code fail to compile? Does the code raise an error? If so, what line causes that error?

 

[theDBguy]

Hi. Welcome to AWF!

Just FYI, I moved your thread out of the Introduction forum.

 

[GPGeorge]

Cross posted here.

 

[cheekybuddha]

Hello and welcome!

Where are your connection and recordset variables (cn, rs) declared?

Do you have a reference set to the ADODB library?

Do you have Option Explicit declared at the top of every code module?

Why are you bothering to set a listbox RowSource using a recordset, rather than just assigning the SQL?

 

[CJ_London]

why are you aliasing here

tblSubCategory.SubCategoryName as [SubCategoryName],.........................,tblCategory.CategoryName as [CategoryName]

1. Implication is another table in your query has fields with the same name which in turn implies poor table design
2. Although you are not prompted not to do it, aliasing a field with the same name as an existing field will create an unexpected error

and agree with cheeky - why are you using ADO when it is not necessary and probably slightly slower than DAO? ADO has it's uses but this isn't one of them.

 

[The_Doc_Man]

In general, when you ask "what is wrong with this xxxx?" it would be nice to tell us what you EXPECTED to do and what it DID (or DIDN'T) do. We're good but ... well, I can't speak for others, but I flunked out of mind-reading school.

 

[jdraw]

As others have mentioned, we know little of you and your set up. To get focused responses, it would be helpful if you provided some information (context) regarding:
-you/your experience
-your application's purpose/criticality
-the rationale for selecting ADO vs DAO
-any testing/results
-expectations