Manually autonumber a field

[apsf68]

Hi,



I have a table with some fields, but one of this fields (id_seq) I need to manually autonumber it, based on another field (company). The "id_seq" field is numbered in a different sequence based on what is in the field "company".
Exemple: id_seq company
1 aa
2 aa
1 bb
The next "id_seq" for company "bb" should be "2".

Thanks,
Paulo Ferreira

[ajetrumpet]

you cannot duplicate an autonumber field. you'll have to do it through form code or something and make it a number of some sort, be it a long, or something else.

[apsf68]

I don't want to duplicate an autonumber field. What I want is create a field to have my own autonumber based on the content of the field "company".

Paulo Ferreira

[Rawb]

You'll have to use either VBA Code or an extra Query.

You can count the number of Records returned for that company and just add 1.

  Dim rstCount as DAO.Recordset

  Dim nbrNextNum as Integer ' Next available "autonumber"

  Set rstCount = CurrentDb().OpenRecordset("SELECT * FROM MyTable WHERE [company]='bb'", dbOpenSnapshot)

  nbrNextNum = rstCount.RecordCount + 1

  rstCount.Close

  Set rstCount = Nothing

[apsf68]

Hi Rawb.

Your code returns the error "Too few parameters. Expected 1".
I know that it's because I replaced "bb" with a field that is in my form, but I don't know how to do it to avoid the error.
I'm sure that it is a simple thing to workout but I realy don't know how.
Help please!

Paulo Ferreira

[Rawb]

That error usually means there's something wrong with your Query. Either the Table name or one of the Field names is misspelled.

If you're familiar with SQL, just go back and double-check the Query for spelling errors and the such.

If you're not familiar with SQL but know how to use Access' Query Builder, put together a Query that returns all the entries for a single company. Then switch to the SQL View and just copy and paste that Query into the VBA Code.

Some quick pointers for checking your Query: "SELECT * FROM MyTable WHERE [company]='bb'"

• MyTable - The name of your Table in Access. If your Table's name has a space in it, make sure to enclose it in square brackets (i.e.: [My Table])
• [company] - The name of the field containing the company's ID or name. If the field is of any numeric type (Integer, Double, etc.) DO NOT use single quotes to encapsulate the value ([company]=3 instead of [company]='3').

[apsf68]

Hi Rawb,

I think I don't make my self clear in the last post.
If the code is: Set rstCount = CurrentDb().OpenRecordset("SELECT * FROM MyTable WHERE [company]=10", dbOpenSnapshot), where the "company" is a number, it works ok.
If the code is: Set rstCount = CurrentDb().OpenRecordset("SELECT * FROM MyTable WHERE [company]=FormFieldFromComboBox", dbOpenSnapshot), where the "company", it wont work and the error "Too few parameters. Expected 1" will show up.
I know that's because I'm not writing the FormFieldFromComboBox the right way, but I already tried some different ways and it always gives me the same error.

[Rawb]

Ah, I gotcha.

When including a Form field in code like that, you need to make sure it's outside of any quotes. If you enter it like this though, it should work.

Set rstCount = CurrentDb().OpenRecordset("SELECT * FROM MyTable WHERE [company]=" & Me!FormFieldName, dbOpenSnapshot)

The ampersand (&) is used to combine two text strings together in VBA, so what we're doing is taking the text string SELECT * FROM MyTable WHERE [company]= and adding the value contained in Me!FormFieldName to the end of it.

[apsf68]

Hi,

Still got an error.
Runtime error 3075: Missing operator ([company]=).
Once again I don't understand why.

[Rawb]

Honestly, I'm not sure what the problem is at this point.

Can you post a screenshot of the line that's highlighted? That will probably help.

[apsf68]

Hi Rawb,

Here is the complete line that is highlighted: Set rstCount = CurrentDb().OpenRecordset("SELECT * FROM hardware WHERE [company]=" & Me!Text341, dbOpenSnapshot).
The complete error message is: Run-Time Error '3075': Syntax Error(Missing Operator) in query expression '[company]'.

[apsf68]

One thing about that line.
The field "text341" is filled based on a column of a combo box. If I replace that field with a number that corresponds to one of the companies that are in the combo box, the code works ok.

Paulo Ferreira

[Rawb]

Try adding the following line of code just before the line that gives you the error. This code will show a popup message box with the SQL Query that's giving you problems.

Since this is from a Combo Box, it could be that the wrong column is bound to it. You might also want to double-check the "Control Source" of the Combo Box as well as the "Bound Column" property.

  MsgBox "SELECT * FROM hardware WHERE [company]=" & Me!Text341 & _
         vbCrLf & vbCrLf & "Me!Text341 = '" & Me!Text341 & "'"

[apsf68]

Hi Rawb,

I know now that "run-time error 3075" appends because "text341" was null when I choose a company, and it was null because I was using event "before insert". When I change it to "after update" it works fine (almost).
The problem then was the new number for "id_seq" was never updated every time I choose a different company.
So I have used this code:
Dim newvalue As Long
Dim maxvalue As Variant
maxvalue = DMax("id_seq", "hardware", "[company]=" & Me!Text341)
If IsNull(maxvalue) Then
maxvalue = 0
End If
newvalue = maxvalue + 1
Me!id_seq = newvalue

Any way, if you have any suggestions to your code, please let me know.

[Rawb]

Because I didn't have any data on your Form or Table setup, there was no way for me to present a working solution. All my above code was, was an example on how to easily capture the value you're looking for. It would have been up to you to modify it for your specific needs.

That said, I now know enough to give you a working copy:

  Dim rstCount as DAO.Recordset

  Set rstCount = CurrentDb().OpenRecordset("SELECT * FROM MyTable WHERE [company]='bb'", dbOpenSnapshot)

  Me!id_seq = rstCount.RecordCount + 1

  rstCount.Close

  Set rstCount = Nothing

Additionally, the functions DMax, DCount, DLookup, etc. should only be used when there is no other alternative. Those functions are slow and don't use proper garbage collection. If you're working with Form or Report Fields, they're OK to use, but in VBA code there's always a better solution.