Last Number

[EmmaJane]

Okay I know what I would like but not sure the best way to explain it!

We have a very high turn over of staff and clients, which generate a lot of files that we need to hold for seven years. Therefore our archive is large. I have a table in access that holds both client and staff and the box number they are placed in. I have now created a form for new entries to this table. What I would like is somewhere on this form a control that shows what the last box number was ??

I’ve done some reading on Dmax but not sure how to use it in this circumstance?

 

[o1110010]

So you want to grab the last record's value of the boxNumber field in the table? Then use that value+1 to auto-set a value on the form? Form is set Data Entry = Yes?

 

[dcx693]

How do the box numbers increment? Are they ordered? If so, then DMax is perfect for that. Use it like DMax("box_number","table_name"). You can assign that to the controlsource of the control showing the last box number.

 

[o1110010]

Are they ordered? If so, then DMax is perfect for that. Use it like DMax("box_number","table_name"). You can assign that to the controlsource of the control showing the last box number.

Even when the form is set with Data Entry = Yes? (It's not working for me.)

 

[dcx693]

Yes, the user just wanted a box with the last number given. The Dmax assumes the numbers increment. Just put it into an unbound text control.

 

[EmmaJane]

Okay all I need on the form ( which is data entry) is the highest box number; I don’t want it to add anything to this as a whole box is entered at one sitting so they just need to know what the last box was numbered.

Each box can have about 30-40 files in each and the box number is entered on each record………

 

[o1110010]

Are they ordered? If so, then DMax is perfect for that. Use it like DMax("box_number","table_name"). You can assign that to the controlsource of the control showing the last box number.

Did that work for you EmmaJane?

 

[EmmaJane]

Did that work for you EmmaJane?

No, I could get it to work but not when the form was set to data entry

 

[o1110010]

No, I could get it to work but not when the form was set to data entry

I'll bet that you'll have to use customize it in VBA. Check out this thread to see if it will help.

 

[EmmaJane]

I'll bet that you'll have to use customize it in VBA. Check out this thread to see if it will help.

Okay had a look but not really sure what I was looking at……. What (in simply terms ) did the code included in this thread do?

 

[o1110010]

Okay had a look but not really sure what I was looking at……. What (in simply terms ) did the code included in this thread do?

I just realised it may not be what you are looking for. How is the table setup? Does each boxNumber only occur once in the table? If so, then yes, that thread could be used. (And I could help explain.) If not, then I'll need a little sample of the data.

Sorry for confusing ya.

 

[EmmaJane]

No worries, just glad for the assistance.

Okay I have attached the form and the table

What I would like is when they open the form they can see what the next box number would be…..

 

[EmmaJane]

Any ideas ??

 

[charityg]

This is a little inelegant, and I assumed a couple of things.

I assumed that you wanted the box number for the employee that was entered last in the table.

I created a query to select the Top 1 record from your table with the ID ordered descending. This will select the last record entered in the table.

I used the query as the datasource for an unlinked subform at the bottom of your form to display the box number for the last record that was entered.

Let me know if my assumption was wrong.

 

[o1110010]

Any ideas ??

Hmmm. This thread wasn't marked as unread. I missed your last two posts. Sorry!

Write back when you try Charityg's solution please.

 

[EmmaJane]

Charityg…. That did it!!! Thank You,

o1110010 Thanks for sticking with me…. Got there in the end !!!!

 

[o1110010]

Charityg…. That did it!!! Thank You,

o1110010 Thanks for sticking with me…. Got there in the end !!!!

Good news. Glad it was figured out. Sorry again for not noticing.

Thank you Charityg for picking up the slack.