Solved

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

Posted on 2014-02-16
7
657 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
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 30

Expert Comment

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

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

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.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
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…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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.

895 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

15 Experts available now in Live!

Get 1:1 Help Now