Pls help, my VBA does not work

F

Frank Situmorang

Heloo,

In one form my ID dereives from chruchID and member sequence number does
work, while the same VBA to combince the addressID with church number does
not work. I do not know what's woring, I do the same in my address form using
Address table. Pls help, below iis the 1stone that workds, while the 2nd one
does not:
Option Compare Database
Option Explicit

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 TOP 1 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

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 TOP 1 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

2ND ONE:
Option Compare Database
Option Explicit
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

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!AddresID.Text = Left(Me!AddresID.Text, Len(Me!AddresID.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_AddresID_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
 
C

Clifford Bass

Hi Frank,

This is much too complex a design that easily leads to problems.
Generally, you should not make the fields do dual/triple use. I would
suggest you have two numeric fields, a ChurchID and a MemberID and make the
two of them your primary key. Keeping the ChurchID separate allows you to
create and enforce referential integrity between your Churches table (you do
have a Churches table don't you?) and other related tables such as Members.
Additionally, if you need the year of joining, make that a separate field
also. Create a combo box on your form from which the user picks the
appropriate church. This will eliminate the need to do any validation, or
other coding on the ChurchID. Populate your MemberID by using a "select
Max(MemberID) from Members where ChurchID = " & cbChurchID recordset in the
before insert event. For the addresses I presume that you are allowing for
multiple addresses per member. In which case use three columns, ChurchID,
MemberID and AddressID, which will be your primary key. You can then set the
AddressID in the address (sub)form's before insert event using the same type
of process as setting the MemberID. Now, if the actual combined
church/member number is needed, you can just blend them together in the
appropriate queries and/or reports.

There are other ways to do this, including making some of the ID fields
autonumber fields so you do not have to do any ID calculations.

Hope that helps,

Clifford Bass

Frank Situmorang said:
Heloo,

In one form my ID dereives from chruchID and member sequence number does
work, while the same VBA to combince the addressID with church number does
not work. I do not know what's woring, I do the same in my address form using
Address table. Pls help, below iis the 1stone that workds, while the 2nd one
does not:
Option Compare Database
Option Explicit

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 TOP 1 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

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 TOP 1 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

2ND ONE:
Option Compare Database
Option Explicit
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

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!AddresID.Text = Left(Me!AddresID.Text, Len(Me!AddresID.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_AddresID_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
Click to expand...
 
F

Frank Situmorang

Thanks Cliff for your response. Let me tell it you the back ground, that I
have finished desingning a database for our local church using memberID and
AddressID AutonumberPrimaryKey. Now I want to develop it to be able to used
by all churches of our denomination. The same software will be used by our
regional office to combine all church membership data. However the primary
key of both memberID and AddressID could duplicate and wil resut in a
confilct when combined in a regional office. So the idea is how to make it
always unique. Also to make it more practical when we are using a combo to
select the address of a member we prefer 1 field PK, because in this database
we can see the members per address to facilitate a pastor to visit that
household.

So since I want to keep these MemberID dan Address ID always Unique I make
the combination of these 2 PK into 1 PK. All the church ID will be alwasy
requested from me as a software developer.

So please help me how can we make these 2 PK into 1 PK.

Thanks in advance,

Frank
--
H. Frank Situmorang


Clifford Bass said:
Hi Frank,

This is much too complex a design that easily leads to problems.
Generally, you should not make the fields do dual/triple use. I would
suggest you have two numeric fields, a ChurchID and a MemberID and make the
two of them your primary key. Keeping the ChurchID separate allows you to
create and enforce referential integrity between your Churches table (you do
have a Churches table don't you?) and other related tables such as Members.
Additionally, if you need the year of joining, make that a separate field
also. Create a combo box on your form from which the user picks the
appropriate church. This will eliminate the need to do any validation, or
other coding on the ChurchID. Populate your MemberID by using a "select
Max(MemberID) from Members where ChurchID = " & cbChurchID recordset in the
before insert event. For the addresses I presume that you are allowing for
multiple addresses per member. In which case use three columns, ChurchID,
MemberID and AddressID, which will be your primary key. You can then set the
AddressID in the address (sub)form's before insert event using the same type
of process as setting the MemberID. Now, if the actual combined
church/member number is needed, you can just blend them together in the
appropriate queries and/or reports.

There are other ways to do this, including making some of the ID fields
autonumber fields so you do not have to do any ID calculations.

Hope that helps,

Clifford Bass

Frank Situmorang said:
Heloo,

In one form my ID dereives from chruchID and member sequence number does
work, while the same VBA to combince the addressID with church number does
not work. I do not know what's woring, I do the same in my address form using
Address table. Pls help, below iis the 1stone that workds, while the 2nd one
does not:
Option Compare Database
Option Explicit

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 TOP 1 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

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 TOP 1 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

2ND ONE:
Option Compare Database
Option Explicit
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

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!AddresID.Text = Left(Me!AddresID.Text, Len(Me!AddresID.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
Click to expand...
Click to expand...
 
C

Clifford Bass

Hi Frank,

I understand your reasoning for wanting to make the church ID part of
the member ID and address ID, but I again strongly encourage you not to go
that route. Making your primary keys compound keys (keys that are made up of
more than one column) will solve the need for uniqueness. And, the issue of
a combo box is not really an issue. You can make the combo box contents
dependent on a church ID field on your form. Also, it is a mistake to say
that you have finished designing the database. Limiting yourself in that way
easily leads to poorly designed systems.

Regarding the need to show other people at the same address I would
change my recommendation to this: The addresses table will use a ChurchID
and an AddressID but not a MemberID. Create a fourth table that links
between the three tables maybe a MemberAddresses table that contains a
ChurchID, a MemberID and an AddressID. This will let you navigate back and
forth between a member, his addresses and anyone else living at his addresses
very easily. It also allows for multiple addresses per member such as a
winter address and a summer address. This of course presumes that no one is
a member of more than one church and that the addresses are specific to each
church. If you want to accomodate for multi-church membership situation and
avoid having duplicate data for the same people and addresses, it will
require some significant rethinking of member and address ID numbers; how to
make sure they are unique across all of the denomination, not just for each
church. Then you would eliminate the church ID from the members and
addresses tables. The MembersAddresses table would provide for the
connections between all of the tables.

However, if you still want to use the IDs as a single field where the
first four digits are the church ID and the second four are the member or
address ID I would suggest the following: Create an unbound combo box that
lists the available churches and defaults to the default church. Make your
member (or address) ID a locked field that is not modifiable by the user.
Then use one of the following select strings to get the new member number in
the before insert event.

This one assumes that you are storing the IDs as long integers:

strSQL = _
"SELECT " & Nz(cbChurchID, 9999) * 10000 & " + " & _
"Nz(Max([ChurchMemberID] Mod 10000), 0) + 1 AS NewMemberID " & _
"FROM tblMembers " & _
"WHERE Int([ChurchMemberID] / 10000) = " & Nz(cbChurchID, 9999)

Or, if you are using eight-character IDs use this:

strSQL = _
"SELECT " & Nz(cbChurchIDText, "9999") & " & " & _
"Format$(Nz(Max(Right$([ChurchMemberIDText], 4)), 0) + 1,
""0000"") AS " & _
"NewMemberIDText " & _
"FROM tblMembers " & _
"WHERE Left$([ChurchMemberIDText], 4) = """ & Nz(cbChurchIDText,
"9999") & """"

I think that I got those statements right. You will need to adjust the
combo box, table and field names as needed. It will always return the next
new member (or address) ID, including when there are not any for the selected
church or if there is no selected church. The only problem will be when a
church exceeds 9,999 members and/or addresses.

Hope that helps,

Clifford Bass
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Meaning of """ 2
VBA works in orignal but fails in copy of database 1
VBA Coding Help for Beginner 0
Help with this code please. 2
VBA code issue...Would love expert help. 5
Checkboxes in DAO sql statement...HELP 1
What's wrong w/ this SQL stmt? 1
Listbox execute SQL error 2

Top