Run-time error 91

[lovetofly32]

I am attempting to consolidate data from 23 different databases (all Access 2010). The content across each database is exactly the same, I just need to consolidate it into a single database. When I attempt do so i am getting the run-time error 91 "Error in modload_consolidated_tables 91 object variable or with block variable not set."

I am using a form to complete this process. It appears that the table I've referenced where the consolidated data will reside is not being set. Here's the code:

Option Compare Database
Option Explicit
Public rsConsolidated As DAO.Recordset
Public Load_Opened As Boolean
Public Load_Failed As Boolean
Dim NullCheck As Boolean
Dim stLoad_Point As String

Public Sub Consolidate_Data()

Set rsConsolidated = CurrentDb.OpenRecordset("tbl_payment_consolidated", dbOpenDynaset)

On Error GoTo Err_Consolidate_Data

Load_Opened = True
Load_Failed = False

If Form_frmConsolidate.rsIndividual.EOF Then
   GoTo Close_Recordset
End If

Form_frmConsolidate.rsIndividual.MoveFirst

Loopit:

Check_Consolidate:

If Form_frmConsolidate.rsIndividual.EOF Then
   GoTo Close_Recordset
End If

rsConsolidated.AddNew

rsConsolidated!TravelOrderNo = Form_frmConsolidate.rsIndividual!TravelOrderNo
rsConsolidated!AuditID = Form_frmConsolidate.rsIndividual!AuditID
rsConsolidated!GovtCardID = Form_frmConsolidate.rsIndividual!GovtCardID
rsConsolidated!LastName = Form_frmConsolidate.rsIndividual!LastName
rsConsolidated!MI = Form_frmConsolidate.rsIndividual!MI
rsConsolidated!FirstName = Form_frmConsolidate.rsIndividual!FirstName
rsConsolidated!TravelClassification = Form_frmConsolidate.rsIndividual!TravelClassification
rsConsolidated!TripPurpose = Form_frmConsolidate.rsIndividual!TripPurpose
rsConsolidated!C_TDYBeginDate = Form_frmConsolidate.rsIndividual!C_TDYBeginDate
rsConsolidated!C_TDYEndDate = Form_frmConsolidate.rsIndividual!C_TDYEndDate
rsConsolidated!C_Destination = Form_frmConsolidate.rsIndividual!C_Destination
rsConsolidated!C_Worksite = Form_frmConsolidate.rsIndividual!C_Worksite
rsConsolidated!C_LodgingNights = Form_frmConsolidate.rsIndividual!C_LodgingNights
rsConsolidated!C_MAIEDays = Form_frmConsolidate.rsIndividual!C_MAIEDays
rsConsolidated!RefundableLodgingDeposit = Form_frmConsolidate.rsIndividual!RefundableLodgingDeposit
rsConsolidated!Pmeals = Form_frmConsolidate.rsIndividual!Pmeals
rsConsolidated!Plodging = Form_frmConsolidate.rsIndividual!Plodging
rsConsolidated!Ptransp = Form_frmConsolidate.rsIndividual!Ptransp
rsConsolidated!Pexpenses = Form_frmConsolidate.rsIndividual!Pexpenses
rsConsolidated!Ptotal = Form_frmConsolidate.rsIndividual!Ptotal
rsConsolidated!PmaieLT = Form_frmConsolidate.rsIndividual!PmaieLT
rsConsolidated!PLodgingLT = Form_frmConsolidate.rsIndividual!PLodgingLT
rsConsolidated!S155maie = Form_frmConsolidate.rsIndividual!S155maie
rsConsolidated!S175maie = Form_frmConsolidate.rsIndividual!S175maie
rsConsolidated!S1Fullmaie = Form_frmConsolidate.rsIndividual!S1Fullmaie
rsConsolidated!S1Lodging = Form_frmConsolidate.rsIndividual!S1Lodging
rsConsolidated!S1Lodging150 = Form_frmConsolidate.rsIndividual!S1Lodging150
rsConsolidated!S1POV = Form_frmConsolidate.rsIndividual!S1POV
rsConsolidated!S255maie = Form_frmConsolidate.rsIndividual!S255maie
rsConsolidated!S275maie = Form_frmConsolidate.rsIndividual!S275maie
rsConsolidated!S2Fullmaie = Form_frmConsolidate.rsIndividual!S2Fullmaie
rsConsolidated!S2Lodging = Form_frmConsolidate.rsIndividual!S2Lodging
rsConsolidated!S2Lodging150 = Form_frmConsolidate.rsIndividual!S2Lodging150
rsConsolidated!S2POV = Form_frmConsolidate.rsIndividual!S2POV
rsConsolidated!MAIEDailyRate = Form_frmConsolidate.rsIndividual!MAIEDailyRate
rsConsolidated!LodgingDailyRate = Form_frmConsolidate.rsIndividual!LodgingDailyRate
rsConsolidated!FullFlatPerDiem = Form_frmConsolidate.rsIndividual!FullFlatPerDiem
rsConsolidated!AirportParking = Form_frmConsolidate.rsIndividual!AirportParking
rsConsolidated!ATMFee = Form_frmConsolidate.rsIndividual!ATMFee
rsConsolidated!BaggageFee = Form_frmConsolidate.rsIndividual!BaggageFee
rsConsolidated!Gasoline = Form_frmConsolidate.rsIndividual!Gasoline
rsConsolidated!HotelParking = Form_frmConsolidate.rsIndividual!HotelParking
rsConsolidated!HotelTaxes = Form_frmConsolidate.rsIndividual!HotelTaxes
rsConsolidated!Laundry = Form_frmConsolidate.rsIndividual!Laundry
rsConsolidated!Mileage = Form_frmConsolidate.rsIndividual!Mileage
rsConsolidated!Misc = Form_frmConsolidate.rsIndividual!Misc
rsConsolidated!Parking = Form_frmConsolidate.rsIndividual!Parking
rsConsolidated!Phone = Form_frmConsolidate.rsIndividual!Phone
rsConsolidated!RentalCar = Form_frmConsolidate.rsIndividual!RentalCar
rsConsolidated!TaxShuLimMet = Form_frmConsolidate.rsIndividual!TaxShuLimMet
rsConsolidated!Tips = Form_frmConsolidate.rsIndividual!Tips
rsConsolidated!Tolls = Form_frmConsolidate.rsIndividual!Tolls
rsConsolidated!TransFee = Form_frmConsolidate.rsIndividual!TransFee
rsConsolidated!Meals = Form_frmConsolidate.rsIndividual!Meals
rsConsolidated!Lodging = Form_frmConsolidate.rsIndividual!Lodging
rsConsolidated!Tranp = Form_frmConsolidate.rsIndividual!Tranp
rsConsolidated!Expenses = Form_frmConsolidate.rsIndividual!Expenses
rsConsolidated!Total = Form_frmConsolidate.rsIndividual!Total
rsConsolidated!UnderOverPaid = Form_frmConsolidate.rsIndividual!UnderOverPaid
rsConsolidated!ImproperPerDiemAmount = Form_frmConsolidate.rsIndividual!ImproperPerDiemAmount
rsConsolidated!ImproperPerDiemComment = Form_frmConsolidate.rsIndividual!ImproperPerDiemComment
rsConsolidated!ImproperClaimAmount = Form_frmConsolidate.rsIndividual!ImproperClaimAmount
rsConsolidated!ImproperClaimPerDiemAmount = Form_frmConsolidate.rsIndividual!ImproperClaimPerDiemAmount
rsConsolidated!ImproperClaimComment = Form_frmConsolidate.rsIndividual!ImproperClaimComment
rsConsolidated!UncollectableAmount = Form_frmConsolidate.rsIndividual!UncollectableAmount
rsConsolidated!UncollectableComment = Form_frmConsolidate.rsIndividual!UncollectableComment
rsConsolidated!TravelCardUseAns = Form_frmConsolidate.rsIndividual!TravelCardUseAns
rsConsolidated!TravelCardUseCom = Form_frmConsolidate.rsIndividual!TravelCardUseCom
rsConsolidated!HomeRecordAns = Form_frmConsolidate.rsIndividual!HomeRecordAns
rsConsolidated!HomeRecordCom = Form_frmConsolidate.rsIndividual!HomeRecordCom
rsConsolidated!SplitPayAns = Form_frmConsolidate.rsIndividual!SplitPayAns
rsConsolidated!SplitPayCom = Form_frmConsolidate.rsIndividual!SplitPayCom
rsConsolidated!TDY30DayAns = Form_frmConsolidate.rsIndividual!TDY30DayAns
rsConsolidated!TDY30DayCom = Form_frmConsolidate.rsIndividual!TDY30DayCom
rsConsolidated!SubmitExpenseAns = Form_frmConsolidate.rsIndividual!SubmitExpenseAns
rsConsolidated!SubmitExpenseCom = Form_frmConsolidate.rsIndividual!SubmitExpenseCom
rsConsolidated!CertifyExpenseAns = Form_frmConsolidate.rsIndividual!CertifyExpenseAns
rsConsolidated!CertifyExpenseCom = Form_frmConsolidate.rsIndividual!CertifyExpenseCom
rsConsolidated!BookTravelETSAns = Form_frmConsolidate.rsIndividual!BookTravelETSAns
rsConsolidated!BookTravelETSCom = Form_frmConsolidate.rsIndividual!BookTravelETSCom
rsConsolidated!AirlineETSAns = Form_frmConsolidate.rsIndividual!AirlineETSAns
rsConsolidated!AirlineETSCom = Form_frmConsolidate.rsIndividual!AirlineETSCom
rsConsolidated!HotelETSAns = Form_frmConsolidate.rsIndividual!HotelETSAns
rsConsolidated!HotelETSCom = Form_frmConsolidate.rsIndividual!HotelETSCom
rsConsolidated!RentalETSAns = Form_frmConsolidate.rsIndividual!RentalETSAns
rsConsolidated!RentalETSCom = Form_frmConsolidate.rsIndividual!RentalETSCom
rsConsolidated!SubmitReceiptsAns = Form_frmConsolidate.rsIndividual!SubmitReceiptsAns
rsConsolidated!SubmitReceiptsCom = Form_frmConsolidate.rsIndividual!SubmitReceiptsCom
rsConsolidated!AirlineReceiptsAns = Form_frmConsolidate.rsIndividual!AirlineReceiptsAns
rsConsolidated!AirlineReceiptsCom = Form_frmConsolidate.rsIndividual!AirlineReceiptsCom
rsConsolidated!BaggageReceiptsAns = Form_frmConsolidate.rsIndividual!BaggageReceiptsAns
rsConsolidated!BaggageReceiptsCom = Form_frmConsolidate.rsIndividual!BaggageReceiptsCom
rsConsolidated!HotelReceiptsAns = Form_frmConsolidate.rsIndividual!HotelReceiptsAns
rsConsolidated!HotelReceiptsCom = Form_frmConsolidate.rsIndividual!HotelReceiptsCom
rsConsolidated!GasReceiptsAns = Form_frmConsolidate.rsIndividual!GasReceiptsAns
rsConsolidated!GasReceiptsCom = Form_frmConsolidate.rsIndividual!GasReceiptsCom
rsConsolidated!RentalReceiptsAns = Form_frmConsolidate.rsIndividual!RentalReceiptsAns
rsConsolidated!RentalReceiptsCom = Form_frmConsolidate.rsIndividual!RentalReceiptsCom
rsConsolidated!RentalAuthorizedAns = Form_frmConsolidate.rsIndividual!RentalAuthorizedAns
rsConsolidated!RentalAuthorizedCom = Form_frmConsolidate.rsIndividual!RentalAuthorizedCom
rsConsolidated!PrepaidFuelAns = Form_frmConsolidate.rsIndividual!PrepaidFuelAns
rsConsolidated!PrepaidFuelCom = Form_frmConsolidate.rsIndividual!PrepaidFuelCom
rsConsolidated!InsuranceAns = Form_frmConsolidate.rsIndividual!InsuranceAns
rsConsolidated!InsuranceCom = Form_frmConsolidate.rsIndividual!InsuranceCom
rsConsolidated!UpgradesAns = Form_frmConsolidate.rsIndividual!UpgradesAns
rsConsolidated!UpgradesCom = Form_frmConsolidate.rsIndividual!UpgradesCom
rsConsolidated!AdminErrorsAns = Form_frmConsolidate.rsIndividual!AdminErrorsAns
rsConsolidated!AdminErrorsCom = Form_frmConsolidate.rsIndividual!AdminErrorsCom
rsConsolidated!AuditorName = Form_frmConsolidate.rsIndividual!AuditorName
rsConsolidated!RecordAddedDate = Form_frmConsolidate.rsIndividual!RecordAddedDate


rsConsolidated.Update

Form_frmConsolidate.rsIndividual.MoveNext

GoTo Loopit

Close_Recordset:

Form_frmConsolidate.rsIndividual.Close

Exit_Consolidate_Data:
   Exit Sub
   
Err_Consolidate_Data:
   Load_Failed = True
   MsgBox "Error in modLoad_Consolidated_Tables " & stLoad_Point & " " & Err.Number & " " & Err.Description
   GoTo Close_Recordset
   
End Sub

I've double checked the table field names, the actual table structure, etc. I am confused why it would not set Any advice is appreciated!

 

[spikepl]

1. It is a bit pointless debugging with error handler on, and then not providing the erring line.
Disable your error handler and idientify the offending line.

2.
Form_frmConsolidate.rsIndividual.EOF

this syntax is likely to kill you sooner or later, because it can open closed forms and do many fun things that most people do not know much about. . USe the traditional syntax for referring to form items, such as
Forms!frmConsolidate.rsIndividual.EOF

 

[pr2-eugin]

Hello lovetofly32, Welcome to AWF..

Remove the Error handler and see where exactly you get this error..

 

[lovetofly32]

1. It is a bit pointless debugging with error handler on, and then not providing the erring line.
Disable your error handler and idientify the offending line.

2.
Form_frmConsolidate.rsIndividual.EOF

this syntax is likely to kill you sooner or later, because it can open closed forms and do many fun things that most people do not know much about. . USe the traditional syntax for referring to form items, such as
Forms!frmConsolidate.rsIndividual.EOF

My apologies, here's the actual offending line

Form_frmConsolidate.rsIndividual.Close

 

[pr2-eugin]

I am not sure if I know this..

Form_frmConsolidate.rsIndividual

Are you trying to copy one recordset to another?

 

[lovetofly32]

I am not sure if I know this..

Form_frmConsolidate.rsIndividual

Are you trying to copy one recordset to another?

Yes, I am actually linked to 23 different databases. I am consolidating those records into a single table within the same database. But I was able to figure out my problem, which were missing fields in the consolidated table. My code was referencing fields that didn't exist. Once I put those in, I ran it with partial success. I say partial, because I was able to consolidate 727 records out of 996 that should have been consolidated.

The following errors were generated

"error in modload_consolidated_tables 6 overflow",

(modload_consolidated_tables is the module that links the actual fields in the 23 different linked tables, to the consolidated table.)

The other error was

"error in modload_consolidated_tables 3197 the microsoft access database engine stopped the process because you and another user are attempting to change the same data at the same time"

I have 16 users I am consolidating data from. I was able to consolidate all the data from 13 users, and some of the data from the other 3 users. I compared the records that were consolidated against the records that were not, I looked at every field and analyzed the data to see if any differences existed, but nothing stood out as a problem. I ran this process tonight, and I verified that no users were logged into their databases.

At this point it's hard to determine if my code is bad, or if the way users are entering the data is bad, but again, there is no difference in the actual data. Plus if my code was bad, how could I consolidate a partial set of data, but not all of it??

 

[thechazm]

Is your form accessing the same dataset you are modifying?

 

[lovetofly32]

Is your form accessing the same dataset you are modifying?

I'm not modifying any data. I'm just consolidating it. All 23 databases produce the exact same data, I'm just consolidating it into a single table. The consolidated database I am using, links to those 23 tables, the form then pulls that data, and consolidates it into a different table in the same database.

 

[thechazm]

Ok well maybe modifying may not have been the right choice of words but what I am getting at is you have the data open in two places at once in the same db. Your form and in your code correct?

 

[lovetofly32]

Ok well maybe modifying may not have been the right choice of words but what I am getting at is you have the data open in two places at once in the same db. Your form and in your code correct?

Honestly I am new to access and VBA and I'm working with a hand-me-down database, so I don't feel qualified to respond to this one, but logically yes, I would agree that the form is pulling the data from the 23 tables, and then transferring it into the single consolidated table.

I'm sure that really helps you huh...lol.

 

[thechazm]

My point being if you are reading from the table with your form and consolidating the table with your code you get buffer overflow problems and is why you are likely recieving the error you got about multiple users.

This happens often but you can clear the buffer on the fly and it will work by using the following code example in your error trapping.

On Error GoTo ErrHandler:
 
ErrHandler:
If Err.Number = 3052 Then
    StatusLabel "Clearing Buffer..."
    Err.Clear
    Resume
Else
    MsgBox Err.Number & " " & Err.Description
End If

It may not be the best way to do it but it's my way that I figured out that works all the time when facing this type of error.

 

[lovetofly32]

My point being if you are reading from the table with your form and consolidating the table with your code you get buffer overflow problems and is why you are likely recieving the error you got about multiple users.

This happens often but you can clear the buffer on the fly and it will work by using the following code example in your error trapping.

On Error GoTo ErrHandler:
 
ErrHandler:
If Err.Number = 3052 Then
    StatusLabel "Clearing Buffer..."
    Err.Clear
    Resume
Else
    MsgBox Err.Number & " " & Err.Description
End If

It may not be the best way to do it but it's my way that I figured out that works all the time when facing this type of error.

I was finally able to figure it out, with some help from a local access guru. It was related to a few fields in the individual databases. They all had #num errors because of the way the formula was setup. I changed the formula to accommodate the inputs, and after that the consolidation process worked like a charm.

Thanks for all the help here. You guys are great.

 

[thechazm]

Glad to hear you are up and running. Good Luck!