autofill a field if another field already exists in a record in the table (1 Viewer)

[russi]

Whew! Hopefully I explain this well.

I have a main form with IDNUMBER and subform that links through that field.
The field VENDOR exists in a table that is the basis of the subform.

Can I have the value of that VENDOR field automatically filled-in with the value already associated with the specific INUMBER that is brought up, if there already exists in the subform table, a record for that IDNUMBER?

Thanks.

 

[AlanS]

In the Form_Current event procedure for the subform, put the following code:

If Me.NewRecord Then
Me.RecordsetClone.FindFirst "IDNUMBER = '" & Me.Parent.IDNUMBER & "'"
If Not (Me.RecordsetClone.NoMatch) Then VENDOR = Me.RecordsetClone!VENDOR
End If

This example assumes that IDNUMBER is a text field - if it's numeric, you'll have to remove the apostrophes. Also, this will use the value of VENDOR in the first existing record, which may or may not be the same as in other existing records.

 

[russi]

Thank you, thank you!

IDNUMBER is a text field actually and I won't have a problem with it taking that field's first associated VENDOR since vendors stay the same. And if the client drops out and comes back in they get a new idnumber which has an 02 suffix.

Again, thanks.

Russ