Solved

Why form won't save current record and go to a new record?

Posted on 2014-02-16
7
651 Views
Last Modified: 2014-02-26
Please see my code.  I cannot figure out why my form won't save the current record and go to a new record.  ???

Private Sub Command133_Click()

        If Nz(Me.txtDate, "") = "" Then
        MsgBox "You must enter a date."
        Me.txtDate.SetFocus
        Exit Sub
        Else
        If Nz(Me.cboToLocation, "") = "" Then
        MsgBox "You must enter a To Location."
        Me.cboFromLocation.SetFocus
        Exit Sub
        Else
        If Nz(Me.txtProductID, "") = "" Then
        MsgBox "You must select a Product ID."
        Me.txtProductID.SetFocus
        Exit Sub
        Else
        If Nz(Me.txtQty, "") = "" Then
        MsgBox "You must enter a quantity."
        Me.txtQty.SetFocus
        Exit Sub
        Else

        End If
        End If
        End If
        End If

        If (MsgBox("Are you sure you want to transfer the inventory quantity of " & txtQty & " from " & txtFromName & " to " & txtToName & ".", vbYesNo)) = vbNo Then
        Cancel = True
        Else


        Dim RS As DAO.Recordset
        Set RS = CurrentDb.OpenRecordset("tblInventoryDetails")
        
        'Code to add negative value to tblInventoryDetails
        RS.AddNew
        RS!TranxDate = Me.txtDate
        RS!SoldQty = Me.txtQty
        RS!ProductID = Me.txtProductID
        RS!LocationID = Me.cboFromLocation
        RS!InvTransfer = True
        RS.Update
            
        'Code to add positive value to tblInventoryDetails
        RS.AddNew
        RS!TranxDate = Me.txtDate
        RS!IncomingQty = Me.txtQty
        RS!ProductID = Me.txtProductID
        RS!LocationID = Me.cboToLocation
        RS!InvTransfer = True
        RS.Update
    
        MsgBox "The inventory has been transferred."
 
        RS.Close
        Set RS = Nothing

        End If
        
        DoCmd.GoToRecord , "", acNewRec

End Sub

Open in new window

0
Comment
Question by:SteveL13
7 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
The string of If's used for editing should be independent rather than nested but that isn't causing a problem unless the nesting is incorrect.  Your formatting hides that though.  When you nest If's, each level should be indented from its parent to show the structure and relationship.

Put a breakpoint in the code and step through it to see where it is going wong.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
Comment Utility
You are writing and saving two records in another table with values from the current (unbound?) form, so from what you show here, there is nothing to save.

/gustav
0
 

Author Comment

by:SteveL13
Comment Utility
You are correct. But I can't figure out why the form won't go to a new record with the code at the bottom.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 30

Expert Comment

by:hnasr
Comment Utility
Upload a sample database.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
DoCmd.GoToRecord , "", acNewRec
Goes to a new EMPTY record.  Were you expecting it to do something else?  Saving the two records and repositioning the form are separate actions.  You originally said the records were not being saved which sounded like you were talking about the ones the code writes.  Now it sounds like you are talking about what is visible on the form.  If the form isn't bound, there is no place defined to save the record to.

Please restate your problem so we know if you are having 1, 2, or 3 problems.
1.  Is the current form bound?
2.  Are you expecting that record to be saved?  If the form is bound it should be when you move to a new record using the GoToRecord method.
3.  Again - put a stop in the code to be sure that nothing is being skipped and step through it.
0
 

Author Comment

by:SteveL13
Comment Utility
1.  Is the current form bound?

  No.  And it was incorrect for me to indicate that I wanted it to save anything since the new records are being written by the code which is how I want it to work.

2.  Are you expecting that record to be saved?  

it was incorrect for me to indicate that I wanted it to save anything since the new records are being written by the code which is how I want it to work

If the form is bound it should be when you move to a new record using the GoToRecord method.

3.  Again - put a stop in the code to be sure that nothing is being skipped and step through it.

I will.  Thanks.
0
 
LVL 26

Assisted Solution

by:Nick67
Nick67 earned 250 total points
Comment Utility
DoCmd.GoToRecord , "", acNewRec

Isn't going to do anything at all on an unbound form.
There are no records to go to.
If the record is being saved by code, the controls need to be emptied by code as well

I'd definitely replace the nested if's with a Select Case True

Select Case True
    Case Something = Bad
         'some action
         'exit sub
    Case AnotherThing = bad
         'some action
         'exit sub
End Select


Easier to extend, simpler to read, less chance of error.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now