Solved

Error in .mde form but not in .mdb form

Posted on 2001-07-31
9
303 Views
Last Modified: 2013-12-05
Hi,

I have a form that merges the data in one record (secondary) into the record in a second record (primary).

I use code to do this (to follow).  When I execute the code in an .mdb file it works PERFECTLY.  No errors and it produces the exact results I want.

When I generate a .mde file from the .mdb and try to execute the script, I get a following error:
The Express On Click you entered as the event property setting produced the following error:.
* 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 evaluating the function, event, or macro.

Since it doesn't happen in the .mdb, I can't get a good diagnosis of the error.  I put MsgBox's along the script to see where it bombed out.  Turns out it dies on the line "rstSecondary.Fields("PER_linkto_Current_ADDR").Value = Null"

I've tried it as "rstSecondary("PER_linkto_Current_ADDR") = Null" and I get the same error.

Does anyone have any idea how I can empty the value of the 4 fields?

Thanks!

Ben

--------------- Code to Follow -------------------


Private Function fncMergePeople()

    Dim strPrimarySQL As String
    strPrimarySQL = "SELECT * FROM tblPersonnel WHERE PER_ID = " & Me.txtPrimaryID
    Dim rstPrimary As ADODB.Recordset
    Set rstPrimary = New ADODB.Recordset
    rstPrimary.ActiveConnection = CurrentProject.Connection
    rstPrimary.LockType = adLockOptimistic
    rstPrimary.CursorType = adOpenKeyset
    rstPrimary.Source = strPrimarySQL
    rstPrimary.Open

    Dim strSecondarySQL As String
    strSecondarySQL = "SELECT * FROM tblPersonnel WHERE PER_ID = " & Me.txtSecondaryID
    Dim rstSecondary As ADODB.Recordset
    Set rstSecondary = New ADODB.Recordset
    rstSecondary.ActiveConnection = CurrentProject.Connection
    rstSecondary.LockType = adLockOptimistic
    rstSecondary.CursorType = adOpenKeyset
    rstSecondary.Source = strSecondarySQL
    rstSecondary.Open
   
    Dim fldCurrent As Field
   
    rstSecondary.Fields("PER_linkto_Current_ADDR").Value = Null    
    rstSecondary.Fields("PER_DateOf_Current_ADDR").Value = ""    
    rstSecondary.Fields("PER_linkto_PrimaryPhone").Value = ""
    rstSecondary.Fields("PER_linkto_PrimaryPhoneDate").Value = ""
   
    rstSecondary.Update
   
    For Each fldCurrent In rstPrimary.Fields
        If ((IsNull(rstPrimary.Fields(fldCurrent.Name).Value)) And (Not IsNull(rstSecondary.Fields(fldCurrent.Name).Value))) Then
            rstPrimary.Fields(fldCurrent.Name).Value = rstSecondary.Fields(fldCurrent.Name).Value
        End If
    Next fldCurrent

    rstSecondary.Update    
    rstPrimary.Update
    Me.sfrPrimaryPerson.Requery
   
    MsgBox "Records Merged Successfully!"

End Function
0
Comment
Question by:funkymonk
  • 5
  • 3
9 Comments
 
LVL 14

Expert Comment

by:mgrattan
ID: 6339384
My first suggestion would be to try the following syntax:

rstSecondary![PER_linkto_Current_ADDR] = Null

OR...

rstSecondary![PER_linkto_Current_ADDR] = ""

Since there are no spaces in your field names you could also get away with eliminating the square brackets that I included.

0
 
LVL 54

Expert Comment

by:nico5038
ID: 6339431
If Mikes suggestions don't solve it, some general precausions:
1) Did you check the references (under VBA Tools/References) for MISSING libraries
2) Did you run a Compile all modules before the .mde creation.

Nic;o)
0
 
LVL 1

Author Comment

by:funkymonk
ID: 6340744
mgrattan,

Thanks for the suggestion, but I get the same error!

Any other ideas?

nico5038,

I don't have any missing libraries and I DID compile all the modules successfully.

I'm at a loss and willing to try anything :)

Ben
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 6341124
I see that you are using an ADODB recordset.  Is it safe to assume that you are using Access 2000?  

Check your Tools/References for entries for ActiveX Data Objects 2.5 and Microsoft DAO 3.6.  If the DAO reference exists in addition to the ADO reference, make sure the DAO one is below the ADO reference in order of priority.  
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 1

Author Comment

by:funkymonk
ID: 6342920
mgrattan,

No Go.  Sorry.  I only had:
- Visual Basic for Applications
- Microsoft Access 9.0 Oject Library
- Microsoft ActiveX Data Ojects 2.1 Library

I then Added DAO 3.6, Giving me:
- Visual Basic for Applications
- Microsoft Access 9.0 Oject Library
- Microsoft ActiveX Data Ojects 2.1 Library
- Microsoft DAO 3.6 Object Library

Yet the problem persists.  More to be done?

Thanks

Ben
0
 
LVL 14

Accepted Solution

by:
mgrattan earned 200 total points
ID: 6343096
You don't need the DAO reference unless you're using DAO in your code.  I was only suggesting that, if you did already have the reference, you could move it down in priority to make sure it's not causing the problem.  Please go ahead and remove the reference if you don't need it.  

Do you want to email a copy to me so I can test it on my machine?  If so, just compact it and ZIP it, then email to me at my outuvtime "at" yahoo.com address.
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 6386435
Need further assistance?
0
 
LVL 1

Author Comment

by:funkymonk
ID: 6387145
mgrattan,

Sorry, I've been out of town.  I figured out how to handle this issue in another way.  I set value to null using a form and then saving the recordset.  It's a bit awkward, but it works.

Thanks for the offer of looking at my database.

Ben
0
 
LVL 14

Expert Comment

by:mgrattan
ID: 6388804
Glad you got it working and thanks for letting us know!

Mike.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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 …

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now