The MS Sample Members DB is excellent to use for your purpose. Access does not use Cells, Columns and Rows exactly the same as Excel so the example formula will not work as is. The MemberID in the Access DB uses an auto number which is a Primary Key and an Indexed Field. You *can* change this... *But*... it requires some major Access brain surgury to do so on a database already setup with an auto number primary key. If you just like to display member codes based on the LastName, FirstInitial, and Date Serial, the easiest way would be to add an unbound field to your Members Form, with a formula to show the member code.
1. Add a new Text Field (Copy the existing 'MemberID' Field and Paste)
2. Right Click, Select Properties, Data Tab
3. Add the following formula to the 'Control Source' item
=UCase(Left([LastName],3) & Left([FirstName],1) & DateDiff("d","1/1/1900",[DateJoined]))
Note: Access does not have the 'Value' or 'DateValue' functions available in Excel. A similar Date Value can be produced with the DatDiff function using 1/1/1900 as the first date.
For some reason there is a 3 day delta between Excel's 'Value' function and 'DateDiff' using the same date. I'm not sure why. The idea behind using the date value is to produce a unique Member ID for each member so the difference does not matter.
FYI: if you REALLY want to change the primary key to use a text format generated key, these are the steps...
1. Open the Database Relationships View
2. Remove Relationship joins between the Members, Payments, and CommitteeMembers Tables.
3. Change the data type in the tables design view to Text format in the Members, Payments, and CommitteeMembers Tables.
4. Return to the Relationships view and re-add the relationsips to the prementioned tables, check cascade updates and deletes in the join properties.
5. Enter Code to genereate primary keys, like:
Code:
Private Sub DateJoined_AfterUpdate()
Dim strMemCode As String
Dim bReqFields As Boolean
bReqFields = (Not IsNull([FirstName]) And Not IsNull([LastName]) And Not IsNull([DateJoined]))
If IsNull(Me.MemberID) Then
If bReqFields Then
strMemCode = GenMemberCode()
Me.MemberID = strMemCode
End If
End If
End Sub
Private Function GenMemberCode()
Dim lName As String
Dim fName As String
Dim strDateCode As String
lName = Left(Me.LastName, 3)
fName = Left(Me.FirstName, 1)
strDateCode = DateDiff("d", "1/1/1900", Me.DateJoined)
GenMemberCode = UCase(lName & fName & strDateCode)
End Function
6.
Debug, Debug, Debug
/
dBforums
>
How do you make a address database?
01-17-05, 17:59
How do you make a address database?
I found a templet - Just need a little help with it
Linear Mode
