I have some code which inserts data from a form into a table. The data inserts into the table fine.
However, upon inserting the date into the field exp_dt, even though the date is inserted into the table field, I still get another "error" table created. When I open the error table, it shows the exp_dt field and the issue is: Date out of Range..
The exp_dt was fixed at 12/31/9999 which I am to understand is the largest value excepted by microsoft. I tried changing it to 9998 and then 3000, but got the same results. Any ideas why this is occuring? Here is the code:
As mentioned, the code runs fine and it does populate the date with 12/31/9999 (or other date less than that) into the table, but I get the error table, which appears to be a contradiction..
Public Sub sub_save_record_pr()
Dim frm As Form
Set frm = Forms("frm_pr_proc_sel_req")
Dim d As Database
Dim str_sql As Variant
Dim str_fld01 As String 'assoc id
Dim str_fld02 As String 'assoc abs id
Dim str_fld03 As String 'assoc fname
Dim str_fld04 As String 'assoc lname
Dim str_fld05 As String 'pr code
Dim str_fld06 As String 'req type
Dim str_fld07 As String 'req email
Dim str_fld08 As String 'id of the record(s)
Dim dte_fld09 As Date 'eff dt
Dim dte_fld10 As Date 'exp dt
Dim dte_fld11 As Date 'date/time run of the request
Dim int_fld12 As Integer
Dim int_fld13 As Integer
Dim str_fld14 As String
Set d = CurrentDb
str_fld01 = frm!txt_assoc_id
If IsNull(frm!txt_assoc_abs_id) Then
str_fld02 = "NON"
Else
str_fld02 = frm!txt_assoc_abs_id
End If
str_fld03 = frm!txt_assoc_fname
str_fld04 = frm!txt_assoc_lname
str_fld05 = frm!txt_aa_code
str_fld06 = frm!txt_req_type_desc
str_fld07 = frm!txt_req_email
str_fld08 = frm.str_req_associd & frm.dte_curr_time
dte_fld09 = Now()
dte_fld10 = #12/31/3000#
dte_fld11 = Now()
int_fld12 = frm!txt_pr_code_unique_id
int_fld13 = frm!txt_req_type_id
str_fld14 = frm!txt_pr_code_desc
str_sql = "insert into " & frm.str_obj_name_tbl _
& "(assoc_id, assoc_abs_id, assoc_fname, assoc_lname, aa_code, req_type_desc, req_email, req_id, eff_dt ," _
& "exp_dt, run_dt, pr_code_id, req_type_id, pr_code_desc) values " _
& "('" & str_fld01 & "', '" & str_fld02 & "', '" & str_fld03 & "', '" & str_fld04 & "', '" & str_fld05 & "','" _
& str_fld06 & "', '" & str_fld07 & "', '" & str_fld08 & "', #" & dte_fld09 & "#, #" _
& dte_fld10 & "#, #" & dte_fld11 & "#, " & int_fld12 & "," & int_fld13 & ", '" & str_fld14 & "')"
d.Execute str_sql
d.Close
frm!cmd_reset_req_email.Enabled = False
End Sub
However, upon inserting the date into the field exp_dt, even though the date is inserted into the table field, I still get another "error" table created. When I open the error table, it shows the exp_dt field and the issue is: Date out of Range..
The exp_dt was fixed at 12/31/9999 which I am to understand is the largest value excepted by microsoft. I tried changing it to 9998 and then 3000, but got the same results. Any ideas why this is occuring? Here is the code:
As mentioned, the code runs fine and it does populate the date with 12/31/9999 (or other date less than that) into the table, but I get the error table, which appears to be a contradiction..
Public Sub sub_save_record_pr()
Dim frm As Form
Set frm = Forms("frm_pr_proc_sel_req")
Dim d As Database
Dim str_sql As Variant
Dim str_fld01 As String 'assoc id
Dim str_fld02 As String 'assoc abs id
Dim str_fld03 As String 'assoc fname
Dim str_fld04 As String 'assoc lname
Dim str_fld05 As String 'pr code
Dim str_fld06 As String 'req type
Dim str_fld07 As String 'req email
Dim str_fld08 As String 'id of the record(s)
Dim dte_fld09 As Date 'eff dt
Dim dte_fld10 As Date 'exp dt
Dim dte_fld11 As Date 'date/time run of the request
Dim int_fld12 As Integer
Dim int_fld13 As Integer
Dim str_fld14 As String
Set d = CurrentDb
str_fld01 = frm!txt_assoc_id
If IsNull(frm!txt_assoc_abs_id) Then
str_fld02 = "NON"
Else
str_fld02 = frm!txt_assoc_abs_id
End If
str_fld03 = frm!txt_assoc_fname
str_fld04 = frm!txt_assoc_lname
str_fld05 = frm!txt_aa_code
str_fld06 = frm!txt_req_type_desc
str_fld07 = frm!txt_req_email
str_fld08 = frm.str_req_associd & frm.dte_curr_time
dte_fld09 = Now()
dte_fld10 = #12/31/3000#
dte_fld11 = Now()
int_fld12 = frm!txt_pr_code_unique_id
int_fld13 = frm!txt_req_type_id
str_fld14 = frm!txt_pr_code_desc
str_sql = "insert into " & frm.str_obj_name_tbl _
& "(assoc_id, assoc_abs_id, assoc_fname, assoc_lname, aa_code, req_type_desc, req_email, req_id, eff_dt ," _
& "exp_dt, run_dt, pr_code_id, req_type_id, pr_code_desc) values " _
& "('" & str_fld01 & "', '" & str_fld02 & "', '" & str_fld03 & "', '" & str_fld04 & "', '" & str_fld05 & "','" _
& str_fld06 & "', '" & str_fld07 & "', '" & str_fld08 & "', #" & dte_fld09 & "#, #" _
& dte_fld10 & "#, #" & dte_fld11 & "#, " & int_fld12 & "," & int_fld13 & ", '" & str_fld14 & "')"
d.Execute str_sql
d.Close
frm!cmd_reset_req_email.Enabled = False
End Sub
