?
Solved

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

Posted on 2014-02-16
7
Medium Priority
?
665 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 40

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 31

Expert Comment

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

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

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …
Suggested Courses

839 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