Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Invalid Data Format in Access VBA

Posted on 2010-09-03
33
1,775 Views
Last Modified: 2012-05-10
When I try to compile my VBA project in Access I get the following error message:

"Invalid Data Format"

How can I fix it?
0
Comment
Question by:fitaliano
  • 13
  • 11
  • 7
  • +1
33 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 33598746
When the error occurs, it generally give you an option to Exit or Debug.  Select Debug, and provide us with the code segment where the error is occuring.  Make sure you tell us which line is highlighted.

Additionally, you need to make sure that ever code module (whether it belongs to a form or a standard code module contains the following two lines:

Option Compare Database
Option Explicit

0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33598781
check if you have missing reference

from VBA window
tools > references

0
 
LVL 75
ID: 33598942
Posting the code would be helpful ...

mx
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:fitaliano
ID: 33599039
Wherever I click, I get the following message:
 
The expression On Click you entered as the event property setting produced the following error: Invalid Data Format
* The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure].
* There may have been an error in evaluating the function, event or macro.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 33599071


try doing compact and repair
Tools >Database utilities > compact and repair database

if problem persists,
* do a decompile
   see this link
http://www.granite.ab.ca/access/decompile.htm

* after that, create a blank db and import all objects
* open the VBA window and do
     Debug > Compile
     correct any errors raised

* do a compact and repair
0
 
LVL 75
ID: 33599098
Try this - to see where the error is occurring:

VBA Editor>>Tools>>Options>>General
*Temporarily* set Error Trapping to Break on All Errors.
Run you code.  It's should break in code at the actual error line.

After the issue is fixed ... be SURE to set back to Break on Unhandled Errors.

mx
Capture1.gif
0
 

Author Comment

by:fitaliano
ID: 33599184
I think I identified the problem. I can pretty much open all the Class Objects and all the modules but 2 modules.
When I click one of them it gives me: invalid data format
When I click on the other one it gives me: out of memory
I tried to delete these modules but it doesn't let me do it.
I keep getting "invalid data format" and "out of memory"
I tried to import a working code from a .bas file but instead of replacing the exiting modules, it creates new modules adding 1 at the end.
Recreating the database will take me 1 month.
0
 
LVL 75
ID: 33599226
Your vba project is Corrupted.  Hope the Decompile suggested above fixes it.

Do you have a backup ?

mx
0
 

Author Comment

by:fitaliano
ID: 33599229
This is what I had in the incriminated modules:
1) -------------------------------------------------------------------------------------------------------
Function fGetOut() As Integer
Dim RetVal As Integer
Dim db As DAO.Database
Dim rst As Recordset
On Error GoTo Err_fGGO
Set db = DBEngine.Workspaces(0).Databases(0)
Set rst = db.OpenRecordset("KickEmOff", dbOpenSnapshot)
If rst.EOF And rst.BOF Then
 RetVal = True
 GoTo Exit_fGGO
Else
 If rst!GetOut = True Then
  'This is where you close down any forms, and quit the database
  'I leave this section as an exercise in creativity
   Application.Quit
 Else
  RetVal = True
 End If
End If
Exit_fGGO:
 fGetOut = RetVal
 Exit Function
Err_fGGO:
 'Note lack of message box on error
 Resume Next
End Function

2) --------------------------------------------------------------------------------------------------------
 

Sub ShowUserRosterMultipleUsers()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim i, j As Long
    Set cn = CurrentProject.Connection
    ' The user roster is exposed as a provider-specific schema rowset
    ' in the Jet 4.0 OLE DB provider.  You have to use a GUID to
    ' reference the schema, as provider-specific schemas are not
    ' listed in ADO's type library for schema rowsets
    Set rs = cn.OpenSchema(adSchemaProviderSpecific, _
    , "{947bb102-5d43-11d1-bdbf-00c04fb92675}")
    'Output the list of all users in the current database.
    Debug.Print rs.Fields(0).Name, "", rs.Fields(1).Name, _
    "", rs.Fields(2).Name, rs.Fields(3).Name
    While Not rs.EOF
        Debug.Print rs.Fields(0), rs.Fields(1), _
        rs.Fields(2), rs.Fields(3)
        rs.MoveNext
    Wend
End Sub
 
 
0
 
LVL 75
ID: 33599232
If you can fix it ... Wayne probably can:

http://www.everythingaccess.com/accessdatabaserepair.htm

mx
0
 

Author Comment

by:fitaliano
ID: 33599245
Yes, I have an old backup.
Is there anyway to delete these modules?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33599259
<Is there anyway to delete these modules?>

select the module and hit the delete key
0
 
LVL 75
ID: 33599266
You can *try* ...  importing everything into a new mdb - suggested above.

Otherwise ...  hit up EverythingAccess ...

mx
0
 

Author Comment

by:fitaliano
ID: 33599373
Capricorn1,
if I select the module and then hit delete nothing happens. The only way I know is to right-click and selct remove module. Am I missing something?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33599402
select the module from the database objects window, then hit delete
0
 

Author Comment

by:fitaliano
ID: 33599592
I did select the module in both the object navigation and the library (see printscreen attached) but I can't delete by hitting the "delete" button.
This could be a life saver, am I doing this right?
0
 

Author Comment

by:fitaliano
ID: 33599625
I forgot to attach the printscreen
Select-Module.ppt
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33599700
close the vba window..
go to the Access window

you should see the database objects window as shown

select the module and hit the delete key
databaseObjectsWindow.jpg
0
 

Author Comment

by:fitaliano
ID: 33599714
Yes, I tried that but those modules do not appear in this window.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33599743
from Access window
Tools >Options
View Tab

check the box for Hidden Objects then click OK
0
 

Author Comment

by:fitaliano
ID: 33599820
I see all the modules but the incriminated 2...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33599841
can you upload the db?
0
 
LVL 75
ID: 33599844
Try the Import into new mdb as suggested above. You might get luck.  Also the Decompile. But ... it's not looking good at this point.  When you cannot delete w/o those error messages, the db is *usually* hosed.

Can you upload ?  We might get lucky on a different system.

If all else fails, EverythingAccess can probably fix this for you ...

mx
0
 

Author Comment

by:fitaliano
ID: 33599857
There you go...
 
******
Uploaded sample removed per Author's request:
http://www.experts-exchange.com/Q_26451240.html 
The sample had no bearing on the progress towards a solution.
 
_alias99
Community Support Moderator
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33599914
fitaliano,

you are using a replica version of the db,
get the master db..
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33599933
or try this

How to make a replicated database a regular database in Access
http://support.microsoft.com/?kbid=290052
0
 

Author Comment

by:fitaliano
ID: 33600082
No I am using the design master. If you remove the design master from its location it will lose its Design Master property.
Just go to Tools --> Replication --> Recover Design Master to do any modification
to access the main menu click on the company logo and type "bvr"
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33600552
??????
0
 

Author Comment

by:fitaliano
ID: 33600592
I ended up re-importing all the objects in another clean db
0
 

Author Comment

by:fitaliano
ID: 33600609
I assigned more points to DBMax who suggested the solution but I also benefitted from Capricorn1 suggestions as I had to decompile and delete select objetcs.
Thank you
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 33600636
fitaliano, see my comment at http:#a33599071

mx just repeated my suggestion of importing all the objects in a new db
0
 
LVL 75
ID: 33600994
fitaliano:
Whereas I do appreciate the pts and maybe a small Assist, the Accepted post (mine) was just pointing out what had ... already been posted by the other expert ... and further suggesting that you try that (importing/decompile) ... which in fact did turn out to be the fix.

You should hit the Request Attention button and change things around.

mx
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question