Solved

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

Posted on 2014-02-16
7
658 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 35

Expert Comment

by:PatHartman
ID: 39862951
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
ID: 39863071
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
ID: 39863076
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 30

Expert Comment

by:hnasr
ID: 39863105
Upload a sample database.
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 39863122
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
ID: 39863163
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
ID: 39869164
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

786 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