Solved

Error in .mde form but not in .mdb form

Posted on 2001-07-31
9
311 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
[X]
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
  • 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

630 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