General discussion

Locked

Look in field on form

By lwwilliams ·
How can I get Access to look for the last number in a field on a form and add one to that number? (Ex: 258 would become 259)

This conversation is currently closed to new comments.

10 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by sgt_shultz In reply to Look in field on form

technet.microsoft.com has articles how to do a counter in vb code. which is what i think you want.
you would hook macro doing the incrementing to an event on your form (like updating the field maybe). the vb code would look something like this i think:
dim counter
counter = formname!fieldname
counter = counter + 1
formname!fieldname = counter

you will have to add a form refresh to see the change...many free sample forms are available along with the vb help file explaining how they work on the second Office installation cd i think in the tools folder.

Collapse -

by lwwilliams In reply to Look in field on form

Thanks, I'll give it a try.

Collapse -

by lwwilliams In reply to Look in field on form

I tried using the code but, I wasn't able to get it to work. We don't have the second Office installation cd here so I wasn't able to look at the free sample forms. Do you know of anything else that I can try?

Collapse -

by jesus h In reply to Look in field on form

All you have to do is use Dmax to get the highest number, add 1 to it and then assign it to your new record

DLast will give you the last record
Dmax will give you the highst value

The code might look something like this:

Private Sub Freight_DblClick(Cancel As Integer)
Dim curY
curY = DLast ("[Freight]", "Orders")
' Add a New Record
DoCmd.GoToRecord , , acNewRec
' assign new value to
Forms!Frm_Orders!Freight = curY + 1
End Sub

Collapse -

by lwwilliams In reply to Look in field on form

Thanks Jesus, I'm a bit of a rooky at this so I'm not quite sure of how to replace your database information with mine in the code. The name of my db is Cooler, the form name is Assembly and the field name is Rework Number. The datatype for that field is text.

Collapse -

by jesus h In reply to Look in field on form

In the third line replace "Assembly" with the table's name, and "Rework Number" with the name of the field in that table.

if the table's name is "Assembly" (same as the form)
and field's name is "Rework Number" (same as the form)

the code should look like:


Private Sub Rework_Number_Click()
Dim curY
curY = DLast("[Rework Number]", "Assembly")
' Add a New Record
DoCmd.GoToRecord , , acNewRec
' assign new value to
Forms!Assembly![Rework Number] = curY + 1
End Sub


Note, it executes the code once you click on the field "Rework Number" ( Private Sub Rework_Number_Click() )

Collapse -

by sgt_shultz In reply to Look in field on form

rookie is ok. helpless is not.
sigh.
here you go:
ACC2000: How to Use DAO to Programmatically Add an AutoNumber Field to a Table
ACC2000: How to Use DAO to Programmatically Add an AutoNumber Field to a Table

the sample forms are free for download, did you have a look?
my home page is support.microsoft.com

Collapse -

by sgt_shultz In reply to

oops. meant for the link to get in there:
http://support.microsoft.com/kb/210405/en-us

Collapse -

by sgt_shultz In reply to

this one might be better:
ACC2000: How to Create a Multiuser Custom Counter
http://support.microsoft.com/kb/210194/en-us

i think this was the first thing i learned how to do in vba. i think it took me 6 months (rdl) after that it was all quite a bit easier.

Collapse -

by sgt_shultz In reply to

ACC2000: Referring to a Field in the Previous Record or Next Record
http://support.microsoft.com/kb/210504/en-us

Back to Web Development Forum
10 total posts (Page 1 of 1)  

Related Discussions

Related Forums