473,326 Members | 2,337 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

VBCode Help for Beginner

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
ThingForNumberOfRecords".

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

Tom Keane
Nov 13 '05 #1
5 1449
>For
example, I would have a string, that says perhaps "LGK1111
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("LGK1111")
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(myphone 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("0123456789", mych) > 0 Then
OnlyNumbers = OnlyNumbers & mych
End If
Next i
End If
End Function

The second is how to get the number of records in a table and stick it in a
variable


You can go

dim lngReocrdsInTable as long
lngReocrdsInTable = dcount("*","NameOfTable")
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
Nov 13 '05 #2
> strNumberPart = mid("LGK1111",4)
intResult = strNumberPart


Yes, the String in question always has the same three letters at the
beginning. I actually found similar code on the net. Thanks though ;)
The second is how to get the number of records in a table and stick it in a variable


You can go

dim lngReocrdsInTable as long
lngReocrdsInTable = dcount("*","NameOfTable")


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
Nov 13 '05 #3
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

Nov 13 '05 #4
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
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
Nov 13 '05 #5
"celtic_kiwi" <ga*********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
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


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...something 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
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal
Nov 13 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Richard B. Kreckel | last post by:
Hi! I was recently asked what book to recommend for a beginner in C++. I am convinced that you needn't study C in depth before learning C++ (though it helps), but cannot find any beginner's...
8
by: Grrrbau | last post by:
I'm a beginner. I'm looking for a good C++ book. Someone told me about Lafore's "Object-Oriented Programming in C++". What do you think? Grrrbau
7
by: Rensjuh | last post by:
Hello, does someone have / know a good C++ tutorial for beginnners? I would prefer Dutch, but English is also fine. Hoi, heeft / kent iemand nog een goede C++ tutorial voor beginners? Het liefste...
27
by: MHoffman | last post by:
I am just learning to program, and hoping someone can help me with the following: for a simple calculator, a string is entered into a text box ... how do I prevent the user from entering a text...
18
by: mitchellpal | last post by:
Hi guys, am learning c as a beginner language and am finding it rough especially with pointers and data files. What do you think, am i being too pessimistic or thats how it happens for a beginner?...
20
by: weight gain 2000 | last post by:
Hello all! I'm looking for a very good book for an absolute beginner on VB.net or VB 2005 with emphasis on databases. What would you reccommend? Thanks!
0
by: cbhavanitr | last post by:
Hello all, Iam new to VB coding.My query is plz someone tell me how to retreive datafields on to the textbox in VB from mysql database.I badly need a Vbcode for it.can any one plz let me know?
0
by: nishjee | last post by:
Hello friends, i need visual basic 6.0 code to export a datareport to html other than the defult option coming with datareport. I need this very urgently because when i am exporting the datareport...
22
by: ddg_linux | last post by:
I have been reading about and doing a lot of php code examples from books but now I find myself wanting to do something practical with some of the skills that I have learned. I am a beginner php...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.