Create Cargo Code with str-date-001 2

[TimTang]

Hi All,

I've got a relatively simple problem but my VB still isn't up to snuff.

I would like to create a Cargo Code using cgo-0708-001 where cgo is just a descriptive string, 0708 is the current month and year, and 001 is incremented with each new record unless the month changes; In which case the number would return to 001.

I can do it in deisel or lisp but my VB is still a little weak. With out error traps, variable definitions, or record sets, The Algorithm would be something like this:

1. Find last strCargoCode
2. Isolate the last 3 char of the code assign it to LastCode
3. Isolate 2 char month from last strCargoCode assign it to Month1
4. Isolate 2 char month from current date assign it to Month2 as string
5. If Month1 not equal Month2
6. Then NEWstrCargoCode = concant "cgo-" & "(current)MMYY" & "-" & "001"
7. Else NEWstrCargoCode = concant "cgo-" & "(current)MMYY" & "-" & "(LastCode+1)"
8. End If
9. Me.strCargoCode = NEWstrCargoCode

The problem I'm having is that the concantenation involves a string, a date converted to a string, and a number converted to a string. While I'm read many example on this site, I'm still having a little trouble getting up to speed with VB syntax.

The reason I'm using this configuration as a code is because a cargo is not an entity that you can really identify with except for the date it entered the database and it becomes redundant very quickly. I would like to eventually purge any cargo records that are more than 3 months old. The code should make it easy to accomplish that.

If there are string tutorials that someone can recommend that would be great.

Thank for any assistance.

Cheers!!

 

[Golom]

Untested but try something like

Dim cgoString As String
Dim NewCgo    As String
Dim MaxCgo    As Variant

[COLOR=black cyan]' Set the first part of the string for current month & year[/color]
cgoString = "cgo-" & Format(Date, "mmyy") & "-"

[COLOR=black cyan]' Find the record with the maximum value starting with that string.[/color]
[COLOR=black cyan]' "CargoCode" is the field in the table.[/color]
[COLOR=black cyan]' "tblCargo" is the table containing the field.[/color]
MaxCgo = DMax("CargoCode", "tblCargo", _
              "Left(CargoCode,9)='" & cgoString & "'")

[COLOR=black cyan]' The new string ends in "001" if the MAX doesn't exist[/color]
[COLOR=black cyan]' and is incremented by one if it does.[/color]
NewCgo = cgoString & IIF (IsNull(MaxCgo), "001", _
                          Format(Val(Right(MaxCgo,3))+1,"000")

 

[TimTang]

WOW!

Thanks Golom!

I'll give this a try tomorrow. Even if it doesn't work this will be an excellent tutorial.

Cheers!!

 

[TimTang]

Hi Golom,

I tried your code and it's almost there. It stops at the very last line if I select the new-record button on the bottom of the form. The DMax value is null all the time so it doesn't seem to be picking the maximum record.

If I go to the last record manually which is also the maximum record, it works!.. by incrementing the existing record but it doesn't go to a new record. It only increments which ever cargo code I double click on. I can go to any cargo number and it will increment the same record but never really find the maximum record; it's using the current record for some reason.

I'm using doubleclick as the event but I want to use the new-record button at the bottom. Where would you find that event? Maybe that's creating a problem?

I created a table with fake code for last monthe "CGO-0806-xxx" to force MaxCgo to null and force the last three digits to "001" but I get a "Invalid use of Null" error.

I've learned more in one day studying this little example than about 2 weeks of reading. Thanks for your help; it's almost there.

Cheers!!

 

[PHV]

Use the BeforeInsert event procedure of the form.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TimTang]

Thanks PHV,

I tried that but it doesn't fire up the code. I just get a blank form with no Cargo Code.

Cheers!!

 

[PHV]

it doesn't fire up the code
Put a MsgBox to be sure.
BTW, is the form bound ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TimTang]

PHV,

The table is bound to tblCargo and the text box containing the Cargo code is bound to the field strCargoCode.

Cheers!!

 

[docjohn52]

I think the code is dying at "IIF" in the last line should be "IF"

Nice chunk of code Golum, I've been using

Right("000" & ltrim(str(x)),3)


John

 

[TimTang]

Hi docjohn52,

Golum is using the IIF function because it's more efficient than splitting the code into if-then-else-end if. In a single statement he is doing what would otherwise take 4 lines of code.

iif ( condition, value_if_true, value_if_false )

I was wondering what IIF was too so I looked it up. I'll be using this more often now that I know it exists.

I think the problem is with the way Access handles "Null". I've read a few articles by experts that say this was one of the first mistakes Microsoft made when designing Access and they haven't fixed it yet.

Some how I've got to incorporate "Nz" into the code rather than "Null". Nz returns a zero if an expression or field is null. From what I have read, Access has more success and is more predictable when dealing with a "0" instead of "Null"

I tried:

NewCgo=cgoString & IIf(("(Nz(MaxCgo))" = "0"),...

And:

NewCgo=cgoString & IIf(((Nz(MaxCgo))=0),...

But niether worked.

Anyone have any ideas.

Cheers!!

 

[Golom]

...But neither worked. ...

... and what happened?

- Wrong value returned?
- Error?
- No Value?

In the specific instance of the line of code that I posted, the issue is whether or not the returned value from DMax was NULL. Converting the potential NULL to a zero (or whatever) is quite possible but probably not relevant. Simply put, if it was NULL then we want to do one thing, if not, then a different thing.

Granted, Access handling of NULL is problematic at times but NULL is something that is defined in the Relational Model. It is not just a Microsoft invention. You will find this debate about the "proper" treatment of NULL to be more or less universal in the world of relational databases ... not just Access.

 

[TimTang]

Sorry Golom,

I'm not critisizing your code or Microsoft in general. I'm just really excited about the new things that you've taught me. You took a 9 line algorythm and reduced to 3 lines of code. I like that, and I want to be able to do the same.

Some things don't work, but the fun is in trying to make it work.

MaxCgo works when it has a value, but fails when Null.

Also...I don't know where to execute the code. I want it to execute when the new record button is hit, but there doesn't seem to be an event that covers that.

Cheers...and thanks for the code and the educational value within it!!

 

[TimTang]

Hi Golom,

I've been out of the loop for a while. I'm trying to learn a new job and Access at the same time, so I've been pretty busy.

The database (while it's not quite state-of-the-art) works surprisingly well and is actually producing results. It could be a lot better but it will take time as I learn more about Access. I'm trying to tidy it up and make it more efficient.

To answer your previous question the error I get is "invalid use of Null"

I tried the "Nz" but it doesn't seem to return a zero or at least be equal to zero.

I might be able to make it work with a if-then-else-end scenario but I'd like to make your code work because it's clean and more efficient.

Cheers!!

 

[PHV]

the error I get is "invalid use of Null"
Which line of code raises this error ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TimTang]

Hi PHV,

This is the code that Golom provided. It works fine if the month never changes but when it does the second line sets MaxCgo to Null; then the last line generates the error "invalid use of Null"

Dim cgoString As String
Dim NewCgo    As String
Dim MaxCgo    As Variant

cgoString = "cgo-" & Format(Date, "mmyy") & "-"

MaxCgo = DMax("CargoCode", "tblCargo", _
              "Left(CargoCode,9)='" & cgoString & "'")

' The new string ends in "001" if the MAX doesn't exist
' and is incremented by one if it does.

NewCgo = cgoString & IIF (IsNull(MaxCgo), "001", _
                          Format(Val(Right(MaxCgo,3))+1,"000")

The other problem I'm having is that I want to trigger the code when the "New Record" or the "*" button at the bottom of the form is clicked, but there doesn't seem to be a way to attach an event to that particular control, at least I can't find it.

I like the code, and I learned something new on each line which is pretty good educational value.

Cheers!!

 

[PHV]

In the BeforeInsert event procedure:

Dim cgoString As String
Dim NewCgo    As String
Dim MaxCgo    As Long
cgoString = "cgo-" & Format(Date, "mmyy") & "-"
MaxCgo = Nz(DMax("Right(CargoCode,3)", "tblCargo", _
              "Left(CargoCode,9)='" & cgoString & "'"), 0)
NewCgo = cgoString & Format(MaxCgo + 1, "000")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TimTang]

ALRIGHT!! PHV

You are entitled to 3 free beers on me the next time you're in Bangkok.

But for the time being all I can give you is a star.

I had to change the cgo to uppercase and the date format to yymm, but otherwise it worked first try.

Thanks a lot man! Between you and Golom I've learned more in this little exercise than a month of reading. People like you Guys restore my faith in Humanity.

Cheers!!