Runtime error 2467: Application-defined or object-defined error.

[Nick Terry]

NOTE: I am nowhere close to an expert with access or vb, but have coding
experience and have had classroom instruction. If anything you see here is
blatantly wrong or unkosher, please don't hesistate to let me know.

I am creating a database to store bids for contracts. The last bug I need
to work out is getting a labor rate to update through all line items and
return a total. The basic structure of my data is:

Table 1: T-Bids - 1
|
Many - Table 2: T-Line Items - 1
|
Many -
Table 3: T-Line Item Details

Table One contains information about each bid. Table Two contains
information about each line item. Table 3 contains information about specific
costs in each line item. These costs are totalled and turned into a unit
price on a corresponding form, and inserted back into table 2.

If the labor rate, which is stored in table 1, is changed, the line items
will only reflect this change after their detail forms are opened.

That is my problem. My question pertains to the solution I created for this
original problem. I decided to put a bit of code in the After Update event
on the field which stored the labor rate. This way, when the labor rate is
changed, the code runs.

The code I wrote basically opens the detail form for each line item as a
hidden window and then closes it. This allows all of the data to push
through and update.

This may seem like a sloppy way to accomplish my goal, but I have searched
long and hard for a better way and given up.

Anyways, here is my code:

Private Sub T_Bids_LaborRate_AfterUpdate()
Dim dbs
Dim table
Dim BidID

Set BidID = Me.BidID
Set dbs = CurrentDb
Set table = dbs.OpenRecordset("T-Line Items")

With table
.MoveFirst

Do While Not .EOF
If BidIDx = ![BidID] Then
DoCmd.OpenForm "F-Line Items Details", acNormal, ,
"LItemID=" & ![LItemID], acFormEdit, acHidden
DoCmd.Close acForm,"F-Line Items Details"
End If
.MoveNext
Loop

End With

table.Close

End Sub


This code is supposed to take in a BidId number and move through each record
in Table 2. If the BidId is the same as the one listed in the table, it will
open the form and close it.

The code that opens/closes the form works, but when I try it with the BidID
constraints, I get a Runtime Error 2467: Application-defined or
object-defined error.

Any Help with this matter would be greatly appreciated.

Thanks a ton,
Nick

 

[Roger Carlson]

BidID is what, a long integer? First of all, you should explicitly declare
your variables:

Dim dbs As DAO.Database
Dim table As DAO.Recordset
Dim BidID As Long

Now, you cannot use the SET command with a regular variable. It can only be
used with an object variable. Therefore, your code should be changed to
this:

BidID = Me.BidID
Set dbs = CurrentDb
Set table = dbs.OpenRecordset("T-Line Items")


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

NOTE: I am nowhere close to an expert with access or vb, but have coding
experience and have had classroom instruction. If anything you see here is
blatantly wrong or unkosher, please don't hesistate to let me know.

I am creating a database to store bids for contracts. The last bug I need
to work out is getting a labor rate to update through all line items and
return a total. The basic structure of my data is:

Table 1: T-Bids - 1
|
Many - Table 2: T-Line Items - 1
|
Many -
Table 3: T-Line Item Details

Table One contains information about each bid. Table Two contains
information about each line item. Table 3 contains information about specific
costs in each line item. These costs are totalled and turned into a unit
price on a corresponding form, and inserted back into table 2.

If the labor rate, which is stored in table 1, is changed, the line items
will only reflect this change after their detail forms are opened.

That is my problem. My question pertains to the solution I created for this
original problem. I decided to put a bit of code in the After Update event
on the field which stored the labor rate. This way, when the labor rate is
changed, the code runs.

The code I wrote basically opens the detail form for each line item as a
hidden window and then closes it. This allows all of the data to push
through and update.

This may seem like a sloppy way to accomplish my goal, but I have searched
long and hard for a better way and given up.

Anyways, here is my code:

Private Sub T_Bids_LaborRate_AfterUpdate()
Dim dbs
Dim table
Dim BidID

Set BidID = Me.BidID
Set dbs = CurrentDb
Set table = dbs.OpenRecordset("T-Line Items")

With table
.MoveFirst

Do While Not .EOF
If BidIDx = ![BidID] Then
DoCmd.OpenForm "F-Line Items Details", acNormal, ,
"LItemID=" & ![LItemID], acFormEdit, acHidden
DoCmd.Close acForm,"F-Line Items Details"
End If
.MoveNext
Loop

End With

table.Close

End Sub


This code is supposed to take in a BidId number and move through each record
in Table 2. If the BidId is the same as the one listed in the table, it will
open the form and close it.

The code that opens/closes the form works, but when I try it with the BidID
constraints, I get a Runtime Error 2467: Application-defined or
object-defined error.

Any Help with this matter would be greatly appreciated.

Thanks a ton,
Nick

 

[Klatuu]

This is an excellent example of why calculated values should not be stored in
a table!

You really don't need the total in table2. As you see, it is causing you
more pain than pleasure.

The ususal and correct way to deal with calculations is to do them only when
you need to present them to a user or use them an another calculation.