Thanks very much Michael for your help. Yes, you help me very much. I will
still prefer the one field Primary key of member and address because in
practice, all this additional is for the upper office level, not in the
church. So we will not show like affiliation form in the church secretary.
This can be possible if I make a default value of text control data property
of the memberform this function:
=Nz(DMax("[YourField]","[YourTable]"),82310000) + 1.
I will try to change my member Autonumber PK as well as address autonumber
PK with just number.
8231(example) comes from affiliation form, the 8 divison no,2 Union no,
3,regional no. 1, chruch no, while the other 000, suppose the number of the
member maximum 9999 members which never reach this number in SDA church for
one church.
This is because I have not understand how can we apply this multiple field
PK in a from, report and other.
I would keep the Affiliation table and put Church 1, Region 3, Union 2 and Division 8 in the
Affiliation table.
I would have a table Defaults with one column named Church, which holds the ChurchID.
This churchID will be unique. Since you distribute the software, you are the one who assigns the
unique ChurchID. You actually unpdate the Affiliation and Defaults table before sending the
software.
You can automatically create the concatenated MemberID in the BeforeInsert event of the Members
Form. Lock the MemberID Field and give it Tab stop = No. As soon as the user atempts to type the
member's name, the key will be filled in.
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
On Error GoTo Err_Form_BeforeInsert
Set db = DBEngine(0)(0)
' built query string to sort MemberID in descending oder
' where the first part of MemberID matches default church number
strSQL = "SELECT MemberID FROM Members "
strSQL = strSQL & "WHERE (((Left([MemberID], 4)) = """
strSQL = strSQL & Right("000" & Nz(DLookup("Church", _
"Defaults"), "9999"), 4) & """))"
strSQL = strSQL & "ORDER BY MemberID DESC;"
'DoCmd.Hourglass True
Set rst = db.OpenRecordset(strSQL)
'DoCmd.Hourglass False
If rst.BOF Then
MsgBox "Initial entry!"
Me!MemberID = Right("000" & Nz(DLookup("Church", _
"Defaults"), "9999"), 4) & "0001"
Else
rst.MoveFirst
' add 1 to MemberID and add leading zeros
Me!MemberID = Right("000" & (rst(0) + 1), 4)
' combine default church number with sequence number
' from previous line
Me!MemberID = Right("000" & Nz(DLookup("Church", _
"Defaults"), "9999"), 4) & Me.MemberID
End If
DoCmd.GoToControl "FirstName"
rst.Close
Set rst = Nothing
Set db = Nothing
Exit_Form_BeforeInsert:
Exit Sub
Err_Form_BeforeInsert:
Select Case Err.Number
Case Else
'Call LogError(Err.Number, Err.Description, _
"Form_Members Form." & "Form_BeforeInsert")
Resume Exit_Form_BeforeInsert
End Select
End Sub
Another way is to have the user type the church number 0001 and then after the 4th character add the
sequence part.
Private Sub MemberID_Change()
' examine each character as it is entered and if it is not an acceptable
' character then ignore it and leave string as it was before
On Error GoTo Err_MemberID_Change
If InStr(1, "0123456789", Right(Me!MemberID.Text, 1)) = 0 Then
Me!MemberID.Text = Left(Me!MemberID.Text, Len(Me!MemberID.Text) - 1)
End If
' automatically increment the member number. The member number is
' formed by using the church number and adding a sequence number,
' ex., 08930008. Get the church number after user enters 4 numbers,
' find the highest number used for that year, and then add 1.
' If starting anew, add 0001. (This allows a church to have a maxium
' of 9999 members.)
' the user types the first 4 characters, which represents the
' church number
If Len(Me!MemberID.Text) = 4 Then
' if church number typed is different than default church
' number, then tell the user
If Me!MemberID.Text <> Right("000" & DLookup("Church", _
"Defaults"), 4) Then
If vbNo = MsgBox("This is not the default church ID! " _
& "Do you wish to continue", vbQuestion + vbYesNo, "Warning!") Then
Exit Sub
End If
End If
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Set db = DBEngine(0)(0)
' built query string to sort MemberID in descending oder
' where the first part of MemberID matches characters typed
strSQL = "SELECT MemberID FROM Members "
strSQL = strSQL & "WHERE (((Left([MemberID], 4)) = """
strSQL = strSQL & Me!MemberID.Text & """))"
strSQL = strSQL & "ORDER BY MemberID DESC;"
'DoCmd.Hourglass True
Set rst = db.OpenRecordset(strSQL)
'DoCmd.Hourglass False
If rst.BOF Then
MsgBox "Initial entry!"
Me!MemberID.Text = Me!MemberID.Text & "0001"
Else
rst.MoveFirst
Me!MemberID.Text = Right("000" & (rst(0) + 1), 8)
End If
DoCmd.GoToControl "FirstName"
rst.Close
Set rst = Nothing
Set db = Nothing
End If
Exit_MemberID_Change:
Exit Sub
Err_MemberID_Change:
Select Case Err.Number
Case Else
'Call LogError(Err.Number, Err.Description, _
"Form_Members Form." & "MemberID_Change")
Resume Exit_MemberID_Change
End Select
End Sub
Having a MemberID made by combining two values is only for expediency.
SELECT Members.LastName,
Members.FirstName,
Regions.RegionName
FROM Members,
Regions
INNER JOIN Affiliations
ON Regions.RegionID = Affiliations.RegionID
WHERE (((Affiliations.ChurchID) = CINT(LEFT([Members].[MemberID],4))))
ORDER BY Members.LastName,
Members.FirstName;
Members and their regions
OK, I do not think I know anything more. You should be able to take it from here.
