Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Update Recordset Adding Extra row

Posted on 2011-03-09
21
Medium Priority
?
508 Views
Last Modified: 2012-08-13
Hi there,

I have a contacts table which is related to the company table using FK and PK of the company ID in both tables. In other words one company can have many contacts etc. I have code which adda a new contact into the "CustomerContactsMain" table and also adds the company_id with the row as to maintain the appropriate relationship between the tables.

So the code currently adds the record as desired but also seems to be adding an extra row beneath the inserted row in the "CustomerContactsMain" table which has the same FK (company_id) and nothing else.

I need it to add only the row I have committed and not add an extra row. Could this be cause by defaults perhaps? Or is my coding all wrong.

Private Sub cmdSaveNew_Click()
On Error GoTo Err_cmdSaveNew_Click
newRec = Me.NewRecord
If newRec = False Then

    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , "", acNewRec
    DoCmd.RunCommand acCmdRefresh
    
    ElseIf IsNull(Me.txtFirstName.Value) Then
    MsgBox ("You have not entered a first name!")
    
ElseIf IsNull(Me.txtEmailAddress.Value) Then
    MsgBox ("An e-mail address is required")
Else
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.RunCommand acCmdRefresh
    
    Dim rs6 As DAO.Recordset
    Dim curDb As DAO.Database
   
    Set curDb = CurrentDb
   
 
    Set rs6 = curDb.OpenRecordset("CustomerContactsMain") 'setting rs to reference the CustomerContactsMain table
    rs6.AddNew
    rs6!company_ID.Value = txtIDReplicated.Value
    rs6.Update
    
    Set rs6 = Nothing
    curDb.Close
    newRec = False
    DoCmd.GoToRecord , "", acNewRec
    

    End If
    
    Me.Parent![sfrMain].Form.Requery
    
    
Exit_cmdSaveNew_Click:
    Exit Sub

Err_cmdSaveNew_Click:
    MsgBox Err.Description
    Resume Exit_cmdSaveNew_Click

End Sub

Open in new window

0
Comment
Question by:databarracks
[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
  • 13
  • 8
21 Comments
 
LVL 28

Assisted Solution

by:omgang
omgang earned 1000 total points
ID: 35083473
Your code is adding two new records.  See my comments below
OM Gang

Else
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.RunCommand acCmdRefresh
   
    Dim rs6 As DAO.Recordset
    Dim curDb As DAO.Database
   
    Set curDb = CurrentDb
   
 
    Set rs6 = curDb.OpenRecordset("CustomerContactsMain") 'setting rs to reference the CustomerContactsMain table
    rs6.AddNew     '<---- you add a new record here
    rs6!company_ID.Value = txtIDReplicated.Value
    rs6.Update
   
    Set rs6 = Nothing
    curDb.Close
    newRec = False
    DoCmd.GoToRecord , "", acNewRec     '<---- you add a new record here
   

0
 

Author Comment

by:databarracks
ID: 35084460
Hi there,

Sorry for the late response, I am trying your code now.
0
 

Author Comment

by:databarracks
ID: 35084493
I have removed   DoCmd.GoToRecord , "", acNewRec  and it is still doing it?
0
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 

Author Comment

by:databarracks
ID: 35084565
I have the same code for another table and it doesn't do that. It only adds the one record that I have saved. Really out of idea now, the only difference between my other table and this is that this table doesn't have a one to one relationship.
0
 

Author Comment

by:databarracks
ID: 35084607
If this helps I have attached code included on my form when the user presses the button to Go to new record which may be affecting it perhaps?
Private Sub cmdNewAdmin_Click()
DoCmd.GoToRecord , , acNewRec
newRec = True
End Sub

Open in new window

0
 
LVL 28

Expert Comment

by:omgang
ID: 35084673
databarracks, is it possible for you to post a copy of your db?  Make sure it doesn't contain any confidential information or, better yet, remove all data from all tables except for a few representative records in each.
OM Gang
0
 

Author Comment

by:databarracks
ID: 35084807
One minute almost done giving you a small sample
0
 

Author Comment

by:databarracks
ID: 35084916
Here you go, please note that the main form is the one call frmAdminMain and you will see exactly what my problem is? Thank you for your help
SampleExperts.accdb
0
 

Author Comment

by:databarracks
ID: 35085652
Any luck yet?
0
 
LVL 28

Expert Comment

by:omgang
ID: 35085766
I'm looking at it now.  The problem occurs when I create a new contact (me).  I set a break point on the first line of executable code (On Error GoTo Err_cmdSaveNew_Click) and then step through the procedure.  The test for new record
newRec = Me.NewRecord
If newRec = False Then
returns True because I am creating a new record, e.g. the record I am entering hasn't been saved yet so it's still new.  So the procedure jumps to the Else statment and proceeds.  The rs6.AddNew statement is what is actually adding the second record you see in your table.  I'm checking on the easiest fix.


Else
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.RunCommand acCmdRefresh
   
    Dim rs6 As DAO.Recordset
    Dim curDb As DAO.Database
   
    Set curDb = CurrentDb
   
 
    Set rs6 = curDb.OpenRecordset("CustomerContactsMain") 'setting rs to reference the CustomerContactsMain table
    rs6.AddNew
    rs6!company_ID.Value = txtIDReplicated.Value
    rs6.Update
   
    Set rs6 = Nothing
    curDb.Close
    newRec = False
    DoCmd.GoToRecord , "", acNewRec

OM Gang
0
 
LVL 28

Expert Comment

by:omgang
ID: 35085862
databarracks, isn't that exactly what you want the button to do?  Save the current record and create a new, blank one with the same companyID?  If not, what is your intent?
OM Gang
0
 

Author Comment

by:databarracks
ID: 35085888
Many thanks for all of your help, if you have a better way of approaching this then by all means please suggest these to me. I really would be delighted if you could help me get past this problem as I have been at it for 3 solid days to no avail.
0
 

Author Comment

by:databarracks
ID: 35085933
Yes and no. Yes I want it to save the current 'mew record' that I am committing but not insert another one straight away until I tell it to commit.

This will end up looking like this:

Contact1
,
Contact2
,
Contact3
,

This isn't ideal as when running a contacts report for the customer or viewing my contact list pain, you will have to see all the annoying"," everywhere? A contact should have a name in it hence my message box prompting the user to add one before they can save anything.
0
 
LVL 28

Expert Comment

by:omgang
ID: 35086058
So, you have a Save and New button and a Save button.  Please tell me what should happen when each of these are clicked.
Thanks,
OM Gang
0
 

Author Comment

by:databarracks
ID: 35086134
To be honest we can do away with the Save and New as I already have a Add new button. Save button should save the visible record and if the record has a new idea should add the record to the table and add the company_id in the same table.
0
 
LVL 28

Expert Comment

by:omgang
ID: 35086623
I guess I'm a little confused.  If the user is creating a new record, for a contact that doesn't already exist in the table, you simply want to save the record.  If the user is editing an existing record you simply want to save the changes.  What do you mean by "if the record has a new idea should add the record to the table...."?  Where can the user select/set the company ID?
OM Gang
0
 
LVL 28

Accepted Solution

by:
omgang earned 1000 total points
ID: 35087720
databarracks, take a look at the changes I made and let me know if this is what you want.
OM Gang
SampleExperts.accdb
0
 

Author Comment

by:databarracks
ID: 35088620
I haven't seen the database yet but the customer  doesn't choose the company as this would be a subform on the company form so it would pick the ID of the current company  displayed if that makes sense. And what I meant is that if the contact form has a new contactadmin_id then it would know that it is new and add it to the contacts table accordingly.

Please bear with me while I try your sample.
0
 

Author Comment

by:databarracks
ID: 35092967
That worked perfectly and with much simpler code I must say. Great job and sincerely appreciate all of the time spent working on this.
0
 
LVL 28

Expert Comment

by:omgang
ID: 35095138
You're welcome.  Good luck with the rest of your project.
OM Gang
0
 

Author Closing Comment

by:databarracks
ID: 35367394
very good job
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

730 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