VBCode Help for Beginner

[Tom Keane]

Okay, this is probably real simple stuff for you guys. I know its simple, I
used to know some of this stuff when I was learning Visual Basic in school,
but yeah. Only a couple of things.

Firstly, I want to know how to extract an integer from a string. For
example, I would have a string, that says perhaps "LGK1111", and I want to
just extract the 1111 and put it in an integer variable.

The second is how to get the number of records in a table and stick it in a
variable, I kinda want to do one of those "For i = 1 to
ThingForNumberO fRecords".

If I could get the simplest ways of doing this with VBCode I would be very
greatful!
Thank you,

Tom Keane

[Albert D. Kallal]

Re: VBCode Help for Beginner

>For[color=blue]
> example, I would have a string, that says perhaps "LGK1111[/color]

Actually, the above can be quite difficult if you don't define the above
problem. Is there ALWAYS going to be 3 letters that we must first skip, or
what about:

1abc2def3

Do we want the above to return 123? As you can see, this kind of problem can
get quite tricky quite fast. I would suggest that we simply first build a
function that REMOVES all non numbers, and THEN we convert to a integer.
Thus our code could go:

dim intResult as integer
dim strNumberPart as string

strNumberPart = OnlyNumbers("LG K1111")
intResult = strNumberPart

ms-access will in fact convert the string number to a integer number when
you "stuff" the above into the intResult variable. Of course, we still need
to define our custom function that only returns numbers. And, I want to
STRESS that if we ALWAYS knew that the first 3 digits would be letters, then
we could dump the function and use:

strNumberPart = mid("LGK1111",4 )
intResult = strNumberPart

However, here is a custom funciton we can place in a stardard module, and
use it anywhere in the appction:

Public Function OnlyNumbers(myp hone As String) As String

Dim i As Integer
Dim mych As String

OnlyNumbers = ""

If IsNull(myphone) = False Then

For i = 1 To Len(myphone)
mych = Mid$(myphone, i, 1)
If InStr("01234567 89", mych) > 0 Then
OnlyNumbers = OnlyNumbers & mych
End If
Next i
End If


End Function

[color=blue]
>The second is how to get the number of records in a table and stick it in a
> variable[/color]

You can go

dim lngReocrdsInTab le as long


lngReocrdsInTab le = dcount("*","Nam eOfTable")


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

http://www.members.shaw.ca/AlbertKallal

[Tom Keane]

Re: VBCode Help for Beginner

> strNumberPart = mid("LGK1111",4 )[color=blue]
> intResult = strNumberPart[/color]

Yes, the String in question always has the same three letters at the
beginning. I actually found similar code on the net. Thanks though ;)
[color=blue][color=green]
> >The second is how to get the number of records in a table and stick it in[/color][/color]
a[color=blue][color=green]
> > variable[/color]
>
> You can go
>
> dim lngReocrdsInTab le as long
>
>
> lngReocrdsInTab le = dcount("*","Nam eOfTable")
>[/color]

Thanks alot for that. But perhaps you could help me further if I elaborate a
little bit. I have two tables, one is called "Jobs" and the other is
"Customer Details". Customer Details is linked to Jobs thru a primary key in
Jobs called "JobNo". Anyways, I have a form that displays each job, and for
each record it displays the records in Customer Details (as a subform)
specific to the job number. So, say I went to JobNo 500, it would display
records for say 10 people. There is a field in Customer Details called
"WCCertNo". Each certificate number, when entered, is in the format
"LGKxxxx" where "x" are numbers.

I have a command button in the Jobs form called cmdAllocate and what I want
to do is to go through each record in Customer Details to find the highest
number value in WCCertNo, so I can then go and allocate new numbers for the
job I have selected.

I hope that makes sense. Your help would be GREATLY appreciated ;)

Thank you,
Tom

[celtic_kiwi]

Re: VBCode Help for Beginner

One simple way is to use two fields. Have one field as text and the
other as an autonumber. Whenever you display the fields in a form or
report combine them to give the illusion it is one text field.

Simpler
Less Coding
Less maintenance

GJ

[Albert D. Kallal]

Re: VBCode Help for Beginner

So, what you will do is put the following code in the sub-forms ON insert
event:

Also, make sure the WCCertNo has a index on it.

The code we will place in the on-insert event will automatically set the
next number....

dim strMax as string
dim lngMax as long

strMax = dmax("WCCertNo" ,"[customer details"])

lngmax = right(strmax,4)

lngmax = lagmax + 1

strMax = "LGK" & lngmax

me!WCCertNo = strMax

The above code will thus automatic enter the WCCertNo if there is not one.
All you have to do is start typing in the sub-form,a nd the WCCertNo will
appear.

It should be noted if this is/will be a multi-user applcaiton, then the
above code would need to be moved to the before udpate event, and the
WCCertNo will not (can not) appear untill the user is done working on that
reocrd. (the reason for this is that two users working would get the same
number, as the current roecrd has NOT been saved.)..

Anway, give the above code and the on-insert idea a try....


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

http://www.members.shaw.ca/AlbertKallal

[Albert D. Kallal]

Re: VBCode Help for Beginner

"celtic_kiw i" <gavin.jolly@gm ail.com> wrote in message
news:1108642192 .122844.111880@ g14g2000cwa.goo glegroups.com.. .[color=blue]
> One simple way is to use two fields. Have one field as text and the
> other as an autonumber. Whenever you display the fields in a form or
> report combine them to give the illusion it is one text field.
>
> Simpler
> Less Coding
> Less maintenance[/color]

I have to 100% agree with the above, however, while it is less work, it is a
(bad) mistake to use any kind of auonumber number for humans to consume.
Autonumbres are not to be given meaning, nor are they to be seen, or used by
users in anyway, shape, or form.

You can read about this tip (#7)..and a few others here:

http://www.mvps.org/access/tencommandments.htm

Of course, the main problem with autonubmers is they are numbers that can
change, get reset (by mere compacting), and if you start to add a record,
but then delete it..the autonumber is used up. Autonumbers are internal
pointers just like memory pointers in word...somethin g that the developers
can use, but not to be of any use by the end users. So, autonumbers are
great for setting relationships between tables, but if the application in
question needs a number that users will refer to, then that number needs to
be custom made as you have NO control how/what the next auto number will be,
and further a compacting of a file can re-set those numbers....

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

http://www.members.shaw.ca/AlbertKallal