Hello,

I am trying to create a module that will check to see if the "fileRef" of a new record is the same as any others already created.

My main table that holds all of my records stores a fileRef over a number of fields. i.e.

123456/156/456/789/458

Would be split into fields "fielda" , "fieldb", .... "fielde".

Fielda = 123456
fieldb = 156....
fielde = 458

Using a query I assemble these fields to give the overall fileRef using the expression below:

Expr1: [fielda] & IIf([fieldb] Is Not Null,"/" & [fieldb]) & IIf([fieldc] Is Not Null,"/" & [fieldc]) & IIf([fieldd] Is Not Null,"/" & [fieldd]) & IIf([fielde] Is Not Null,"/" & [fielde])

Using a form called "fileViewNewSec1" the user can create a new file and can enter a new fileRef into the 5 different fields "fielda-e".

When the user clicks on the "save & close" button the following code will be run...

Public Sub noDuplicates()

Dim fileRef As String
Dim rs As Recordset

fileRef = Form_fileViewNewSec1.fielda

If IsNull(Form_fileViewNewSec1.fieldb) = False Then
fileRef = fileRef & "/" & Form_fileViewNewSec1.fieldb

If IsNull(Form_fileViewNewSec1.fieldc) = False Then
fileRef = fileRef & "/" & Form_fileViewNewSec1.fieldc

If IsNull(Form_fileViewNewSec1.fieldd) = False Then
fileRef = fileRef & "/" & Form_fileViewNewSec1.fieldd

If IsNull(Form_fileViewNewSec1.fielde) = False Then
fileRef = fileRef & "/" & Form_fileViewNewSec1.fielde
End If
End If
End If
End If


Set rs = Form_section1.RecordsetClone
rs.FindFirst "[Expr1] = " & fileRef

MsgBox rs.NoMatch


End Sub

However, even tho I know that the value of the "fileRef" variable is equal to i.e. 123456/1 a record in the "expr1" field of the "section1" form the NoMatch property is returning True.

I also tried to use the DoCmd.FindRecord also but access tell me "it isnt available right now" or something. God knows why.

Can anyone know why that might be happening or even know of any other way I can achieve my goal.

Cheers Mark

Mark,

I can't quite follow the logic here, but I think you have a serious
problem with the nesting of your if statements.

As coded, the last field (Form_fileViewNewSec1) will only be added
in if ALL other fields are present! I don't think you want the IF
statements nested.


If IsNull(Form_fileViewNewSec1.fieldb) = False Then
fileRef = fileRef & "/" & Form_fileViewNewSec1.fieldb
'
' Note: These lines only get executed if the above is TRUE
' I don't think that's the intention
'
If IsNull(Form_fileViewNewSec1.fieldc) = False Then
fileRef = fileRef & "/" & Form_fileViewNewSec1.fieldc
If IsNull(Form_fileViewNewSec1.fieldd) = False Then
fileRef = fileRef & "/" & Form_fileViewNewSec1.fieldd
If IsNull(Form_fileViewNewSec1.fielde) = False Then
fileRef = fileRef & "/" & Form_fileViewNewSec1.fielde
End If
End If
End If
End If


Additionally, you might run into situations where the form variable
is not null, but is an empty string. There is a big difference there.
You might want to use the Len function or the Nz function.

Also, instead of a recordset, you could use the DLookup or DCount
function ... much easier.

Wayne

Hey,

Thanks for your advice on using Dlookup I was at first having problems with matching the values I believe because I was missing the 's around the string value. i.e.

I was doing this.....
temp = DLookup("Expr1", "section1", "[Expr1]= 595000/1")

Instead of this....
temp = DLookup("Expr1", "section1", "[Expr1]= '595000/1'")

I need the If statements nested as I do not want later fields populated unless the previous was also. If not I would end up with fileRefs like.

123456/123//123

instead of:

123456/123/123

Cheers for your help!