Database or Object read only and Type Mismatch error for a Serial Number Database (1 Viewer)

[surferdude800]

Hello,
I'm having an issue with the database being read only and a type mismatch due to OpenRecordset. I know this database use to work when it when it was just CurrentSerNum = RecSet(0) +1 was doing the incrementing.
I need the database to increment in a different way now.
Q=manufacture location
1st number is new=0 or used=1
2nd and third number =82
4th is single digit year aka 2015 = 5
6 and 7th are work week aka 25 for this week
8,9,10 are incremental numbers for the units made that workweek, so 001,002,003.
Then week 26 starts the increment starts back at 001.
Any help would be appreciated.
Thanks!


Private Sub MakeThoseProducts_Click()
On Error GoTo Err_MakeThoseProducts_Click
Dim NumericSerial As Double
Dim WorkWeek As String
Dim WorkYear As String
Dim WorkWeekNumber As Integer
Dim WorkYearNumber As Integer
Dim Location As String
Dim ETN As String
Dim ProductNumber As Integer
Dim CheckWW As String
Dim Serials As String
Dim ThreeDigitIncrement As String
Dim SerialIncrement As String



'Define Required Fields
Validation = 0
If IsNull(product_no.Value) Then MsgBox ("Please select a Product Model!")
If IsNull(product_no.Value) Then Validation = 1
If IsNull(assembly_by.Value) Then MsgBox ("Please enter Assembler 1's Initials!")
If IsNull(assembly_by.Value) Then Validation = 1
If IsNull(sales_order_num.Value) Then MsgBox ("Please enter a Sales Order Number!")
If IsNull(sales_order_num.Value) Then Validation = 1
If IsNull(sold_to.Value) Then MsgBox ("Please enter a Customer!")
If IsNull(sold_to.Value) Then Validation = 1
If IsNull(dev_num.Value) Then MsgBox ("Please enter a Job Number!")
If IsNull(dev_num.Value) Then Validation = 1
If IsNull(pcb_version.Value) Then MsgBox ("Please enter a BOM Rev!")
If IsNull(pcb_version.Value) Then Validation = 1
If IsNull(split_ratio.Value) Then MsgBox ("Please enter Fiber Type!")
If IsNull(split_ratio.Value) Then Validation = 1


If Validation = 0 Then
'Calculate serial number

Set RecSet = CurrentDb.OpenRecordset("SELECT Last(serial) as [MAX] FROM SN")
CurrentSerNum = RecSet(0)
CheckWW = Left(Right(CurrentSerNum, 5), 2)
WorkWeek = Format(Date, "ww")

If (CheckWW <> WorkWeek) Then
SerialIncrement = "001"
Else
ThreeDigitIncrement = Right(CurrentSerNum, 3)
SerialIncrement = ThreeDigitIncrement + 1
End If

ProductNumber = 82
Location = "Q"
WorkYear = Format(Date, "yy")
WorkWeekNumber = Val(WorkWeek)
WorkYearNumber = Val(WorkYear)
OneDigitYear = (WorkYearNumber - 10)
ETN = 0

RecSet(0) = Location & ETN & ProductNumber & OneDigitYear & WorkWeekNumber & SerialIncrement

RecSet.Close
Set RecSet = Nothing

'Number of products to be generated
IterationsLeft = QTY.Value
While IterationsLeft > 0

'Start the insert statement
SQLString = "INSERT INTO SN"

'Define Table Fields
SQLString = SQLString & "("
SQLString = SQLString & "product_no" & ", "
SQLString = SQLString & "serial" & ", "
SQLString = SQLString & "version_no" & ", "
SQLString = SQLString & "sales_order_num" & ", "
SQLString = SQLString & "sold_to" & ", "
SQLString = SQLString & "split_ratio" & ", "
SQLString = SQLString & "fiber_type"
If Not IsNull(pre_test_by.Value) Then SQLString = SQLString & ", " & "pre_test_by"
If Not IsNull(pre_test_date.Value) Then SQLString = SQLString & ", " & "pre_test_date"
If Not IsNull(assembly_by.Value) Then SQLString = SQLString & ", " & "assembly_by"
If Not IsNull(assembly_date.Value) Then SQLString = SQLString & ", " & "assembly_date"
If Not IsNull(mezz_card_serial.Value) Then SQLString = SQLString & ", " & "mezz_card_serial"
If Not IsNull(notes_for_customer.Value) Then SQLString = SQLString & ", " & "notes_for_customer"
If Not IsNull(notes.Value) Then SQLString = SQLString & ", " & "notes"
If Not IsNull(splitter_test_report.Value) Then SQLString = SQLString & ", " & "splitter_test_report"
If Not IsNull(FPGA1_ver.Value) Then SQLString = SQLString & ", " & "fpga1_ver"
If Not IsNull(CPLD1_ver.Value) Then SQLString = SQLString & ", " & "cpld1_ver"
If Not IsNull(FPGA2_ver.Value) Then SQLString = SQLString & ", " & "fpga2_ver"
If Not IsNull(CPLD2_ver.Value) Then SQLString = SQLString & ", " & "cpld2_ver"
If Not IsNull(connector_type.Value) Then SQLString = SQLString & ", " & "connector_type"
If Not IsNull(xcvrs_no.Value) Then SQLString = SQLString & ", " & "xcvrs_no"
If Not IsNull(xcvrs_type.Value) Then SQLString = SQLString & ", " & "xcvrs_type"
If Not IsNull(options_installed.Value) Then SQLString = SQLString & ", " & "options_installed"
If Not IsNull(pcb_version.Value) Then SQLString = SQLString & ", " & "pcb_version"
If Not IsNull(link_safe.Value) Then SQLString = SQLString & ", " & "link_safe"
If Not IsNull(custom.Value) Then SQLString = SQLString & ", " & "custom"
If Not IsNull(mezz_card_type.Value) Then SQLString = SQLString & ", " & "mezz_card_type"
If Not IsNull(ps1.Value) Then SQLString = SQLString & ", " & "ps1"
If Not IsNull(ps2.Value) Then SQLString = SQLString & ", " & "ps2"
If Not IsNull(fan.Value) Then SQLString = SQLString & ", " & "fan"
If Not IsNull(pcb_pn.Value) Then SQLString = SQLString & ", " & "pcb_pn"
If Not IsNull(pcb_serial.Value) Then SQLString = SQLString & ", " & "pcb_serial"
If Not IsNull(mgmt_rev.Value) Then SQLString = SQLString & ", " & "mgmt_rev"
If Not IsNull(MAC_address.Value) Then SQLString = SQLString & ", " & "MAC_address"
If Not IsNull(trans_rev.Value) Then SQLString = SQLString & ", " & "trans_rev"
If Not IsNull(psafe_rev.Value) Then SQLString = SQLString & ", " & "psafe_rev"
If Not IsNull(lcd_cpld.Value) Then SQLString = SQLString & ", " & "lcd_cpld"
If Not IsNull(lcd_rev.Value) Then SQLString = SQLString & ", " & "lcd_rev"
If Not IsNull(mod1_pn.Value) Then SQLString = SQLString & ", " & "mod1_pn"
If Not IsNull(mod1_sn.Value) Then SQLString = SQLString & ", " & "mod1_sn"
If Not IsNull(mod1_opt.Value) Then SQLString = SQLString & ", " & "mod1_opt"
If Not IsNull(mod1_mpn.Value) Then SQLString = SQLString & ", " & "mod1_mpn"
If Not IsNull(mod1_msn.Value) Then SQLString = SQLString & ", " & "mod1_msn"
If Not IsNull(mod1_m2pn.Value) Then SQLString = SQLString & ", " & "mod1_m2pn"
If Not IsNull(mod1_m2sn.Value) Then SQLString = SQLString & ", " & "mod1_m2sn"
If Not IsNull(mod2_pn.Value) Then SQLString = SQLString & ", " & "mod2_pn"
If Not IsNull(mod2_sn.Value) Then SQLString = SQLString & ", " & "mod2_sn"
If Not IsNull(mod2_opt.Value) Then SQLString = SQLString & ", " & "mod2_opt"
If Not IsNull(mod2_mpn.Value) Then SQLString = SQLString & ", " & "mod2_mpn"
If Not IsNull(mod2_msn.Value) Then SQLString = SQLString & ", " & "mod2_msn"
If Not IsNull(mod2_m2pn.Value) Then SQLString = SQLString & ", " & "mod2_m2pn"
If Not IsNull(mod2_m2sn.Value) Then SQLString = SQLString & ", " & "mod2_m2sn"
If Not IsNull(mod3_pn.Value) Then SQLString = SQLString & ", " & "mod3_pn"
If Not IsNull(mod3_sn.Value) Then SQLString = SQLString & ", " & "mod3_sn"
If Not IsNull(mod3_opt.Value) Then SQLString = SQLString & ", " & "mod3_opt"
If Not IsNull(mod3_mpn.Value) Then SQLString = SQLString & ", " & "mod3_mpn"
If Not IsNull(mod3_msn.Value) Then SQLString = SQLString & ", " & "mod3_msn"
If Not IsNull(mod3_m2pn.Value) Then SQLString = SQLString & ", " & "mod3_m2pn"
If Not IsNull(mod3_m2sn.Value) Then SQLString = SQLString & ", " & "mod3_m2sn"
If Not IsNull(mod4_pn.Value) Then SQLString = SQLString & ", " & "mod4_pn"
If Not IsNull(mod4_sn.Value) Then SQLString = SQLString & ", " & "mod4_sn"
If Not IsNull(mod4_opt.Value) Then SQLString = SQLString & ", " & "mod4_opt"
If Not IsNull(mod4_mpn.Value) Then SQLString = SQLString & ", " & "mod4_mpn"
If Not IsNull(mod4_msn.Value) Then SQLString = SQLString & ", " & "mod4_msn"
If Not IsNull(mod4_m2pn.Value) Then SQLString = SQLString & ", " & "mod4_m2pn"
If Not IsNull(mod4_m2sn.Value) Then SQLString = SQLString & ", " & "mod4_m2sn"
If Not IsNull(mezz1_pn.Value) Then SQLString = SQLString & ", " & "mezz1_pn"
If Not IsNull(mezz1_sn.Value) Then SQLString = SQLString & ", " & "mezz1_sn"
If Not IsNull(mezz2_pn.Value) Then SQLString = SQLString & ", " & "mezz2_pn"
If Not IsNull(mezz2_sn.Value) Then SQLString = SQLString & ", " & "mezz2_sn"
If Not IsNull(mezz3_pn.Value) Then SQLString = SQLString & ", " & "mezz3_pn"
If Not IsNull(mezz3_sn.Value) Then SQLString = SQLString & ", " & "mezz3_sn"
If Not IsNull(mezz4_pn.Value) Then SQLString = SQLString & ", " & "mezz4_pn"
If Not IsNull(mezz4_sn.Value) Then SQLString = SQLString & ", " & "mezz4_sn"
If Not IsNull(mezz5_pn.Value) Then SQLString = SQLString & ", " & "mezz5_pn"
If Not IsNull(mezz5_sn.Value) Then SQLString = SQLString & ", " & "mezz5_sn"
If Not IsNull(mezz6_pn.Value) Then SQLString = SQLString & ", " & "mezz6_pn"
If Not IsNull(mezz6_sn.Value) Then SQLString = SQLString & ", " & "mezz6_sn"
If Not IsNull(mezz7_pn.Value) Then SQLString = SQLString & ", " & "mezz7_pn"
If Not IsNull(mezz7_sn.Value) Then SQLString = SQLString & ", " & "mezz7_sn"
If Not IsNull(mezz8_pn.Value) Then SQLString = SQLString & ", " & "mezz8_pn"
If Not IsNull(mezz8_sn.Value) Then SQLString = SQLString & ", " & "mezz8_sn"
If Not IsNull(optics.Value) Then SQLString = SQLString & ", " & "optics"
If Not IsNull(hwsuppdur.Value) Then SQLString = SQLString & ", " & "hwsuppdur"
If Not IsNull(swsuppdur.Value) Then SQLString = SQLString & ", " & "swsuppdur"
If Not IsNull(platsuppdur.Value) Then SQLString = SQLString & ", " & "platsuppdur"
If Not IsNull(trans_sn.Value) Then SQLString = SQLString & ", " & "trans_sn"
If Not IsNull(psafe_sn.Value) Then SQLString = SQLString & ", " & "psafe_sn"
If Not IsNull(lcd_sn.Value) Then SQLString = SQLString & ", " & "lcd_sn"
If Not IsNull(mezz1_pm.Value) Then SQLString = SQLString & ", " & "mezz1_pm"
If Not IsNull(mezz2_pm.Value) Then SQLString = SQLString & ", " & "mezz2_pm"
If Not IsNull(mezz3_pm.Value) Then SQLString = SQLString & ", " & "mezz3_pm"
If Not IsNull(mezz4_pm.Value) Then SQLString = SQLString & ", " & "mezz4_pm"
If Not IsNull(mezz5_pm.Value) Then SQLString = SQLString & ", " & "mezz5_pm"
If Not IsNull(mezz6_pm.Value) Then SQLString = SQLString & ", " & "mezz6_pm"
If Not IsNull(mezz7_pm.Value) Then SQLString = SQLString & ", " & "mezz7_pm"
If Not IsNull(mezz8_pm.Value) Then SQLString = SQLString & ", " & "mezz8_pm"
If Not IsNull(mod1_pm1.Value) Then SQLString = SQLString & ", " & "mod1_pm1"
If Not IsNull(mod1_pm2.Value) Then SQLString = SQLString & ", " & "mod1_pm2"
If Not IsNull(mod2_pm1.Value) Then SQLString = SQLString & ", " & "mod2_pm1"
If Not IsNull(mod2_pm2.Value) Then SQLString = SQLString & ", " & "mod2_pm2"
If Not IsNull(mod3_pm1.Value) Then SQLString = SQLString & ", " & "mod3_pm1"
If Not IsNull(mod3_pm2.Value) Then SQLString = SQLString & ", " & "mod3_pm2"
If Not IsNull(mod4_pm1.Value) Then SQLString = SQLString & ", " & "mod4_pm1"
If Not IsNull(mod4_pm2.Value) Then SQLString = SQLString & ", " & "mod4_pm2"
If Not IsNull(dev_num.Value) Then SQLString = SQLString & ", " & "dev_num"
If Not IsNull(elma_sn.Value) Then SQLString = SQLString & ", " & "elma_sn"
SQLString = SQLString & ")"

'Define matching Values
SQLString = SQLString & " VALUES ("
SQLString = SQLString & product_no.Value & ", "
SQLString = SQLString & CurrSerNum & ", "
SQLString = SQLString & "'" & version_no.Value & "', "
SQLString = SQLString & "'" & sales_order_num.Value & "', "
SQLString = SQLString & "'" & sold_to.Value & "', "
SQLString = SQLString & split_ratio.Value & ", "
SQLString = SQLString & fiber_type.Value
If Not IsNull(pre_test_by.Value) Then SQLString = SQLString & ", '" & pre_test_by.Value & "'"
If Not IsNull(pre_test_date.Value) Then SQLString = SQLString & ", '" & pre_test_date.Value & "'"
If Not IsNull(assembly_by.Value) Then SQLString = SQLString & ", '" & assembly_by.Value & "'"
If Not IsNull(assembly_date.Value) Then SQLString = SQLString & ", '" & assembly_date.Value & "'"
If Not IsNull(mezz_card_serial.Value) Then SQLString = SQLString & ", '" & mezz_card_serial.Value & "'"
If Not IsNull(notes_for_customer.Value) Then SQLString = SQLString & ", '" & notes_for_customer.Value & "'"
If Not IsNull(notes.Value) Then SQLString = SQLString & ", '" & notes.Value & "'"
If Not IsNull(splitter_test_report.Value) Then SQLString = SQLString & ", '" & splitter_test_report.Value & "'"
If Not IsNull(FPGA1_ver.Value) Then SQLString = SQLString & ", '" & FPGA1_ver.Value & "'"
If Not IsNull(CPLD1_ver.Value) Then SQLString = SQLString & ", '" & CPLD1_ver.Value & "'"
If Not IsNull(FPGA2_ver.Value) Then SQLString = SQLString & ", '" & FPGA2_ver.Value & "'"
If Not IsNull(CPLD2_ver.Value) Then SQLString = SQLString & ", '" & CPLD2_ver.Value & "'"
If Not IsNull(connector_type.Value) Then SQLString = SQLString & ", '" & connector_type.Value & "'"
If Not IsNull(xcvrs_no.Value) Then SQLString = SQLString & ", '" & xcvrs_no.Value & "'"
If Not IsNull(xcvrs_type.Value) Then SQLString = SQLString & ", '" & xcvrs_type.Value & "'"
If Not IsNull(options_installed.Value) Then SQLString = SQLString & ", '" & options_installed.Value & "'"
If Not IsNull(pcb_version.Value) Then SQLString = SQLString & ", '" & pcb_version.Value & "'"
If Not IsNull(link_safe.Value) Then SQLString = SQLString & ", " & link_safe.Value
If Not IsNull(custom.Value) Then SQLString = SQLString & ", " & custom.Value
If Not IsNull(mezz_card_type.Value) Then SQLString = SQLString & ", '" & mezz_card_type.Value & "'"
If Not IsNull(ps1.Value) Then SQLString = SQLString & ", '" & ps1.Value & "'"
If Not IsNull(ps2.Value) Then SQLString = SQLString & ", '" & ps2.Value & "'"
If Not IsNull(fan.Value) Then SQLString = SQLString & ", '" & fan.Value & "'"
If Not IsNull(pcb_pn.Value) Then SQLString = SQLString & ", '" & pcb_pn.Value & "'"
If Not IsNull(pcb_serial.Value) Then SQLString = SQLString & ", '" & pcb_serial.Value & "'"
If Not IsNull(mgmt_rev.Value) Then SQLString = SQLString & ", '" & mgmt_rev.Value & "'"
If Not IsNull(MAC_address.Value) Then SQLString = SQLString & ", '" & MAC_address.Value & "'"
If Not IsNull(trans_rev.Value) Then SQLString = SQLString & ", '" & trans_rev.Value & "'"
If Not IsNull(psafe_rev.Value) Then SQLString = SQLString & ", '" & psafe_rev.Value & "'"
If Not IsNull(lcd_cpld.Value) Then SQLString = SQLString & ", '" & lcd_cpld.Value & "'"
If Not IsNull(lcd_rev.Value) Then SQLString = SQLString & ", '" & lcd_rev.Value & "'"
If Not IsNull(mod1_pn.Value) Then SQLString = SQLString & ", '" & mod1_pn.Value & "'"
If Not IsNull(mod1_sn.Value) Then SQLString = SQLString & ", '" & mod1_sn.Value & "'"
If Not IsNull(mod1_opt.Value) Then SQLString = SQLString & ", '" & mod1_opt.Value & "'"
If Not IsNull(mod1_mpn.Value) Then SQLString = SQLString & ", '" & mod1_mpn.Value & "'"
If Not IsNull(mod1_msn.Value) Then SQLString = SQLString & ", '" & mod1_msn.Value & "'"
If Not IsNull(mod1_m2pn.Value) Then SQLString = SQLString & ", '" & mod1_m2pn.Value & "'"
If Not IsNull(mod1_m2sn.Value) Then SQLString = SQLString & ", '" & mod1_m2sn.Value & "'"
If Not IsNull(mod2_pn.Value) Then SQLString = SQLString & ", '" & mod2_pn.Value & "'"
If Not IsNull(mod2_sn.Value) Then SQLString = SQLString & ", '" & mod2_sn.Value & "'"
If Not IsNull(mod2_opt.Value) Then SQLString = SQLString & ", '" & mod2_opt.Value & "'"
If Not IsNull(mod2_mpn.Value) Then SQLString = SQLString & ", '" & mod2_mpn.Value & "'"
If Not IsNull(mod2_msn.Value) Then SQLString = SQLString & ", '" & mod2_msn.Value & "'"
If Not IsNull(mod2_m2pn.Value) Then SQLString = SQLString & ", '" & mod2_m2pn.Value & "'"
If Not IsNull(mod2_m2sn.Value) Then SQLString = SQLString & ", '" & mod2_m2sn.Value & "'"
If Not IsNull(mod3_pn.Value) Then SQLString = SQLString & ", '" & mod3_pn.Value & "'"
If Not IsNull(mod3_sn.Value) Then SQLString = SQLString & ", '" & mod3_sn.Value & "'"
If Not IsNull(mod3_opt.Value) Then SQLString = SQLString & ", '" & mod3_opt.Value & "'"
If Not IsNull(mod3_mpn.Value) Then SQLString = SQLString & ", '" & mod3_mpn.Value & "'"
If Not IsNull(mod3_msn.Value) Then SQLString = SQLString & ", '" & mod3_msn.Value & "'"
If Not IsNull(mod3_m2pn.Value) Then SQLString = SQLString & ", '" & mod3_m2pn.Value & "'"
If Not IsNull(mod3_m2sn.Value) Then SQLString = SQLString & ", '" & mod3_m2sn.Value & "'"
If Not IsNull(mod4_pn.Value) Then SQLString = SQLString & ", '" & mod4_pn.Value & "'"
If Not IsNull(mod4_sn.Value) Then SQLString = SQLString & ", '" & mod4_sn.Value & "'"
If Not IsNull(mod4_opt.Value) Then SQLString = SQLString & ", '" & mod4_opt.Value & "'"
If Not IsNull(mod4_mpn.Value) Then SQLString = SQLString & ", '" & mod4_mpn.Value & "'"
If Not IsNull(mod4_msn.Value) Then SQLString = SQLString & ", '" & mod4_msn.Value & "'"
If Not IsNull(mod4_m2pn.Value) Then SQLString = SQLString & ", '" & mod4_m2pn.Value & "'"
If Not IsNull(mod4_m2sn.Value) Then SQLString = SQLString & ", '" & mod4_m2sn.Value & "'"
If Not IsNull(mezz1_pn.Value) Then SQLString = SQLString & ", '" & mezz1_pn.Value & "'"
If Not IsNull(mezz1_sn.Value) Then SQLString = SQLString & ", '" & mezz1_sn.Value & "'"
If Not IsNull(mezz2_pn.Value) Then SQLString = SQLString & ", '" & mezz2_pn.Value & "'"
If Not IsNull(mezz2_sn.Value) Then SQLString = SQLString & ", '" & mezz2_sn.Value & "'"
If Not IsNull(mezz3_pn.Value) Then SQLString = SQLString & ", '" & mezz3_pn.Value & "'"
If Not IsNull(mezz3_sn.Value) Then SQLString = SQLString & ", '" & mezz3_sn.Value & "'"
If Not IsNull(mezz4_pn.Value) Then SQLString = SQLString & ", '" & mezz4_pn.Value & "'"
If Not IsNull(mezz4_sn.Value) Then SQLString = SQLString & ", '" & mezz4_sn.Value & "'"
If Not IsNull(mezz5_pn.Value) Then SQLString = SQLString & ", '" & mezz5_pn.Value & "'"
If Not IsNull(mezz5_sn.Value) Then SQLString = SQLString & ", '" & mezz5_sn.Value & "'"
If Not IsNull(mezz6_pn.Value) Then SQLString = SQLString & ", '" & mezz6_pn.Value & "'"
If Not IsNull(mezz6_sn.Value) Then SQLString = SQLString & ", '" & mezz6_sn.Value & "'"
If Not IsNull(mezz7_pn.Value) Then SQLString = SQLString & ", '" & mezz7_pn.Value & "'"
If Not IsNull(mezz7_sn.Value) Then SQLString = SQLString & ", '" & mezz7_sn.Value & "'"
If Not IsNull(mezz8_pn.Value) Then SQLString = SQLString & ", '" & mezz8_pn.Value & "'"
If Not IsNull(mezz8_sn.Value) Then SQLString = SQLString & ", '" & mezz8_sn.Value & "'"
If Not IsNull(optics.Value) Then SQLString = SQLString & ", '" & optics.Value & "'"
If Not IsNull(hwsuppdur.Value) Then SQLString = SQLString & ", '" & hwsuppdur.Value & "'"
If Not IsNull(swsuppdur.Value) Then SQLString = SQLString & ", '" & swsuppdur.Value & "'"
If Not IsNull(platsuppdur.Value) Then SQLString = SQLString & ", '" & platsuppdur.Value & "'"
If Not IsNull(trans_sn.Value) Then SQLString = SQLString & ", '" & trans_sn.Value & "'"
If Not IsNull(psafe_sn.Value) Then SQLString = SQLString & ", '" & psafe_sn.Value & "'"
If Not IsNull(lcd_sn.Value) Then SQLString = SQLString & ", '" & lcd_sn.Value & "'"
If Not IsNull(mezz1_pm.Value) Then SQLString = SQLString & ", '" & mezz1_pm.Value & "'"
If Not IsNull(mezz2_pm.Value) Then SQLString = SQLString & ", '" & mezz2_pm.Value & "'"
If Not IsNull(mezz3_pm.Value) Then SQLString = SQLString & ", '" & mezz3_pm.Value & "'"
If Not IsNull(mezz4_pm.Value) Then SQLString = SQLString & ", '" & mezz4_pm.Value & "'"
If Not IsNull(mezz5_pm.Value) Then SQLString = SQLString & ", '" & mezz5_pm.Value & "'"
If Not IsNull(mezz6_pm.Value) Then SQLString = SQLString & ", '" & mezz6_pm.Value & "'"
If Not IsNull(mezz7_pm.Value) Then SQLString = SQLString & ", '" & mezz7_pm.Value & "'"
If Not IsNull(mezz8_pm.Value) Then SQLString = SQLString & ", '" & mezz8_pm.Value & "'"
If Not IsNull(mod1_pm1.Value) Then SQLString = SQLString & ", '" & mod1_pm1.Value & "'"
If Not IsNull(mod1_pm2.Value) Then SQLString = SQLString & ", '" & mod1_pm2.Value & "'"
If Not IsNull(mod2_pm1.Value) Then SQLString = SQLString & ", '" & mod2_pm1.Value & "'"
If Not IsNull(mod2_pm2.Value) Then SQLString = SQLString & ", '" & mod2_pm2.Value & "'"
If Not IsNull(mod3_pm1.Value) Then SQLString = SQLString & ", '" & mod3_pm1.Value & "'"
If Not IsNull(mod3_pm2.Value) Then SQLString = SQLString & ", '" & mod3_pm2.Value & "'"
If Not IsNull(mod4_pm1.Value) Then SQLString = SQLString & ", '" & mod4_pm1.Value & "'"
If Not IsNull(mod4_pm2.Value) Then SQLString = SQLString & ", '" & mod4_pm2.Value & "'"
If Not IsNull(dev_num.Value) Then SQLString = SQLString & ", '" & dev_num.Value & "'"
If Not IsNull(elma_sn.Value) Then SQLString = SQLString & ", '" & elma_sn.Value & "'"
SQLString = SQLString & ")"

'Execute SQL String
CurrentDb.Execute (SQLString)
IterationsLeft = IterationsLeft - 1
CurrSerNum = CurrSerNum + 1

Wend
MsgBox ("Products created!")
'MsgBox SQLString & CurrSerNum
End If

Exit_MakeThoseProducts_Click:
Exit Sub

Err_MakeThoseProducts_Click:
MsgBox SQLString
MsgBox Err.Description
Resume Exit_MakeThoseProducts_Click

End Sub

 

[MarkK]

Hi and welcome to the forum.

If you post an error, and you post code, it will improve your likelihood of positive results if you indicate which line causes the error. Also, to tag your code, highlight the text and hit the number sign (#) above.

This has the effect of 
   offsetting your code
      and preserving your indents
      which makes your work much easier for others to understand
      and increases your chance of getting useful feedback

Hope this helps,

 

[surferdude800]

Somewhere in these two lines is my issue. I think because its a text field and then I'm doing a string & number mix into a string. That's causing this issue.

Set RecSet = CurrentDb.OpenRecordset("SELECT Last(serial) as [MAX] FROM SN")
CurrentSerNum = RecSet(0)

RecSet(0) = Location & ETN & ProductNumber & OneDigitYear & WorkWeekNumber & SerialIncrement

RecSet.Close
Set RecSet = Nothing

 

[vbaInet]

Comment out the line below and re-run the code. It will stop at the line it errors.

On Error GoTo Err_MakeThoseProducts_Click

 

[surferdude800]

Run-Time error '94'
Invalid use of Null

 

[vbaInet]

Which code line does it highlight?

 

[MarkK]

Remember, for us to understand the error in your code you need to communicate

1) the error, and 
2) the code that causes it.

 

[surferdude800]

Sorry, please disregard my previous post. I had to rebuild my database and it didn't have any previous serial numbers to get to increase the increment. That why it was a null value.

For for the Read only error I got:

Run-time error '3027':
Connot update. Database or object is read-only.

Stopped and highlighted this line:

RecSet(0) = Location & ETN & ProductNumber & OneDigitYear & WorkWeekNumber & SerialIncrement

Thanks for all your help!

 

[vbaInet]

Here's a link re read-only recordsets:

http://allenbrowne.com/ser-61.html

See if you can spot the reason why yours is read-only. If you're having difficulty I'll point it out to you.

By the way, is there any reason why you're not using a bound form? Plus I can see a not so ideal table structure from your code.

 

[jdraw]

I agree with vbaInet re your table structure. And that is one pile of IF statements.

What exactly, in simple English, is this statement suppose to do?

RecSet(0) = Location & ETN & ProductNumber & OneDigitYear & WorkWeekNumber & SerialIncrement

For reference, readers know nothing of you, your environment, or your database. It is often helpful to set a context for your post by describing to readers what your opportunity/problem is in business terms. We have no idea of what business your database is intended to support.

From experience, I can say that getting your tables and relationships designed to meet the requirements is a key first step with database.

Also, relational database is designed to work with atomic fields --1 fact 1 field.

Good luck with your project.

 

[surferdude800]

Thank you vbaInet and jdraw. I did see that re post before, but I didn't manage to fix anything using the suggestions I saw. I will look again and see if I find anything.

Any tips on better programming this are appreciated.

This database is a serial number database for the company I work for. We have a wealth of information that goes into what we build. Plus final inspection test data before we send the unit to the customer. So, there are multiple people using this database at anyone time.

Basically, what i'm doing now is rewriting the way our database does serial numbers, because we secured the use of are products in a third parties security product for customers. And they want the units serial numbered in a certain way. Which is what this statement is for:

RecSet(0) = Location & ETN & ProductNumber & OneDigitYear & WorkWeekNumber & SerialIncrement

It basically combines all the data I need to generate a serial number. I described what each piece is in my first post.

 

[MSAccessRookie]

Thank you vbaInet and jdraw. I did see that re post before, but I didn't manage to fix anything using the suggestions I saw. I will look again and see if I find anything.

Any tips on better programming this are appreciated.

This database is a serial number database for the company I work for. We have a wealth of information that goes into what we build. Plus final inspection test data before we send the unit to the customer. So, there are multiple people using this database at anyone time.

Basically, what i'm doing now is rewriting the way our database does serial numbers, because we secured the use of are products in a third parties security product for customers. And they want the units serial numbered in a certain way. Which is what this statement is for:


It basically combines all the data I need to generate a serial number. I described what each piece is in my first post.

I did not see that this has been covered yet, but the pieces of your new Serial Number are not all strings. If you are going to concatenate, then all of the pieces will need to be strings, since a string cannot be concatenated with an Integer (or any other type of number). When a value is an Integer (or any other type of number), you should use cStr(theValue) instead.

 

[spikepl]

MSAccessRoookie:

all of the pieces will need to be strings

Not really. They become so anyway, because Access does an implicit conversion. And of course you'd wind up in big doo doo, if some values were decimal numbers and you had "," as decimal separator.

But you do have a very valid point, that one ought make it a habit to convert explicitly.

 

[vbaInet]

Thank you vbaInet and jdraw. I did see that re post before, but I didn't manage to fix anything using the suggestions I saw. I will look again and see if I find anything.

Set RecSet = CurrentDb.OpenRecordset("SELECT Last(serial) as [MAX] FROM SN")

In red, point number 3 in the link sent.

Any tips on better programming this are appreciated.

It basically combines all the data I need to generate a serial number. I described what each piece is in my first post.

* Use bound forms
* Derive each part of the serial number, save them as separate values (I think you're already doing this), and concatenate the values when you need to present the serial number. You don't need to save the concatenated value as the serial number, it's duplication of information and calculated shouldn't be saved but simply calculated when required.
* Look into normalisation.

 

[surferdude800]

How would you suggest fixing it?

 

[vbaInet]

There's really nothing to fix there. I've already given you suggestions on the best ways to work.

 

[surferdude800]

So, update on what i'm doing. I found out that it is the Q in my serial that causing the issue. But thank you guys for your help.