duplicate control

[marlun]

Hi!
I have an entry-form, where I add records to my database. When I fill in a S/N (which has to be unique) and I would like to have a VB-code to check for duplicates in that field.
The S/N looks liks follows 4-700-1.0250009. I have a code

Dim rs As Object
Dim svar
Set rs = Me.Recordset.Clone
rs.FindFirst "[S/N] = '" & Str(Nz(Me![Inskjut], 0)) & "'"
If rs.NoMatch Then

But I get a run-time error '13' type missmatch

Please help!!!

Thanks

 

[katho]

Hi marlun,

I guess if you don't use the NZ function, you will get no errors (you don't need that function probably if you set the properties for the control not to allow null entries).

Greetings,

katho

 

[FancyPrairie]

I agree with katho, you have some unecessary stuff in the sql statement. Also, you're referencing the RecordsetClone incorrectly. Try this:

Me.RecordsetClone.FindFirst "[S/N] = '" & [Inskjut] & "'"
if (Me.RecordsetClone.NoMatch)

 

[marlun]

Private Sub Inskjut_LostFocus()

Me.RecordsetClone.FindFirst "[S/N] = '" & [Inskjut] & "'"
If (Me.RecordsetClone.NoMatch) Then

MsgBox ("Doesn't exist!&quot
DoCmd.GoToRecord , , acNewRec
ProdSN = Inskjut
Antal.SetFocus
Else:
'If the ProdSN already exists I have to fill in a new S/N

But when I fill in a S/N that already exists it doesn,t jump to the Else:.
It says "Doesn't exist!" And then I can continue to fill out the form, and when I click the Add record- button it says "Can't go to that record!"

I'm confused, seems to be something wrong with the If-line

the "[S/N] = '" & [Inskjut] & "'" worked like a charm!

So many thanks for your help.

 

[KenReay]

Hi

Not sure this is the problem but, you have ELSE as a label

acNewRec
ProdSN = Inskjut
Antal.SetFocus
Else:
because you have a : at the end Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[marlun]

I removed the comma ) but it didn,t make any difference.
This is the code for Inskjut_lost_focus:

Private Sub Inskjut_LostFocus()

Me.RecordsetClone.FindFirst "[S/N] = '" & [Inskjut] & "'"

If (Me.RecordsetClone.NoMatch) Then

MsgBox ("Doesn't Exist!&quot
DoCmd.GoToRecord , , acNewRec
ProdSN = Inskjut
Antal.SetFocus


Else
svar = MsgBox("S/N already exists, fill in a new S/N! ", vbInformation)
Inskjut.SetFocus

Sign.Locked = True
DatIn.Locked = True
Lagervärde.Locked = True
Leverantör.Locked = True
Antal.Locked = True
Plats.Locked = True
Marknad.Locked = True
ProjIn.Locked = True
Ank_kont_rap.Locked = True
IB_in.Locked = True
Benämning.Locked = True
Pris.Locked = True

End If
End Sub

I hope tou see something...

 

[KenReay]

Hi

Try same code, but in the after update event rather than lostfocus event Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[marlun]

Didn't make any difference.

I don't think this makes a difference but I use a barcode-reader and when it has read a barcode it automaticly tabs to next textbox. Whats the difference between Lost_focus and After_Update.

 

[KenReay]

Hi

Lost focus is when the focus moves off the control, after update (of the control) is just after the input has been moved from the input buffer to the form control.

Sorry at the moment I am out of ideas on this, let is stew for a while, and it may come to mind! Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK