Updating Combo box in form where items don't exist

[MichaelWaimauku]

I'm having trouble completing the code for my form. It updates a table if the item doesn't exist in the combo box.

The following error displays:
Run-time error '3464': Data type mismatch in criteria expression.

If DCount("Reason", "NonConformity", "Reason = '" & Me.Reason.Text & "'") = 0 Then strOpenArgs = Me.Reason.Text

I've attached the database. The form NonConfomity, field Reason, is where the code is operated from using double click.

Any help appreciated.

 

[pbaldy]

Wrong syntax:

http://www.mvps.org/access/general/gen0018.htm

 

[MichaelWaimauku]

ok, but it is text that is getting entered however, it is to go in the second column of the combo box and not the first one which is an autonumber. How can I re-write this?

 

[pbaldy]

But the field is numeric:

DCount("Reason", "NonConformity", "Reason = " & Me.Reason)

 

[MichaelWaimauku]

Yeah I know, it's a lookup and displays the text but the primary key is numeric. Not sure how to explain it. There are 2 fields in the table, the combo box in the form only displays the second field and that's the field I want to update.

 

[pbaldy]

The code I posted works without error and a second form is opened. You have limit to list set to Yes, so you won't be able to add a new record that way. Typically the notinlist event is used to add records.

 

[MichaelWaimauku]

I used the code from this database and tried to replicate it but got stuck as I don't know vba. I want the list to be limited. However, once someone tries to enter some code that's not on the list, the error message appears to double click to open a new form. From there, it's meant to take the text entered and enter it in as a new record. Not sure what I'm doing wrong???

 

[MichaelWaimauku]

Sorry, I uploaded the wrong db. Here's my one, with the numeric primary key. I'm trying to replicate the sample db previously uploaded. I just can't make out why this is not working:

Private Sub Combo2_DblClick(Cancel As Integer)

Dim lngcombo2 As Long
Dim strOpenArgs As String

If DCount("ItemName", "Tbl_Items", "ItemName = '" & Me.Combo2.Text & "'") = 0 Then strOpenArgs = Me.Combo2.Text

If IsNull(Me.Combo2) Then
Me.Combo2.Text = ""
Else
lngcombo2 = Me![Combo2]
Me.Combo2 = Null
End If

DoCmd.OpenForm "FRM_Items", , , , , acDialog, "New#" & strOpenArgs

Me.Combo2.Requery

If lngcombo2 <> 0 Then
Me.Combo2 = lngcombo2
Else If lngInfoXchg <> 0 Then
Me.Combo2 = lngInfoXchg
End If


End Sub