reference only pulling one value

[andyeastes]

I am using this snippet:
Forms!PullToShipMainFRM!InvLocFRM.Form.Location <> Me.Combo11.Value

to see if the values in subform1's combo11 does not have a match in subform2's location field. Subform 2 is a datasheet subform and could have either zero or multiple lines. If it has zero or one it works fine but when there are more than one and the record I am trying to edit has a location that is not the top one on subform 2 it is not catching that there is a match.

So basically it is only checking the first row for:
Forms!PullToShipMainFRM!InvLocFRM.Form.Location

Any ideas?

 

[spikepl]

Your snippet checks for match in the subform's current record (if any). All controls (in form or subform) contain the values of the respective current records.

If you want to check for existence in ANY subform record, you need to explicitly scan all of them. How to do that depends on the context (what exactly is needed) you could try to find a match using .FindFirst in the Me.SubformName.Form.RecordsetClone (of the subform)

With Me.SubformName.Form.RecordsetClone
.FindFirst "FieldToSearch=" & ValueToFind ' if numeric - else single quotes around the string
If Not .NoMatch Then
'found the value in a record
Else
MsgBox "Did not find match"
End if

End With

 

[andyeastes]

Ok I have never used the find first or If before but I am trying it, it was working how I had it just only checking first (or current record) like you said and not the whole list - here is what I have now:

With Forms!PullToShipMainFRM!InvLocFRM.Form.RecordsetClone
.FindFirst "Forms!PullToShipMainFRM!InvLocFRM.Form.Location = & Combo11"
If Me.Type.Column(1) = "Out" And .NoMatch Then
Beep
MsgBox "Not valid location, please start over and select valid location", vbOKOnly
DoCmd.SetWarnings No
DoCmd.OpenQuery "PullToShipDelQRY"
DoCmd.SetWarnings Yes
Me.Requery
Me.Combo13.SetFocus
Else
Call PullToShipMOD
Call CompleteTransactionsMOD
Forms!PullToShipMainFRM.InvLocFRM.Form.Requery
Me.Requery
End If
End With

It says syntax error missing operator error and highlights the .findfirst line

any thoughts on that?

 

[andyeastes]

So for anyone wondering here is what I ended up getting to work:

ElseIf Me.Type.Column(1) = "Out" And IsNull(DLookup("Location", "InvTranInvCheckQRY", ("Location = '" & Me.Combo11.Value & "'"))) Then