ERROR:Unable to impersonate a DCOM client???

[brewpedals]

Hi,

I get the subject error when I run this code.

Private Sub AFE_No_Change()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String

sql = "SELECT Count([PC_PCR Log].AFE_No) AS CountOfAFE_No " & _
      "FROM [PC_PCR Log] " & _
      "HAVING (((Count([PC_PCR Log].AFE_No))=[Me].[AFE_No].[Value]));"
         
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sql)

Me!PCR_No.Value = rst!UserCount + 1
Me!unb_PCR_No.Value = Me!AFE_No.Value & " - " & Me!PCR_No.Value

End Sub

I have a table PC_PRC_Log with two primary keys AFE_No and PCR_No.

I want the user to pick the AFE_NO, and have the code count the nuber of records with the same AFE_NO, add 1, and place the value in the forms PCR_No field then display the concatenated AFE_No and PCR_No in an unbound field.

Can you tell what I've done wrong?

I did a search on this board for DCOM and found only two threads, one was associated with ActiveX. I have an ActiveX control on this form, but I get the error even ater the ActiveX control was deleted.

Many thanks for your time.

 

[WayneRyan]

brew,

I "isolated" your [AFE_No] field. When Access parses it now,
it will use the value of Me.[AFE_No]. If it is a number, then
you should be OK.

All of the embedded spaces in your names gets confusing. There
are very subtle differences (major to Access) in:

[PC_PCR_Log]
[PC_PCR Log]
[PC PCR_Log]
[PC PCR Log]

Sometimes Access will "give you" the underscores, sometimes not.
It's just best to avoid the confusion. It is possible that might
be an additional problem here.

sql = "SELECT Count([PC_PCR Log].AFE_No) AS CountOfAFE_No " & _
      "FROM [PC_PCR Log] " & _
      "HAVING Count([PC_PCR Log].AFE_No) = " & Me.[AFE_No] & ";"

Wayne

 

[brewpedals]

Thanks Wayne,

Partial success.

I took your advise and removed the space from my table name and changed the query accordingly.

My problem now is AFE_No is a text field that contains two periods "XX.XXXX.XXXXX" and I get a RunTime 3075 - an illegal use of "."'s

Refresh my memory, how do I isolate it so it reads text?

I tried:
"" & Me.[AFE_No] & ""
""""" & Me.[AFE_No] & """""
' & Me.[AFE_No] & '
"'" & Me.[AFE_No] & "'"
& Me.[AFE_No] &

Thanks - you got me on the right track.

 

[WayneRyan]

brew,

Add single-quotes. Data must have no single-quotes in it!!! To handle
that do a search here for Chr(34) - just use double-quotes as delimiter.

sql = "SELECT Count([PC_PCR Log].AFE_No) AS CountOfAFE_No " & _
      "FROM [PC_PCR Log] " & _
      "HAVING Count([PC_PCR Log].AFE_No) = '" & Me.[AFE_No] & "';"

Wayne

 

[brewpedals]

That worked, Thanks again!

I was faced with a datatype error that was solved by adding a "group by" to my query.

sql = "SELECT Count(PC_PCRLog.PCR_No) AS CountOfPCR_No " & _
      "FROM PC_PCRLog " & _
      "GROUP BY PC_PCRLog.AFE_No " & _
      "HAVING (PC_PCRLog.AFE_No)='" & Me.AFE_No.Value & "';"