PK & FK problems in data consolidation

[Frank Situmorang]

Michael:

I do the same method for make the addressID unique,

The name of my address table is
KbyAngAlamat
The name of the Address PK is
AddresID
Ang the name of my form is:
ALAMAT PER KELUARGA_JEMAAT KBY

And here is my modifed VBA but does not work.
Private Sub AddresID_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_AddresID_Change

If InStr(1, "0123456789", Right(Me!AddresID.Text, 1)) = 0 Then
Me!MemberID.Text = Left(Me!MemberID.Text, Len(Me!MemberID.Text) - 1)
End If


' automatically increment the AddresID number. The AddresID 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 AddresID.)

' the user types the first 4 characters, which represents the
' church number

If Len(Me!AddresID.Text) = 4 Then

' if church number typed is different than default church
' number, then tell the user

If Me!AddresID.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 AddresID in descending oder
' where the first part of AddresIDmatches characters typed

strSQL = "SELECT TOP 1 AddresID FROM KbyAngAlamat "
strSQL = strSQL & "WHERE (((Left([AddresID], 4)) = """
strSQL = strSQL & Me!AddresID.Text & """))"
strSQL = strSQL & "ORDER BY AddresID DESC;"

'DoCmd.Hourglass True
Set rst = db.OpenRecordset(strSQL)
'DoCmd.Hourglass False

If rst.BOF Then
MsgBox "Initial entry!"
Me!AddresID.Text = Me!AddresID.Text & "0001"
Else
rst.MoveFirst
Me!AddresID.Text = Right("000" & (rst(0) + 1), 8)
End If
DoCmd.GoToControl "HOUSEHOLDNAME"

rst.Close

Set rst = Nothing
Set db = Nothing
End If

Exit_AddresID_Change:
Exit Sub

Err_MAddresID_Change:
Select Case Err.Number
Case Else
'Call LogError(Err.Number, Err.Description, _
"Form_ALAMAT PER KELUARGA_JEMAAT KBY Form." & "AddresID_Change")
Resume Exit_AddresID_Change
End Select
End Sub

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 AddresID in descending oder
' where the first part of AddresID matches default church number

strSQL = "SELECT TOP 1 AddresID FROM KbyAngAlamat"
strSQL = strSQL & "WHERE (((Left([AddresID], 4)) = """
strSQL = strSQL & Right("000" & Nz(DLookup("Church", _
"Defaults"), "9999"), 4) & """))"
strSQL = strSQL & "ORDER BY AddresID DESC;"

'DoCmd.Hourglass True
Set rst = db.OpenRecordset(strSQL)
'DoCmd.Hourglass False

If rst.BOF Then
MsgBox "Initial entry!"
Me!AddresID = Right("000" & Nz(DLookup("Church", _
"Defaults"), "9999"), 4) & "0001"
Else
rst.MoveFirst

' add 1 to AddresID and add leading zeros
Me!AddresID = Right("000" & (rst(0) + 1), 4)

' combine default church number with sequence number
' from previous line
Me!AddresID = Right("000" & Nz(DLookup("Church", _
"Defaults"), "9999"), 4) & Me.AddresID
End If
DoCmd.GoToControl "HOUSEHOLDNAME"

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_ALAMAT PER KELUARGA_JEMAAT KBY Form." & "Form_BeforeInsert")
Resume Exit_Form_BeforeInsert
End Select

End Sub

Out of this topic, like you see on my member form menu no 2, there is note
which tje sibform of this main form. I asked in this forum that child record
FK will be automatically poulted if the mainform PF is Autonumber, Can it
still be working if my member's id nos is no longer autonumber, but still
number?

With many thanks

Frank

 

[Michael Gramelspacher]

Thanks very much Michael, I hope that my dream comes true. As I mentioned in
my denomination mailing list, most of the sale proceeds of this will be used
to help the poor in my almamateur University at Pematang Siantar, North
Sumatra. We hope if this is successful, many financially poor students can be
helped to pay their tuitions.

After this I will post on separate thread on how to make in the form and
report caption foreingn languages. because customized it manually is not
practical.

Thanks & Regards

Frank, I really do not have the time to devote to your project. But just a few parting comments.
The code has BeforeInsert and Change events. If you want to use BeforeInsert, the Change event
never is triggered, because the MemberID is locked and MemberID has no tab stop set. The Change
event code is just an alternate way to consider as it allows managing multiple church in one
database.

You do not have any relationships defined in your database. It look like you have a Members table,
a Households table and an Addresses table. Members belong to Households and Households have
Addresses. All three of these tables need to have keys that combine church number and a sequence
number. This is so that when you have data from multiple churches in the same database at the
Region level, you will be able to distinguish the churches.

It seems that you should be able to handle this on your own now.