Application-defined or object-defined error

[chanvba]

HI

I am new to VBA, I am trying to populate 10 textboxes with values from a table. I am getting "Application-defined or object-defined error" when I open the form.
Can you please find where is it going wrong?


Text boxes are named compl_01 to compl_10.
And NUMer value in sql is supposed to change to NUM01 …NUM10 with each loop.


Thanks a lot

If Measure = "CIS" Then

'Const cintLastTextBoxNum As Integer = 10
Dim i As Integer
Dim qdf As QueryDef
Dim rs As DAO.Recordset
Dim COMPl As String
Dim COMPla As String
Dim NUMer As String
Dim dba As Database
Dim sqlb As String

For i = 1 To 10
NUMer = "NUM" & Format(i, "00")
COMPla = "compl_" & Format(i, "00")

sqlb = "SELECT DISTINCT HEDIS_HYBRID_CMPL_ALL.[compliance]" _
& "FROM [HEDIS_HYBRID_CMPL_ALL]" _
& "WHERE HEDIS_HYBRID_CMPL_ALL.ENCRYPT_ID = '" & Left(Tracking_ID, 7) & "'" _
& "And HEDIS_HYBRID_CMPL_ALL.CMPL_RULE_ID = '" & NUMer & "'"
Set dba = CurrentDb
Set qdf = dba.CreateQueryDef("", sqlb)
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
COMPl = ""
Do While Not rs.EOF
If COMPl = "" Then
COMPl = rs![compliance].Value
Else
COMPl = "COMPl" & "," & rs![compliance].Value
End If

rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Set dba = Nothing
Set qdf = Nothing
Forms(stDocName).COMPla = COMPl
Next i
End If

 

[Uncle Gizmo]

Add the following message box as shown:-

MsgBox " >>> " & sqlb
Set dba = CurrentDb

and post the text from the message generated here.

 

[chanvba]

Thank you Gizmo,

Here is the message i got :

>>>SELECT DISTINCT HEDIS_HYBRID_CMPL_ALL.[compliance]FROM [HEDIS_HYBRID_CMPL_ALL] WHERE HEDIS_HYBRID_CMPL_ALL.ENCRYPT_ID = 'CIMP109' And HEDIS_HYBRID_CMPL_ALL.CMPL_RULE_ID = 'NUM01'

When i click OK, i again get original error message "Application-defined or object-defined error"

Thanks,

 

[Uncle Gizmo]

Change this row from:-
sqlb = "SELECT DISTINCT HEDIS_HYBRID_CMPL_ALL.[compliance]" _

To:-
sqlb = "SELECT DISTINCT HEDIS_HYBRID_CMPL_ALL.[compliance] " _

 

[chanvba]

Thank you,
I modified as you suggested, and the msgbox I received is attached.

 

[GinaWhipp]

What happened when you clicked Okay?

 

[Uncle Gizmo]

You can use this method to copy the SQL from the message box and paste it in to a Query.

http://hmemcpy.com/2009/02/awesome-tip-copy-text-from-any-messagebox-to-clipboard/

You may get more meaningful info about the error.

 

[chanvba]

What happened when you clicked Okay?

I again got same error message.

 

[GinaWhipp]

Try this...

sqlb = "SELECT DISTINCT HEDIS_HYBRID_CMPL_ALL.[compliance] " & _
"FROM [HEDIS_HYBRID_CMPL_ALL] " & _
"WHERE HEDIS_HYBRID_CMPL_ALL.ENCRYPT_ID = '" & Left(Tracking_ID, 7) & "' " & _
"And HEDIS_HYBRID_CMPL_ALL.CMPL_RULE_ID = '" & NUMer & "'"

 

[Uncle Gizmo]

HEDIS_HYBRID_CMPL_ALL.ENCRYPT_ID

And
HEDIS_HYBRID_CMPL_ALL.CMPL_RULE_ID

Look like they are supposed to be numbers?

Could you post some examples of values they contain?

 

[chanvba]

Both ENCRYPT_ID and CMPL_RULE_ID are string variables. I do not have any field with numbers.

 

[chanvba]

Sorry forgot to post examples:
ENCRYPT_ID have values like- CIMP001, CIMP002, CIMP003 etc
CMPL_RULE_ID have values NUM01, NUM02 ..... NUM10

 

[chanvba]

Hi Gina, Thank you, I tried your sql code, its the same as before.

I guess i am not able to assign query value to the text field.
either do loop failing or
Forms(stDocName).COMPla = COMPl
is not able to pass on the result to the text field.
its my guess , I am not sure.

If i do not use for next cycle, instead write code 10 times for NUM01 to NUM10 for 10 text fields, it works fine, but each time i change
Dim qdf As QueryDef
Dim rs As DAO.Recordset
Dim COMPl As String
Dim COMPla As String
Dim NUMer As String
Dim dba As Database
Dim sqlb As String

to 10 different names for 10 different codes, and it works. But as soon as i use for next loop to do it in one step , i get Application-defined or object-defined error

 

[GinaWhipp]

What line does it stop on when you get that error?

 

[chanvba]

I did some debugging, the code was stopping at:
Forms(stDocName).COMPla = COMPl
i changed to :
Forms(stDocName).controls(COMPla).Value = COMPl

then it worked like charm.

Thanks a lot to you and Gizmo for help