Trigger Access AND SQL events at the same time?

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
LVL 8
Galisteo8Asked:
Who is Participating?
 
ragoranConnect With a Mentor Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
(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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
ngolovinCommented:
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
 
ngolovinCommented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
Okay, it's easy enough to spot wherre the actual formula goes... But could you explain the other code?
0
 
ragoranCommented:
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
 
Galisteo8Author Commented:
>>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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
If I were to "convert" my VB code into a SQL query.... Hmm... Can I still do an If-Then-Else statement?
0
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
ragoranCommented:
As for the multi-user issue... post your code as you think it should be and we will work from that point on.
0
 
Galisteo8Author Commented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Holy cow this thread is getting long.  I hope these experts are getting at least a case of beer out of this...
0
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
You got it.


0
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
>>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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
Are you sure that in the OnCurrent event or other you don't programmatically assign a value to a control of the form ?

0
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
So... Me.Dirty=False will prevent the change if the value isn't different?
0
 
ragoranCommented:
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
 
Galisteo8Author Commented:
Wait, scratch that question.  :) I'll give this a go. It may provide a workaround until I can re-code the AfterUpdate event.
0
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
ADO recordsets do not have a Findfirst method. but a Find method.

0
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
<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
 
ragoranCommented:
I don't believe you can requery or refresh the recordsetclone (actually never tried it).  I would do it on the form
0
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
<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
 
ragoranCommented:
What is LotNum:

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

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

0
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
Does it make a difference that ltPK is of type String?
0
 
ragoranCommented:
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
 
Galisteo8Author Commented:
<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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
small o is for an object type variable
Capital F for Form


0
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
I am happy I could be of some help.  
0
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
Oh, that's right.  Thanks!  :D

0
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
ragoranCommented:
BTW, "go" as a prefix stands for Global Object.
0
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
That's got it.
Could you once again explain why that works?
0
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
Then the problem is more likely in the anLotForm code.

Post it and I will have a look

0
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
Thanks again for your help -- it has been invaluable!
Sorry about tacking follow-ups onto this PAQ....
0
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
I know the feeling.

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

0
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
<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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
You may want to try this:

Private Sub OpenAuctionForm_Click()
goContext.anAuctionForm.visible = true
End Sub
0
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
Hey,

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

Thanks
0
 
Galisteo8Author Commented:
??? That's what you suggested a couple of posts ago:

Private Sub OpenAuctionForm_Click()
goContext.anAuctionForm.visible = true
End Sub
0
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
No triggers.

What is the code for a break point? Or do you just mean a msgbox?
0
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
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
 
Galisteo8Author Commented:
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
 
ragoranCommented:
So the AuctionForm is activated between 7 and 8.

Have a look at the onLotEntryform code .


0
 
Galisteo8Author Commented:
Compact & Repair didn't fix it.

It's weird... If I just change "Set oF1 = goContext.anLOTentryForm" back to "Set oF1 = goContext.anLOTForm", it works just fine.
0
 
Galisteo8Author Commented:
I see nothing at all in the anLOTentryForm -- it's structurally identical to anLotForm:

************************************
Property Get anLOTentryForm() As Form_frmLOTentry
   Dim X As String
   On Error Resume Next
   
   If moFrmLOTentry Is Nothing Then
      Set moFrmLOTentry = Forms!frmLOTentry   'will try to link to any previously opened frmLOTentry
      If Err.Number <> 0 Then
         'if no form, then create a new instance of frmLOTentry
          Set moFrmLOTentry = New Form_frmLOTentry
      End If
   End If

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

   Set anLOTentryForm = moFrmLOTentry
   
End Property
0
 
ragoranCommented:
Ok,

Do you have code behind the frmLOTEntry form that reference the auction form ?
0
 
Galisteo8Author Commented:
Nope. None of its event codes reference the AuctionForm in any way. Even the data source is just the underlying LOT table in SQL. Is there any other place to look?
0
 
ragoranCommented:
maybe we are looking at the wrong place.

Maybe it is something it he lot form that keeps the focus that is NOT in the LotEntry form.

Is it modal ?

Does the Lot form as a setfocus call in the initialize or load event ?

0
 
Galisteo8Author Commented:
The LOTentry form is set to Modal... Does that have an effect?
0
 
Galisteo8Author Commented:
And the LotForm doesn't have any load/current/open/activate events that set focus.
0
 
ragoranCommented:
Try to set the LotEntry form to NOT modal to see what happens.
0
 
Galisteo8Author Commented:
That didn't make a difference...

So then I stuck msgboxes throughout the context anLOTentryForm() moduel, as follows:

****************
Property Get anLOTentryForm() As Form_frmLOTentry
MsgBox ("anLOTentryForm() running")
   Dim X As String
   On Error Resume Next

   MsgBox ("an 1")

   If moFrmLOTentry Is Nothing Then
      MsgBox ("an 2")
      Set moFrmLOTentry = Forms!frmLOTentry
         MsgBox ("an 3")

      If Err.Number <> 0 Then
          Set moFrmLOTentry = New Form_frmLOTentry
             MsgBox ("an 4")
      End If

   End If

   MsgBox ("an 5")

   X = moFrmLOTentry.Name
      MsgBox ("an 6")

   If Err.Number > 0 Then
      MsgBox ("an 7")
      Set moFrmLOTentry = Forms!frmLOTentry
         MsgBox ("an 8")

      If Err.Number <> 0 Then
             MsgBox ("an 9")
          Set moFrmLOTentry = New Form_frmLOTentry
             MsgBox ("an 10")
      End If

         MsgBox ("an 11")
   End If

   MsgBox ("an 12")

   Set anLOTentryForm = moFrmLOTentry
      MsgBox ("an 13. Ending anLOTentryform()...")
End Property
**********************************

I got all the msgboxes up through "9" ; after "9", the AuctionForm OnActivate event kicked in... before or during "Set moFrmLOTentry = New Form_frmLOTentry".
0
 
Galisteo8Author Commented:
Interesting... The statement just after msgbox 9 is the exact same statement as just before msgbox 4.... And I got the message "4" during execution, which means that that statement ran at that time. Why would it run the first time, but error out the second time?
0
 
Galisteo8Author Commented:
When I step through the anLotForm() property in a similar manner, there is no error. I did notice another difference, however.

When the anLotFOrm() runs "Set moFrmLOT = New Form_frmLOTform" before msgbox #4, I actually see the LotForm open up, then I get the msgbox #4; when that command runs again just after msgbox #9, the LotForm flickers a bit as it "opens" a second time.

However, when the anLOTentryForm() runs "Set moFrmLOTentry = New Form_frmLOTentry" before msgbox #4, the LOTentryform does NOT open up. Then, when that command runs again just after msgbox #9, the AuctionForm's OnActivate event runs.

-------------

Note: In the Context module declarataions, I have
Private moFrmLOTentry As Form_frmLOTentry.

Also, the Private Sub Class_Terminate inlcudes
   Set moFrmLOTentry = Nothing

I am still stumped. Do you see anything here?
0
 
ragoranCommented:
Do you have code in the Lotform load event that is not there in the LotEntry form.

Instancianting a form thru the new keyword should not make the form visible ounless there is some code that explicitely set me.visible = true.

Also, you may want to add this line in all anXXXform method where you have msgbox("an 4") because if you go there, you should not go to 9

err.clear

This line will reset the err.number to 0

0
 
Galisteo8Author Commented:
The LotForm doesn't have any load events that set Visible property.


0
 
ragoranCommented:
I don't have a specific thing in mind, but my bets are that there is a difference between the two lot forms that causes this "side effect".  The difference may be in the form properties or in the form's controls properties.  It may also be in the code or even in the underlying recordset.

Not having all this here, I found it difficult to be more accurate.  You will need to compare differences and try what is going on.  You can ask me questions on specific properties if you need.

0
 
Galisteo8Author Commented:
Well... Adding the Err.Clear statement prevented the AuctionForm OnActivate from running during the anLOTentryForm() execution!  SO now, although the LOTentryForm doesn't actually get displayed until its GotoNew sub runs, it at least appears to be open up without any problems. I also re-set the LOTentryForm to Modal. Isn't that supposed to make it pop open as a separate window?

--------

Oh, one more thing:

Also, if I want to check and make sure that a textbox control on a form has something typed in it prior to the form closing, what format do I use for the control name?

Me.Description -- doesn't work
Me!Description -- doesn't work
Me!Description.Value -- doesn't work

I'm trying to use it like this:
If <control-name> Is Null Or <control-name> = "" Then...

--------
Thanks for all your help ... again!
0
 
ragoranCommented:
If I remember correctly, Modal is not applied when using form thru variables as we do.

As for the format, I typically do somthing like:

if len(nz(me.txtName.value,"")) = 0 then


nz() will convert null value to empty string

Len() returns the lenght of the string (verry fast in VB because of how strings are store in memory)
0
 
Galisteo8Author Commented:
>>len(nz(me.txtName.value,""))

Oh, that's right. I finally realized that I couldn't use "is null" in my statement.  I did find that "If Me!Description.Value = "" Then " works by itself, but it probably won't account for Nulls.
0
 
ragoranCommented:
You are correct.

If you want to explicitely test for a null variable (not an object) then you must use the function isnull() as in:

isnull(me.txtname.value)

the "xxx is null" synthax is for sql

the "xxx is nothing" synthax is for an object that contains nothing


ah... the joy of programming.
0
 
Galisteo8Author Commented:
LOL

btw, I'm trying to open up the LOTentry form as a modal form, or basically as a non-maximized window that is a certain size and low on the screen, so that the user can still the top half of the AuctionForm behind it.  I've set it to Modal, and it also has the following Load event:

Private Sub Form_Load()
    DoCmd.MoveSize 1440, 3500, 9100, 2000
End Sub

And yet it still opens maximized... Did I miss something?
0
 
ragoranCommented:
Modal has nothing to do with the size and placement of the form.  It states the while the form is open, the user can only use this form (can't click on anything else).

Access is an MDI application.  It means that ALL windows within access will behave the same maximised wise.  If one form is maximised, then they will all be.  You can use to form control button to "restore" a form, but the all the form will be restored as well.



0
 
ragoranCommented:
I am leaving for tonight

0
 
Galisteo8Author Commented:
Okay.

As for the LOTentryForm, I've kept it on Modal, but also set Pop-Up property to Yes. Having done this, now it can be properly moved and sized with DoCmd.MoveSize.

However, when I close it, the AuctionForm OnActivate event does NOT fire, so the updated Lot info that is entered and saved via the LOTentryForm does not dynamically dispaly in AuctionForm's subform. What other event can I use? OnFocus doesn't work either....
0
 
ragoranCommented:
I very rarely use OnActiviate or OnFocus event to code processing /transaction logic.  In fact, I can't remember last I may have done so.

I am afraid that the alternative will involve  *many* changes in your application and I am reluctant to propose it.

A possible short cut will be to have the LotEntry form call a public procedure in the AuctionForm to have it trefresh itself.

In AuctionForm, create a new procedure:

public sub RefreshThySelf
  'insert logic to refresh, could be copied from OnActivatie
  ...
end sub

In the LotEntry form, change the GotoNew method to add a Form Parameter.  Add a private variable to memorize this value

private moCallFrom as form


public sub gotoNew(pLotNum as ..., pCallFrom as form)
  ...
   set moCallFrom = pCallFrom
  ...
end sub

Now in the code that "close and save" the lot entry, add this snipet:

   if not moCallFrom is nothing then
      mocallfrom.RefreshThySelf
      set moCallFrom = nothing
   endif

also, in the unload event of the form, be sure to add this line:
 
   set moCallFrom = nothing


Finally, in the Auction form, you will need to add the reference to me on the gotonew call:

oF1.GotoNew Me.LotNum.Value, me





If the LotEntry form is called from other forms, to the same changes in the other forms as in the AuctionForm.
0
 
Galisteo8Author Commented:
I'll give all that a try. I was trying to have the LotEntry close button call a public refresh sub in the AuctionForm earlier this morning, but it keeps generating an error ("No Object variable or With block..."). I'll try setting it up your way.

On a related note, would you know how I could call the GotoOrder sub from another form while passing it a string of several values, so that the OrderForm would move to the first record in the list, and then the user would click a "Next" button (on the search form, which would need to stay in front) to have the code loop to the next OrderForm in the string... Does this even sound like it could be done?

I've got all the code set up with the queries, and the list of OrderID's is in a string... I just need to know how I might implement GotoOrder by passing it this string, and having each record pop-up and wait for the user to hit "next" or "cancel". I can start a new question for this if you like. But you are most familiar with how the GotoXXXX are set up.
0
 
ragoranCommented:
About your Goto question.

I will parse the string of IDs into an array (using the split() function, look into the help file).  Then I will keep a module variable as the current index in the array.  The next/previous button will change the index (+1 or -1, testing for limits) and then do a "classic" gotoOrder on the value from the array.

0
 
Galisteo8Author Commented:
What makes the code "wait" for the user to click the next/previous buttons? That would be a separate OnClcik event, wouldn't it?
0
 
ragoranCommented:
Yes, it will be on the next/previous buttons onClick evetn that you will increment or decrement the index than issue a gotoOrder to the lotnum id at that position in the array.

0
 
Galisteo8Author Commented:
If I declare an array: Dim ordArray()

...and then resize it to fit the number of records in my query-based recordset:

If rs.RecordCount > 0 Then
   ReDim OrdArray(rs.RecordCount)
End If

...and then use the split function to populate the array:  OrdArray = Split("stOrderValue", ",")

...how do I then reference a particular value in the array? I have set a modular vaiable iOrder as LONG, and set it to zero within the sub, thinking that "ordArray(iOrder)" would work, but it doesn't.
0
 
ragoranCommented:
I don't think you need to redim the array as the Split function will give you one.

Synthax to get an arry item is (note the square brackets)

ordArray[iOrder]

I am about to leave, I will be back tuesday
0
 
Galisteo8Author Commented:
Have a good weekend.
0
 
Galisteo8Author Commented:
Ragoran,
Turns out I was trying to convert the string values into LONG when they still had single quotes around them. I had to include another sub to remove the quotes, then do the conversion:

If Len(stOrderValues) = 0 Then
     MsgBox ("No Orders were found matching your search criteria.")
   Else
     stOrderValues = Mid(stOrderValues, 2) 'gets rid of the first comma
     stOrderValues = fn_remove_apostrophes(stOrderValues) 'gets rid of apostrophes
     OrdArray = Split(stOrderValues, ",") 'creates an array (declare as Public)
     OrdFirst = CLng(OrdArray(iOrder))
     Set oF = goContext.anOrderForm
     oF.GotoOrder OrdFirst
End If

This works! Oddly, changing (iOrder) to [iOrder] did not work.

0
 
ragoranCommented:
Good,

About the [], don't worry, I might have taught of another languages...

0
 
Galisteo8Author Commented:
Ragoran, I am pulling my hair out over new issue, related to the requery of my LotForm. I will paste the code below in case it is helpful.  I have created a form (NoPrice) that is based on a query: SELECT Client, LotNum, Description, ClientPrice FROM dbo.LOT.  I have set the form's Unique Table property to LOT. The user opens up frmNoPrice from a command button which filters for a given Client, and where ClientPrice is 0 or NULL, in order to see a list of LOTs that need a ClientPrice. Then the user can enter ClientPrice values into each ClientPrice control. At this time an AfterUpdate event runs, which updates profit/loss figures in the underlying database, and ends with a requery of LotForm (if LotForm is open). It appears that all of this works correctly... Except that the ClientPrice control on LotForm remains empty in appearance. Observations:

1) If the LotForm was open to the record whose ClientPrice was being edited on frmNoPrice, then ClientPrice remains empty following the event and requery.  In order to make it visible, the entire instance of LotForm must be closed and re-opened.
2) If the LotForm was open to a different Lot record during the event/requery, and then the user clicks over to the Lot record of the affected Lot, everything looks fine.
3) If the LotForm was closed during the event/requery, and then the user opens it to the record of the affected Lot, everything looks fine.

What I can't figure out is why the ClientPrice control on LotForm remains blank even after the requery. The other controls that display the calculated profit/loss values are properly updated to LotForm during the requery, and the ClientPrice value entered by the user *is* properly updated in the underlying SQL table.... but somehow just doesn't make it to the screen ...

I am clueless. I have tried renewing my ADP by importing forms/reports/modules into a new ADP, but that didn't help. Can you think of anything?

Here's the AfterUpdate code of the ClientPrice control on frmNoPrice. It's almost identical to the subs I've used elsewhere in this ADP.

******************************************************
Private Sub ClientPrice_AfterUpdate()

Dim cn As ADODB.Connection
Dim wRS_Auction As ADODB.Recordset
Dim wRS_Lot As ADODB.Recordset
Dim wRS_Client As ADODB.Recordset
Dim wPayback As Double
Dim wAuGross As Double
Dim wLtGross As Double
Dim wClProfit As Double
Dim wRprofit As Double
Dim wPercent As Double

wPayback = 0
wAuGross = 0
wLtGross = 0
wClProfit = 0
wRprofit = 0
wPercent = 0.02

Set cn = CurrentProject.Connection   ' Connects to SQL database to update LOT table

cn.CursorLocation = adUseServer   'to ensure lock on recordset

'Open and lock the records
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 LotNum = '" & Me![LotNum] & "'"

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![LotNum] & "'"

Set wRS_Client = New ADODB.Recordset
Set wRS_Client.ActiveConnection = cn
wRS_Client.LockType = adLockPessimistic  'No one else will be using this record
wRS_Client.CursorType = adOpenDynamic
wRS_Client.Open "SELECT ProfitSplit, AuctPercent FROM Client INNER JOIN LOT ON CLIENT.FirstName = LOT.Client WHERE LotNum = '" & Me![LotNum] & "'"

cn.BeginTrans

If (Nz(Me.ClientPrice, 0) > 0) And (Nz(wRS_Auction!Gross, 0) > 0) Then

      wLtGross = (wRS_Auction!Gross - Me.ClientPrice)
     
      If wRS_Client.Fields("AuctPercent") = True Then 'Tests for specific profit calc scenario
         wClProfit = Round(((wLtGross - (wPercent * wRS_Auction!Gross)) * wRS_Client.Fields("ProfitSplit")), 2)
         'Subtracts wPercent of AuctionGross from LotGross before calculating profit figures
      Else 'Normal profit calc
         wClProfit = Round((wLtGross * wRS_Client.Fields("ProfitSplit")), 2)
      End If
     
      wPayback = (Me.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

' 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

End Sub
0
 
Galisteo8Author Commented:
Actually, I've discovered the gist of the problem. The LotForm is "lagging behind" as I enter ClientPrice values into frmNoPrice. Basically, if I enter $100 into frmNoPrice!ClientPrice, then after the OnClick code runs (and requeries the LotForm), the LotForm will still show the OLD value of ClientPrice in its own LotForm!ClientPrice control. If I then enter $150 onto frmNoPrice, THEN the LotForm updates to $100. Then I enter $300, and LotForm updates to $150. Etc... At this point, the value actually stored in the SQL table is $300, but the LotFOrm is still displaying $150. I have to close/re-open my LotForm instance to refresh the Recordset.
0
 
ragoranCommented:
HI,

Sorry for not replying yesterday, but I have a very very busy week at the office.  I may not have time today to read your problem description in depth...

Is your requery done in the field after_udate or te Form AFter update event ?
 
IT should be in the form as this is when the database has been updated.  The Field after update event is when the lcoal value of the field as been "accepted" but this value is not save to the database before the form is udpated,

Bye
0
 
Galisteo8Author Commented:
Yep, the Requery needed to be in the form.

I hope your workload eases up some! I just started a new job last week, so now I've got a day-job AND this contract database project.
0
 
Galisteo8Author Commented:
And my part-time database work continues....

Ragoran, would you please take a look at
http://www.experts-exchange.com/Databases/MS_Access/Q_21291930.html

You have always been able to understand my stranger questions, and this one has me quite confused.  Many thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.