Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-02-16
7
Medium Priority
?
664 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
[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
7 Comments
 
LVL 39

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 52

Accepted Solution

by:
Gustav Brock earned 500 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
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 
LVL 31

Expert Comment

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

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 500 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

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

650 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