• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 672
  • Last Modified:

Access 2003 not running code at report loading

I have an access database originally written in 2007 but saved for 2003 and running on 2003. It has a form to collect and store purchase order data, then a report to print the actual purchase order.  The "header" of the purchase order containing the customer, address, buyer, PO number et al, works fine.  That is pulled from an orders table.  The line items are pulled from another table called order_items and is populated via VBA code using the Private Sub Report_Load() function.  The problem is that it isn't populating the report with this data.  I put in a msgbox to confirm the Report_Load() was running and it doesn't pop up.  Doesn't Report_Load() work with 2003?
Private Sub Report_Load()
    MsgBox ("report load")
    LoadItemsData
End Sub
 
Private Sub ResetField(i As Integer)
    On Error GoTo Err_ResetField
    
    Controls.Item("txtQuantity" & i).Value = Nothing
    Controls.Item("txtDescription" & i).Value = Nothing
    Controls.Item("txtUnitPrice" & i).Value = Nothing
    Controls.Item("txtTotalPrice" & i).Value = Nothing
    Controls.Item("txtBuyer" & i).Value = Nothing
    
Exit_ResetField:
    Exit Sub
    
Err_ResetField:
    MsgBox Err.Description
    Resume Exit_ResetField
End Sub
 
Private Sub LoadItemsData()
    On Error GoTo Err_LoadItemsData
 
    Const cstrQuery As String = "OrderItems Query"
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim ctr As Control
    Dim i As Integer
    
    Set dbs = CurrentDb()
    Set qdf = dbs.QueryDefs(cstrQuery)
    
    qdf.Parameters("OrderID") = CInt(Purchase_Order_No.Value)
    ' Open recordset on the query
    Set rst = qdf.OpenRecordset()
    MsgBox ("begin populating")
    For i = 1 To FieldsCount
        If Not rst.EOF Then
            MsgBox (txtDescription & i.Value)
            Controls.Item("txtQuantity" & i).Value = rst![quantity]
            Controls.Item("txtDescription" & i).Value = rst![Description]
            Controls.Item("txtUnitPrice" & i).Value = rst![UnitPrice]
            Controls.Item("txtTotalPrice" & i).Value = rst![TotalPrice]
            Controls.Item("txtBuyer" & i).Value = rst![Buyer]
            
            rst.MoveNext
        Else
            ResetField i
        End If
    Next i
    MsgBox ("end populating")
    rst.Close
    qdf.Close
    dbs.Close
    
Exit_LoadItemsData:
    Exit Sub
    
Err_LoadItemsData:
    MsgBox Err.Description
    Resume Exit_LoadItemsData
End Sub

Open in new window

0
oncalltech
Asked:
oncalltech
  • 16
  • 12
  • 3
5 Solutions
 
tbsgadiCommented:
Hi oncalltech,

Try changing it to Report_Open


Good Luck!

Gary
0
 
tbsgadiCommented:
oncalltech,
No Report_Load in 2003

Gary
0
 
oncalltechAuthor Commented:
I changed it to Private Sub Report_Open() and now I get the error :
The expression On Open you entered as the event property setting produced the following error:  Procedure declaration does not match description of event or procedure having the same name.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
tbsgadiCommented:
Should be
Private Sub Report_Open(Cancel As Integer)
0
 
oncalltechAuthor Commented:
Thanks that was a big help. It is now launching the second part, the problem is I am now getting an invalid qualifier here:

Set rst = qdf.OpenRecordset()
MsgBox ("begin populating")
For i = 1 To FieldsCount
If Not rst.EOF Then
MsgBox (txtDescription & i.Value)
Controls.Item("txtQuantity" & i).Value = rst![quantity]
The bold i is getting the compile error invalid qualifier
0
 
tbsgadiCommented:
Check that txtQuantity1,txtQuantity2 ... all exist
0
 
Jeffrey CoachmanMIS LiasonCommented:
What is "FieldsCount"?
I don't see it declared anywhere.

Do you mean: rst.Fields.Count
...perhaps?
0
 
oncalltechAuthor Commented:
sorry i missed the field count.  It was in a msgbox that i had the error.  I removed the field and it runs now, but I get an error stating I entered an expression that has no value.  I assume this is from the cancel as integer.  It prompts me for the PO number, I enter it and it displays the report.. with no data in the txtquantity1,2,3,.. etc or any of the other fields I am trying to populate
Private Const FieldsCount As Integer = 12
 
Private Sub Report_Click()
    'Me.Requery
End Sub
 
Private Sub Report_Open(Cancel As Integer)
    'MsgBox ("report load")
    LoadItemsData
End Sub
 
Private Sub ResetField(i As Integer)
    On Error GoTo Err_ResetField
    
    Controls.Item("txtQuantity" & i).Value = Nothing
    Controls.Item("txtDescription" & i).Value = Nothing
    Controls.Item("txtUnitPrice" & i).Value = Nothing
    Controls.Item("txtTotalPrice" & i).Value = Nothing
    Controls.Item("txtBuyer" & i).Value = Nothing
    
Exit_ResetField:
    Exit Sub
    
Err_ResetField:
    MsgBox Err.Description
    Resume Exit_ResetField
End Sub
 
Private Sub LoadItemsData()
    On Error GoTo Err_LoadItemsData
 
    Const cstrQuery As String = "OrderItems Query"
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim rst As DAO.Recordset
    Dim ctr As Control
    Dim i As Integer
    
    Set dbs = CurrentDb()
    Set qdf = dbs.QueryDefs(cstrQuery)
    
    qdf.Parameters("OrderID") = CInt(Purchase_Order_No.Value)
    ' Open recordset on the query
    Set rst = qdf.OpenRecordset()
    MsgBox ("begin populating")
    For i = 1 To FieldsCount
        If Not rst.EOF Then
            Controls.Item("txtQuantity" & i).Value = rst![quantity]
            Controls.Item("txtDescription" & i).Value = rst![Description]
            Controls.Item("txtUnitPrice" & i).Value = rst![UnitPrice]
            Controls.Item("txtTotalPrice" & i).Value = rst![TotalPrice]
            Controls.Item("txtBuyer" & i).Value = rst![Buyer]
            
            rst.MoveNext
        Else
            ResetField i
        End If
    Next i
    MsgBox ("end populating")
    rst.Close
    qdf.Close
    dbs.Close
    
Exit_LoadItemsData:
    Exit Sub
    
Err_LoadItemsData:
    MsgBox Err.Description
    Resume Exit_LoadItemsData
End Sub

Open in new window

0
 
tbsgadiCommented:
You still need to have somewhere for i=1 to ...
0
 
Jeffrey CoachmanMIS LiasonCommented:
FWIW,

We Experts here have seen many cases where Code that worked in Access 2003 does not work in Access 2007.

If your DB was originally in Access 2007 and you "Converted" it back to Access 2003 (Not reccommended) then this may be part of the issue.

Access 2007 is a totally different format than 2003.
If you have even 1 small feature in your db that is 2007 specific (which you may not even know about), then I would be leary of "Converting" it back to 2003.

I have one test database that I have toggled back an forth between Access 2003 and 2007, and it is constantly crashing.

The other day all the code for all the Forms and Reports simply vanished.

Now this very well might be the result of something in my database, but I am just letting you know my experience.

At the very least, when you convert a database, run the Compact/Repair Utility on the DB in the converted format.

For example if you convert a 2007 DB to the 2003 format, open the db in Access 2003 and run the compact/repair utility.

JeffCoachman
0
 
Jeffrey CoachmanMIS LiasonCommented:
Because Reports are much more complex in Access 2007 (Report View interactivity)
The Load event may not function the Exact same way as it did in 2003.

What some developers do is to keep the DB in the lowest format.
(at least until everyone in the organization upgrades to the newest version)
0
 
oncalltechAuthor Commented:
So I get no more errors now, but it is still not filling in the order item numbers.  I checked my code to the best of my ability and it no longer has any 2007 specific items in it.
0
 
tbsgadiCommented:
In 2007 it filled in the numbers?
Can you post the SQL of the qdf?
0
 
oncalltechAuthor Commented:
here is the orderitems sql

PARAMETERS OrderID Long;
SELECT OrderItems.ID, OrderItems.OrderID, OrderItems.Quantity, OrderItems.Description, OrderItems.UnitPrice, OrderItems.TotalPrice, OrderItems.Buyer
FROM OrderItems
WHERE (((OrderItems.OrderID)=[OrderID]));

through message boxes i have found the stopping point is found in the code snippet.  I get the messaeg setting, then I am prompted with "You entered an expression that has no value" and i am prompted to input an order id.  I put in the order id number and the report comes up without the order item data

 MsgBox ("setting")
    qdf.Parameters("OrderID") = CInt(Purchase_Order_No.Value)
    ' Open recordset on the query
    Set rst = qdf.OpenRecordset()
    MsgBox ("begin populating")
    For i = 1 To FieldsCount
        If Not rst.EOF Then
            'MsgBox (txtDescription & i.Value)
            Controls.Item("txtQuantity" & i).Value = rst![quantity]
            Controls.Item("txtDescription" & i).Value = rst![Description]
            Controls.Item("txtUnitPrice" & i).Value = rst![UnitPrice]
            Controls.Item("txtTotalPrice" & i).Value = rst![TotalPrice]
            Controls.Item("txtBuyer" & i).Value = rst![Buyer]
            
            rst.MoveNext
        Else
            ResetField i
        End If
    Next i
    MsgBox ("end populating")
    rst.Close
    qdf.Close
    dbs.Close

Open in new window

0
 
tbsgadiCommented:
Try writing the ORDERID manually to in the query to see if it works
0
 
oncalltechAuthor Commented:
I entered the order id manually in place of the code CInt (Purchase_Order_No.Value), i put just CInt(71)
When I ran it i got the next msgbox "populatng" and then got the error "you can't assign a value to this object and again i was prompted for an orderid
0
 
tbsgadiCommented:
try WHERE (((OrderItems.OrderID)=71));
0
 
tbsgadiCommented:
If that works the try replacing 71 with something else not [OrderID]
0
 
oncalltechAuthor Commented:
I edited the query to hardcode 71.  I made a mistake before and I am not getting the populating message box sot the error has to be in the Set rst = qdf.OpenRecordset()  I am currently getting the message "you entered an expression that has no value" and i am prompted for the orderid again
0
 
tbsgadiCommented:
Can you run the query?
0
 
oncalltechAuthor Commented:
If I run the query with 71 hard coded to replace orderid in the WHERE it prompts me for 71 and I have to enter a number.  If i enter 71, i get the recordset.  If i run it with the WHERE as originally posted, i get the recordset after prompting for orderid.  Neither case affects the report
0
 
tbsgadiCommented:
Remove PARAMETERS OrderID Long and hopefully it won't prompt you
0
 
oncalltechAuthor Commented:
Ii removed the parameters line from the start of the query, I set the WHERE statement to =71 and the query pulled the correct records.  I ran the report, and got the same errors.about expression with no value and prompted for the order id
0
 
tbsgadiCommented:
Try now
PARAMETERS FilterID Long

WHERE (((OrderItems.OrderID)=[FilterID]));


qdf.Parameters(0)= 70
0
 
oncalltechAuthor Commented:
Well hard coding got some progress.  It is now into the if statement but fails on the first controls.item with "you can't assign a value to this object"  

??
0
 
tbsgadiCommented:
The txtboxes should all be unbound
0
 
oncalltechAuthor Commented:
they are.
0
 
tbsgadiCommented:
You can't set the values of textboxes in report_Open
You need to set it in the Format_event  of the relevant group eg Detail_Format
0
 
tbsgadiCommented:
Alternatively create a temporary table & use dlookups or base the report on that
0
 
oncalltechAuthor Commented:
So for the record I am now hating Access.  I will have to find someone to write that code then as I don't know how to set the value using the format_event.  Thanks so much for your help tbsgadi
0
 
tbsgadiCommented:
Went a bit around in circles!
At least we now know where the problems are!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 16
  • 12
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now