Solved

Error in .mde form but not in .mdb form

Posted on 2001-07-31
9
309 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…

685 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