UtterAccess Discussion Forums > Assistance On Code

Kamulegeya

Apr 16 2012, 03:31 AM

Hello UA

I have a code i want to use to create "Packages". I have two tables. tblSections and tblLBCPackages.

They are related by SectionID a foreign key in tblLBCPackages.

I have a field PackageRef in tblLBCPackage. it is text field . Each year we create new packages and they are numbered like "LBC/FPL/2011_2012/001".

To help me get the last bit of the numbering, i added a sequence field . Each Financial Year, it must begin from 1.

My tblLBCPackage then have this fields: ID(auto),SectionID,PackageRef, FinancialYearID(FK to tblFinancialYears) and other fields.

I created a form with combo box, cboFinancialYear. I want to select a financial Year and be able to create the packages.

I use a record set to get the sectionIDs from tblSections , open recordset on the LBCPackage tables and add the values in the loop.

The problem is that the Sequence field is not incrementing.

Here is the code.

The part i want to increment on in the loop is

CODE

!Sequence = Nz(DMax("Sequence", "tblLBCPackage", "FinancialYearID=" & Me.cboFY.Column(0)), 0) + 1

CODE

Private Sub cmdCreate_Click()
''''''use Transactions to ensure all success or failure
''''''date 12/03/2012
Dim db As DAO.Database
Dim wks As DAO.Workspace
Dim rst As DAO.Recordset ''''recordset for sections
Dim rst1 As DAO.Recordset  ''''recordset to add new packages
Dim strSQL As String     '''''store IDs
Dim mySQL As String ''''''update previous packages to non current
Dim strmySQL As String  ''''update contractor information
Dim strString As String
Dim I As Integer
On Error GoTo myErr
strSQL = "Select SectionID" & _
        " From tblSections" & _
        " Where CurrentSection=True;"
mySQL = " UpDate tblLBCPackage Set CurrentPackage=No Where FinancialYearID<>" & Me.cboFY.Column(0)
strmySQL = " Update tblPackage_Contractor set Active=No" & _
            " Where PackageID In(Select PackageID From tblLBCPackage Where FinancialYearID<>" & Me.cboFY.Column(0) & ")"
If IsNull(Me.cboFY) Then
MsgBox " No Financial Year selected. Select one to proceed", vbExclamation
Exit Sub
End If
If DCount("*", "tblLBCPackage", "FinancialYearID=" & Me.cboFY.Column(0)) <> 0 Then
MsgBox " LBC Packages for this FY already created", vbCritical
Exit Sub
End If
'''initialise variables
Set wks = DBEngine.Workspaces(0)
Set db = CurrentDb()
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
Set rst1 = db.OpenRecordset("tblLBCPackage", dbOpenDynaset)
wks.BeginTrans
rst.MoveFirst
Do Until rst.EOF
With rst1
    .AddNew
    !SectionID = rst!SectionID
    !FinancialYearID = Me.cboFY.Column(0)
    strString = Nz(DMax("Sequence", "tblLBCPackage", "FinancialYearID=" & Me.cboFY.Column(0)), 0) + 1
    Debug.Print strString
    !Sequence = Nz(DMax("Sequence", "tblLBCPackage", "FinancialYearID=" & Me.cboFY.Column(0)), 0) + 1
    !PackageRef = "UNRA" & "/" & DLookup("Prefix", "Stations") & "/" & "LBC" & "/" & Replace(Me.cboFY.Column(1), "/", "_") & "/" & Format(!Sequence, "000")

    !CreatedBy = GetCurrentUserName()
    !DateCreated = Date
    !CurrentPackage = True
    .Update
    End With

rst.MoveNext
I = I + 1   ''''count each package created
Loop
CurrentDb().Execute mySQL, 128
CurrentDb.Execute strmySQL, 128
wks.CommitTrans
MsgBox I & " LBC Packages created", vbInformation
myExit:
On Error Resume Next
rst.Close
rst1.Close
Set rst = Nothing
Set rst1 = Nothing
Set db = Nothing
Set wks = Nothing
Exit Sub
myErr:
wks.Rollback
MsgBox " Error is " & Err.Description & " Error Number is " & Err.number
Resume myExit
End Sub

The reason i put a string there was for testing purposes.

The problem is that it craetes all Packages with the same Reference 001.

Why is that the sequence field not incremented in the loop?

Ronald

Schizolocal

Apr 16 2012, 03:35 AM

Are you sure that your FinacialYearID is the default (Column(0)) field of your combo box?

Stuart

Kamulegeya

Apr 16 2012, 03:40 AM

QUOTE (Schizolocal @ Apr 16 2012, 11:35 AM)

Are you sure that your FinacialYearID is the default (Column(0)) field of your combo box?

Stuart

Hello Stuart

It is . The Combo record source has two columns. for FinancialYearID, and FinancialYear.

Even when i debug, i see the correct FinancialYearID.

Ronald

LPurvis

Apr 16 2012, 04:29 AM

Hopefully you'll find sufficient clue as to why in this thread.

Cheers.

Kamulegeya

Apr 16 2012, 04:39 AM

QUOTE (LPurvis @ Apr 16 2012, 12:29 PM)

Hopefully you'll find sufficient clue as to why in this thread.

Cheers.

Hello LPurvis

Removing the wks.BeginTrans and wks.CommitTrans made it work.

What happens behind the scene? the table is not updated until the transactions is committed?

Any work around? wanted the transaction bit.

Ronald

LPurvis

Apr 16 2012, 04:47 AM

Even with the transaction removed, you're using distinct data access libraries to update and retrieve values - and therein lies scope for discrepency.
Using the same connection to the data (within the scope of the same transation) will allow you to see the pending data updates.
However, my question is: what are you protecting against ith the DMax checks? The possibility that another user is simultaneously performing the same task?

i.e. why not grab the sequence value before you begin inserting rows, and then just add 1 with each iteration?
What are the subsequent lookups protecting against?

Your transaction is possibly ensuring that other users aren't performing the same task anyway. ;-)

Cheers.

Kamulegeya

Apr 16 2012, 05:05 AM

QUOTE (LPurvis @ Apr 16 2012, 12:47 PM)

Even with the transaction removed, you're using distinct data access libraries to update and retrieve values - and therein lies scope for discrepency.
Using the same connection to the data (within the scope of the same transation) will allow you to see the pending data updates.
However, my question is: what are you protecting against ith the DMax checks? The possibility that another user is simultaneously performing the same task?

i.e. why not grab the sequence value before you begin inserting rows, and then just add 1 with each iteration?
What are the subsequent lookups protecting against?

Your transaction is possibly ensuring that other users aren't performing the same task anyway. ;-)

Cheers.

Hello LPurvis

These packages are created one every fiscal year. I first made a data entry form but i thought it would be nice to create them at once .

I use Dmax to be able to get sequencing numbers starting from 1 for each financial year.

There is no possibility that another user can be performing the same task at the same time.

I have not understood this statement.

QUOTE

Even with the transaction removed, you're using distinct data access libraries to update and retrieve values - and therein lies scope for discrepency.
Using the same connection to the data (within the scope of the same transation) will allow you to see the pending data updates.

You mean

CODE

Set wks = DBEngine.Workspaces(0)
Set db = CurrentDb()

are not within the same scope?

What changes can i make then?

Ronald

Kamulegeya

Apr 16 2012, 06:31 AM

QUOTE (LPurvis @ Apr 16 2012, 12:47 PM)

Even with the transaction removed, you're using distinct data access libraries to update and retrieve values - and therein lies scope for discrepency.
Using the same connection to the data (within the scope of the same transation) will allow you to see the pending data updates.
However, my question is: what are you protecting against ith the DMax checks? The possibility that another user is simultaneously performing the same task?

i.e. why not grab the sequence value before you begin inserting rows, and then just add 1 with each iteration?
What are the subsequent lookups protecting against?

Your transaction is possibly ensuring that other users aren't performing the same task anyway. ;-)

Cheers.

Hello lPurvis

Re-read you answer again and understood your suggestion.

i have declared a long Inter lngID and assigned it like

CODE

lngID = Nz(DMax("Sequence", "tblLBCPackage", "FinancialYearID=" & Me.cboFY.Column(0)), 0) + 1

Then modified my code as below

CODE

With rst1
    .AddNew
    !SectionID = rst!SectionID
    !FinancialYearID = Me.cboFY.Column(0)
    !Sequence = lngID
    !PackageRef = "UNRA" & "/" & DLookup("Prefix", "Stations") & "/" & "LBC" & "/" & Replace(Me.cboFY.Column(1), "/", "_") & "/" & Format(lngID, "000")
    !CreatedBy = GetCurrentUserName()
    !DateCreated = Date
    !CurrentPackage = True
    .Update
    End With
rst.MoveNext
I = I + 1   ''''count each package created
lngID = lngID + 1
Loop

It has worked great

Thank you so much

God bless

Ronald

LPurvis

Apr 16 2012, 08:11 AM

Hi

Glad you got there and managed it.
Just to address pending questions:

QUOTE

I use Dmax to be able to get sequencing numbers starting from 1 for each financial year.

You mean
Set wks = DBEngine.Workspaces(0)
Set db = CurrentDb()
are not within the same scope?

DMax is of a different data access scope than either of the DAO objects you list. DMax is an Access application method (using Access own connection).
The implicit DAO connection (the phrase "connection" here is not refering to an object such as the Connection of ADO's object model, but an inherent connection to the database) will commit and read data as required - and while within a transaction is an effective black box to external data operations. (i.e. data access technologies outside of its scope).
So if you performed your data lookup using objects within the scope of the current provider and transaction - i.e. CurrentDb and DAO recordsets - then you'd be OK.
But I still maintain that's unnecessary overhead given that no other processes are adding records, so your own iteration must be the next consecutive value. ;-)

So you're good to go as is.

Cheers.