duplicate control 1

[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

 

[KenReay]

Hi

To begin

Set rs = Me.Recordset.Clone

should be

Set rs = Me.RecordsetClone
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]

Hi again. It didn't work, I still get run-time error '13' type missmatch.
There is something wrong with this line:

rs.FindFirst "[ProdSN] = '" & Str(Nz(Me![Inskjut], 0)) & "'"

Inskjut is a unboung textbox and ProdSN a textbox on my form connected to a field in a table. It had the format "text" and have the following settings:
Field size: 50
Required: Yes
Allow zerolength: No
Indexed: No
Unicode Co: Yes

What does Nz do???

thanks

 

[KenReay]

Hi

Nz() returns the value you sent, or if that value is null, it returns the second parameter

so Nz(Me![Inskjut], 0)

returns the contents of Me![Inskjut] or if that is null it returns zero Regards

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

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

UK

 

[KenReay]

Hi another point, not the solution I suspect, but you should say

Dim rs As Recordset

cos that is what it is
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]

OK. Can you see what is wrong with the line below

rs.FindFirst "[ProdSN] = '" & Str(Nz(Me![Inskjut], 0)) & "'"

still get the same error message. I'm stuck with this problem. I use it to add a new item in the db, and it checks if there is any record with the same ProdSN. The prodSN can look like 4-700-1.0250009 or BLM7001, that means numbers, letters and "-" "."

Very greatful for any help

/marlun

 

[KenReay]

Hi

Which version of Access are you using? 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]

Access 2000
I changed it to

Dim rs As Recordset.

But now I get the following error message.

Compile error
Method or data member not found!

.FindFirst is marked when I open the VB-window

 

[KenReay]

Hi

That is why I asked the version of Access

Check your references (open any code module in design view, and choose Tools \ References), see if you have a reference to DAO

The syntax you are using is DAO syntax, in ADO the findfirst command is replaced by simply Find 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]

The Microsoft DAO 3.6 Object Library box was not checked, i guess that is what you ment???

 

[KenReay]

Hi

Yes, that is correct

In Access97 DAO is the default data access librray, but in Access2000 and AccessXP the default is ADO, so if you want to use DAO you have to select the library 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]

Ok, I have checked that box but now I get a runtime error '13' type missmatch when i type a SN with a "-" in it.
This is my code

Dim rs As Recordset
Dim svar
Set rs = Me.RecordsetClone
rs.FindFirst "[ProdSN] = '" & Str(Nz(Me![Inskjut], 0)) & "'"
If rs.NoMatch Then
.
.
.

when I type a number(45) I don't get a error message.
I'm very confused.

 

[KenReay]

Hi

How is ProdSN defined in your table?

I notice in an earlier post you gave the table definition of Inksjut but not ProdSN

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]

Hi again!
Inskjut is an unbound textbox. And ProdSN is connected to a table. The form works with numbers, but not when I use dots etc ".","-".
I use a barcode-reader thats why I have to get this to work. I'm not that experienced with access, but to me it seems like there is something with this line that doesn't understand dots "." and others figures.

rs.FindFirst "[ProdSN] = '" & Str(Nz(Me![Inskjut], 0)) & "'"

here is the whole code

Dim rs As Recordset
Dim svar
Set rs = Me.RecordsetClone
rs.FindFirst "[ProdSN] = '" & Str(Nz(Me![Inskjut], 0)) & "'"
If rs.NoMatch Then
.
.
.
I'm stuck, please help

 

[KenReay]

Hi

Based on the information you have given I cannot see anything wrong with your code

Humor me and try

rs.FindFirst "[ProdSN] = '" & Me!Inskjut & "'"
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]

Hi again, it worked, it accepts the whole ProdSN.

But when I have add a record to the db and I try to add it again, the db should alarm me "the ProdSN already exists". But it doesn't.
After I have filled out the form and press the add button it says "can't go to that record". I want the db to alarm me when the "Inskjut"-textbox loses its focus.

Seems like the "If" line doesnt work like it should?????

Many thanks for the solution

 

[Coxylaad]

Ah - just found out - you cant run DAO and ADO in the same database?

so if this is the case (correct me if I am wrong) but is there a recordsetclone equivalent in ADO?

Thanks

Ian

 

[PHV]

Search you local drives for files named ado*.chm

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

you cant run DAO and ADO in the same database
Why not ?
You simply have to fully qualify the ambiguous objects:
Dim rs1 As DAO.Recordset, rs2 As ADODB.Recordset

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886