431,805 Members | 1,202 Online
Need help? Post your question and get tips & solutions from a community of 431,805 IT Pros & Developers. It's quick & easy.

# Custom Autonumber

 P: n/a 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: - 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* Nov 13 '05 #1
22 Replies

 P: n/a 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 , 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 Nov 13 '05 #2

 P: n/a 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: - 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 Nov 13 '05 #3

 P: n/a 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: - 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* Nov 13 '05 #4

 P: n/a 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* Nov 13 '05 #5

 P: n/a 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* Nov 13 '05 #6

 P: n/a 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 , 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 FROM WHERE " 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* Nov 13 '05 #7

 P: n/a 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 FROM WHERE " but how can I get values of what SELECT returned? Small example would be good. 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 Nov 13 '05 #8

 P: n/a Dariusz Kuliński / TaKeDa wrote: Dim rst As RecordsetstrSQL = "Select Max(IDNumber) As MaxNum From TableWithID WhereAlphaYear = 'A'"Set rst = Currentdb.openrecordset(strSQL,dbopensnapshot)If rst.recordcount > 0 then MyMax = rst!MaxNum + 1Else MyMax = 1Endif 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 Nov 13 '05 #9

 P: n/a 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 Nov 13 '05 #10

 P: n/a 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* Nov 13 '05 #11

 P: n/a 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* Nov 13 '05 #12

 P: n/a 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 FROM WHERE " but how can I get values of what SELECT returned? Small example would be good. 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* Nov 13 '05 #13

 P: n/a Dariusz Kuliński / TaKeDa wrote: But it doesn't seem to workThe function won't work by itself. You have to call it somewhere to letsomething 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. 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 Nov 13 '05 #14

 P: n/a 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 acommand-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 Nov 13 '05 #15

 P: n/a 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 Nov 13 '05 #16

 P: n/a 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. Nov 13 '05 #17

 P: n/a 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 Nov 13 '05 #18

 P: n/a 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* Nov 13 '05 #19

 P: n/a 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* Nov 13 '05 #21

 P: n/a Dariusz Kuliński / TaKeDa wrote: Also DAO/ADO? Why do you use opensnapshot? Usually I don't care and livewith 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 Nov 13 '05 #22

 P: n/a 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 livewith 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* Nov 13 '05 #23

### This discussion thread is closed

Replies have been disabled for this discussion.