Solved

Error in .mde form but not in .mdb form

Posted on 2001-07-31
9
307 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
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.

 
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
 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
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…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

776 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