Need help please! (1 Viewer)

Hutchy · Jun 28, 2013

I'm new to vba coding and I would like some assistance please.

I want whenever I'm updating or adding records to my form, the ID automatically take the value of the previous ID and increment it by 1.

The field type is text (mixed with number) - PM0000000.

I've done some research, what I understand is that I need to:
-do a lookup and
-find the MAX of the number portion.

Name of form - Payment

Name of table - Payment

Name of field - payment_id

------------

I tried these, but to no avail...

Private Sub payment_id_Click()

payment_id = DLookup(("[payment_id]", "Payment", "[payment_id]=Forms![Payment]![payment_id]-1")payment_id + 1)

End Sub

--------------

Private Sub payment_id_Click()

payment_id = "PM" & 000000 + 1

End Sub

--------------

MarkK · Jun 28, 2013

I strongly recommend that you not store the "PM" string and only store the number. Ideally, set the ID field type to Autonumber, which is a long integer that auto-increments at the table level. This is much more reliable and much simpler. Storing the string "PM" with your numeric code requires that you always parse it out at retrieval time, and tack it on at storage time, and it always slows all your searches, sorts and joins.

Also, use DMax() rather than DLookup() to find the highest value in a field.

Hutchy · Jun 28, 2013

The reason for me using that format is because I have other IDs in the form and I wanted to differentiate among them.

I'm considering your advice, but what if the field is indexed, wouldn't that make it faster?

MarkK · Jun 28, 2013

Yeah indexing will make it faster, but indexing won't change the fact you have to parse out that "PM" every time you want to work with the number, and then you have to concatenate it back in for every record you add. That's just extra work for zero value.

Hutchy · Jun 28, 2013

OK. Changing it.
Thanks for the assistance.

jdraw · Jun 28, 2013

Just curious -- what exactly is the PM for?
Since the identifier is in the Payment table, don't you get all the info you need from the number even without the PM?

Hutchy · Jun 28, 2013

Yes I do.

Payment for short PM.
I used it because I'll have collector id and vendor id in the same form and they'll all be indistinguishable.

I guess I'll have to make them all autonumber, seeing as placing distinguishable acronyms before the numbers is a drag.

EDIT

Private Sub payment_id_Click()

payment_id = "PM" & 000000 + 1

End Sub

The above code result in this PM1, but each id would be the same. Hence I wanted a code that would retrieve previous number and increment it.

MarkK · Jun 28, 2013

If you want to tack on that "PM" when you write that number on a report, that is super easy. Like, in a query you can just do . . .

Code:

HumanReadableID: "PM" & Format(PaymentID, "00000")

. . . as an option for a field in addition to the PaymentID field.

Hutchy · Jun 28, 2013

Thanks.

Here's what I did...

I created a new field and gave it the autonumber type with this format...
"PM"#

EDIT

I found this code and it helped! --> LINK

Need help please! (1 Viewer)

Hutchy

Registered User.

MarkK

bit cruncher

Hutchy

Registered User.

MarkK

bit cruncher

Hutchy

Registered User.

jdraw

Super Moderator

Hutchy

Registered User.

MarkK

bit cruncher

Hutchy

Registered User.

Similar threads

Users who are viewing this thread