×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

AddNew sometimes not working

AddNew sometimes not working

AddNew sometimes not working

(OP)

I'm working on a database from another designer. This has a Customer form that summaries Orders in a subform. Highlighting an order and clicking an Orders button opens that order in a separate form that in a subform shows order items, or for new orders allows new ones to be entered. All this works fine and details of existing orders display correctly.

The problem arises with some new orders, initiated by running this code to add a new order for the current customer. OrderID in the Orders table isn't an AutoNumber so the code increments the last value used.

CODE -->

Public Sub AddRecord()
  With Me.RecordsetClone
            .AddNew
           	!CustomerID = Me![CustomerID]
           	!OrderDate = Date
           	!OrderID = DMax("OrderID", "Orders") + 1
            .Update
            .Bookmark = .LastModified
            Me.Bookmark = .Bookmark
            Me.SalesTaxRate = DLookup("[SalesTaxRate]", "My Company Information")
    End With
End Sub 

Sometimes this does not open a new blank record, but stays on the previous one even though I can see that !OrderID has incremented properly.

RE: AddNew sometimes not working

I would think you would have to requery and move to the new record.

CODE -->

Public Sub AddRecord()
   dim newID as long
   newID = DMax("OrderID", "Orders") + 1
   With Me.Recordsetclone
            .AddNew
           	!CustomerID = Me![CustomerID]
           	!OrderDate = Date
               	!OrderID = newID
            .Update
    End With
    me.requery
    me.recordset.FindFirst "OrderID = " & newID
    Me.SalesTaxRate = DLookup("[SalesTaxRate]", "My Company Information")
End Sub 

RE: AddNew sometimes not working

(OP)
Thanks MajP, tried this and it produced an unexpected result. Instead of opening a new blank record it showed the earliest numbered one for the Customer. Debug.Print showed that newID was indeed the one that should have been showing, but for some reason the wrong one was being displayed. Using the navigation buttons did allow the new one to be displayed but this wouldn't be user friendly.

I suspect something else is causing this so am going back a stage.

RE: AddNew sometimes not working

When you say a new blank record do you actually mean the newly created record with
orderID = DMax("OrderID", "Orders") + 1
CustomerID = Me![CustomerID]
OrderDate = Date
?

So the form should move to the new orderID. It might not be moving because there is a problem in the findfirst statement or the newID was never created. Can you verify it actually finds the newID.

CODE -->

Public Sub AddRecord()
   dim rs as dao.recordset
   dim newID as long
   set rs = me.recorset
   newID = DMax("OrderID", "Orders") + 1
   With RS
            .AddNew
           	!CustomerID = Me![CustomerID]
           	!OrderDate = Date
               	!OrderID = newID
            .Update
    End With
    me.requery
    Rs.FindFirst "OrderID = " & newID
    if Rs.Nomatch then
      Msgbox "Could not find OrderID = " & newId
    end if
    Me.SalesTaxRate = DLookup("[SalesTaxRate]", "My Company Information")
End Sub 

RE: AddNew sometimes not working

(OP)

Yes, what I need is a new order with the details subform blank but these values added

orderID = DMax("OrderID", "Orders") + 1
CustomerID = Me![CustomerID]
OrderDate = Date

I've tried your new procedure and it looks very good so far! I notice that on clicking my Add New Order button it does a blink with the current order still visible then moves to a new one ready for order details to be added. Stepping through the code it's the line

CODE -->

Rs.FindFirst "OrderID = " & newID 

that is doing this. I'll report back if still running into difficulties but many thanks so far.

RE: AddNew sometimes not working

The blink is because you create a new record and then requery. So the requery will take you to the first record and then move to the last.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close