I´m getting nutts here!

[Niklas Östrergren]

Hi!

I have strougled with this all day and can´t figure out a good dynamic way
of solving this:

I have two variables
- glngarrFamilyMRegID ()
which is an global array holding
primary key for memberships beeing created
for several person´s in a family.

- gintNoOfMemberShip
which is an integer variable holding
number of memberships that shall be
created for each person.
gintNoOfMemberShip can, at this ´moment have a
value of 1 or 2 but may change in
the future. The explanation behind this is
that if a new member pay the
memberfee after a breakdate (ex. 1 of
september) then shall the new member
get a membership not only for the rest
of the year but also a membership
for the whole next year as well.

I loop through the array glngarrFamilyMRegID and add records with rec.AddNew
and by using:

For i = 0 to UBound(glngarrFamilyMRegID)
With rec
.AddNew
glngarrFamilyMRegID(i) = !ID
!StartDate = 'SomeDateHere
!EndDate = 'SomeOtherDateHere
.Update
End With
Next i

If gintNoOfMemberShip = 1 I create ONE record with !StartDate = Date() and
!EndDate = Last date of current year (2004-12-31).

If gintNoOfMemberShip = 2 Then I create 2 records. One record with
!StartDate = Date() and !EndDate = Last date of current year (2004-12-31)
AND one record with !StartDate = First date of next year and !EndDate = Last
Date of next year.

The problem I´m having is that I just can´t figure out how to loop through
this procedure adding primary key to array glngarrFamilyMRegID but only
creating the right amount of records?

I know that this sound´s a little bit confusing but I can´t explain it
better than this. So what I´ll do is that I´ll post the code I´m using right
now and perhaps you can see my problem.

'Code starts here ======================================

Public Function AddFamMemberIntblRegNewMShip() As Integer
'***************************************************
' Description: Create record in tblRegistrationOfNewMemberShip for
' family members.
' Return: Number of family members added in
tblRegistrationOfNewMemberShip
' Author: Niklas Östergren
' Date: 2004-12-27
'**************************************************

Dim db As DAO.Database
Dim rec As DAO.Recordset ' Holds the recordset
Dim intIdx As Integer
Dim i As Integer

Set db = Currentdb()

'============================================
' ReDim Array
'============================================
Erase glngarrFamilyMemberShipRegID
ReDim glngarrFamilyMemberShipRegID(UBound(glngarrFamilyMemberID) *
gintNumberOfNewMemberShips)
gbooFamilyMemberShipRegIDFlag = True

' Open recordset
Set rec = db.OpenRecordset("tblRegistrationOfNewMemberShip",
dbOpenDynaset)

'================================================
' Create new records in tblRegistrationOfNewMemberShip
'================================================
intIdx = 0
i = 1
For intIdx = 0 To UBound(glngarrFamilyMemberShipRegID) - 1
For i = 1 To gintNumberOfNewMemberShips
With rec
.AddNew
glngarrFamilyMemberShipRegID(intIdx) = rec!ID
!PersonID = glngarrPersonDataFamilyMembersID(intIdx)
!MemberID = glngarrFamilyMemberID(intIdx)

If Not glngNewMemberShipTypeID = 0 Then
!MemberShipTypeID = glngNewMemberShipTypeID
End If

' If membership fee IS payed then get membership price
If gbooMemberFeeIsPayed Then
!EntryFeeToPay = DLookup("MemberShipPrice",
"tblLookUpMemberShipType", "MemberShipTypeID = " & glngNewMemberShipTypeID)
End If

' If i = 1 then it´s the first membership so set date to
today´s date
' If i = 2 then it´s an extra free of charge membership so
then
' set date to first date of next year.

If i = 1 Then
!NewMemberShipStartDate = Date
End If

If i = 2 Then
!NewMemberShipStartDate = CDate(Format(Date, "yyyy") + 1
& "-01-01")
End If

If i = 1 Then
!NewMemberShipEndDate = CDate(DatePart("yyyy", Date) &
"-12-31")
End If

If i = 2 Then
!NewMemberShipEndDate = CDate(Format(Date, "yyyy") + 1 &
"-12-31")
End If

!RegistratedUser = WinUserName()
!RegistrationDate = Date

.Update
End With
Next i
Next intIdx

' Close recordset and clean up
rec.Close
Set rec = Nothing

' Return number of created records.
If gbooFamilyMemberShipRegIDFlag Then
AddFamMemberIntblRegNewMShip = UBound(glngarrFamilyMemberShipRegID)
Else
AddFamMemberIntblRegNewMShip = 0
End If

End Function
' Code Ends here =================================================
TIA!
// Niklas

 

[Niklas Östrergren]

I have solved it! I just needed a little break to clear my mind!

// Niklas