check if value exist in subform but only in the records currently displayed

[sspreyer]

Hi , all

wonder if any one can point me in the right direction to be honest not even sure i'm using the correct function.

I'm currently trying to check if a value exist in a field in my sub form by taking the value i have entered in a text box on my main form this is were i am so far.....

If IsNull(DLookup("linkcount", "tbl2", "[linkcount] = [forms]![test form]![History record Count]"))

this does work but checks the every record in tbl2 table & field= linkcount if the value exists. Now here comes the but.... how do i get it only check the records displaying on the sub form that are link to the main form record i have currently open

Use full Info Maybe ?
My main form is linked to a sub form by Main form = ID and Subform = LinkID so i can store multiple records in the sub form under one record in my main form

any help much appreciated

Shane

 

[moke123]

i think you have to include the ID or LinkID (whichever is appropriate to the table) in your third arguement.

 

[arnelgp]

you need to use the subform's recordset and recursed through it.

dim rs as dao.recordset
dim bolExists As boolean
set rs = [forms]![test form]![yourSubformName].Form.RecordsetClone
with rs
if not (.bof and .eof) then .movefirst
do while not .eof
bolExists = (!linkCount = [forms]![test form]![History record Count])
if bolExists then exit do
.movenext
loop
.close
end with
set rs=nothing
if bolExists 'here we found that it exists
' what shall you do when it exists
else
' what shall you do when it does not exists
end if

 

[sspreyer]

hi ,

Thanks Mole123 for the help

i have manage to get the result using a query and then using DLookup to get the result i was after BUT is a very messy way of doing it.

Arnelgp i like the idea of this way but got one issue i'm get error on the line shown in red say invalid use of Nulls any Ideas

Dim rs As dao.Recordset
Dim bolExists As Boolean
Set rs = [Forms]![test form]![Tbl2 subform].Form.RecordsetClone
With rs
If Not (.BOF And .EOF) Then .MoveFirst
Do While Not .EOF
[COLOR=Red]bolExists = (!Linkcount = [Forms]![test form]![History record Count])[/COLOR]
If bolExists Then Exit Do
.MoveNext
Loop
.Close
End With
Set rs = Nothing
If bolExists Then
MsgBox "i know your there"
Else
MsgBox " your Not there"
End If

Thanks

 

[arnelgp]

wrapped this code inside the Nz() function:

bolExists = (Nz(!Linkcount,0) = [Forms]![test form]![History record Count])