Access 2003 not running code at report loading

Hi oncalltech,

Try changing it to Report_Open


Good Luck!

Gary

oncalltech,
No Report_Load in 2003

Gary

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.

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

Check that txtQuantity1,txtQuantity2 ... all exist

What is "FieldsCount"?
I don't see it declared anywhere.

Do you mean: rst.Fields.Count
...perhaps?

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

Select allOpen in new window

You still need to have somewhere for i=1 to ...

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

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)

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.

In 2007 it filled in the numbers?
Can you post the SQL of the qdf?

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

Select allOpen in new window

Try writing the ORDERID manually to in the query to see if it works

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

If that works the try replacing 71 with something else not [OrderID]

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

Can you run the query?

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

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

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"  

??

The txtboxes should all be unbound

they are.

Alternatively create a temporary table & use dlookups or base the report on that

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

Went a bit around in circles!
At least we now know where the problems are!