Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Error in .mde form but not in .mdb form

Posted on 2001-07-31
9
Medium Priority
?
317 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
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

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

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
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.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

926 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