0

Have a data entry form, where Certificate_No is based on School, and the last number used.

A SQL statement:

sSQL = "SELECT TOP 1 Exam.School, Exam.Certificate_No as LN FROM Exam WHERE Exam.School = S ORDER BY Exam.Certificate_No DESC;"

Based on entering the School number (S), works in Query.

In vba I have this:

Dim db As DAO.Database
    Set db = CurrentDb()
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset(sSQL)
      sSQL = "SELECT TOP 1 Exam.School, Exam.Certificate_No as LN FROM Exam WHERE Exam.School = S ORDER BY Exam.Certificate_No DESC;"

But keep getting an error at Set rs....

Once I am able to read the LastNumber I can increment the next number.

Second part

I am using AfterUpdate successfully for copying dates down to the next record. Will this work for the above SQL, once working?

The AfterUpdate is :

Private Sub Exam_Date_AfterUpdate()
Me.Exam_Date.DefaultValue = """" & Me.Exam_Date.Value & """"
End Sub

Thanks

Additional Info: 7/1 1500 EDT

BTW, DMAX will only work if you can run a query on the Exam database to optain all Cert no's for a given school, which is why I'm trying to get the SQL statement to work.

Cert no's are formatted as School-NO-5 digits, or 100-12345 as a string. If I find the last number for school 100 is 100-22222, I can use right(Certificate_no,5) to an integer, again, add 1 and recombine into 100-22223 as a string.

But can't do this automatically if I can't get SQL statement to work. Manually, if I type a new cert no into the certificate_no field during an insert (via a form), I can get the next record to increment, but not the third or additional records automatically.

Conclusion

I asked this question a different way see New Question and was able to find an answer that works.

  • For Part1, since you want a single value from table, use DMax() instead of opening a recordset. However you retrieve value, will have to parse it into string and number parts, increment number, then recombine. Creating custom alpha-numeric unique identifier is a common topic. – June7 Jul 01 '23 at 15:49
  • One approach uses Data Macro https://stackoverflow.com/questions/25554760/creating-a-unique-id-for-an-individual-that-incorporates-data-for-the-individual and https://stackoverflow.com/questions/35947449/create-sequential-id-value-based-on-the-year-that-a-record-is-added – June7 Jul 01 '23 at 15:55
  • VBA approach with DMax() https://dba.stackexchange.com/questions/107209/automatic-creation-of-unique-alphanumeric-id – June7 Jul 01 '23 at 15:58
  • Is this a multiple simultaneous user split db? There is a risk that multiple users can create same identifier. Two options to reduce risk 1) immediately create ID and commit new record to table; 2) create ID when data entry is completed and committed to table. – June7 Jul 01 '23 at 16:01
  • Answers: June7, I have the code to split and recombine, which works. I just can't get the result to repeat itself more than once, while Date field which as shown, do. Only 1 person will be using this "batch-entry" form at a time. –  Jul 01 '23 at 18:06
  • Where do you run code to build ID? Edit your question. – June7 Jul 01 '23 at 18:26
  • Simply, take the last cert no (which is text), put the root into an integer, add 1, and concat with prefix back to text. straight forward (to eliminate the line of questioning, I'm stuck with what the client wants). –  Jul 01 '23 at 18:48
  • That doesn't clarify where code runs. What event triggers? I don't understand your reasoning for not using DMax(). If you can pull data with SQL, should be able to pull with domain aggregate. Is this a split design database? If so, is Access the backend and Access frontend links to it? – June7 Jul 01 '23 at 20:56
  • It is a all Access (but the db is split from the front-end), but that is not the issue. Triggering could be onClick, before/AfterUpdate, or a function. That again is immaterial. For all I know, it could be a "reference" missing (MSSQL is horrible). As I said, that same SQL line runs in a query and works perfectly, why not in VBA? The question is what don't I have in my VBA to execute that particular SQL statement. I think the answer (which I don't know, I'm not really an Access programmer, nor do I really want to be, and haven't used SQL in ages) is simple, and not complex. –  Jul 01 '23 at 21:54

2 Answers2

0
  1. You can run an empty SQL, so:
sSQL = "SELECT TOP 1 Exam.School, Exam.Certificate_No as LN FROM Exam WHERE Exam.School = S ORDER BY Exam.Certificate_No DESC;"  
Set rs = CurrentDb.OpenRecordset(sSQL)
  1. Use a string expression for the date value:
Me!Exam_Date.DefaultValue = Format(Nz(Me!Exam_Date.Value, Date), "\#yyyy\/mm\/dd\#")
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Gustav, I think you misunderstood. The 2nd part works for dates, for as many adds as I do on the form (in datasheet mode). I need to similar for Certificate_no, while incrementing the value vs static date(s). –  Jul 01 '23 at 18:10
0

The first problem of why the Set fails is patently obvious. You have only given a value to the sSQL after you use it in the OpenRecordset command.

The SQL itself is pointlessly complex and is the same as simply using MAX(). The DMAX() would also work if you stored the data properly.

The school number should not be stored as part of the certificate number. They should be two separate fields and concatenated when they need to be displayed. This ends the need to parse it.

Technically, as you are already storing the school code in one field, having it also stored in the certificate is a breach of normalization.

Galaxiom
  • 109
  • 4