Data type mismatch error

[PaulA]

Hi, all -

I am modifying an existing database and replacing an early text field with an autonumber. The field name is the same as previously with with the values and data type being different. The field in question is PTID

In the previous version, I had code that worked fine to determine the existence of a record through search criteria and if such a record didn't exist, one would be created. The search criteria comes from the value in a lookup combo box.

With the values and the data type being different, I'm getting a data type mismatch in criteria expresson error, but not sure how to correct it.

Here is the code:

Private Sub cbxResidentLookup_AfterUpdate()
On Error GoTo cbxResidentLookup_AfterUpdate_Err

   Dim strCriteria As String      ' This is the argument to the FindFirst method.
   Dim rstClone As Object ' Recordset used to search.
   Dim strID As String    ' The PtID# to search for.
   Dim Count As Integer
   Dim n As Integer
   Dim rs as Recordset
    
  ' Build the criteria.
          strID = Me.cbxResidentLookup 
          strCriteria = "[PtID] = '" & strID & "'"
          Count = 0
          n = DCount("PtID", "tblResGeneralClinical", strCriteria)
   
   Perform the search.
          Count = Count + n
          If Count = 0 Then

          MsgBox "A clinical record has been added for this patient."
      
              With rs
           
                  DoCmd.GoToRecord , , acNewRec
       Me.PtID = strID
               
             End With
  
       End If

Any throughts on how to correct this would be appreciated.

Thanks.

 

[CJ_London]

after 350 posts, you should know by now that your code is difficult to read without using code tags to preserve indenting.

 

[PaulA]

Appologies - hope the edit is an improvement.

 

[CJ_London]

looks the same to me - go to the advanced editor, paste your indented code and then highlight the code and click the code button (the one with #)

Or click the code button first and insert your code between the two elements

 

[PaulA]

OK - Worked this time. Thanks.

 

[bob fitz]

AFAIK it not possible to assign a value to an Auto-Number field.

 

[PaulA]

Thanks for your reply.

Actually, in this situation, the field value referrs to an autonumber in another table but in the table that is being searched, it is a regular integer. Not sure if this makes a difference.

 

[bob fitz]

I am modifying an existing database and replacing an early text field with an autonumber. The field name is the same as previously with with the values and data type being different. The field in question is PTID

In the code you posted:

Me.PtID = strID

I don't think you can assign the value in strID to the field PtID if PtID is an auto-number field.

 

[Galaxiom]

For numeric criteria, change this line:

strCriteria = "[PtID] = '" & strID & "'"

to

strCriteria = "[PtID] = " & strID

However naming a numeric textbox with the prefix str is going to confuse.

 

[PaulA]

A simple solution worked.

Thanks, Galaxiom.

 

[CJ_London]

OK - Worked this time. Thanks

goes to show - layout your code so people can read it and you get plenty of responses