Check if Exists

[jossimon]

On click of button, I want the VBA to check if an id exists. I know it sounds simple, but I tried the following and its not working....

If IsNull("Select * From TItemXRef where TItemXRef.ItemNumber = Forms!FU_TItemXRef2.SITMTxt") Then
MsgBox "The PeopleSoft Item Number (BK Number) you have entered already exists. Please check the number. Try again or edit the existing number."
Else

I am not getting the message when the number already exists. Any thoughts?

 

[Dryseals]

I use a recordest and then check to see the record count of the recordset to see if certian things exist. You could also use a findfirst.

 

[redapples]

Have you tried opening a recordset of that table and then using the recordset.find method? This could be caught by using e.g. (air code assumes dimensioned variables for recordset and connection and using ADO)

 rs.open "TItemXRef", conn,3,3
rs.find "ItemNumber = '" &  Forms!FU_TItemXRef2.SITMTxt & "'"
If Not recordset.EOF then
     MsgBox "The PeopleSoft Item Number (BK Number) you have entered already exists.  Please check the number.  Try again or edit the existing number."
Else
...
End if

If it cant find it it goes to the end of the recordset thus making EOF true

Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk

 

[jossimon]

Do not understand what this means, "e.g. (air code assumes dimensioned variables for recordset and connection and using ADO)" What is air code? What is ADO?

I did the code above and got the following error:
Object Required.

Sorry I am still really new to VBA.

 

[PHV]

If DCount("*", "TItemXRef", "ItemNumber='" & Forms!FU_TItemXRef2!SITMTxt & "'") > 0 Then
MsgBox "The PeopleSoft Item Number (BK Number) you have entered already exists. Please check the number. Try again or edit the existing number."
Else
...
If ItemNumber is defined as numeric in TItemXRef then get rid of the single quotes.

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

 

[redapples]

sorry about that-
Air code - code done on the fly and not tested
Dimensioned variables - Dim Myvar as variant e.g.

so for about you would need to have

dim rs as new ADODB.Recordset
Dim cnn as ADODB.Connection

Set cnn = CurrentProject.Connection

ADO - ActiveX Data Objects

the reason you got the error was that neither rs or cnn were dimensioned.

look for this helpfile on your machine for more info

ADO210.CHM

Want the best answers? See FAQ181-2886

http://www.redapples.v21hosting.co.uk