Expert: Rob Henderson - 2/10/2005

Question
Hi Rob,

I need a field in an access database that will automatically populate with a combination of other field values.  For example, I have two fields, "customer code" and "access type".  If I enter the text 001234 in customer code, and D in access type, I would like the field "ccat" to return the text 001234D automatically.

How do I make the "ccat" field do this?

Also, I want the "ccat" field to be a key field, no duplicates.

Thanks for your help.

Answer
Hi Jerry

You can do this quite easily by using some VBA

Sub CombineFields()


   Dim strTextBox1         As String
   Dim varLookUp           As Variant
   
   
   ' Grab the values to parse
   strTextBox1 = Forms!NameofYourForm!NameofYourField1.Value & Forms!NameofYourForm!NameofYourField1.Value
   
   ' Perform a DLookUp on the table to establish if this is unique
   varLookUp = DLookup("yourfieldtosearchfor", "yourtable", "yourfieldtosearchfor'" = strTextBox1 & "'")
   
   ' Check the return value - if nothing - flag a message
   If IsNull(varLookUp) = False Or varLookUp <> "" Then
   
       MsgBox "This value is a duplicate.", vbInformation, "Duplicate Found."
   
   
   End If


End Sub

The above sub can be called from a command button to check the duplicate status or from the Before_Update event of the textbox itself

simply call using Call CombineFields()

HTH

rob