About Rob Henderson Expertise I can answer most MS Access design questions. I also welcome questions on database design and implementation and VBA programming questions.
I also have expierence in application design for all the Office components (Excel, Outlook, etc).
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
