Solved

Trigger Access AND SQL events at the same time?

Posted on 2004-09-23
206
817 Views
Last Modified: 2010-06-05
Here's an interesting dilemma, for me anyway.

I've got an ADP (Access 2000 front-end, SQL Server 2000 back-end). The database deals with Auctions and Lots (items sold at auction). Due to a one-to-many relationship between them (one LOT can have more than one AUCTION), I have created a different Access form for each -- the LOTform (based on the underlying SQL table LOT), and the AUCTIONform (based on the underlying SQL table AUCTION).

When a winning bid (WinBid) is entered into a control on the AuctionForm, and the auction is paid by the winner (noted by another control, PaidStatus, becoming = TRUE), then a profit $ is calc'ed for the Auction nad displayed in a control called AuctionGross; this control is bound to the SQL table AUCTION.

WHEN THAT HAPPENS, I also need for a couple of values to get calc'ed and stored over in the SQL table LOT, whether or not the LOT form is open in Access. (Yes, I need them to be stored, in case the underlying formulae change in the future, I need for the original values to remain unchanged in the database records rather than re-caclc'ed every time those records are displayed in the Access forms.)

I already have AfterUpdate events in the appropriate places on the Auction Form so that when WinBid and PaidStatus are updated, the AuctionGross gets calculated in the front-end (and stored in a control that's bound to the back-end). But can I also trigger calculations in the back-end from an Access AfterUpdate event?

Hope this makes sense. If I'm barking up a wrong or non-existent tree, somebody let me know. Thanks!

--Galisteo8
0
Comment
Question by:Galisteo8
  • 113
  • 88
  • 3
  • +1
206 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 12135241
Instead of doing all of this with one line of code, best way is to bundle all this into a transaction.  Code should go something like this...

Private Sub MyButton.OnClick()

dim cn as adodb.connection
set cn = currentproject.connection

cn.begintrans
' Do all your stuff here...

cn.committrans

error_handler:
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 50 total points
ID: 12135252
(let me try this again)
Instead of doing all of this with one line of code, best way is to bundle all this into a transaction.  Code should go something like this...

Private Sub MyButton.OnClick()

on error goto error_handler

dim cn as adodb.connection
set cn = currentproject.connection

cn.begintrans
' Do all your stuff here...

cn.committrans

exit_sub:
  exit sub

error_handler:
  cn.rollback
  'other error handling stuff here

end sub
0
 
LVL 2

Expert Comment

by:ngolovin
ID: 12135335
Yes, of course. You can set so-called trigger on a table, so that when it gets updated, the SQL code in trigger is invoked and can calculate something, do some updates or inserts or other stuff. You can create your trigger right from your Access front end(The trigger will be stored on the server, not in the front end) -- just go to Tables and right-click your table AUCTION, and select Trigger, new trigger. Then, you can enter somthing like this:

CREATE TRIGGER MYSQLcontentnodes_Trigger1
ON dbo.MYSQLcontentnodes
FOR UPDATE
AS
 declare @AuctionGross money
 IF UPDATE (WinBid)
 begin
         SET @AuctionGross = inserted.WinBid*1.20
         update LOT set AuctionGross=@AuctionGross where LOT.Auctionid=inserted.auctionid
 end


although I of course do not know the names of your fields etc.
0
 
LVL 2

Expert Comment

by:ngolovin
ID: 12135410
This:

>CREATE TRIGGER MYSQLcontentnodes_Trigger1
>ON dbo.MYSQLcontentnodes

comes from my database :=) On your database it should read something like

>CREATE TRIGGER AUCTION_Trigger1
>ON dbo.AUCTION
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12135591
Hi,

You can use SQL Triggers, I do it quite frequently, and the suggestions above should help you.

However, I will put ALL the formulas at the same place, either Access front end OR the triggers. If you spread your business logic everywhere, it may become a maintenance nightmare.

You can, in the afterupdate event of a control in Access, put SQL code to update another table.


Just my humble opinion...

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12135942
From the suggestions above, I am leaning toward either: 1) setting the trigger directly onto the table (although I would really need it set to run not when "the table" as a whole is updated  but when "a certain column in the table" is updated); or 2) putting the SQL code directly into the AfterUpdate event along with the VB code I've already got there.

Hmm... How do I put SQL code in with my VB code in an AfterUpdate event?
0
 
LVL 14

Accepted Solution

by:
ragoran earned 450 total points
ID: 12136081
JimHorn already has part of the solution, all you need is to fill in where he says ' Do all your stuff here...

(you will need to replace some name with yours)

dim wCom as adodb.command
dim wResult as double
wResult = A + B  ' put your formula here

set wcom = new adodb.command
set wCom.ActiveConnection = cn

wCom.CommandText  = "Update LOT set FieldName = " & wResult & " where LOT_ID = " & me.txtLot_ID

wCom.execute

set wCom = nothing
 
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12136213
Okay, it's easy enough to spot wherre the actual formula goes... But could you explain the other code?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12136354
Because you are developing an ADP application, you should use ADODB to talk directly to the database.

Jim's code showed you how to get the active connection to your database.  Whenever you talk to a database using ADODB, it has to be tru a connection.

ADODB has 2 other important objects.  Recordset, that returns the result of a query, and Command use to issue other SQL instruction (insert, delete, update) or execute store procedures.

In our case, we need to issue an Update, so we are using a command.

We set the command active connection to the connection object set in Jim's portion,  Then we build the SQL to do the update ans assign it to the CommandText property of the command.  All left to do is to execute the query.


In Jim's code, he added begintransaction and commit commands.  This is typical in database operation to ensure coherent transactions.  IT is particuarly usefull when applying many changes at once.

Be warned that if you put this code in the afterudpate of the field but then the user undo his change, the LOT table will already be updated.  I will rather put this code in the AfterUpdate event of the Form, so you know that the entered value is in the table.

There may be other scenarios to think of, it depends on the formula.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12136481
>>I will rather put this code in the AfterUpdate event of the Form<<

You may recall from an earlier question (http://www.experts-exchange.com/Databases/MS_Access/Q_21108928.html) that the AfterUpdate event is not actually on the TotalPaid control, since TotalPaid is a calc'ed control; rather the AfterUpdate was put on the subform [AuctionPmt Subform] which is where payments are totalled to determine if they equal the winning bid, thus triggering TotalPaid to become TRUE.  So, fortunately for me, you are suggesting putting this new code into the AfterUpdate of a form, and so I wll be.  :)

I'll take a crack at combining the code suggestions from above, and I'll post it here for an expert once-over.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12137008
If I have Jim's code as a wrap-around and then Ragoran's code performing some SQL updates in the middle, and I put all of this into the AfterUpdate event of a form, where do I put the VB code that is already part of my AfterUpdate event?

Basically, you are suggesting the following:

- Jim's opening block of code sets active connection to SQL database
- Ragoran's suggested code builds and executes a SQL query to update values in LOT table
- Jim's closing block of code commits the transaction and closes the connection.

My current AfterUpdate event already calculates some values (AuctionGross and TotalPaid) for the AUCTION table. The calc happens in the front-end, and the values are then stored in the AUCTION table. How do I mesh together the new SQL-query stuff with the existing front-end calc stuff?


0
 
LVL 14

Expert Comment

by:ragoran
ID: 12137079
You can just put the new code after yours.  Although it is, in my opinion, a good habit to group all the DIM statement at the begining of the procedure.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12137379
Say... If I'm going to be using a SQL query in this code to update a value in the LOT table, then should I do the same thing with the AuctionGross value that's currently being calc'ed in the front-end and stuck into AuctionGross control text box (which is bound to the AUCTION table)? That is, should I just lump both of the calcs together into the "SQL" portion of the modificed AfterUpdate event?

It would look something like this:

Begin AfterUpdate sub on [AuctionPmt Subform]
- Declare variables
- Me.Requery 'to force udpated subform totals to propogate out to TotalPaid
- Jim's opening block of code sets active connection to SQL database
- Ragoran's suggested code builds and executes a SQL query to calculate and store/insert LOT.Payback and AUCTION.Gross
- Jim's closing block of code commits the transaction and closes the connection to SQL database
- Requery the main AuctionForm, so that user sees AuctionGross value immediately
- Requery LOTform, in case it is open, so that user sees Payback value immediately
End AfterUpdate sub
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12137428
I think it is a good idea.  That way, we have similar method to process all the calculation.  It will make the application simpler.

Actually, if you were to update a control box in the afterupdate event of the form, you will never been able to keep the data in a save state( user make change, save the modification, the form change a value in the box, it become dirty again, user save changes, form put a value in a box, becomes dirty again even if it is the same value, ad nauseaum)

So your idea is best...

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12137787
If I were to "convert" my VB code into a SQL query.... Hmm... Can I still do an If-Then-Else statement?
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12137845
Here's the current VB code that calc's the AuctionGross control and the PaidStatus control:
**********************************
Private Sub Form_AfterUpdate()
Dim Gross As Currency
Gross = 0

' Force a requery of AuctionPmt_Subform so that Text10 updates and assigns new value
' to the TotalPaid control on the main AuctionForm *before* comparing TotalPaid to WinBid
Me.Requery

If (([Forms]![auctionform]![WinBid] > 0) And ([Forms]![auctionform]![TotalPaid] >= [Forms]![auctionform]![WinBid])) Then
      Gross = Nz([Forms]![auctionform]![TotalPaid], 0) - Nz([Forms]![auctionform]![EbayListFee], 0)
      Gross = Gross - Nz([Forms]![auctionform]![EbayComm], 0)
      Gross = Gross - Nz([Forms]![auctionform]![CCFee], 0)
      [Forms]![auctionform]![AuctionGross] = Gross
      [Forms]![auctionform]![PaidStatus] = True
Else:
      [Forms]![auctionform]![PaidStatus] = False
      [Forms]![auctionform]![AuctionGross] = 0
End If
End Sub
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12137860
I am not sure I understand.  I will use VB to compute the value then use SQL only for the update statement.

I have to leave for tonight,  I will continue tomorow if you haven't fix your problem

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12137938
Okay... so before establishing the SQL connection ADODB, I should calc the AuctionGross in VB the way I already am. Then... assign that value to a variable, and use that in the SQL update statement. Correct?

I'll try that. Talk to you guys tomorrow.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12138254
Okay, here's my first draft of this monster. I am not certain of syntax in a few places -- you'll see some 'comments with my questions, seeking advice. :) My basic approach: in VB calculate the AuctionGross as wGross, then after connecting to SQL post that value to AUCTION.Gross; then (still in SQL) calculate the Payback amount to the client as wPayback and post that value to LOT.Payback; then requery both AuctionForm and LotForm. Feedback please...?  Thanks a bunch.

*********************************************
Private Sub Form_AfterUpdate()

on error goto error_handler

dim cn as adodb.connection
dim wCom as adodb.command
dim wPayback as double
dim wGross As Double
dim wPaid as .....Bit? String?

Me.Requery  ‘Requeries AuctionPmt_Subform so that Text10 recalcs and updates [AuctionForm]![TotalPaid]

wGross = 0

If (([Forms]![AuctionForm]![WinBid] > 0) And ([Forms]![AuctionForm]![TotalPaid] >= [Forms]![AuctionForm]![WinBid])) Then
      wGross = Nz([Forms]![auctionform]![TotalPaid], 0) - Nz([Forms]![auctionform]![EbayListFee], 0)
      wGross = wGross - Nz([Forms]![auctionform]![EbayComm], 0)
      wGross = wGross - Nz([Forms]![auctionform]![CCFee], 0)
      wPaid = True
Else:
      wGross= 0
      wPaid = False
End If

set cn = currentproject.connection   ' Connects to SQL database to update LOT and AUCTION tables
cn.begintrans    

set wcom = new adodb.command
set wCom.ActiveConnection = cn

wCom.CommandText  = "Update AUCTION set Gross = " & wGross & " where EbayNum = " & me.EbayNum
wCom.execute ‘<<<Does wCom.execute post values immediately?

wPayback = (AUCTION.Gross – LOT.ClientPrice) * LOT.ProfitSPlit  ‘<<<How is this done in SQL?

wCom.CommandText  = "Update LOT set Payback = " & wPayback & " where LotNum = " & [Forms]![AuctionForm]![CCFee] ‘<<<How is this done in SQL?
wCom.execute

set wCom = nothing

cn.committrans ‘<<< Is this where new values are posted? Or do they post with wCom.execute?

exit_sub:
  exit sub

Forms.AuctionForm.Requery
Forms.LotForm.Requery

error_handler:
  cn.rollback   'other error handling stuff can be added here, too

End Sub
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12142707
Try this (comments in the next post):

*********************************************
Private Sub Form_AfterUpdate()

on error goto error_handler

dim cn as adodb.connection
dim wCom as adodb.command
dim wRS as adodb.recordset
dim wPayback as double
dim wGross As Double
dim wPaid as boolean     '.....Bit? String?

'this is not required, the form just got updated, I'll be surprise if it need to be refresh
'Me.Requery  ‘Requeries AuctionPmt_Subform so that Text10 recalcs and updates [AuctionForm]![TotalPaid]

wGross = 0
wPayback = 0
wPaid = false


If (([Forms]![AuctionForm]![WinBid] > 0) And ([Forms]![AuctionForm]![TotalPaid] >= [Forms]![AuctionForm]![WinBid])) Then

     set cn = currentproject.connection   ' Connects to SQL database to update LOT and AUCTION tables

      wGross = Nz([Forms]![auctionform]![TotalPaid], 0) - Nz([Forms]![auctionform]![EbayListFee], 0)
      wGross = wGross - Nz([Forms]![auctionform]![EbayComm], 0)
      wGross = wGross - Nz([Forms]![auctionform]![CCFee], 0)

      set wRS = new adodb.recordset
      set wRS.ActiveConnection = cn
      wRS.open "Select ClientPrice, ProfitSPlit  from LOT where LotNum = " & [Forms]![AuctionForm]![CCFee]

      if not wRS.EOF then
         wPayback = (wGross – wRS.Fields("ClientPrice")) * wRS.Fields("ProfitSPlit")
      end if
     wRs.close
     set wRs = nothing

     cn.begintrans    

     set wcom = new adodb.command
     set wCom.ActiveConnection = cn

     wCom.CommandText  = "Update AUCTION set Gross = " & wGross & " where EbayNum = " & me.EbayNum
     wCom.execute ‘<<<Does wCom.execute post values immediately?  Yes and No

     wCom.CommandText  = "Update LOT set Payback = " & wPayback & " where LotNum = " & [Forms]![AuctionForm]![CCFee]
     wCom.execute

     set wCom = nothing
     
     cn.committrans ‘<<< Is this where new values are posted? Or do they post with wCom.execute?
     set cn = nothing

end if

exit_sub:
  exit sub

Forms.AuctionForm.Requery
Forms.LotForm.Requery

error_handler:
  cn.rollback   'other error handling stuff can be added here, too
  set wCom = nothing
  set wRS = nothing
  set cn = nothing
End Sub
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12142820
Comments for previous post:

wPaid should be boolean

I think you want to update the tables only if there is an amount, so I put the input code in the IF Then block.  I may be wrong.

When using transaction, you want to put as little code as possible between the BeginTrans and Commit.  To get the current values from the LOT table, I use a recordset object to query the table.  This is done before the transaction.  It may cause an error if the application is used in a multiuser context where two user could update the same LOT table but I am not familiar enough with your application as whole to know if this is an issue or not.  If so, tell me and I will show you how to fix it.

About the posting in the database when using transaction.  This is a bit tricky but I will try to explained as simple as I can. Transaction are staging area where all the changes are kept pending until a commit is issued. The pending modifications are visible to the caller code (connection) so from this, and only this connection, the pending changes seems to be done.  But if you stop the code execution and use another connection (e.g. SQL Query Analyser), you will not see the pending changes.  

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12183699
I'm going to re-work your code suggestion a bit, as I noticed some errant parts, probably due to my poor explanations of what I'm trying to do.  But you are correct: ALL of these changes (calc's and updates) are necessary when TotalPaid >= WinBid on the main AuctionForm, so it makes sense to put the SQL connection into the IF statement.

Anyway, in English: When a payment is entered into the AuctionPmt subform, the AfterUpdate will requery the subform in order to ensure that the Total field is calc'd and that the Total value is picked up by the TotalPaid control on the main form. Then, we check to see if this new TotalPaid >= WinBid, and if so, the changes ensue. These changes include:
1) Calculating the AuctionGross, which is TotalPaid - EbayComm, EbayFee, and CCfee.
2) In SQL Updating the AUCTION table with the AuctionGross value.
3) In SQL Calculating the LotGross, which is AuctionGross - Lot.ClientPrice
3a) In SQL, updating the LOT table with LotGross
4) In SQL, calculating Payback, which is Lot.ClientPrice + (Lot.LotGross * Lot.ProfitSplit)
5) In SQL, updating LOT table with Payback value.
6) Calculating and updating Lot.RProfit, which is LotGross - Payback.
7) Requerying the main AuctionForm (so that all values are presented properly in their controls)
8) Requerying the LotForm (in case it's open, so that all values are presented properly in their controls, including RProfit)

At least that's how I envision this thing. Note that after each calculation in the SQL transaction, the newly calc'd values are needed to perform the next calculation. You are saying that each calculated value does not have to be committed prior to the next calculation, but that they can be calc'ed together in the transaction and then committed all together at the end?

I am going to re-draft the code and re-post for your perusal.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12183825
Also, due to multi-user requirements, I will need a workaround for opening the LOT table recordset prior to the transaction... You have something in mind?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12183895
Yes.

Let me rephrase in my own word what I will do in a typical instance.  This is to limit the number of hits on the DB

1- get all the information from the DB using as little Select's as possible

2- Compute the result in memory using working variables

3- Update the tables with the results.

So you should be able to group together your steps 1, 3, 4 and 6 (calculating) and group steps 2, 3a, 5 and 6 (updating)

Especially steps 3a, 5 and 6 because you are updating the same table. So you have one update instead of three.



0
 
LVL 14

Expert Comment

by:ragoran
ID: 12183967
As for the multi-user issue... post your code as you think it should be and we will work from that point on.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12184779
Okay, my code follows. The overall process looks like this:
1) variables are set (I added a few to facilitate the calculations)
2) a connection to the SQL database is made
3) the IF statement begins, which generally comprises the rest of the code
4) IF TRUE, the calc's are done, a transaction makes the appropriate updates to the SQL tables, the "PaidStatus" control is set to TRUE, and the connection to the SQL database is closed
5) IF FALSE, a transaction makes the appropriate updates to the SQL tables (that is, sets some values to 0), the "PaidStatus" control is set to FALSE, and the connection to the SQL database is closed
6) AuctionForm and LotForm are requeried.

Here's the code:

*********************************************
Private Sub Form_AfterUpdate()

on error goto error_handler

dim cn as adodb.connection
dim wCom as adodb.command
dim wRS as adodb.recordset
dim wPayback as double
dim wAuGross As Double
dim wLtGross As Double
dim wClProfit As Double
dim wRprofit As Double
 
Me.Requery  ‘Requeries AuctionPmt_Subform so that Text10 recalcs and updates [AuctionForm]![TotalPaid]

wPayback = 0
wAuGross = 0
wLtGross = 0
wClProfit = 0
wRprofit = 0

set cn = currentproject.connection   ' Connects to SQL database to update LOT and AUCTION tables

If (([Forms]![AuctionForm]![WinBid] > 0) And ([Forms]![AuctionForm]![TotalPaid] >= [Forms]![AuctionForm]![WinBid])) Then

      wAuGross = Nz([Forms]![auctionform]![TotalPaid], 0) - Nz([Forms]![auctionform]![EbayListFee], 0)
      wAuGross = wAuGross - Nz([Forms]![auctionform]![EbayComm], 0)
      wAuGross = wAuGross - Nz([Forms]![auctionform]![CCFee], 0)

      set wRS = new adodb.recordset
      set wRS.ActiveConnection = cn
      wRS.open "Select ClientPrice, ProfitSPlit  from LOT where LotNum = " & [Forms]![AuctionForm]![LotNum]

      if not wRS.EOF then
         wLtGross = (wAuGross – wRS.Fields(“ClientPrice”))
         wClProfit = (wLtGross * wRS.Fields("ProfitSPlit")) ‘Be sure to insert more complex formula as per boss
         wPayback = (wRS.Fields(“ClientPrice”) + wClProfit)
         wRProfit = wLtGross - wClProfit
      end if

     wRs.close
     set wRs = nothing

     cn.begintrans    

     set wcom = new adodb.command
     set wCom.ActiveConnection = cn

     wCom.CommandText  = "Update AUCTION set Gross = " & wAuGross & " where EbayNum = " & me.EbayNum
     wCom.execute

     wCom.CommandText  = "Update LOT set GrossProfit = " & wLtGross & “, Payback = " & wPayback & “, RinnerProfit = " & wRProfit & " where LotNum = " & [Forms]![AuctionForm]![LotNum]
     wCom.execute

     set wCom = nothing
     
     [Forms]![AuctionForm]![PaidStatus] = True

     cn.committrans
     set cn = nothing

Else:

     cn.begintrans    

     set wcom = new adodb.command
     set wCom.ActiveConnection = cn

     wCom.CommandText  = "Update AUCTION set Gross = " & 0 & " where EbayNum = " & me.EbayNum
     wCom.execute

     wCom.CommandText  = "Update LOT set GrossProfit = " & 0 & “, Payback = " & 0 & “, RinnerProfit = " & 0 & " where LotNum = " & [Forms]![AuctionForm]![LotNum]
     wCom.execute

     set wCom = nothing

     [Forms]![AuctionForm]![PaidStatus] = False

     cn.committrans
     set cn = nothing

end if

exit_sub:
  exit sub

Forms.AuctionForm.Requery
Forms.LotForm.Requery

error_handler:
  cn.rollback   'other error handling stuff can be added here, too
  set wCom = nothing
  set wRS = nothing
  set cn = nothing

End Sub
*********************************************
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 12184793
Holy cow this thread is getting long.  I hope these experts are getting at least a case of beer out of this...
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12189612
Looks good but...

Is that code run from the AuctionForm ?

If so, I will propose some important change to it.

After I get your answer, I will post whet you most change to ensure integrity within a multi-user environment.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12191258
LOL Jim. It *is* causing *me* to drink...

Ragoran, the code is being placed into the AfterUpdate event of a subform on the AuctionForm -- called [AuctionPmt Subform]. The subform is the one being referenced as Me.
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12192288
My comments in the next post:
=======================

Private Sub Form_AfterUpdate()

On Error GoTo error_handler

Dim cn As ADODB.Connection
Dim wRS_Auction As ADODB.Recordset
Dim wRS_Lot As ADODB.Recordset
Dim wPayback As Double
Dim wAuGross As Double
Dim wLtGross As Double
Dim wClProfit As Double
Dim wRprofit As Double
 
Me.Requery  'Requeries AuctionPmt_Subform so that Text10 recalcs and updates [AuctionForm]![TotalPaid]

wPayback = 0
wAuGross = 0
wLtGross = 0
wClProfit = 0
wRprofit = 0

Set cn = CurrentProject.Connection   ' Connects to SQL database to update LOT and AUCTION tables
cn.CursorLocation = adUseServer   'to ensure lock on recordset
cn.BeginTrans


'Open and lock the record
Set wRS_Auction = New ADODB.Recordset
Set wRS_Auction.ActiveConnection = cn
wRS_Auction.LockType = adLockPessimistic  'No one else will be using this record
wRS_Auction.CursorType = adOpenDynamic
wRS_Auction.Open "select * from Auction where EbayNum = " & Me.EbayNum

Set wRS_Lot = New ADODB.Recordset
Set wRS_Lot.ActiveConnection = cn
wRS_Lot.LockType = adLockPessimistic  'No one else will be using this record
wRS_Lot.CursorType = adOpenDynamic
wRS_Lot.Open "select * from Lot where LotNum = " & Me.Parent![LotNum]





If ((Me.Parent![WinBid] > 0) And (Me.Parent![TotalPaid] >= Me.Parent![WinBid])) Then

      wAuGross = Nz(Me.Parent![TotalPaid], 0) - Nz(Me.Parent![EbayListFee], 0)
      wAuGross = wAuGross - Nz(Me.Parent![EbayComm], 0)
      wAuGross = wAuGross - Nz(Me.Parent![CCFee], 0)

      wLtGross = (wAuGross - wRS_Lot.Fields("ClientPrice"))
      wClProfit = (wLtGross * wRS_Lot.Fields("ProfitSPlit")) 'Be sure to insert more complex formula as per boss
      wPayback = (wRS_Lot.Fields("ClientPrice") + wClProfit)
      wRprofit = wLtGross - wClProfit
     
      wRS_Auction!Gross = wAuGross
      wRS_Auction!PaidStatus = True ' replacing Me.Parent![PaidStatus] = False
      wRS_Auction.Update
     
      wRS_Lot!GrossProfit = wLtGross
      wRS_Lot!PayBakc = wPayback
      wRS_Lot!RinnerProfit = wRprofit
      wRS_Lot.Update
     
Else

      wRS_Auction!Gross = 0
      wRS_Auction!PaidStatus = False ' replacing Me.Parent![PaidStatus] = False
      wRS_Auction.Update
     
      wRS_Lot!GrossProfit = 0
      wRS_Lot!PayBakc = 0
      wRS_Lot!RinnerProfit = 0
      wRS_Lot.Update


End If

wRS_Auction.Close
Set wRS_Auction = Nothing

wRS_Lot.Close
Set wRS_Lot = Nothing

cn.CommitTrans

Set cn = Nothing

exit_sub:
  Exit Sub

Forms.AuctionForm.Requery
Forms.LotForm.Requery

error_handler:
  cn.rollback   'other error handling stuff can be added here, too
  Set wRS_Auction = Nothing
  Set wRS_Lot = Nothing
  Set cn = Nothing

End Sub


0
 
LVL 14

Expert Comment

by:ragoran
ID: 12192346
This code compile, but I can't test it as I don't have all the table.

Small change in syntax, I replace forms!AuctionForm to me.parent which will be faster and, I think, easier to understand what object you are talking about.


When dealing with transaction the reads value from a record, used them to compute new values and store the result back in the record, all within multiuser environment, you must ensure that no changes are done on the records between the time you read it and the moment you want to upgrade it.  We have to put a lock on it.

I am suggesting to open a recordset on each records to update, with the required options to lock the record.  You can read the input value from the recordset and to the update using ADO instead of a SQL command.  The end result will be the same.

Another small change.  If the Parent form is bound to the Auction table, then might as well update the PaidStatus flag using the recordset instead of using the control on the form.  If not, the put your line of code back in but, before leaving the procedure, you should add this line to force the saving of the new values:

me.parent.dirty = false


0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12192501
The following lines appear just before the error_handler:

>exit_sub:
> Exit Sub
>
>Forms.AuctionForm.Requery
>Forms.LotForm.Requery

I suppose that when the sub is running properly then the exit_sub acts as a Go To statement, correct? If so, then I will move the two requeries to occur before exit_sub.
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12192517
You are correct.  Exit sub terminates the sub and no code after is being executed.  You should move the two line of code before the Exit Sub

The code after the Error_handler label is run only when ther is an error (because of the instruction On Error Goto error_handler at the top of the procedure).
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12192854
I have encountered an error, as a matter of fact, but it can't process the cn.rollback statment in the error_handler!
I've removed the error_handler for the time being to track down the root-cause error...
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12192902
If the error happened before you initialize the cn variable, or open the transaction, then the rollback won't work.

You idea to remove the roll back for debuging is ok.  
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12192997
Okay, the code errors out on the following line: wRS_Lot.Open "select * from Lot where LotNum = " & Me.Parent![LotNum].  (This line is AFTER the transaction is opened.)

Some examples of the eror message:

1) When the LotNum associated with the Auction is 29-H, the error is:
"Run-time error '-2147217900 (80040e14)': Invalid column name 'H'."

2) When the LotNum associated with the Auction is FR-123, the error is:
"Run-time error '-2147217900 (80040e14)': Invalid column name 'FR'."

3) Interestingly, when the LotNum associated with the Auction is 4345, the error is:
"Run-time error '-2147217913 (80040e07)': Syntax error converting the
varchar value '100-R' to a column of data type int."
(LotNum 100-R is the first record in the LOT table.)

In the LOT table, LotNum is varchar; I can find nothing that would require it to convert to an int anywhere in the event code.
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12193022
The select statement should be :

wRS_Lot.Open "select * from Lot where LotNum = '" & Me.Parent![LotNum] & "'"

Because LotNum is varchar, so the value must be encapsulated in quotes.

Move the cn.begintrans after the block of code where we open the recordset, before the if statement.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12194078
I've been correcting various errors that were occuring (once I took the error-handler out of the way), and now I've a couple of question:

Although this whole process is set in motion by changes to a subform on the AuctionForm, some of the updates are to tables bound to controls that are on the LotForm. That very last requery (Forms.LotForm.Requery) is intended to ensure that if the LotForm is already open when this code is run that the updates will occur not only in the underlying LOT table but will also appear in the appropriate LotForm controls automatically and immediately. It seems to work, I think... However, if the LotForm is NOT open, then I get an error that the "Object doesn't support this property or method." Is there some way to determine if LotForm is open before running the requery?

There may be some more tweaky questions as I continue to troubleshoot. But it looks like the calc's and updates are occuring properly now!
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12194132
Just remember that I helped you put together a way to access forms through code using a global object and its property.  Are you still using this method.  If so you should not used calls to forms.LotForm at it may or may not point to the open lot form.

We could add some properties inthe global object you could get to know if a particular form is open or not.  You can then asked the requery thru the global object property.

Most likely, I am not clear here.

Let me look back into previous messages so I can use actual object and property names
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12194311
In the clsContext module, add a property for each forms that the class manages (Auction, Lot and a third one that I don't remember).  The properties will be based on the following pattern but you will need to adapted it for each form object using the correct "mo.." variable name and formname where needed:

Public property get isXFormLoaded() As Boolean

   Dim wResult As Boolean
   Dim x As String
 
   On Error Resume Next
   
   bresult = True
   If moXFrm Is Nothing Then
      Set moXFrm = Forms!LotForm
      If Err.Number <> 0 Then
          wResult = False
      End If
   Else
      x = moXFrm.Name
      If Err.Number > 0 Then
         Set moXFrm = Forms!LotForm
         If Err.Number <> 0 Then
             wResult = False
         End If
      End If
   End If


   Set isXFormLoaded = wResult
   
End property


Then in the after_update code, replace forms.lotForm.requery with

dim oF as form
if goContext.isLotFormLoaded() then
   set oF =  goContext.anLofForm
   oF.requery
end if

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12194567
Ah! I didn't even think of the global objects!  I am such a noob -- ha ha -- I appreciate your patience with me.

So, you are suggesting this new "Get Loaded" property for each of the forms (Auction, Lot, and maybe Order) in addition to the other Get properties that are already in the clsContext module?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12194598
You got it.


0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12194721
In the subform's AfterUpdate event:

dim oF as form
if goContext.isLotFormLoaded() then
   set oF =  goContext.anLofForm
   oF.requery
end if

I can see that this will requery the LotForm, but won't it also pull it to the foreground? (I don't need it to.)
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12194765
Upon testing I get a Compile Error: Object Required
on this line in the clsContext property:    Set isLotFormLoaded = wResult

The syntax is the same as the other Get properties, and they appear to work just fine....  
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12199845
Sorry for the delay, I left office yesterdy before receiving this email, but I missed it be 1 or 2 minutes..

My error.  I copied the code from somewhere else and forgot to remove the SET verb.  It is required only when assigning a reference of an object to a variable, which is not the case here because we are dealing with scalar values (boolean to be exact).

The instruction should only be

isLotFormLoaded = wResult


0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12204070
Ah, okay.

What about this in the AfterUpdate:

dim oF as form
if goContext.isLotFormLoaded() then
   set oF =  goContext.anLofForm
   oF.requery
end if

Will that pull the LotForm to the foreground, or just requery it (if it's open)?

--Galisteo8

p.s. Now I'm out of the office today, so I can't try the code again until Monday!
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12216488
If I remember correctly, it is in the Goto... procedures that you set the visible property to true and put the focus on the form.  We are not calling that procedure here,so it should not make the LOT form on top.  Best way to know is to test it ...

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12228426
Good morning!

Results of testing:

1) I no longer get the compile error. However, I *do* get a Write Error. Here's what I did. I opened AuctionForm and the LotForm. Then I entered sufficient payments into the AuctionForm subform (AuctionPMT) in order to cause TotalPaid >= WinBid. Since LotForm was open, it should have been updated. Instead, when I go back to the LotForm, it is still displaying the "old" numbers. If I attempt to view a different LotForm record, I get the Write Error: "This record has been changed by another user since you started editing it..."

2) For some reason, after entering the payment into the subform, if I click anywhere else (like on the main form) in order to commit the change, the main AuctionForm goes back to record #1. THis would be caused by something in the subform's AfterUpdate, but I'm just not seeing it...
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12228523
Hi,

1) This message happens because the form is in edit mode while the record has been updated in the DB thru code.  You have to make sure that you don't update any controls on the Lot form, even ig you just copy the same value.  If your not sure, you could add these lines before the transaction in the afterUpdate event although it will be best to spot where the form is being updated.

if goContext.isLotFormLoaded() then
   set oF =  goContext.anLofForm
   oF.Dirty = false   'this forces the save of any pending changes.
end if


2) Whenever you requrey a form, the "current" record is reset to the first one.  It has less impact on a continuous form because we already display all records.  Try Refresh instead of Requery.  Also refresh will be faster as it only update the current record while Requery will reload the entire recordset.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12228799
>>This message happens because the form is in edit mode ... It will be best to spot where the form is being updated.<<

Hmmm... Isn't an Access form automatically in "edit mode" whenever a user opens it? That is, the user can change the data...
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12228829
Sorry, I meant that it is "dirty" in the database sense.  To check it, keep the record selector on the form.  Whenever you see a pencil instead of a triangle, it means that the data on the form has somehow been modified either by the user or by a line of code.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12228958
1) >>Whenever you see a pencil instead of a triangle<<
Yes, I see what you mean. When I open the LotForm and use the selector arrows to move from record to record, I see the arrow -- until I get to the record of the Lot that has been sold and paid for at Auction (that is, the one that was updated through the AfterUpdate code above). On that record alone do I see the pencil, even if I just look at it in the Access form and make no changes to it. Which means what -- that it is always locked in some way??

2) Oddly, I replaced Forms.AuctionForm.Requery with Forms.AuctionForm.Refresh in the AfterUpdate event -- and there has been no change. The form still bounces back to record #1.


P.S. I need to sttep out of the office for a bit. I will check back in later or tomorrow morning. Thanks!
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12241083
Raghoran,

Let's forget the Requery/Refresh thing in this thread. I opened a different question for that. :)

So the problem at hand is the fact that my Lot record seems to be stuck in an "edit mode". Not all Lot records viewed therough the Access form; just the one that is associated with an Auction that has been paid...
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12241112
Are you sure that in the OnCurrent event or other you don't programmatically assign a value to a control of the form ?

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12242008
Hmmm... There is an OnCurrent event in the LotForm. So if the Lot being viewed in LotForm is related to an Auction that has been paid, the AfterUpdate stuff above calcs the AuctionGross, then when LotForm is opened (or is already open) the AuctionGross2 subform gets requeried to include the newly paid Auction and will now have a record related to the Lot sold in that Auction... resulting in the calc and assignation of LotForm.GrossProfit. <sigh> Is there any way around this?

Right now, the OnCurrent event re-calc's the GrossProfit only if there is a related record in the AuctionGross2 subform. If there is one, then GrossProfit recalc's by default, even if that subform record hasn't actually changed... I suppose this code could ALSO be placed into the AfterUpdate event...?

-Galisteo8

P.S. Here's the LotForm's OnCurrent event:

Private Sub Form_Current()
[LotOrders Subform].Requery
[AuctionGross2 Subform].Requery

Dim GProfit As Single
Set rs = [AuctionGross2 Subform].Form.RecordsetClone

If rs.RecordCount <> 0 Then
GProfit = Me.[AuctionGross2 Subform].Form!Gross - Me.ClientPrice
Me.GrossProfit = GProfit

End If

End Sub
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12242081
Bravo. I think you put your finger onthe problem and As someone said, knowing the problem is half the solution...

Computation in the OnCurrent event should only be done for display only field.  You should put this computation in the afterupdate event of the subform.

However, there is a quick workaround that should help you get going:

If rs.RecordCount <> 0 Then
GProfit = Me.[AuctionGross2 Subform].Form!Gross - Me.ClientPrice
if Me.GrossProfit <>  GProfit then
  Me.GrossProfit = GProfit
  me.dirty = false
end if
end if

As you see, You can update the field only if it is different and then save the record right now.

From a good application design perspective, you should put the computation in the afterupdate, but I understand that it may involved more work.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12242221
So... Me.Dirty=False will prevent the change if the value isn't different?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12242244
No,

Me.Dirty forces access to save the change to disk.  Basically click on the pencil for you.

if Me.GrossProfit <>  GProfit then

is the line that will do the changes only of required
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12242245
Wait, scratch that question.  :) I'll give this a go. It may provide a workaround until I can re-code the AfterUpdate event.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12243378
I'll have to get back to this tomorrow. I'm plagued right now with the entire friggin' AfterUpdate failing to run when I actually make an update.... <grumble> Worked earlier; now it doesn't...
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12250384
Okay, back on track. I had some form curruption to deal with..

I *think* everything is generally working as it should. I am still going to put some of that code into the AuctionPMT's AfterUpdate instead of the LotForm's OnCurrent event. But in the meantime.. this issue with the forms "flipping back" to the first record when I do a requery...  Let's ignore for the moment that that didn't used to happen last week. <sigh>  But is there some handy way to just ensure, after all the calculations in the AfterUpdate, that the newly calc'd value are "pushed out" to their respective controls on the Access forms?
I would like to replace this line -- Forms.AuctionForm.Requery -- with something specifically trargeting the AuctionGross control. Here's what I've tried:

Forms.AuctionForm.AuctionGross.Requery
Forms.AuctionForm![AuctionGross].Requery

Neither of these work. Oddly, they don't generate any errors; they just don't actually update the value displayed in the AuctionGross control. Do I have the wrong syntax?
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12251507
ALso... When I have a form with a subform, and a little "pencil" icon appears near the form's upper-left corner after the AfterUpdate event runs, does this mean that the FORM is still in edit mode, or that the SUBFORM is still in edit mode?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12251698
Because you are developping an ADP project, the recordset for the form is a snapshot.  The only way to refresh its data is to requery th form.

What you will need to do is to memorize the current key value that is displayed, requery the form then move back to the memorize key.  The logic is similar, but not identical, to the gotoLot method I proposed.

As for the other question, in your example, is it the form that is being edited.  To help you understand, have th subform show its record selector as well.  You can hide them later, but it will help you understand this behavior much more easily thant anything I could explain here (if a picture is worth 1000 words, then hand-on experiences is worth a million).
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12251849
Oh, I see. The record selectors were already turned on for the subform.  :)

As for the requery code... if I requery the form and then move back to the key'd record, will that  be "resource-heavy"?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12251919
If the form is bound to a table, then you will refresh the snapshot of the table.  If the table contains a large number of record, yes, it can take resources.

To tell you the truth, I have more experiences with MDB's than with ADP's.  I am pretty sure you are not the first one to have this requirement.  I suggest you post a new question about resynchronizing the currrent record data with the database in an ADP form, without doing a full requery.  Maybe someone else has a trick for that.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12252097
I did post a question yesterday, but in reference to a form that did not have all of these coding requirements on it. I ultimately found I could just remove the requery, and it was fine. However, one reponder did suggest the following which perhaps might be helpful here (I have already adapted it to fit this AfterUpdate event). Would the use of the RecordsetClones in this case help minimize the calls to the database?
************************************************

' Refreshes AuctionForm to send new values to AuctionForm controls
Dim auPK as Long
auPK=Forms.AuctionForm!EbayNum
Forms.AuctionForm.Requery
Forms.AuctionForm.RecordsetClone.FindFirst "[EbayNum]=" & auPK
Forms.AuctionForm.Bookmark=Forms.AuctionForm.RecordsetClone.Bookmark

' Refreshes LotForm, if it's open (determined in the clsContext module),
' in order to send new values to LotForm controls immediately
Dim oF As Form
Dim ltPK as Long
If goContext.isLotFormLoaded() Then
   ltPK=Forms.LotForm!LotNum
   Set oF = goContext.anLotForm
   oF.Requery
   oF.RecordsetClone.FindFirst "[LotNum]=" & ltPK
   oF.Bookmark=oF.RecordsetClone.Bookmark
End If
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12252220
You can interpret the recordsetclone as a second pointer to the same recordset.  So you have two "current record", one on the form, and one on the recordsetclone.  This is usefull when you need to ue the value from another record and you don't want the fomr to go to this record or, as used in your example, you want to move the form to a new record by swapping the bookmark value.

The recordsetclone will not refresh the recordset.  Only the Requery and Refresh methods do that. So the use of recordsetclone does, in this case, reduce the hits to the database.

Actually, the example you just provided is the suggestion I made about memorizing the current key value, the doing a requery and moving back to the previous value.

I read a little about requery and refresh.  I am not 100% sure, but I think that in your case, refresh will be more efficient thant requery.  
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12252457
I realized that the suggestion I had received in another question matched your suggestion for memorizing the primary key value. That's when a little bell went off and I thought it might be useful here. :)

I'll try it with a refresh first and see how that goes.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12252626
I get an error "Object doesn't support this property or method" on the following line:

Forms.AuctionForm.RecordsetClone.FindFirst "[EbayNum]=" & auPK

Is there some other way I should express or identify the AuctionForm form?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12252825
ADO recordsets do not have a Findfirst method. but a Find method.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12253087
That got rid f the error. Thanks for the clarification.
When I run this code with either Requery OR Refresh, though, the form still bounces back to Record #1. I'll press forward on this issue in my other question thread.  :)  Unless you have anything else up your sleeve?

In the meantime though, I do get an error "Runtype error '13': Type Mismatch" on the following line:
   ltPK = Forms.LotForm!LotNum

This seems kind of odd, since the similar line above it -- auPK = Forms.AuctionForm!EbayNum
 -- works just fine. The syntax is the same...
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12253125
Type mismatch means that the variable and the result of the expression are not of the same type.

ltPk is dimmed as long, but if I remember correclty, LotNum is string...

Change the type of ltPk to string.

As for Requery Vs Refresh, I know that both will bounce back to record #1 but I *think* that requery wil actually run the query against the database while refresh will simply synchronis the existing recordset for modified data only.  Thus refresh may be more efficient.  But this is just my interpretation of the documentation.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12253291
<smacks forehead> String for LotNum -- you remembered and I didn't! lol

As for requery vs. refresh... If I use Refresh, should I do it against the Recordset?? That is, should I do
>> Forms.AuctionForm.RecordsetClone.Refresh
instead of just
>> Forms.AuctionForm.Refresh ?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12253415
I don't believe you can requery or refresh the recordsetclone (actually never tried it).  I would do it on the form
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12263350
Hey ragoran,

According to Microsoft, in an ADP, Requery and Refresh will both do a requery -- and it's the requery that bounces my form back to Recod 1. So, since I need to work with that, I simply need an effective way to get back to the record I started at. Hence, I think I can use the GotoAuction functions we've already got set up. For example:
  Dim auPK As Long
  auPK = Forms.AuctionForm!EbayNum 'gets key of current record
  Forms.AuctionForm.Requery
  goContext.anAuctionForm.GotoAuction auPK

As I step through this code, I can see the form bounce back to record #1, and then go back to the record 'auPK' when the goContext line runs...

I think will solve my "bouncing forms" problem. Once I get it tweaked, I can better analyze whether my original issue (triggering all the SQL calc's and form updates) is actually working properly.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12263430
<sigh> Quick question from an exasperated noob...

I am still getting an "unsupported method" error when I try to set the value of a string variable as follows:

Dim ltPK As String
ltPK = Forms.LotForm.LotNum    '  <<< error occurs here

Previsuly, I had Forms.LotForm.LotNum.Value, but that didnt work either.
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12287834
What is LotNum:

A control on the form ...  forms.lotform!LotNum

A field in the recordset ... forms.lotForm.recordset!LotNum

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12289603
Field in the recordset, I suppose.  I am trying assign the value currently displayed in the LotForm's LotNum control to the variable ltPK.

Oddly, the error doesn't occur all the time... But I'll try to track that down.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12303155
Bah... What is this???

"Runtime error '6': Overflow" on this line: auPK = Forms.AuctionForm!EbayNum

(I also tried Forms.AuctionForm.Recordset!EbayNum, but same error.)
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12309426
Ragoran, what is the difference between a control on the form and a field in the recordset? I thought that the data loaded into the form IS the recordset...?

(P.S. Ignore my overflow post.)
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12309920
A control on a form is an object by itself that is or is not bound to a field from the form's underlying recordset .

The confusion arise from the (badly designed IMO) fact that when you create a form by dragging fields on it, Access will name the control as the field name.  I ALWAYS rename my controls afterward.

So if the field in the recordset is named "EBayNum", I will name the textbox that is bound to it "txtEbayNum".

So there is no confusion as

    me!txtEbayNum --> that is the control
 
    me.EbayNum --> that is the field.



0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12310252
Hey, that makes perfect sense. Thank you.

So when I am wanting to set a variable equal to the current LotNum of the LotForm which is open, is there any reason I would want to set the variable equal to the control's value as opposed to the field's value? Or the other way around?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12310296
The only difference is when the form is Dirty (e.g. pending changes not yet save).  The control will hold the "new" value while the recordset will still have the "old" one.  

I typically access the controls values in the form logic because these are what the form is all about ... and I don't have a better reason not to... ;-)
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12310462
In the AfterUpdate event we've been working on O these many long weeks, I am requerying the AuctionForm each time it runs, and requerying the LotForm only if it is already open. Thus, in both cases, I am assigning the value of the current record (EbayNum and LotNum respectively) to a variable prior to the requery:

auPK = Forms.AuctionForm.Recordset!EbayNum  '<-- this one works
ltPK = Forms.LotForm.Recordset!LotNum  '<-- this one doesn't

When I try to assign ltPK, it results in a "Runtime error 438 - This object doesn't support this property or method." The same error happens if I use the following:

ltPK = Forms.LotForm.LotNum

I was hoping understanding the difference between recordset and controls would help me figure this out, but it hasn't.


0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12310484
Does it make a difference that ltPK is of type String?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12311125
Hmmmm.

If the form is loaded using the goContext object, then it will not show in the Forms collection.  Only the form open by the user from the database window or using docmd.open are put in this collection.

use this code:

dim oF as form
if goContext.isLotFormLoaded() then
   set oF =  goContext.anLofForm
   ltPK = of!LotNum
   oF.requery
   oF.gotoLot ltPK   'I don't remember the exact name of the goto method we did way back then
end if
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12311283
<smacking forehead -- again>
THAT explains some of my inconsistent results!!  While teting this AfterUpdate event, I would sometimes open the LotForm from the database window, and other times I would open it using the "Open Lot" button on the Auction form (which uses the goContext object).

Thanks for the enlightement. I'll try your new suggestion when I get a chance this afternoon. (Other tasks are keeping me away at the moment.)
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12322378
Ragoran, thanks -- this solved the problem of the LotForm popping to record #1. Thanks for explaining that the form doesn't show up in the Forms collection if it's loaded using the goContext object. There is still a problem, though -- the LotForm pops to the foreground. Is there some statement I can include to keep that from happening? The user will be using a subform on the AuctionForm when all of this code triggers, and they should still be looking at the AuctionForm when it finishes (without having to bounce to the LotForm and then back to AuctionForm).  What I would like is for LotForm, if open, to 1) requery, 2) go to the record it was on before the requery, and 3) stay in the background as all of this happens.
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12322475
I think that you are using the GotoLot method to go back to the previous record after the requery.  If I remember correctly, we added a call to setfocus method of the form because you wanted the form to come on top when the user click on button to navigate from one form to the other.

I agree that in this situation, this is not advisable.  You have two alternative.  Copy the logic of the GotoLot into another method (GetBackLot) except for the call to setfocus and use this new method in this situation.

OR add a boolean  parameter to the GotoLot method, set it to tru when you want to get the form on top, false otherwise.  You will need to test this parameter before calling the setfocus method.  You will also need to modify your code wherever you called the GotoLot method to add a value for this parameter.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12323391
Okay, that makes perfect sense. I opted to simply create another method and call that method following the LotForm requery. I also used the goContext approach for the AuctionForm requery so that it too would stop bouncing back to Record #1 (I had tried to use goContext with the AuctionForm requery last week, but was still having problems).

So here's what I've got at the end of the AfterUpdate event, after the transaction is committed:

' Requery LotForm, if it's open (determined in the clsContext module), in order to
' send new values to LotForm controls immediately
Dim oF1 As Form
Dim ltPK As String
If goContext.isLotFormLoaded() Then
   Set oF1 = goContext.anLotForm
   ltPK = oF1!LotNum
   oF1.Requery
   oF1.GoBackToLot ltPK
End If

' Refresh AuctionForm to send new values to AuctionForm controls
Dim oF2 As Form
Dim auPK As Long
   Set oF2 = goContext.anAuctionForm
   auPK = oF2!EbayNum
   oF2.Requery
   oF2.GotoAuction auPK

By the way, you suggested naming the variable oF -- what does that stand for?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12323413
small o is for an object type variable
Capital F for Form


0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12323512
Hey -- I think we're finally through with this thread! Thank you, thank you for your patience!

Final query: Can this entire AfterUpdate evet be invoked (like with some kind of pointer) from another control's AfterUpdate event? I have several manual-entry controls that affect the calculation of AuctionGross on the form, and so they will also need to execute the very same code if they are updated by the user.
If not, I will just copy the code logic into each control's AfterUpdate event.
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12323542
Don't copy the logic, it will just add more maintenance work.

What I typically do is to create a private sub in the form and call that sub from each event I want to

Example:

private sub DoSomeStuff
.... put your code here
end sub


private sub xx_AfterUpdate()

   doSomeStuff

end sub



That way you code your logic only once and reuse it as required.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12323674
But I can't call a Private Sub if it's in a different form, right? That is, this is the AfterUpdate for the subform. I also need to re-use that logic for AfterUpdate events on some of the controls on the main form. I'll have to caopy the sub into the main form, and then re-use it there, but still leave the sub in the subform as well.
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12337788
You are correct, you can't call private subs from another modules.  You can either copy the sub to each form or copy the sub in a Module and make it public.  When I face such a decision, I will look at what the sub is doing and with what type of information.  If the sub requires access to read/update controls values, I will tend to leave it in the form.  I will put in module only subs, or functions, that don't access any of the forms control.  They may received information as parameters though.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12348650
Okay, I've applied the AfterUpdate logic to a private module in the AuctionForm, and now the other controls on the AuctionForm refer to it in their own AfterUpdate events. Seems to work great... except for the following.

If I open AuctionForm and test it out, triggering the AfterUpdate event, etc. it seems to work fine. Then, I switch to Design View to tweak something, and the switch back to Form View to test it again. Suddenly, when we get to the goContext anAuctionForm property (which is run at the end of the AfterUpdate code), moFrmAuction.Name is empty. It's not Nothing; rather, it's just empty. This triggers an error that causes a new instance of AuctionForm to be opened.

Here's the code:

Private moFrmAuction As Form_AuctionForm
.......
Property Get anAuctionForm() As Form_AuctionForm

   Dim x As String
   
   On Error Resume Next
   If moFrmAuction Is Nothing Then
      Set moFrmAuction = Forms!AuctionForm   'will try to link to any previously opened AuctionForm
      If Err.Number <> 0 Then   'if no form, then create a new instance of the AuctionForm
          Set moFrmAuction = New Form_AuctionForm
      End If
   End If

  'need to test to ensure reference is still valid
   x = moFrmAuction.Name  '<<<<<<< I flagged this line to see value of x, and there is no value!
   If Err.Number > 0 Then
      'form has been closed
      Set moFrmAuction = Forms!AuctionForm  'will try to link to any previously opened AuctionForm
      If Err.Number <> 0 Then
          'if no form, then create a new instance of the AuctionForm
          Set moFrmAuction = New Form_AuctionForm
      End If
   End If
   Set anAuctionForm = moFrmAuction
   End Property

This ONLY appears to happen when I switch to Design View and then back to Form View. Is this an actual problem?? Or does switching back and forth like that some "drop" the form from the Forms collection....??
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12350554
Name is a string, a scalar value.  "Nothing" is only applicable to an object.

When you switch from "run" to "design" view, the form object still exist, thus the variable moFrmAuction is not nothing, but the object is no longer valid.  If you tried to access one of its property, such as name, you will get an error.  This is excatly why I suggested to put this test in the code.   If you don't trap the error, then you application will surely crash later.

I am afraid there not much you can do except that when you finished doing the design changes, close the form than reopen it as you have in the first time (from your menu ?).

0
 
LVL 14

Expert Comment

by:ragoran
ID: 12350644
Just an aftertaught,

Access is an interpreter.  It means that you can change code as the application is running.  This has advantages and inconveniants.  On the plus side, it is faster to debug and implement quick fixes.  On the minus site, it is easy for the developer to get into an unstable situation where some objects or variables might have loose their content, as you experienced.

If you were to develop using a compiled language, such as VB.Net, then this situation will not happened as whenever you make a change in your code, you have to stop then restart the application to test it.  It may seems a pain in the neck, but in ensure integrity of the variables space.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12351093
Okay, thanks for the explanation. I've just been stopping/starting the application (or at least closing/opening the ADP file).

Ragoran, thanks so much for all your help, and your patience.  EE shouldn't set a points limit on their questions, as I feel you have exceeded it.  :)

I'm splitting a few of the points to JimHorn for his early contribution as well.

--Galisteo8
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12351105
I am happy I could be of some help.  
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12351135
I was scrolling through the responses to make the split, and I cam across a comment you meade earlier: "I will post whet you most change to ensure integrity within a multi-user environment."

Are there any changes I should make in this regard?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12351232
It ws in the next posting:

wRS_Auction.LockType = adLockPessimistic  'No one else will be using this record


Is to ensure there is no change on the record while we are computing the formulas...

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12351437
Oh, that's right.  Thanks!  :D

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 8

Author Comment

by:Galisteo8
ID: 12437548
Ragoran, a quick question if I may:

If the class module is named clsContext, then why do I reference it in the code as goContext?

Example: if goContext.isLotFormLoaded() then...

How does that work?

Thanks!
--Galisteo8
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12437639
This is Object Oriented lingo...

A class is meant to be instanciated as an object (as a control on a form).  So you refer to the object not the class.  You could have many instances (e.g. objects) of the same class at the same time, each one having it own set of values, as you can have many text boxes on a form.  

Somewhere in your code, you must have a line like:

public goContext as new clsContext

This is where the object (goContext) gets instanciated.

Because of its use, there should be only one instance of the clsContext class.  This is what we call a "singleton".  We could have use a module but a class is a little bit more flexible (it automatically run the Initialize and Terminate methods when created and destroyed).

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12437806
Hmmm...
Would this line (public goContext as new clsContext) be in the class itself, or in the module of the form from which it is being called? Oddly, I don't find it in either place.......
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12437827
I most likely suggested to put it in a module because it is a global variable (can't put them in a form or class).

Do a search for "new clsContext" in your project

0
 
LVL 14

Expert Comment

by:ragoran
ID: 12437835
BTW, "go" as a prefix stands for Global Object.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12437894
Ah!  I did put it in a regular module.

Yesterday I kept getting "object required" errors on one of the goContext statements in my event code, although it stopped throwing errors today.   ???   I still don't know why, but I wanted to make sure I had all the appropriate elements in place.

Thanks!
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12465537
Ragoran, I have also created a GotoNew public sub in my LotForm. It is called when a user needs to create a new Lot to associate with an auction, if the LotForm is already open. It's activated by an event on a command button on the AuctionForm.

If goContext.isLotFormLoaded() Then            '<< On the COmmand Button click event
    Set oF1 = goContext.anLotForm
    oF1.GotoNew
Else:
.........


Public Sub GotoNew()                   '<<<<<<In the LotForm code
   Dim rs As Recordset
   Set rs = Me.RecordsetClone
   rs.AddNew
End Sub


1 -- If the LotForm is closed when the command button is clicked, the Else takes care of that.
2 -- If the LotForm is open, but was opened from the database window by the user, then IsLotFormOpen recognizes that fact, and the it moves to a new record.
3 -- If the LotForm is open, but was opened by goContext.anLotForm.GotoLot, then IsLotFormLoaded does NOT recognize this fact.

I recall that forms opened through the goContext object won't appear in the Forms collection. What, then, should I do to recognize an open LotForm REGARDLESS of which way it was opened?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12465635
When you say "IsLotFormLoaded does not recognize this fact", I assume it will open a new form.

Could you post the code in IsLotFormLoaded  and GotoLot, it has been some time and I don't recall the latest version...



0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12465776
By "does not recognize this fact" I mean that nothing happens. The open lot form doesn't move to any new record, nor is a new LotForm opened.

IsLotFormLoaded:
**********************************
Public Property Get isLotFormLoaded() As Boolean
   Dim wResult As Boolean
   Dim x As String
 
   On Error Resume Next
   
   wResult = True
   If moFrmLot Is Nothing Then
      Set moFrmLot = Forms!LOTForm
      If Err.Number <> 0 Then
          wResult = False
      End If
   Else
      x = moFrmLot.Name
      If Err.Number > 0 Then
         Set moFrmLot = Forms!LOTForm
         If Err.Number <> 0 Then
             wResult = False
         End If
      End If
   End If

   isLotFormLoaded = wResult
   
End Property


GotoLot:
**********************************
Public Sub GotoLot(pNum As String)
   Dim rs As Recordset
   Set rs = Me.RecordsetClone
   rs.MoveFirst
   rs.Find "LotNum = '" & pNum & "'"
   If rs.EOF Then
      MsgBox "Not Found"
   Else
      Me.Bookmark = rs.Bookmark
      Me.Visible = True
      Me.SetFocus
   End If
End Sub
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12465865
Hmmm... I am surprise that it works because in your GotoNew methods, you are simply adding a record to the clone.  The form should not see that action.

Please try this code for the GotoNew and tell me what happens.

public sub GotoNew
   me.visible = true
   me.setfocus
   docmd.GoToRecord , , acNewRec
end sub


0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12466221
That's got it.
Could you once again explain why that works?
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12466281
Also, if the user has entered a LotNum into the AuctionForm *before* clicking the "Create New Record" command button, would it be possible to paste that number into the new LotForm record's Lotnum control?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12466495
You have to tell the form to go into "new" mode, not the underlying recordset.  This is the docmd.gotorecord instruction does.

To get the lotNum, use this code instead:

public sub GotoNew(optional pNum as variant)
   me.visible = true
   me.setfocus
   docmd.GoToRecord , , acNewRec
   if not ismissing(pNum) then
      me.txtLotNum = pNum
   end if
end sub

where me.txtLotNum is the name of the CONTROL bound to the LotNum on the form.

When you call GotoNew, simply add the value you whish.

If goContext.isLotFormLoaded() Then            '<< On the COmmand Button click event
    Set oF1 = goContext.anLotForm
    oF1.GotoNew me.txtLotNum    '<< name of the control on the Auction Form
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12466692
Ragoran, many many thanks. It all seems so obvious when you talk about this stuff. Quite logical. I must have a mental block or something...  :)
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12467938
Oh!  In the case that the LotForm must be opened, the following code does the work:
DoCmd.GoToRecord , , acNewRec

After it has instantiated LotForm, can I still direct it to assign Me!LotNum from the AuctionForm to the Me!LotNum control of newly opened LotForm?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12467975
docmd.gotorecord will act on the form that has the focus.  this is why I put the me.visible and me.sefocus before in the gotoNew method.

It is always best to pass parameters instead of having a form read directly the values from another form.  It will facilitate test and debug and  reuse of your forms/objects.  



0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12468069
I thought you could only pass parameters to functions. Like in the case when the LotForm is already open I call the GotoNew function and pass it a parameter:

oF1.GotoNew Me.LotNum  

But in the case where LotNum has to be opened from scratch, there is no function being called, just a command being given:

DoCmd.GoToRecord , , acNewRec

How can I pass a paramter after that?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12468099
DoCmd.GotoRecord  does not open a new form...

Something else is.  Look at your code (or post it).  After the form is open, whatever the means, use GotoNew to go to e new record.


I have to leave, be back tomorrow.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12468447
Ah, okay. So instead of using  "DoCmd.GoToRecord" I can replace it with "GotoNew Me.LotNum". (If I leave it in there, then I open LotForm to a new record, and then Goto another new record -- kind of repetitive.)

So here's the new version of the OnClick event's IF statement:

********************************
If goContext.isLotFormLoaded() Then
    Set oF1 = goContext.anLotForm
    oF1.GotoNew Me.LotNum
Else:
    stDocName = "LotForm"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Set oF1 = goContext.anLotForm
    oF1.GotoNew Me.LotNum                    '<< Error occurs here: See below
End If
********************************

This all works as long as the user enteres a LotNum into AuctionForm before clicking the command button. However, if the user does NOT enter a LotNum, AND LotForm isn't already open, then TWO LotForms open up -- DoCmd.OpenForm opens LotForm to its first record, and then GotoNew winds up opening a second LotForm to a blank record, but kicks up the following error:
"There was a problem accessing a property or method of the OLE object."

If I say "Okay" to the error, then I can use the blank form without any further issues, but there is still the fact that two forms were instantiated...
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12472697
If you haven't change much anLotForm, it will open a new form it one is not already open.  So you can replace the code youposted to simply:

*****************************
Set oF1 = goContext.anLotForm
oF1.GotoNew Me.LotNum.value
*************************


Note that I add the .value property to the control name.  It should also solve the problem when there are no values in the control.

WHY:  There is a concept of default property in VBA.  A control default property is its value.  At first hand it make sense but it can cause undesired effects.

The GotoNew method accept a Variant as a parameters (which means anything really).  If the control as a value, Access will assume that is aht you want.  If not, it will pass a reference to the control itself, which is no good for GotoNew.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12474623
I presume that you mean:

If goContext.isLotFormLoaded() Then
    Set oF1 = goContext.anLotForm
    oF1.GotoNew Me.LotNum
Else:
    Set oF1 = goContext.anLotForm
    oF1.GotoNew Me.LotNum.Value
End If
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12475326
Using the code I just posted above, here's the results:

LotForm already open from database window:
......If the user enters a LotNum before clicking command button
............- Code goes into Else statment, moves LotForm to new record
............- Value gets assigned
............- No errors
......If the user leaves LotNum blank before clicking command button
............- Code goes into Else statment, moves LotForm to new record
............- Error msg: "Problem accessing a property or method of the OLE object."

LotForm already open from goContext method:
......If the user enters a LotNum before clicking command button
............- Code goes into If statment, moves LotForm to new record
............- Value gets assigned
............- No errors
......If the user leaves LotNum blank before clicking command button
............- Code goes into If statment, moves LotForm to new record
............- Error msg: "Problem accessing a property or method of the OLE object."

If the LotForm is still closed:
......If the user enters a LotNum before clicking command button
............- Code goes into Else statment, opens up LotForm
............- Value gets assigned
............- No errors
......If the user leaves LotNum blank before clicking command button
............- Code goes into Else statment, opens up LotForm
............- Error msg: "Problem accessing a property or method of the OLE object."

So at least only one LotForm is opening, but half the time it's giving me an error. If I click "OK" on the error, then everything appears operable afterward.
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12476201
No, you don't need to test for isLotFormLoaded() because anLotForm does if for you.

Replace the if.. then..else..endif with the two lines of code (with the .Value)

I also recommand this change to GotoNew

public sub GotoNew(pNum as string)
   me.visible = true
   me.setfocus
   docmd.GoToRecord , , acNewRec
   if len(pNum) > 0 then
      me.txtLotNum = pNum
   end if
end sub
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12476580
The Len statement is a good idea, thanks.

Okay, I pared down the code to remove the If-Then stuff, leaving just:
Set oF1 = goContext.anLotForm
oF1.GotoNew Me.LotNum.value

That fixed most of the stray forms and all of the errors. However, if the user clicks the command button when the LotForm is already open by way of the database window, then a NEW (second) LotForm opens up. (There are no issues if the LotForm was opened by way of goContext.) I still can't figure out why...
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12476960
Then the problem is more likely in the anLotForm code.

Post it and I will have a look

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12477071
Property Get anLotForm() As Form_LOTform

   Dim x As String
   
   On Error Resume Next
   If moFrmLot Is Nothing Then
      Set moFrmLot = Forms!LOTForm   'will try to link to any previously opened LotForm
      If Err.Number <> 0 Then
         'if no form, then create a new instance of the LotForm
          Set moFrmLot = New Form_LOTform
      End If
   End If

  'need to test to ensure reference is still valid
   x = moFrmLot.Name
   If Err.Number > 0 Then
      'form has been closed
      Set moFrmLot = Forms!LOTForm  'will try to link to any previously opened LotForm
      If Err.Number <> 0 Then
          'if no form, then create a new instance of the LotForm
          Set moFrmLot = New Form_LOTform
      End If

   End If
   
   Set anLotForm = moFrmLot
   
End Property
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12477172
Hmmm, that looks ok to me.

Can I asked you a question.

Is it possible that when you test your application, at some point, you open the lot form using the button on the auction form, then you close it, then you open the lot form from the database window and then you try to goto new and notice that the code reopen another copy of the form ?

If so then:

When you close the window open from the button, your are simply hiding it (so it will be quicker next time the user click on the button).  So goContext still has a reference to the instance of the form. If you open the form thru the database window, Acces will open  a new one but goContext does not know that. So on the next call to anLotForm, you get the reference to the hidden form.

In my application, the users never use the database window, so I don't have a problem with this technique.  In your case, if you expect the end-user to open form directly from the database window, then we mya have to change the anXForm property slightly.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12477298
I re-tested by shutting down Access completely between tests, and the duplicate LotForm was not instantiated.

Hmmm.... Ultimately, I plan on having users open all of the forms from either: a) a menu screen (called a switchboard?); or b) from the "View X Detail" command buttons associated with some of the subforms (the "goContext" method).  So, I don't really need to worry about this issue?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12477322
If you control how and when the forms are opened, then this should not be an issue as long as you always open by goContext.anXForm method.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12477894
Thanks again for your help -- it has been invaluable!
Sorry about tacking follow-ups onto this PAQ....
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12557433
Hey Ragoran!  :)

Following up on the GotoNew event..

When I click the Create New Lot command button on my AuctionForm when the AuctionForm's LotNum control is empty, I ultimately get an error when GotoNew runs: Operation is not allowed when the object is closed. I click "Okay", and the blank LotForm is ready and waiting for me...  But what's with the error? What does that mean?

Here's the GotoNew code again, with some msg markers:

Public Sub GotoNew(Optional pNum As String)
   Me.Visible = True
   Me.SetFocus
   DoCmd.GoToRecord , , acNewRec
   MsgBox ("Getting ready to test length of pNum")
   If Len(pNum) > 0 Then
      MsgBox ("pNum was > 0")
      Me.LotNum = pNum
      Else:
      MsgBox ("pNum was = 0")  '<< Error occurs AFTER this msg
   End If
End Sub

The error only pops up if the length of pNum = 0.
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12557493
If the error happens AFTER the msgbox "pnum=0", then I will look at the code from which GotoNew is called.  "End if" and "End Sub" are just place holder in the code, nothing ever happens as they are not "true" instruction.

0
 
LVL 14

Expert Comment

by:ragoran
ID: 12557505
You could put a msgbox after the line where GotoNew is called  and see if the error message is after or before that one.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12557716
Good point...  The error is not occuring in the GotoNew event of the LotForm; rather, it is occuring in the OnClick event of the command button on the AuctionForm -- but only when the LotNum value is of 0 length. I'm not sure what's causing it.

Here's the relevant parts of the OnClick event:
**************************************
Private Sub Create_New_Lot_Click()
On Error GoTo Err_Create_New_Lot_Click

Dim oF1 As Form
Dim cn As ADODB.Connection
Dim RS_lot As ADODB.Recordset

Set cn = CurrentProject.Connection
Set RS_lot = New ADODB.Recordset
Set RS_lot.ActiveConnection = cn

If Len(Nz(Me.LotNum, "")) = 0 Then
Set oF1 = goContext.anLotForm
oF1.GotoNew   '<< Here's where the new form opens up
Else:        '<< I took out the Else stuff for brevity's sake
End If

RS_lot.Close
Set RS_lot = Nothing
Set cn = Nothing

Exit_Create_New_Lot_Click:
    Exit Sub

Err_Create_New_Lot_Click:
    MsgBox Err.Description   '<<< Here's the error message
    Resume Exit_Create_New_Lot_Click
   
End Sub
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12557799
Do you actually have colon (:) after the else ? that will turn the instruction into a label (same as exit_create_new_lot_click:  )  . So the code in the Else will be executed (I think).  Either way ensure that there is no colon after Else, it is confusing.

I think that the problem is with rs_lot.close.  This statement can work only on opened recordset.  I am assuming that you open the recordset within the else block, so you should close it within the same block.
You can leave the set rs_lot - nothing where it is. that is ok.


0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12558137
Now that you mention it, I have Else's all over my code, some with colons and some without...

Anyway, I moved rs_lot.close into the same block as rs_lot.open, and of course that did the trick!  Thanks -- I'm too close to this stuff now, and it helps to have a second pair of eyes.
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12558346
I know the feeling.

If you have "else:" all over the place and it does not cause problems, then it may be ok...

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12728605
Ragoran, another followup...

When a user opens a new Auction record via the AuctionForm, they can enter the LotNum of the lot that's going to be sold. If this LotNum is not in the database yet, they will be asked if they want to create a new Lot. If they say Yes, then I open the LotForm with the goContext.anLotForm, setting it to a new record, and assigning the LotNum value they entered on the AuctionForm to the LotNum field of the new LotForm record (so they don't have to enter that number again). Then, after entering the Lot data (i.e. Description, Warehouse Location, etc.) on the LotForm, they can close the LotForm or simply use the task bar to get back to the AuctionForm. WHEN THAT HAPPENS, I wanted the AuctionForm to update itself so that the newly-entered Lot Description would appear on the AuctionForm. To accomplish this, I set the following OnActivate event for the AuctionForm:

Private Sub Form_Activate()
Dim oF2 As Form
Dim auPK As String
   Set oF2 = goContext.anAuctionForm
   auPK = oF2!EbayNum
   Me.Dirty = False
   oF2.Requery
   oF2.GotoAuction auPK
End Sub

It works just fine -- when the AuctionForm is activated again, it basically requeries itself, and then returns to the EbayNum where it started.

HOWEVER... If I try to open the AuctionForm using one of the ViewAuction command buttons on another form (which launches goContext.anAuction), the Context module gets stuck in a loop. It appears that, following either IF loop in the code, the module starts over again... presumably because it is being relaunched due to the OnActivate code event running again... and again... and again...  Then my system freezes up with all these AuctionForm instances opening up.

Oddly, if AuctionForm is already open and I just click over to it (which runs the OnActivate event each time), the goContext.anAuction loop does NOT occur.

For reference, I've put the goContext.anAuction code below, as well as the Click Event for one of the command buttons. Can you see any reason why the loop occurs when using the ViewAuction command buttons, but NOT when simply running the OnActivate event?

--Galisteo8

**************************************
ViewAuction Command Button...................

Private Sub ViewAuction_Click()
   Dim oF As Form_AuctionForm
'The IF statement prevents opening of AuctionForm when no auction is selected in the subform
   If IsNumeric(Me.[OrderLots Subform].Form!EbayNum.Value) Then
       Set oF = goContext.anAuctionForm
       oF.GotoAuction Me.[OrderLots Subform].Form!EbayNum
   End If
End Sub

**************************************
Get anAuctionForm property...................

Property Get anAuctionForm() As Form_AuctionForm
   Dim x As String
   On Error Resume Next
   If moFrmAuction Is Nothing Then
      Set moFrmAuction = Forms!AuctionForm   'will try to link to any previously opened AuctionForm
      If Err.Number <> 0 Then
         'if no form, then create a new instance of the AuctionForm
          Set moFrmAuction = New Form_AuctionForm
      End If
   End If

  'need to test to ensure reference is still valid
   x = moFrmAuction.Name
   If Err.Number > 0 Then
      'form has been closed
      Set moFrmAuction = Forms!AuctionForm  'will try to link to any previously opened AuctionForm
      If Err.Number <> 0 Then
          'if no form, then create a new instance of the AuctionForm
          Set moFrmAuction = New Form_AuctionForm
      End If
   End If

   Set anAuctionForm = moFrmAuction
End Property
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12729258
Correction: The loop only occurs when the ViewAuction button is used AND there is no instance of AuctionForm already open.  SO... when the Get property opens the new form, the OnActivate event runs, which launches the Get property again... but then the loop should STOP after that, because on the second time through the Get property, there's already an AuctionForm open....?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12729831
Hi, it has been a while, so I hope my understanding of your application is not too rusty.

If I understand correctly, the form_activate event is in the AuctionForm.  Then you do not need to access to context object to get a reference of your self.  Try this instead:

Private Sub Form_Activate()
Dim auPK As String
   auPK = me!EbayNum
   Me.Dirty = False
   me.Requery
   me.GotoAuction auPK
End Sub


If I am correct, it should fix your immediate problem and is "cleaner" and "simpler".  The previous method should have work (in theory) because anAuctionForm should return a reference of the open form.... weird. May be cause by a problem with events recursively calling each other.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12731070
<slaps forehead>

Thanks!  I'm not sure why I had it calling up the goContext method originally. Perhaps I was trying to keep a second instance of AUctionForm from opening up, Remember that I had an "issue" previously where the goContext method would open up a second instance of AuctionForm -- but that was because I had opened the first instance from the database window rather than from the main switchboard. Since users will be using the switchboard, the "second instance" problem didn't really exist. ANyway -- probelm solved. Thanks again for your feedback!

By the way, could you explain to me again where in memory forms are held when they are opened from different places (database window vs. command button) -- that is, how come sometimes the goContext method can "see" them and sometimes it can't?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12731128
When forms are open:

1- manually from the database window
2- using "docmd.open ..." command

their reference is added to the Forms collection, which is available every where.

When forms are open using a variable

dim oFrm as form_AuctionForm
set ofrm = new form_AuctionForm
ofrm.visible = true
...

Their reference IS NOT added to the Forms collection, so they can be access only true the variables.  This is why anAuctionForm method keeps the reference in a module variable (moAuctionForm).

Hope it helps

I am leaving now, back to work tomorrow.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12738550
What about if a form is opened with: OpenForms("AuctionForm")  ?

Here's why I ask... My switchboard has, for example, a command button for opening the AuctionForm. It's OnClick property is the expression  =OpenForms("AuctionForm") .  Once the AuctionForm is open, I click the ViewLot button on it, which opens the LotForm. Then, on the LotForm, I click the ViewAuction button and a NEW instance of the AuctionForm opens up...
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12738842
OpenForms() ?? never seen this before.  Are you sure it is not a VBA function you or someone else wrote ?

In the code editor, hightlight it and, select "definition" in the right-click menu.

In the switchboard, you could have the click event call a sub you write that uses goContext.anAuctionForm instead...
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12740095
Apparently, I patterned this switchboard off of the Northwind example or the client's existing switchboard. In Design View, the OnClick property of the command button opens up an Expression Builder window when I click the "..." button to edit its code...

So, it doesn't show up in Code Editor. It shows up in Expression Builder.
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12740456
I don't have northwind installed on my PC.

Open the switchboard form in design mode and look into the code behind the page.  Is there a sub or function named OpenForms ?

From the VB editor, do a find for OpenForms in the whole project ...

I am leaving for the weekend, be back next monday.


Bye

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12740784
Ragoran,

Yes, setting the button to just use goContext.anAuctionForm.GotoAuction would work fine. But the command expects an argument, i.e. which Auction record to display.  I'm having a momentary lapse here: how do I reference the first record generically, so that AuctionForm just opens up to the first record, whatever that is?

Private Sub OpenAuctionForm_Click()
goContext.anAuctionForm.GotoAuction ________  <<< ???
End Sub
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12753949
You may want to try this:

Private Sub OpenAuctionForm_Click()
goContext.anAuctionForm.visible = true
End Sub
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12754047
As an alternative, if this is still the gotoAuction sub:

Public Sub GotoAuction(pEbay As Long)
   Dim rs As Recordset
   Set rs = Me.RecordsetClone
   rs.MoveFirst
   rs.Find "eBayNum = " & pEbay
   If rs.EOF Then
      MsgBox "Not Found"
   Else
      Me.Bookmark = rs.Bookmark
      Me.Visible = True
   End If
End Sub

You could modify it to :

Public Sub GotoAuction(pEbay As Long)
   Dim rs As Recordset
   Set rs = Me.RecordsetClone
   rs.MoveFirst
   if pEbay > 0 then
      rs.Find "eBayNum = " & pEbay
      If rs.EOF Then
         MsgBox "Not Found"
      Else
         Me.Bookmark = rs.Bookmark
         Me.Visible = True
      End If
   else
       me.visible = true
   end if
End Sub


This way, if you want to goto the first record, you simply pass a value = 0 or less (-1).



0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12756709
Hey, I did find an OpenForms function in the code for the switchboard. It is as follows:
******************************
Function OpenForms(strFormName As String) As Integer
' This function is used in the Click event of command buttons that
' open forms on the Main Switchboard. Using a function is more efficient
' than repeating the same code in multiple event procedures.
On Error GoTo Err_OpenForms
    ' Open specified form.
    DoCmd.OpenForm strFormName
Exit_OpenForms:
    Exit Function
Err_OpenForms:
    MsgBox Err.Description
    Resume Exit_OpenForms
End Function
******************************

I suppose I could just replace the DoCmd statement with goContext.anAuctionForm.visible = true ...
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12756764
Actually, just using "goContext.anAuctionForm.visible = true" by itself as the OnClick event works just fine. The form opens to the first Auction record. Plus, this approach DOES use an existing function rather than "repeating the same code in multiple event procedures" as the OpenForm comments suggest.  :)
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12756819
Hey,

You taught me something I did not know:  "just using "goContext.anAuctionForm.visible = true" by itself as the OnClick event "

Thanks
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12756873
??? That's what you suggested a couple of posts ago:

Private Sub OpenAuctionForm_Click()
goContext.anAuctionForm.visible = true
End Sub
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12851836
Ragoran, a new issue popped up for me last week. Not sure why I never noticed it before, but perhaps because of the timing of certain event procedures it became more pronounced. If you get a chance, could you take a look at http://www.experts-exchange.com/Databases/MS_Access/Q_21240674.html
?

Basically, I'm getting an #Error result in a sum([Amount]) control in the footer of one of my subforms... Ever seen that before? To me it's like MS Access is telling me 2+2+2=#Error...
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12896019
Ragoran, Hello again (if you are still getting emails from this PAQ!).

I have been very appreciative of your help and your patience over the last several months, and your suggestions have always been right-on. I'm going to have to do another "Points for Ragoran" question for all your assistance above-and-beyond my original question.  I will get that done soon.

In the meantime (you knew I had to have another question!), would you have some insight into the issue I've posted at http://www.experts-exchange.com/Databases/MS_Access/Q_21252548.html ?
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12921320
Ragoran, what do you know about the following error:
"Object variable or With block variable not set"?

I am getting this error almost every time I run the GotoNew function in my LotForm. I also sometimes get it when opening the AuctionForm or the LotForm from my switchboard (these employ the gocontext module you helped me set up). Strangely, the OrderForm opens from the switchboard the same way, but does not seem to ever encounter this error.

This particular error does not appear to be consistent; sometimes it errors, sometimes it doesn't. BUT, it does seem to be happening more frequently.
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12943679
This is error happens when you try to access an bobject property or method and the variable is not, or has lost, its instance.  

Dim oFrm as form
debug.print oFrm.name

The code above will produce this error because oFrm has not ben instanciated.

Dim oFrm as form
set ofrm = new form...
debug.print ofrm.name

This code will work because the variable has been instanciated.

A variable may also loose its instanciation if it is set to nothing, if it goes out of scope (a variable declare in a procedure goes out of scope at the end of it) or if you go into debug mode and either click on "end" or on reset button.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12963761
Good morning, Ragoran!

I had to change up some of the cn transaction code that we developed earlier, and I'm wondering if you might take a look at it. Ebverything seems to work fine except the following statement: wRS_Lot!GrossProfit = 0.  This statement sits alongside others, such as wRS_Lot!Payback = 0 and wRS_Lot!RinnerProfit = 0. When that block of code runs and the transaction is committed, the only one of these that is NOT 0 is GrossProfit. Do you see any reason in the code below why that should be so? I even stuck a flag after it (msgbox("GrossProfit is now " & wRS_Lot!Payback), which yielded "GrossProfit is now 0".... and yet when I looked at the LotForm, it was NOT 0 -- although Payback and RinnerProfit were 0.  ANy ideas?

--Galisteo8

**********************************
cn.BeginTrans

If ((Me![WinBid] > 0) And (Me![TotalPaid] >= Me![WinBid])) Then

      wAuGross = Nz(Me![TotalPaid], 0) - Nz(Me![EbayListFee], 0)
      wAuGross = wAuGross - Nz(Me![EbayComm], 0)
      wAuGross = wAuGross - Nz(Me![CCFee], 0)

      wRS_Auction!Gross = wAuGross
      wRS_Auction!PaidStatus = True
      wRS_Auction.Update
     
Else

      wRS_Auction!Gross = 0
      wRS_Auction!PaidStatus = False
      wRS_Auction.Update
     
      wRS_Lot!GrossProfit = 0
      wRS_Lot!Payback = 0
      wRS_Lot!RinnerProfit = 0
      wRS_Lot!LotPaid = False
      wRS_Lot.Update
     
End If

If (Nz(wRS_Lot.Fields("ClientPrice"), 0) > 0) And (wRS_Auction!Gross > 0) Then

      wLtGross = (wRS_Auction!Gross - wRS_Lot.Fields("ClientPrice"))
      If wLtGross > 0 Then
            wClProfit = Round((wLtGross * wRS_Client.Fields("ProfitSplit")), 2)
            Else: wClProfit = wLtGross
      End If
      wPayback = (wRS_Lot.Fields("ClientPrice") + wClProfit)
      wRprofit = wLtGross - wClProfit
     
      wRS_Lot!GrossProfit = wLtGross
      wRS_Lot!Payback = wPayback
      wRS_Lot!RinnerProfit = wRprofit
      wRS_Lot!LotPaid = True
      wRS_Lot.Update
     
Else

      wRS_Lot!GrossProfit = 0
      wRS_Lot!Payback = 0
      wRS_Lot!RinnerProfit = 0
      wRS_Lot!LotPaid = False
      wRS_Lot.Update

End If

wRS_Auction.Close
Set wRS_Auction = Nothing

wRS_Lot.Close
Set wRS_Lot = Nothing

wRS_Client.Close
Set wRS_Client = Nothing

cn.CommitTrans

Set cn = Nothing
*************************************************
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12963874
At first look, this code seems fine.

You said that after the transaction, you look in the form and see a value.  Can you look directly in the DB (may want to put a break point after CommitTrans.) to see what is in the table ?

Most likely, you code somewhere else that recompute the value as or before it is displayed.  You don't have any triggers in the database ?

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12963933
No triggers.

What is the code for a break point? Or do you just mean a msgbox?
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12964063
to put a breakpoint, you simply have to click in the left margin by the line of code you want to stop on.  A red circle will be added in the margin.  Then execution will stop next time, before running this line.  You can then query the content of the variables.

0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12964477
OK. Hmmm... I can't seem to duplicate the problem this morning. I will continue to monitor it.

The users are going to start using this ADP in production starting today (hopefully), and this is one of the last minor issues I've got going.
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12974817
Ragoran, good morning! Another issue has popped up with the code we developed earlier...

On my AuctionForm the user can enter a LotNum (the Lot to be sold at the Auction). If the Lotnum doesn't yet exist in the database, then the LotForm is opened up so that they can create the Lot record. The LotForm is opened by way of the context module:
<snip>***************
    RS_lot.Open "select * from LOT WHERE LotNum = '" & Me![LotNum] & "'"

    If RS_lot.RecordCount = 0 Then
        If MsgBox(strMsg, vbQuestion + vbYesNo, "Create Lot") = vbYes Then
            Set oF1 = goContext.anLotForm
            oF1.GotoNew Me.LotNum.Value
        Else
             Cancel = True
             Me.LotNum.Undo
        End If
<snip>***************

For various reasons, I needed to create a second, smaller form for Lot entry to be used in the situation described above, although I have left the "main" LotForm for direct Lot record creation and maintenance. To that end, I have created a LOTentry form, which is supposed to pop open specifically when the user enters an un-recognized LotNum on the AuctionForm. I am trying to make it work just like it did with the normal LotForm -- that is, I have created a context property anLOTentryForm, and a GotoNew sub in the LOTentry form itself.

The problem is that when the code SHOULD be opening the new LOTentry form, instead the AuctionFOrm's On Activate event runs!!!  And I can't figure out why.

Here's the current code, noting where the problem occurs:

<snip>***************
    If RS_lot.RecordCount = 0 Then
        If MsgBox(strMsg, vbQuestion + vbYesNo, "Create Lot") = vbYes Then
'<<<<<<<<<AuctionForm OnActivate event runs right here for some reason...>>>>>>>
            Set oF1 = goContext.anLOTentryForm
            oF1.GotoNew Me.LotNum.Value
        Else
             Cancel = True
             Me.LotNum.Undo
        End If
    Else
<snip>***************

When the OnActivate runs, it sets Me.Dirty to False. But since it's running here while the user is in the middle of trying to enter a LotNum value on the AuctionForm, Dirty *can't* be set to False since the LotNum can't be null. It throws an error, and then the form is closed with nothing saved.

Can you see why the OnActivate event runs? Is it because the msgbox is getting closed, thereby "activating" the AuctionForm again? I can't see that that would be the problem, since this same code approach worked without a hitch when it was opening the LotForm instead of my new LOTentry form...
0
 
LVL 14

Expert Comment

by:ragoran
ID: 12974954
That is a weird one...


Have you tried to compact and repair the application file.  This *sometimes* fixes such odd behaviors and because it is easy and fast, it is worth a try.


Are you sure that the OnActivate run right after the MsgBox line (thus suspending the processing in the sub).  Maybe you could put a series of msgbox in the OnActivate event as well as in the IF block (before set oF1 and issuig GotoNew and after) to see exactly when is the form activitated
0
 
LVL 8

Author Comment

by:Galisteo8
ID: 12975173
Yes, I put some msgboxes in there:

    RS_lot.Open "select * from LOT WHERE LotNum = '" & Me![LotNum] & "'"
MsgBox ("5")
    If RS_lot.RecordCount = 0 Then
MsgBox ("6")
        If MsgBox(strMsg, vbQuestion + vbYesNo, "Create Lot") = vbYes Then
MsgBox ("7")
            Set oF1 = goContext.anLOTentryForm
MsgBox ("8")
            oF1.GotoNew Me.LotNum.Value
        Else
             Cancel = True
             Me.LotNum.Undo
        End If
    Else

Right after I respond "Yes" to the "Create new lot" question, I get the "7", and then the OnActivate event runs (I have a msgbox in the OnActivate event that indicates "OnActivate is running").
0
 
LVL 14