UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Recordset.addnew Not New, Access 2016    
 
   
lex
post Dec 28 2017, 08:36 PM
Post#1



Posts: 706
Joined: 20-October 05



I've got a function that creates a new record and passes back the new key, but unfortunately the key is not new. Any thoughts are appreciated.

Thanks
Lex


CODE
Public Function NewContactKey(Optional Company As String, Optional Category As String, Optional LastName As String, Optional FirstName As String, Optional EmailAddress As String, Optional JobTitle As String, Optional BusinessPhone As String, Optional HomePhone As String)
'create a new contact and return the key
On Error GoTo Error_Handler

    Dim db As Database
    Dim rec As Recordset
    
    Set db = CurrentDb
    Set rec = db.OpenRecordset("Contacts", dbOpenDynaset)
    
    'Add the record, storing new key value as variable and
    'passing it out as the function name
    With rec
        .AddNew
        NewContactKey = rec!ID 'get the new key
        
        If Not IsMissing(Company) Then
            rec!Company = Company
        End If
        If Not IsMissing(Category) Then
            rec!Category = Category
        End If
        If Not IsMissing(LastName) Then
            rec![Last Name] = LastName
        End If
        If Not IsMissing(FirstName) Then
            rec![First Name] = FirstName
        End If
        If Not IsMissing(EmailAddress) Then
            rec![E-mail Address] = EmailAddress
        End If
        If Not IsMissing(JobTitle) Then
            rec![Job Title] = JobTitle
        End If
        If Not IsMissing(BusinessPhone) Then
            rec![Business Phone] = BusinessPhone
        End If
        If Not IsMissing(HomePhone) Then
            rec![Home Phone] = HomePhone
        End If

        
        .Update
        .Close
    End With
    
    Set rec = Nothing

Exit_Procedure:
    Exit Function
Error_Handler:
    MsgBox "An error has occurred in this application.  " _
    & "Please contact your technical support person and tell" _
    & " them this information:" _
    & vbCrLf & vbCrLf & "Error Number " & Err.Number & ", " & _
    Err.Description, Buttons:=vbCritical, Title:="My Application"
    Resume Exit_Procedure
    Resume

End Function
Go to the top of the page
 
theDBguy
post Dec 28 2017, 09:34 PM
Post#2


Access Wiki and Forums Moderator
Posts: 72,432
Joined: 19-June 07
From: SunnySandyEggo


Hi Lex,

If you're not getting the new key, what are you getting instead?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
lex
post Dec 28 2017, 09:46 PM
Post#3



Posts: 706
Joined: 20-October 05



Hey there, theDBguy. Great profile photo!

I'm getting an existing key ... I'm getting "duplicate values" error and when I break and check the ID - it's an existing one!

Happy Holidays! smile.gif

lex
Go to the top of the page
 
moke123
post Dec 28 2017, 09:53 PM
Post#4



Posts: 1,278
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



try opening an empty recordset and move NewContactKey = rec.fields(0) to the bottom before .update

CODE
strSql = "select * from contacts where ID = 0"
Set rec = db.OpenRecordset(strSql)

This post has been edited by moke123: Dec 28 2017, 09:56 PM
Go to the top of the page
 
theDBguy
post Dec 28 2017, 09:58 PM
Post#5


Access Wiki and Forums Moderator
Posts: 72,432
Joined: 19-June 07
From: SunnySandyEggo


Hi Lex,

Thanks! Is the key field (ID) an Autonumber field?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
lex
post Dec 28 2017, 10:16 PM
Post#6



Posts: 706
Joined: 20-October 05



Hey moke123... thanks for the reply

Please help me understand better. What is this doing? Do I do this within my code? (ie just before my .update?0

Does the code you provide do what you've described in the text? when you say "move" does the assignment statement

CODE
NewContactKey = rec.fields(0)


perform that ?

Sorry - I don't quite get it.
Go to the top of the page
 
lex
post Dec 28 2017, 10:17 PM
Post#7



Posts: 706
Joined: 20-October 05



theDBGuy - yes - it's an autonum

Lex
Go to the top of the page
 
theDBguy
post Dec 28 2017, 10:34 PM
Post#8


Access Wiki and Forums Moderator
Posts: 72,432
Joined: 19-June 07
From: SunnySandyEggo


Hi Lex,

Thanks for clarifying about the Autonumber field. May I suggest you try the following structure:

CODE
Public Function NewContactKey(...) As Long
...
Dim lngID As Long
...

Set rec = db.OpenRecordset(...

With rec
    .AddNew
   If Not Missing...
      ...
   End If
   If Not Missing...
   ...
   ...

   lngID = !ID

   .Update
   .Close

End With

Set rec = Nothing

NewContactKey = lngID

...
End Function

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
moke123
post Dec 29 2017, 06:52 AM
Post#9



Posts: 1,278
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



hi lex

yes I meant move the piece of code to the bottom just before the update statement, same as in DBG's example.
If I'm just adding a new record I open an empty recordset (ID = 0) The reason being that if i'm only adding a new record I dont need to pull any records from the table.

Maybe DBG can clarify better than me, but I always thought IsMissing needed a variant datatype (which can be null) to work.

Also as DBG included in his code you should declare the datatype of your function - Public Function NewContactKey(...) As Long
Go to the top of the page
 
moke123
post Dec 29 2017, 07:28 AM
Post#10



Posts: 1,278
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



just curious, Are all those arguments fields on an unbound form? what I mean is, do you have an unbound form with text boxes named Company, category, lastname, firstname, etc. and your calling your function with something like
Call NewContactKey(Me.Company,Me.Category,Me.LastName,etc ?

if so, it may be simpler to just pass the form object to your function like
CODE
Call NewContactKey(Me)


And declare your function like
CODE
Public Function NewContactKey(frm as form) As Long


then you can reference those form fields directly in your code.

CODE
            rec.AddNew
            rec!Company = frm.Company
      
            rec!Category = frm.Category
      
            rec![Last Name] = frm.LastName
      
            rec![First Name] = frm.FirstName
        
            rec![E-mail Address] = frm.EmailAddress

            rec![Job Title] = frm.JobTitle
        
            rec![Business Phone] = frm.BusinessPhone
        
            rec![Home Phone] = frm.HomePhone

            lngID = rec.Fields(0)

            rec.Update





Go to the top of the page
 
lex
post Dec 29 2017, 11:43 AM
Post#11



Posts: 706
Joined: 20-October 05



theDBguy, your suggestion worked! In short, the difference was assignment of the new key to a local var (within the "with" statement) in lieu of the assigning to the function return. I'm not sure WHY this worked. If you have any knowledge to share - I'd greatly appreciate it.

Thank you SO much. As always, you are a great benefit to all those here at UtterAccess.

Lex
Go to the top of the page
 
lex
post Dec 29 2017, 11:50 AM
Post#12



Posts: 706
Joined: 20-October 05



Hey there moke123. Thanks for all your thoughts. Those fields are in a bound form. I like your thought of passing the form ... I've never done that before. I'll remember it for next time. The more I code... the better I get.

Have a great holiday!!

Lex

Go to the top of the page
 
theDBguy
post Dec 29 2017, 11:54 AM
Post#13


Access Wiki and Forums Moderator
Posts: 72,432
Joined: 19-June 07
From: SunnySandyEggo


Hi Lex,

Glad to hear you got it working. I couldn't understand why you were getting an existing ID. I probably should have asked if you were consistently getting either the first ID or the last one. But the problem with your original setup is Access only assigns a new Autonumber as soon as the new record is dirtied. My guess was simply using .AddNew does not necessarily dirty a new record. However, it does not explain why you would get a "duplicate record" error though.

Cheers!

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
moke123
post Dec 29 2017, 01:27 PM
Post#14



Posts: 1,278
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



strange but i cant recreate that problem. Even with nothing between the .addnew and .update statements it still adds a new ID key and returns the correct value.

Just to clarify something I said earlier about ismissing.

All of your ismissing statements are returning false no matter whether they are in fact missing or not.
In order for IsMissing to return a correct value they must be declared as a variant datatype. If declared as a string it will return false every time.

All your if Statements are saying "if Ismissing is not true then..."
In other words they are not doing anything.

Also if these fields are bound fields on a bound form aren't you duplicating data?


Go to the top of the page
 
lex
post Jan 2 2018, 09:40 PM
Post#15



Posts: 706
Joined: 20-October 05



Happy New Year moke123 and theDBguy!!

moke123...Thanks for sharing re IsMissing.

I originally defined the function using variants - but changed it when I was having issues. Also, I had read that runtime is faster without variants.

There is a very long story behind why I'm doing what I'm doing... Your oversight is appreciated, so I'll try to make the story brief...

I'm wanting my system to allow users to tap into their Outlook contacts. I'm in an Exchange corporate environment and MS graciously provided a tool called ImportFromOutlook (both macro and DoCmd.RunCommand acCmdAddFromOutlook). Unfortunately, it's not well documented and doesn't work well. I've tried for hours (more like days and weeks) to get it to work correctly, and the only way I could get it to work was to (basically) follow the guidance here

https://social.msdn.microsoft.com/Forums/en...forum=accessdev

solution Proposed as answer by Geoffrey G Friday, March 29, 2013 8:20 AM

I tried other methods,

How to programmatically export Outlook items to Access

https://support.microsoft.com/en-us/help/29...items-to-access

And even using the ribbon Import>FromOutlook ...see

http://www.UtterAccess.com/forum/index.php...2046108&hl=

The ONLY one that works well, is the first above, having a separate ContactsTemp table and frmContactsTemp, and then updating my Contacts table with the new entry. This makes management a headache - but works.

So, when you say "duplicating" ... yes, I am. It's the only way to get it to function.

Back to the definition of IsMissing as variants - I'm going to have to spend some time figuring out where/how I'm going to message the user regarding minimum field entry for all entry points into my local Contact table. I'm no coding expert, but how would you form the function to accept null (or zero-length strings) and return the new key

If you have any experience with this - I'd like to chat with you regarding it.

Thank you again - and Happy New Year!

Lex
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd June 2018 - 12:47 AM