What's wrong with my code?

[ychousa]

Hi. 2 tables are involved in this case. Serial Number Table and Inventory. The naming is not so good, but anyways, the logic is like this: Serial Number Table and Inventory are related by a third field called OrderID. Inventory contains the list of current inventory and availability of each serial number(Ready), I want to update the inventory table when Serial Number is entered on Serial Number Form which contains Inventory subform. But my code works only for the first If statement, and the remaining conditions don't work. What is wrong with my code? I spent a long hours to make this work, but couldn't make it. I need a solution ASAP because I have to finish this project this week. Thanks in advance for any help.

Private Sub Serial_Number_AfterUpdate()
Dim Serial1 As String, Serial2 As String, Ready As String
Dim CurDB As Database
Dim rs1 As Recordset, rs2 As Recordset, rs3 As Recordset


If Not IsNull(Me![Serial Number]) Then

Serial1 = "SELECT [Serial Number Table].[Serial Number] FROM [Serial Number Table] WHERE [Serial Number Table].[Serial Number] = '" & Me![Serial Number] & "'"
Serial2 = "SELECT [Inventory].[Serial Number] FROM [Inventory] WHERE [Inventory].[Serial Number] = '" & Me![Serial Number] & "'"
Ready = "SELECT [Inventory].[Ready] FROM [Inventory] WHERE [Inventory].[Serial Number] = '" & Me![Serial Number] & "'"

'Debug.Print IngYear

Set CurDB = CurrentDb()
Set rs1 = CurDB.OpenRecordset(Serial1, dbOpenDynaset)
Set rs2 = CurDB.OpenRecordset(Serial2, dbOpenDynaset)
Set rs3 = CurDB.OpenRecordset(Ready, dbOpenDynaset)

If Not rs1.EOF Then

If Len(rs1![Serial Number]) > 0 Then
MsgBox ("The Serial Number you entered is a duplicate record.")


Else
If Len(rs2![Serial Number]) = 0 Then
MsgBox ("The Serial Number you entered is not in Inventory table")

Else
If Len(rs3![Ready]) = 0 Then
MsgBox ("The Serial Number you entered is not ready yet")

Else
Me![Inventory subform].Form.[Serial Number] = Me![Serial Number]

Me![Inventory subform].Form.[Showmodel] = Me.Parent.Showmodel



End If

End If

End If

End If
rs1.Close
rs2.Close
rs3.Close

Set rs1 = Nothing
Set rs2 = Nothing
Set rs3 = Nothing

Set CurDB = Nothing

End If

End Sub

 

[hymn]

What version of Access do you have?
Above 97 you will need to reference and declare DAO

Hope this helps
Hymn

 

[ychousa]

I use 2002 and DAO is referenced. I found that the 3rd If statementIf Len(rs1![Serial Number]) > 0 Then
) is somehow causing the error. The commands under the statement don't work. If I remove the If statement and End If line, the commands work. Any suggestions?