Custom Autonumber

[Dariusz Kuliñski / TaKeDa]

I guess that was asked milion times, but I don't have good luck finding
working answer on google.

Most of the answers tell what to do, but not how.

My situation is that I want to have ID in following format:
<letter>-<three digits>

i.e.
A-001
B-322

letter represents year A=2001, B=2002 and so on, number is resetted every
year.

After reading informations on google I decided that it would be simpler to
have two columns, one for that letter and second for the number.

I figured out how to calculate letter:
Dim letter As String
letter = Chr$(Year(Date) - 2001 + 65)

The problem is with number, I guess I should have separate table to store
last used number for given year, but I don't really know how to get/set
given record in table in VB. I looked in access manual and there are seem
to be three different ways, and examples doesn't seem to work.

Basically I want to have table with columns:
Year[primary key], LastNumber

then, anyone know how I can from VB:
- get last number for given year
- insert new row
- change value for given row
- delete given row

If anyone could help me with this I would be graceful.
--
ta****@IRCnet.EFnet, ICQ# 15827691, TLEN: taked4
*http://eggdrop.takeda.tk - forum dot. botów na bazie eggdropa*
*http://eggwiki.takeda.tk - pomoc w u¿ywaniu botów po polsku*

[Bas Cost Budde]

Dariusz Kuliñski / TaKeDa wrote:

After reading informations on google I decided that it would be simpler to
have two columns, one for that letter and second for the number.

I figured out how to calculate letter:
Dim letter As String
letter = Chr$(Year(Date) - 2001 + 65)
You could use asc("A") instead of 65. Its numerical value is the same,
of course, but it will be apparent what the 65 means.
Basically I want to have table with columns:
Year[primary key], LastNumber
You want to maintain a separate table with the latest used number? That
is a stable approach to multi user record entry. Good. For single user,
that is not necessary (but will work anyway).
then, anyone know how I can from VB:
- get last number for given year
dmax("yourNumberField","yourTable","yourYearField= " & givenYear)
- insert new row
either by executing a SQL statement (INSERT INTO), which is done via
currentdb.execute <sql-string>, or by using the .AddNew of a RecordSet
object.
- change value for given row
likewise: by using SQL (UPDATE), or using .Edit and .Update of a
RecordSet (after you've done .FindFirst)
- delete given row

SQL: DELETE, RecordSet.Delete (after you've done .FindFirst)

If you just want to get the first available number for a given year, you
could use the DMax() function as above on the main table. That is short
and, for single user, reliable.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Salad]

Dariusz Kuliñski / TaKeDa wrote:

I guess that was asked milion times, but I don't have good luck finding
working answer on google.

Most of the answers tell what to do, but not how.

My situation is that I want to have ID in following format:
<letter>-<three digits>

i.e.
A-001
B-322

letter represents year A=2001, B=2002 and so on, number is resetted every
year.

After reading informations on google I decided that it would be simpler to
have two columns, one for that letter and second for the number.

I figured out how to calculate letter:
Dim letter As String
letter = Chr$(Year(Date) - 2001 + 65)

The problem is with number, I guess I should have separate table to store
last used number for given year, but I don't really know how to get/set
given record in table in VB. I looked in access manual and there are seem
to be three different ways, and examples doesn't seem to work.

Basically I want to have table with columns:
Year[primary key], LastNumber

then, anyone know how I can from VB:
- get last number for given year
- insert new row
- change value for given row
- delete given row

If anyone could help me with this I would be graceful.

I would recommend you keep the values separate as you are currently
doing. I would not use them as the primary key however...I'd use an
autonumber.

You can get the Max thru a query or you can use the DMAZX() function.
MyMax = NZ(Dmax("IDNumber","TableWithID","AlphaYear = 'A'"),0) + 1
will find the highest id number in the table where year is "A" or...

Dim rst As Recordset
strSQL = "Select Max(IDNumber) As MaxNum From TableWithID Where
AlphaYear = 'A'"
Set rst = Currentdb.openrecordset(strSQL,dbopensnapshot)
If rst.recordcount > 0 then
MyMax = rst!MaxNum + 1
Else
MyMax = 1
Endif
As far as deleting, adding, etc, look at Edit, AddNew, Delete in help. Ex:

Dim rst As Recordset
strSQL = "Select * From TableWithID Where AlphaYear = 'A' And IDNumber =
1234"
Set rst = Currentdb.openrecordset(strSQL,dbopensnapshot)
If rst.recordcount > 0 then
rst.Delete 'delete record
or
rst.Edit
....process
rst.Update
or
rst.AddNew
...process
rst.Update

[Alan Webb]

Dariusz,
The way I do it is to keep a one row table of sequence numbers with as many
columns in it as there are different custom sequences. Then I write a
function which generates the next number in the sequence and stores it in my
table. Assuming all this worked, my function then returns the next number
to whatever called it. As for incrementing letters, use the numeric value
of the letter.

"Dariusz Kuliñski / TaKeDa" <55*******@NOsneakemailSPAM.com> wrote in
message news:1r***************@stupidworms.takeda.tk...

I guess that was asked milion times, but I don't have good luck finding
working answer on google.

Most of the answers tell what to do, but not how.

My situation is that I want to have ID in following format:
<letter>-<three digits>

i.e.
A-001
B-322

letter represents year A=2001, B=2002 and so on, number is resetted every
year.

After reading informations on google I decided that it would be simpler to
have two columns, one for that letter and second for the number.

I figured out how to calculate letter:
Dim letter As String
letter = Chr$(Year(Date) - 2001 + 65)

The problem is with number, I guess I should have separate table to store
last used number for given year, but I don't really know how to get/set
given record in table in VB. I looked in access manual and there are seem
to be three different ways, and examples doesn't seem to work.

Basically I want to have table with columns:
Year[primary key], LastNumber

then, anyone know how I can from VB:
- get last number for given year
- insert new row
- change value for given row
- delete given row

If anyone could help me with this I would be graceful.
--
ta****@IRCnet.EFnet, ICQ# 15827691, TLEN: taked4
*http://eggdrop.takeda.tk - forum dot. botów na bazie eggdropa*
*http://eggwiki.takeda.tk - pomoc w u¿ywaniu botów po polsku*

[Dariusz Kuliñski / TaKeDa]

On Wed, 24 Nov 2004 02:02:58 -0500, Alan Webb wrote:

Dariusz,
The way I do it is to keep a one row table of sequence numbers with as many
columns in it as there are different custom sequences. Then I write a
function which generates the next number in the sequence and stores it in my
table. Assuming all this worked, my function then returns the next number
to whatever called it. As for incrementing letters, use the numeric value
of the letter.

Thanks, but I don't know enough to be able read/write to table from VB
code.

Anyway I was thinking to store values in that fashion:

Year | Last
-----|-----
A | 564
B | 467
C | 543
D | 342

So each row is for one year, and Last column tells what's the last number
used. In case that I don't have last number for the year, I could tryu to
fail back to DMax and calculate it. I don't want to use DMax all the time
because as data will grow it will be slower and slower with time.

--
ta****@IRCnet.EFnet, ICQ# 15827691, TLEN: taked4
*http://eggdrop.takeda.tk - forum dot. botów na bazie eggdropa*
*http://eggwiki.takeda.tk - pomoc w u¿ywaniu botów po polsku*

[Dariusz Kuliñski / TaKeDa]

On Sat, 20 Nov 2004 17:13:33 GMT, Salad wrote:

I would recommend you keep the values separate as you are currently
doing. I would not use them as the primary key however...I'd use an
autonumber.

You can get the Max thru a query or you can use the DMAZX() function.
MyMax = NZ(Dmax("IDNumber","TableWithID","AlphaYear = 'A'"),0) + 1
will find the highest id number in the table where year is "A" or...

Thanks, but I have problem with making DMax working (I explained it in
response to Bas)
Dim rst As Recordset
strSQL = "Select Max(IDNumber) As MaxNum From TableWithID Where
AlphaYear = 'A'"
Set rst = Currentdb.openrecordset(strSQL,dbopensnapshot)
If rst.recordcount > 0 then
MyMax = rst!MaxNum + 1
Else
MyMax = 1
Endif
I wrote besed on your reply something like that:

Public Sub getNumer()
Dim rst As Recordset
Dim sql As String
Dim res As Integer

Set sql = "SELECT Last FROM LastNumber WHERE Year = 'D'"
Set rst = CurrentDb.OpenRecordset(sql, dbopensnapshot)
If rst.RecordCount > 0 Then
Set res = rst!Last + 1
Else
Set res = 1
End If

MsgBox (res)
End Sub

I used sub, and not function because I'm testing it.

This code doesn't even compile it says:
"Compile error:
Object required"

after clicking OK it marks "Public Sub getNumer()" using yellow color.
Any idea what's wrong?

I was complaining on Visual C++ for not having really meaningful errors,
but Access is even worse in that :/

BTW: in "Dim rst As Recordset" recordset is not blue like the rest, so it's
a proper variable type? Maybe error is there?
As far as deleting, adding, etc, look at Edit, AddNew, Delete in help. Ex:

Dim rst As Recordset
strSQL = "Select * From TableWithID Where AlphaYear = 'A' And IDNumber =
1234"
Set rst = Currentdb.openrecordset(strSQL,dbopensnapshot)
If rst.recordcount > 0 then
rst.Delete 'delete record
or
rst.Edit
....process
rst.Update
or
rst.AddNew
...process
rst.Update

Thank you, I'll try that code once I successfully will be able read from
table.
--
ta****@IRCnet.EFnet, ICQ# 15827691, TLEN: taked4
*http://eggdrop.takeda.tk - forum dot. botów na bazie eggdropa*
*http://eggwiki.takeda.tk - pomoc w u¿ywaniu botów po polsku*

[Dariusz Kuliñski / TaKeDa]

On Sat, 20 Nov 2004 17:56:28 +0100, Bas Cost Budde wrote:

After reading informations on google I decided that it would be simpler to
have two columns, one for that letter and second for the number.

I figured out how to calculate letter:
Dim letter As String
letter = Chr$(Year(Date) - 2001 + 65)

You could use asc("A") instead of 65. Its numerical value is the same,
of course, but it will be apparent what the 65 means.

Thanks for the tip, it looks nicer that way.

Basically I want to have table with columns:
Year[primary key], LastNumber

You want to maintain a separate table with the latest used number? That
is a stable approach to multi user record entry. Good. For single user,
that is not necessary (but will work anyway).

then, anyone know how I can from VB:
- get last number for given year

dmax("yourNumberField","yourTable","yourYearField= " & givenYear)

I tried to use it, but I don't have much luck, I created module with
following code:

Option Compare Database
Option Explicit

Public Function Numer() As Integer
Dim myyear As String
Dim mynumer As Integer

myyear = Chr$(year(Date) - 2001 + Asc("A"))
mynumer = DMax("[FileNumber]", "FileIndex", "[FileYear] = " & myyear)

MsgBox (mynumer)
Numer = mynumer

End Function
But it doesn't seem to work, I'm getting:
"Run-time error '2001':
You canceled, the previous operation."

The text doesn't make any sense to me.

- insert new row

either by executing a SQL statement (INSERT INTO), which is done via
currentdb.execute <sql-string>, or by using the .AddNew of a RecordSet
object.

- change value for given row

likewise: by using SQL (UPDATE), or using .Edit and .Update of a
RecordSet (after you've done .FindFirst)

- delete given row

SQL: DELETE, RecordSet.Delete (after you've done .FindFirst)

If you just want to get the first available number for a given year, you
could use the DMax() function as above on the main table. That is short
and, for single user, reliable.

I didn't even tried that code yet, because I have hard time to make DMax
work, I know this is alternative, but I think I would decide first to use
DMax, and then perhaps tried that solution if it won't be enough.

Anyway, how to retrieve record from database? I mean I could do
currentdb.execute "SELECT <column> FROM <table> WHERE <if statement>"
but how can I get values of what SELECT returned? Small example would be
good.

Thank you,

PS. sorry for my late reply, but I couldn't find time to test what you
wrote.
--
ta****@IRCnet.EFnet, ICQ# 15827691, TLEN: taked4
*http://eggdrop.takeda.tk - forum dot. botów na bazie eggdropa*
*http://eggwiki.takeda.tk - pomoc w u¿ywaniu botów po polsku*

[Bas Cost Budde]

Dariusz Kuliñski / TaKeDa wrote:

PS. sorry for my late reply, but I couldn't find time to test what you
wrote.
Not at all, not at all! You're the one with the question! :-)

dmax("yourNumberField","yourTable","yourYearFiel d=" & givenYear)

I tried to use it, but I don't have much luck, I created module with
following code:

Option Compare Database
Option Explicit

Public Function Numer() As Integer
Dim myyear As String
Dim mynumer As Integer

myyear = Chr$(year(Date) - 2001 + Asc("A"))
mynumer = DMax("[FileNumber]", "FileIndex", "[FileYear] = " & myyear)
myYear is a String, and should be within quotes in the DMax. Like this:

mynumer = DMax("[FileNumber]","FileIndex","[FileYear]='" & myyear & "'")

MsgBox (mynumer)
Numer = mynumer

End Function
I take it the MsgBox is there for testing. The function looks all right
to me.

There is one small issue--I usually use a function that returns the next
*free* number. This function returns the last (highest) number *in use*.
Keep that in mind!
But it doesn't seem to work
The function won't work by itself. You have to call it somewhere to let
something happen. How do you do that?
I'm getting:
"Run-time error '2001':
You canceled, the previous operation."

The text doesn't make any sense to me.
2001 usually means an event was started (by Access) and then some result
lead it to think the action, insertion of a new record possibly, was
cancelled. I too find the wording a little confusing. With time you get
to understand what error message means what. When I develop
applications, I try to overcome this. Developers usually indicate what
the problem is; users don't care, they want to know what the *solution*
is for a given situation. But that aside :-) [snip SQL story]
I didn't even tried that code yet, because I have hard time to make DMax
work, I know this is alternative, but I think I would decide first to use
DMax, and then perhaps tried that solution if it won't be enough.
I forgot, is this a single user application? If so, don't worry about
DMax, it will do.
Anyway, how to retrieve record from database? I mean I could do
currentdb.execute "SELECT <column> FROM <table> WHERE <if statement>"
but how can I get values of what SELECT returned? Small example would be
good.

<g> you cannot really Execute a SELECT. Execute is meant for data
manipulation, like insert, delete, update.

If you want a function that returns a value from a table, for a given
row, you can use the built-in DLookup. See Help. That behavior can be
mimicked using a RecordSet object, not with SQL alone. Oh, a SELECT will
surely return records--but you have to tell Access *where* to return
these into.

RecordSet. In other database systems, you talk about a cursor. A cursor,
much like the one on the screen, is a position indicator, this time on
which record you are in the table.

Suppose you really want to have a VBA function that accepts the table
name, a valid (!) WHERE condition without the word WHERE, and a field
name to return the value from (air code, assuming A97/DAO ):

Function getFieldValue(cTable as string, cWhere as string, cFieldname as
string) as variant
' I return a Variant because maybe no record is found; will return Null then
dim rs as recordset
dim cSQL as string'a way to prevent line wrap in the code
csql="SELECT " & cfieldname & " FROM "
csql=csql & ctable & " WHERE " & cWhere
set rs=currentdb.openrecordset(cSQL)
if rs.recordcount=0 then
getfieldvalue=null
else
getfieldvalue=rs(0)
endif
rs.close
set rs=nothing
end function

In this function, rs(0) refers to the first field in the resulting
recordset.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Bas Cost Budde]

Dariusz Kuliñski / TaKeDa wrote:

Dim rst As Recordset
strSQL = "Select Max(IDNumber) As MaxNum From TableWithID Where
AlphaYear = 'A'"
Set rst = Currentdb.openrecordset(strSQL,dbopensnapshot)
If rst.recordcount > 0 then
MyMax = rst!MaxNum + 1
Else
MyMax = 1
Endif

I wrote besed on your reply something like that:

Public Sub getNumer()
Dim rst As Recordset
Dim sql As String
Dim res As Integer

Set sql = "SELECT Last FROM LastNumber WHERE Year = 'D'"
Set rst = CurrentDb.OpenRecordset(sql, dbopensnapshot)
If rst.RecordCount > 0 Then
Set res = rst!Last + 1
Else
Set res = 1
End If

MsgBox (res)
End Sub

This code doesn't even compile it says:
"Compile error:
Object required"
Correct: you don't need the Set statement for a scalar variable (res is
a simple datatype). If you need to see a keyword, you can use Let, but
that is optional. "res=1" will do.
after clicking OK it marks "Public Sub getNumer()" using yellow color.
Any idea what's wrong?
Yellow means: the code is running. This line will be interpreted next.
I was complaining on Visual C++ for not having really meaningful errors,
but Access is even worse in that :/
Nono, same. :-L

But, er, with the error, you will get an inverted blue portion (I got it
with "Set sql=" which is indeed the first error of this type). That is
meaningful to me.

As on meaningful: most meaning comes only after once you know...
BTW: in "Dim rst As Recordset" recordset is not blue like the rest, so it's
a proper variable type? Maybe error is there?

That is a funny signal. But it doesn't say all. If the type were not
recognized, you would get an error for that. Try the button Compile that
should be visible on the toolbar (stack of papers with a blue arrow
pointing down to it). That should highlite obvious errors just above the
syntax level (those are indicated in red when you leave the line) but
not all errors that may be.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Bas Cost Budde]

Dariusz Kuliñski / TaKeDa wrote:

Anyway, how to retrieve record from database?

Take this code fragment and put it in any module. It provides a
command-line like interface in SQL to your tables.

Use the debug window for this. You can now write something like

sql "select filenumber from fileindex"

and my routine recognizes this as a display statement, writes the result
onto the debug window.

Would you write

sql "update fileindex set filenumber='322'"

you would see nothing (you can uncomment the two lines in the Else of
the SQL sub) but all filenumbers in the table would be set to 322. Don't
try this at home ;-)

'*** code start ***

Sub SQL(cSQL As String)
Dim db As Database
Dim rs As Recordset
If Left(cSQL, 6) = "SELECT" Then
Set rs = CurrentDb.OpenRecordset(cSQL)
dumpRs rs
Else
Set db = CurrentDb
db.Execute cSQL, dbFailOnError
' Debug.Print cSQL
' Debug.Print db.RecordsAffected & " beinvloed"
db.Close
Set db = Nothing
End If
End Sub

Sub dumpRs(rs As Recordset)
Dim cBook As String
'On Error Resume Next
Dim fd As Field
If rs.RecordCount = 0 Then Exit Sub
cBook = rs.Bookmark
rs.MoveFirst
For Each fd In rs.Fields
dumpFd fd, rs.Fields.Count, True
Next
Do Until rs.EOF
For Each fd In rs.Fields
dumpFd fd, rs.Fields.Count, False
Next
rs.MoveNext
Loop
rs.Bookmark = cBook
End Sub

Sub dumpFd(fd As Field, mf As Long, Optional bName = False)
If fd.OrdinalPosition > 0 Then
Debug.Print ", ";
End If
If bName Then
Debug.Print fd.Name;
Else
Debug.Print fd.Value;
End If
If fd.OrdinalPosition = mf - 1 Then Debug.Print
End Sub

'*** code end ***

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Dariusz Kuliñski / TaKeDa]

On Fri, 26 Nov 2004 09:28:28 +0100, Bas Cost Budde wrote:

Dariusz Kuliñski / TaKeDa wrote:

Anyway, how to retrieve record from database?
Take this code fragment and put it in any module. It provides a
command-line like interface in SQL to your tables.

Thank you very much for your help, but I still have problems:

[...]
Sub dumpFd(fd As Field, mf As Long, Optional bName = False)
If fd.OrdinalPosition > 0 Then

^^^^^^^^^^^^^^^^
[...]

code fails to compile it stops at this point saying:
"Method or data member not found"

--
ta****@IRCnet.EFnet, ICQ# 15827691, TLEN: taked4
*http://eggdrop.takeda.tk - forum dot. botów na bazie eggdropa*
*http://eggwiki.takeda.tk - pomoc w u¿ywaniu botów po polsku*

[Dariusz Kuliñski / TaKeDa]

On Fri, 26 Nov 2004 09:23:09 +0100, Bas Cost Budde wrote:

This code doesn't even compile it says:
"Compile error:
Object required"

Correct: you don't need the Set statement for a scalar variable (res is
a simple datatype). If you need to see a keyword, you can use Let, but
that is optional. "res=1" will do.

I thought "Set" doesn't mean much, I put it there because I thought code
looks nicer. I mistaken it with Basic's "Let", which should be used in
assignments but can be skipped. But now I see that "Let" is still thee :)

after clicking OK it marks "Public Sub getNumer()" using yellow color.
Any idea what's wrong?

Yellow means: the code is running. This line will be interpreted next.

I was complaining on Visual C++ for not having really meaningful errors,
but Access is even worse in that :/

Nono, same. :-L

But, er, with the error, you will get an inverted blue portion (I got it
with "Set sql=" which is indeed the first error of this type). That is
meaningful to me.

As on meaningful: most meaning comes only after once you know...

I hope so.

BTW: in "Dim rst As Recordset" recordset is not blue like the rest, so it's
a proper variable type? Maybe error is there?

That is a funny signal. But it doesn't say all. If the type were not
recognized, you would get an error for that. Try the button Compile that
should be visible on the toolbar (stack of papers with a blue arrow
pointing down to it). That should highlite obvious errors just above the
syntax level (those are indicated in red when you leave the line) but
not all errors that may be.

I fixed those "set's"
and here is the code:

Public Function getNumer() As Integer
Dim rst As Recordset
Dim sql As String
Dim res As Integer

sql = "SELECT Last FROM LastNumber WHERE Year = 'D'"

Set rst = CurrentDb.OpenRecordset(sql, dbopensnapshot)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^

If rst.RecordCount > 0 Then
res = rst!Last + 1
Else
res = 1
End If

MsgBox (res)
getNumer = res
End Function

it compiles fine, but fails on marked line with error "type mismatch"
(error code 13)

--
ta****@IRCnet.EFnet, ICQ# 15827691, TLEN: taked4
*http://eggdrop.takeda.tk - forum dot. botów na bazie eggdropa*
*http://eggwiki.takeda.tk - pomoc w u¿ywaniu botów po polsku*

[Dariusz Kuliñski / TaKeDa]

On Fri, 26 Nov 2004 09:15:36 +0100, Bas Cost Budde wrote:

Option Compare Database
Option Explicit

Public Function Numer() As Integer
Dim myyear As String
Dim mynumer As Integer

myyear = Chr$(year(Date) - 2001 + Asc("A"))
mynumer = DMax("[FileNumber]", "FileIndex", "[FileYear] = " & myyear)
myYear is a String, and should be within quotes in the DMax. Like this:
mynumer = DMax("[FileNumber]","FileIndex","[FileYear]='" & myyear & "'")

Hurray, it's working :)

MsgBox (mynumer)
Numer = mynumer

End Function

I take it the MsgBox is there for testing. The function looks all right
to me.

Yeah, it's for testing.
There is one small issue--I usually use a function that returns the next
*free* number. This function returns the last (highest) number *in use*.
Keep that in mind!
Originally it was + 1, I was changing it when trying to figure out where
was the problem.

But it doesn't seem to work

The function won't work by itself. You have to call it somewhere to let
something happen. How do you do that?

I was calling it from editor using that Play button (I think it's
equivalent to pressing F5)

I'm getting:
"Run-time error '2001':
You canceled, the previous operation."
The text doesn't make any sense to me.

2001 usually means an event was started (by Access) and then some result
lead it to think the action, insertion of a new record possibly, was
cancelled. I too find the wording a little confusing. With time you get
to understand what error message means what. When I develop
applications, I try to overcome this. Developers usually indicate what
the problem is; users don't care, they want to know what the *solution*
is for a given situation. But that aside :-)

I would never guessed that "You canceled, the previous operation" means
that there are missed apostrofes in DMax function :)
[snip SQL story]

I didn't even tried that code yet, because I have hard time to make DMax
work, I know this is alternative, but I think I would decide first to use
DMax, and then perhaps tried that solution if it won't be enough.

I forgot, is this a single user application? If so, don't worry about
DMax, it will do.

What exactly single user means?
If it means single computer, then that's currently is true.
But client might set up network for his computers to exchange files, and it
would be great if he could change db from each one. It's highly unlikely
that 2 or more people would try to change there anything, but it's possible
that this program might run for each computer at the same time.

Basically I think it would be one user, but on few computers. But it would
be good idea to prepare it for multiuser...

Anyway, how to retrieve record from database? I mean I could do
currentdb.execute "SELECT <column> FROM <table> WHERE <if statement>"
but how can I get values of what SELECT returned? Small example would be
good.

<g> you cannot really Execute a SELECT. Execute is meant for data
manipulation, like insert, delete, update.

If you want a function that returns a value from a table, for a given
row, you can use the built-in DLookup. See Help. That behavior can be
mimicked using a RecordSet object, not with SQL alone. Oh, a SELECT will
surely return records--but you have to tell Access *where* to return
these into.

RecordSet. In other database systems, you talk about a cursor. A cursor,
much like the one on the screen, is a position indicator, this time on
which record you are in the table.

Suppose you really want to have a VBA function that accepts the table
name, a valid (!) WHERE condition without the word WHERE, and a field
name to return the value from (air code, assuming A97/DAO ):

Function getFieldValue(cTable as string, cWhere as string, cFieldname as
string) as variant
' I return a Variant because maybe no record is found; will return Null then
dim rs as recordset
dim cSQL as string'a way to prevent line wrap in the code
csql="SELECT " & cfieldname & " FROM "
csql=csql & ctable & " WHERE " & cWhere
set rs=currentdb.openrecordset(cSQL)
if rs.recordcount=0 then
getfieldvalue=null
else
getfieldvalue=rs(0)
endif
rs.close
set rs=nothing
end function

In this function, rs(0) refers to the first field in the resulting
recordset.

Thanks, I think I understand this code, but it doesn't seem to work, it
compiles fine but when I run it from immediate window:

? getFieldValue("LastNumber", "Year = 'Z'", "Number")
(I previously added record to table, so select statement should work)

I'm getting error:
"Run-time error '3061':
Too few parameters. Expected 1."

When I click "Debug" It points at that line:
"Set rs = CurrentDb.OpenRecordset(cSQL)"

I think I should load some reference (Tools/References...) to make it work,
but which one?

--
ta****@IRCnet.EFnet, ICQ# 15827691, TLEN: taked4
*http://eggdrop.takeda.tk - forum dot. botów na bazie eggdropa*
*http://eggwiki.takeda.tk - pomoc w u¿ywaniu botów po polsku*

[Bas Cost Budde]

Dariusz Kuliñski / TaKeDa wrote:

But it doesn't seem to work
The function won't work by itself. You have to call it somewhere to let
something happen. How do you do that?

I was calling it from editor using that Play button (I think it's
equivalent to pressing F5)

Hm, when you call a function, that is usually on the right side (I mean
right as opposite to left, not to wrong :-) ) of an assignment: that is
something like

variable = expression

In the expression part, you can have a function call (or more of them).
A function is evaluated, and returns a value; so during execution, the
call is replaced by its actual value for that expression.

Quite abstractly.
I would never guessed that "You canceled, the previous operation" means
that there are missed apostrofes in DMax function :)
That in itself is not true, fortunately. There is an action that
started, during which a faulty component was found; so the action is
cancelled--the code must halt--and you get the last error that occurred.
Most of the time you will see the first error that occurs. <shrug>
What exactly single user means?
Application whose changeable resources are not to be shared among users.
The effects of multi user are several: you have to consider what path to
follow if two or more people try to change the same record; Access (Jet,
actually) has a locking mechanism for this. In the event of creation of
a new key that must be unique of course, you must ascertain that
simultaneous insert attempts (for a record with such a key) do not
result in the same key occurring twice.
it's possible
that this program might run for each computer at the same time.
That is not a problem; the danger lies in the same table(s) being
accessed from several locations.

[snip code]
Thanks, I think I understand this code, but it doesn't seem to work, it
compiles fine but when I run it from immediate window:

? getFieldValue("LastNumber", "Year = 'Z'", "Number")
(I previously added record to table, so select statement should work)

I'm getting error:
"Run-time error '3061':
Too few parameters. Expected 1."
"Too few parameters" is alternative for: you spelled one of the names WRONG

I see I have confusingly changed the order of the arguments to my
function; there is a DLookup that does the same, but in the sequence
Field, Table, Where. So I read it again...

The error can mean:
* you don't have a table "LastNumber"
* you don't have a field "Year" (I recommend you choose another name,
Year has some built-in meaning which will cause confusion somewhere. On
reports, for example)
* there is no field "Number"
When I click "Debug" It points at that line:
"Set rs = CurrentDb.OpenRecordset(cSQL)"

I think I should load some reference (Tools/References...) to make it work,
but which one?

Nonono, don't charge the cannons yet. The error is raised there because
that is the moment the SQL statement is tried. You could check the value
of cSQL at that point (Ctrl-G; "? cSQL") but check with your table for
the names.

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Bas Cost Budde]

Dariusz Kuliñski / TaKeDa wrote:

On Fri, 26 Nov 2004 09:28:28 +0100, Bas Cost Budde wrote:

Dariusz Kuliñski / TaKeDa wrote:

Anyway, how to retrieve record from database?

Take this code fragment and put it in any module. It provides a
command-line like interface in SQL to your tables.

Thank you very much for your help, but I still have problems:

[...]

Sub dumpFd(fd As Field, mf As Long, Optional bName = False)
If fd.OrdinalPosition > 0 Then

^^^^^^^^^^^^^^^^
[...]

code fails to compile it stops at this point saying:
"Method or data member not found"

Oh, this code assumes A97. If you have a later version, you could either
change "Field" into "DAO.Field", or rearrange your references such that
DAO comes before ADO. Huh? Instructions:
- open any code module
- choose menu Tools->References
- locate DAO (3.51 possibly, don't know)
- use the arrows to move it up

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Bas Cost Budde]

Dariusz Kuliñski / TaKeDa wrote:

Set rst = CurrentDb.OpenRecordset(sql, dbopensnapshot)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^

it compiles fine, but fails on marked line with error "type mismatch"
(error code 13)

Also DAO/ADO? Why do you use opensnapshot? Usually I don't care and live
with the default (opendynaset, I think)

I suggest you do a

rst.close
set rst=nothing

at the end of the procedure. Just to make sure. Access is known to hang
on exit for some cases (mostly database variables, not recordsets)

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Alan Webb]

Dariusz,
If not in this project then in some other you will have to bite the bullet
and learn how to interact with a database from within VB. There is a lot of
free-information around on how to use the ADODB library or the DAO type
library to interact with a Jet database. Learning to write a custom
sequence is a good place to start. And . . . I dislike the use of DMax,
DMin, or it's sister functions. It stinks of lazy/poorly thought out
coding.

"Dariusz Kuliñski / TaKeDa" <55*******@NOsneakemailSPAM.com> wrote in
message news:57**************@stupidworms.takeda.tk...

On Wed, 24 Nov 2004 02:02:58 -0500, Alan Webb wrote:

Thanks, but I don't know enough to be able read/write to table from VB
code.

[Bas Cost Budde]

Alan Webb wrote:

I dislike the use of DMax,
DMin, or it's sister functions. It stinks of lazy/poorly thought out
coding.

Is that a valid assertion (i.e. do you still think so) in the case of
using DMax() to get a new sequence number?

--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Dariusz Kuliñski / TaKeDa]

On Tue, 30 Nov 2004 00:58:28 -0500, Alan Webb wrote:

Dariusz,
If not in this project then in some other you will have to bite the bullet
and learn how to interact with a database from within VB. There is a lot of
free-information around on how to use the ADODB library or the DAO type
library to interact with a Jet database. Learning to write a custom
sequence is a good place to start.
I know, but belive me it's really difficult even for experienced programmer
(who was writting in different language) to understand it. The problem is
that there seem to be many different ways to access the database, from what
I understand there is DAO, ADO, Jet. When I looked into help there are
mostly examples that access outside databases. Also many of them doesn't
seem to work (I think it's because of those References setting.)
And . . . I dislike the use of DMax,
DMin, or it's sister functions. It stinks of lazy/poorly thought out
coding.

I dislike it too, that's why I wanted to make db access work even when I
was finally able to made it work.

I think I found answers for all my questions so far, I'll need just to put
it all together and it should be ok :)

--
ta****@IRCnet.EFnet, ICQ# 15827691, TLEN: taked4
*http://eggdrop.takeda.tk - forum dot. botów na bazie eggdropa*
*http://eggwiki.takeda.tk - pomoc w u¿ywaniu botów po polsku*

[Dariusz Kuliñski / TaKeDa]

On Mon, 29 Nov 2004 08:57:51 +0100, Bas Cost Budde wrote:

Dariusz Kuliñski / TaKeDa wrote:

On Fri, 26 Nov 2004 09:28:28 +0100, Bas Cost Budde wrote:

Dariusz Kuliñski / TaKeDa wrote:

Anyway, how to retrieve record from database?

Take this code fragment and put it in any module. It provides a
command-line like interface in SQL to your tables.

Thank you very much for your help, but I still have problems:

[...]

Sub dumpFd(fd As Field, mf As Long, Optional bName = False)
If fd.OrdinalPosition > 0 Then

^^^^^^^^^^^^^^^^
[...]

code fails to compile it stops at this point saying:
"Method or data member not found"

Oh, this code assumes A97. If you have a later version, you could either
change "Field" into "DAO.Field", or rearrange your references such that
DAO comes before ADO. Huh? Instructions:
- open any code module
- choose menu Tools->References
- locate DAO (3.51 possibly, don't know)
- use the arrows to move it up

In previous post regarding "Too few parameters. Expected 1." I found that I
actually wrote column type instead column name (I can say only that I was
experimenting with it at evening, so I was little tired :)))

Anyway I got another error but this advice fixed it, thank you I don't know
what I would do without your advices.

Thanks again.

--
ta****@IRCnet.EFnet, ICQ# 15827691, TLEN: taked4
*http://eggdrop.takeda.tk - forum dot. botów na bazie eggdropa*
*http://eggwiki.takeda.tk - pomoc w u¿ywaniu botów po polsku*

[Dariusz Kuliñski / TaKeDa]

On Mon, 29 Nov 2004 09:21:01 +0100, Bas Cost Budde wrote:

Dariusz Kuliñski / TaKeDa wrote:

Set rst = CurrentDb.OpenRecordset(sql, dbopensnapshot)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ ^^^^

it compiles fine, but fails on marked line with error "type mismatch"
(error code 13)
Also DAO/ADO? Why do you use opensnapshot? Usually I don't care and live
with the default (opendynaset, I think)

"Salad", who provided that code used it, I don't actually have clue what's
the difference.
I suggest you do a

rst.close
set rst=nothing

at the end of the procedure. Just to make sure. Access is known to hang
on exit for some cases (mostly database variables, not recordsets)

I understand rst.close, but what's the reason for resetting the value?
Doesn't access do it automatically when is exiting the function?

--
ta****@IRCnet.EFnet, ICQ# 15827691, TLEN: taked4
*http://eggdrop.takeda.tk - forum dot. botów na bazie eggdropa*
*http://eggwiki.takeda.tk - pomoc w u¿ywaniu botów po polsku*

[Bas Cost Budde]

Dariusz Kuliñski / TaKeDa wrote:

Also DAO/ADO? Why do you use opensnapshot? Usually I don't care and live
with the default (opendynaset, I think)

"Salad", who provided that code used it, I don't actually have clue what's
the difference.
Does it work with dbopendynaset?
I understand rst.close, but what's the reason for resetting the value?
Doesn't access do it automatically when is exiting the function?

It should, and for a Recordset i think it does clean up; but for a
database variable the process is known to lead to memory problems (in
A97 at least) which causes Access to not close.

And, I like my code to be explicit and clean. Close what you Open, Unset
what you Set.
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

[Dariusz Kuliñski / TaKeDa]

On Sat, 04 Dec 2004 12:02:44 +0100, Bas Cost Budde wrote:

Also DAO/ADO? Why do you use opensnapshot? Usually I don't care and live
with the default (opendynaset, I think)

"Salad", who provided that code used it, I don't actually have clue what's
the difference.

Does it work with dbopendynaset?

Looks like it does.

I understand rst.close, but what's the reason for resetting the value?
Doesn't access do it automatically when is exiting the function?

It should, and for a Recordset i think it does clean up; but for a
database variable the process is known to lead to memory problems (in
A97 at least) which causes Access to not close.

And, I like my code to be explicit and clean. Close what you Open, Unset
what you Set.

Yeah, you're right. Thanks for the tip.

--
ta****@IRCnet.EFnet, ICQ# 15827691, TLEN: taked4
*http://eggdrop.takeda.tk - forum dot. botów na bazie eggdropa*
*http://eggwiki.takeda.tk - pomoc w u¿ywaniu botów po polsku*