Enter new number in sequence in PO

[tchirpich]

Is this Form ONLY for Data Entry?



If so - then it should not be a problem.

Yes it is only for Data entry, it stores the information in the table.

[Robeen]

You will have to do some work on your Form before this will work correctly for you - but I think it answers your initial question.

I added this code to the On Lost Focus Event of the PurchaseOrderID field [using Alan's formula]:

 
Private Sub PurchaseOrderID_LostFocus()
 
Me.PurchaseOrderNumber.SetFocus
Me.PurchaseOrderNumber.Text = Nz(DMax("[PO Number]", "[Purchase Orders]") + 1, 0)
 
Me.SupplierID.SetFocus
 
End Sub

Now - when you create a New Record - Click on the PO ID field and then hit your Tab key.
The next PO Number in the sequence will be put into the PO Number Box and the focus will go to the Supplier field.

I'm not sure how you want your Form to work - but one way I have used when i want to automatically increment a value on a Form when a new record is created is by creating an 'Add New Record' button and then putting the code in there.

If there is a way to put code behind the New Record ( >*) button on the Access built-in Record Navigation bar of the Form - I don't know how to do that.

I'm not sure this totally resolves your issue but one of the others may be able to help as well.

[Robeen]

Try this instead:

 
Private Sub PurchaseOrderID_LostFocus()
If Me.NewRecord Then
 
    Me.PurchaseOrderNumber.SetFocus
    Me.PurchaseOrderNumber.Text = Nz(DMax("[PO Number]", "[Purchase Orders]") + 1, 0)
 
    Me.SupplierID.SetFocus
End If
End Sub

This worked a little better for me.
I didn't think of the Me.NewRecord syntax in my previous post.

I hope this helps!

[tchirpich]

Is this Form ONLY for Data Entry?

If so - then it should not be a problem.

It is for DATA Entry and stores it in a table named purchase orders.
I am not sure I have everything correct as I am still learing Access.
I just kind of put this together to keep track of our hand written purchase orders, so I have a record and can be able to search for a PO when I need to reference back to it.

Tim

[Robeen]

The more you work with it - the better you'll get.
You'll find that you can do a LOT with your data using Access.

If you will be using this database to search for things a lot - then you might as well try & create a search form when you get a chance.
The advantage will be that you can make the search form read-only & there won't be any danger of you [or someone else] deleting or modifying data by accident.

You will find plenty of help from very competent Access developers on this site. I have almost always got very good answers.

Let us know if you have any more questions.

All the best!

[tchirpich]

Where would I insert this information.
Or can you send my corrected Database back
to me???

Tim

Try this instead:

 
Private Sub PurchaseOrderID_LostFocus()
If Me.NewRecord Then
 
    Me.PurchaseOrderNumber.SetFocus
    Me.PurchaseOrderNumber.Text = Nz(DMax("[PO Number]", "[Purchase Orders]") + 1, 0)
 
    Me.SupplierID.SetFocus
End If
End Sub

This worked a little better for me.
I didn't think of the Me.NewRecord syntax in my previous post.

I hope this helps!

[Robeen]

1. Open your Purchase Orders form in design mode.
2. Select the PurchaseOrderID field.
3. Open the Property Sheet and click on the Event tab.
4. Click in the row beside 'On Lost Focus'.
5. Click the elipsis [...] to the right of the row.
6. Select 'Code Builder' and click ok.
7. Paste this:

If Me.NewRecord Then

Me.PurchaseOrderNumber.SetFocus
Me.PurchaseOrderNumber.Text = Nz(DMax("[PO Number]", "[Purchase Orders]") + 1, 0)

Me.SupplierID.SetFocus
End If

between the 'Private Sub PurchaseOrderID_LostFocus()' and the 'End sub'.

Now - when you are actually running your Form - when you start a new record, click in the PurchaseOrderID field, and then Tab off that field - you will get the incremented number in your PO Number field.

Let me know if it works.

[tchirpich]

For some reason it is not working for me, I am so sorry, it should be simple, but I can not get it to work. I tab off the PO ID field andit says new, but the po number is still blank. What can I be doing wrong?
I did what you actually instructed of me. If you get it to work on the file I sent to you then I should be able to do the same here, but I just can not get it to work. There is something I am not doing correctly.
Please do not get frustrated with me, I am trying my best.

Tim

1. Open your Purchase Orders form in design mode.
2. Select the PurchaseOrderID field.
3. Open the Property Sheet and click on the Event tab.
4. Click in the row beside 'On Lost Focus'.
5. Click the elipsis [...] to the right of the row.
6. Select 'Code Builder' and click ok.
7. Paste this:

If Me.NewRecord Then

Me.PurchaseOrderNumber.SetFocus
Me.PurchaseOrderNumber.Text = Nz(DMax("[PO Number]", "[Purchase Orders]") + 1, 0)

Me.SupplierID.SetFocus
End If

between the 'Private Sub PurchaseOrderID_LostFocus()' and the 'End sub'.

Now - when you are actually running your Form - when you start a new record, click in the PurchaseOrderID field, and then Tab off that field - you will get the incremented number in your PO Number field.

Let me know if it works.

[Robeen]

Don't worry. Like I said - it is not working perfectly.

Let me try running it again and I'll get back with you and tell you how you can see it at work.

I'm a little busy right now . . .

[tchirpich]

Thank you
I really appreciate this.

Tim

Don't worry. Like I said - it is not working perfectly.

Let me try running it again and I'll get back with you and tell you how you can see it at work.

I'm a little busy right now . . .

[Robeen]

I just entered three new orders into your Purchase Orders form - like this:

1. Run the Form.
2. Click the New (blank) Record button at the bottom of your form [>*].
3. If the cursor is not in the PO ID field then - Make sure to click in that field.
4. Press the Tab key on your keyboard.

When I do step 4 [press Tab key while the cursor is in the PO ID field] - I get the new PO Number automatically inserted in the PO Number field.

Is this exactly what you are doing?

If you are still not getting it to work - paste your full code here - inclucing the 'Private Sub . . . and the 'End Sub'.

[tchirpich]

I think that I am doing it correctly, I have sent you the whole database again. See if it was you have.
Can you send back the corrected database that you are using so I can just use that one???
Tim

I just entered three new orders into your Purchase Orders form - like this:

1. Run the Form.
2. Click the New (blank) Record button at the bottom of your form [>*].
3. If the cursor is not in the PO ID field then - Make sure to click in that field.
4. Press the Tab key on your keyboard.

When I do step 4 [press Tab key while the cursor is in the PO ID field] - I get the new PO Number automatically inserted in the PO Number field.

Is this exactly what you are doing?

If you are still not getting it to work - paste your full code here - inclucing the 'Private Sub . . . and the 'End Sub'.

[tchirpich]

Private Sub PurchaseOrderID_LostFocus()
If Me.NewRecord Then
Me.PurchaseOrderNumber.SetFocus
Me.PurchaseOrderNumber.Text = Nz(DMax("[PO Number]", "[Purchase Orders]") + 1, 0)
Me.SupplierID.SetFocus
End If

End Sub

I think that I am doing it correctly, I have sent you the whole database again. See if it was you have.
Can you send back the corrected database that you are using so I can just use that one???
Tim

[Robeen]

Hi Tim!

I just opened the 2nd db you sent and ran it.

You did everything just like I told you to.

Here's what I did:
From the form 'Main Screen' I clicked 'Browse Purchase Orders in form'.
When the Purchase Orders form opened, I clicked the New (blank) record button - and the focus went to the Po ID text box - which now said '(New)'.
I pressed the Tab key and the PO ID changed [the latest PO ID is now 25], the PO Number appeared in the PO Number box, and the cursor was in the Supplier box.
I selected an Supplier and Tabbed through the Form selecting Employee [you ! ] and Shipping Method.
Then I kept Tabbing till the cursor went back to the PO ID box.
When it did - there was (New) in the PO ID box again.
Then I tabbed off that field and the next PO Number showed up and the cursor went to the Supplier box again.

Then I closed out of the forms and opened the Purchase Orders Table. All the data I had just put in was in there.

Your Form is working like I expected.

Are you expecting something different?

[tchirpich]

Well it will not work that way for me, I have tried everything, I do not know what is going on. I click on the main screen create new purchase order and it comes up with the cursor in the field for PO id and says new, and I tab off of that and it goes right into the PO number , but nothing is in there. then I even tab past that to supplier. I have even tried it your way by clicking on browse forms and clicking the new entry, but it does the same things. I know something isn;t just right. Is it possible to send me the dbase back to my email of tim@generaltruckparts.com???
with the way that you are using it.???? I can't pinpoint what is different.
I really appreciate what you are trying to help me out with. When you tab off of the PO ID what po number does it put in for you?? mine wont even put in a number. I have to manually add them.

Tim

Hi Tim!

I just opened the 2nd db you sent and ran it.

You did everything just like I told you to.

Here's what I did:
From the form 'Main Screen' I clicked 'Browse Purchase Orders in form'.
When the Purchase Orders form opened, I clicked the New (blank) record button - and the focus went to the Po ID text box - which now said '(New)'.
I pressed the Tab key and the PO ID changed [the latest PO ID is now 25], the PO Number appeared in the PO Number box, and the cursor was in the Supplier box.
I selected an Supplier and Tabbed through the Form selecting Employee [you ! ] and Shipping Method.
Then I kept Tabbing till the cursor went back to the PO ID box.
When it did - there was (New) in the PO ID box again.
Then I tabbed off that field and the next PO Number showed up and the cursor went to the Supplier box again.

Then I closed out of the forms and opened the Purchase Orders Table. All the data I had just put in was in there.

Your Form is working like I expected.

Are you expecting something different?