Hi folks,
I need your help again.....
I'm building a db which will record 'Errors'. These errors need to have unique id's.
I have two tables and a query. Table A (TBL - Fund errors) records the log date, error reference & error number. Table B (TBL - Maxerr) records the max error number per year based on Table A. Query A pulls the max error number from Table B for the current year.
When I open a data entry form I want the db to check for the max error number for the current year.
If one doesn't exist then I need to add a record to Table B where [Table B]![Maxyr] = current date and [Table B]![Maxerr] = 0001. I then need to build an error reference for a field on the data entry form which is bound to Table A producing an error ref such as E20030001 (E & YYYY & 0001)
If a max error for the current year does exist then I need to update the max error record in Query A to [Maxerr]+1 and undate the [Maxyr] to the current date. I then need to build an error reference for the field on the data entry producing an error ref such as E20030002 (E & YYYY & [Maxerr]+1)
I'm using a second table to avoid delays from querying the main error table for max dates & error numbers.
The code I've written so far is;
Dim Form1 As String
Dim Maxerr1 As Variant
Form1 = "FRM - Fund errors (data entry)"
Maxerr1 = DLookup("[Maxerr]", "TBL - Maxval", "[Maxyr]>" & DateSerial(DatePart("yyyy", Date), 1, 1))
If IsNull(Maxerr1) Or Maxerr1 = Empty Or Maxerr1 = "" Then
DoCmd.RunSQL "INSERT INTO TBL - Maxval(Maxyr,Maxerr) values(" & Date & ",1)"
DoCmd.OpenForm Form1
[Forms]![FRM - Fund errors (Data entry)]![Creator] = CurrentUser()
[Forms]![FRM - Fund errors (Data entry)]![ERREF] = "E" & DatePart("yyyy", Date) & "0001"
Else
DoCmd.RunSQL "UPDATE QRY - Current year (maxerr) SET [Maxyr]= " & Date & "and [Maxerr]=" & Maxerr1 + 1 & ";"
DoCmd.OpenForm Form1
[Forms]![FRM - Fund errors (Data entry)]![Creator] = CurrentUser()
[Forms]![FRM - Fund errors (Data entry)]![ERREF] = "E" & DatePart("yyyy", Date) & Maxerr1 + 1
End If
Doesn't seem to want to work...any ideas where I'm going wrong? I'm getting SQL syntax errors.
I need your help again.....
I'm building a db which will record 'Errors'. These errors need to have unique id's.
I have two tables and a query. Table A (TBL - Fund errors) records the log date, error reference & error number. Table B (TBL - Maxerr) records the max error number per year based on Table A. Query A pulls the max error number from Table B for the current year.
When I open a data entry form I want the db to check for the max error number for the current year.
If one doesn't exist then I need to add a record to Table B where [Table B]![Maxyr] = current date and [Table B]![Maxerr] = 0001. I then need to build an error reference for a field on the data entry form which is bound to Table A producing an error ref such as E20030001 (E & YYYY & 0001)
If a max error for the current year does exist then I need to update the max error record in Query A to [Maxerr]+1 and undate the [Maxyr] to the current date. I then need to build an error reference for the field on the data entry producing an error ref such as E20030002 (E & YYYY & [Maxerr]+1)
I'm using a second table to avoid delays from querying the main error table for max dates & error numbers.
The code I've written so far is;
Dim Form1 As String
Dim Maxerr1 As Variant
Form1 = "FRM - Fund errors (data entry)"
Maxerr1 = DLookup("[Maxerr]", "TBL - Maxval", "[Maxyr]>" & DateSerial(DatePart("yyyy", Date), 1, 1))
If IsNull(Maxerr1) Or Maxerr1 = Empty Or Maxerr1 = "" Then
DoCmd.RunSQL "INSERT INTO TBL - Maxval(Maxyr,Maxerr) values(" & Date & ",1)"
DoCmd.OpenForm Form1
[Forms]![FRM - Fund errors (Data entry)]![Creator] = CurrentUser()
[Forms]![FRM - Fund errors (Data entry)]![ERREF] = "E" & DatePart("yyyy", Date) & "0001"
Else
DoCmd.RunSQL "UPDATE QRY - Current year (maxerr) SET [Maxyr]= " & Date & "and [Maxerr]=" & Maxerr1 + 1 & ";"
DoCmd.OpenForm Form1
[Forms]![FRM - Fund errors (Data entry)]![Creator] = CurrentUser()
[Forms]![FRM - Fund errors (Data entry)]![ERREF] = "E" & DatePart("yyyy", Date) & Maxerr1 + 1
End If
Doesn't seem to want to work...any ideas where I'm going wrong? I'm getting SQL syntax errors.
