Simple Recordset Question

[sanders720]

Below is a segment of my code.

I am opening a table in a recordset. This part of the loop is checking for data. If the data does not exist, it's okay to add. Otherwise, it should not add it. The problem is, it adds it every time.

I believe there is something wrong with the logic here:

If rsNewTable.EOF Then

I've also tried

If rsNewTable.recordcount=0 then


Complete Segment Listing:

Dim sqlNewTable As String
sqlNewTable = "SELECT * FROM tblSubAssyIsolator " & _
"WHERE JobNo = " & Combo6.Value & " And " & _
"InventorFileName = '" & rs1.Fields("InventorFileName") & "' And " & _
"SubAssy = '" & rs1.Fields("SubAssy") & "'"

If addrec = 1 Then
rsNewTable.Open sqlNewTable, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
If rsNewTable.EOF Then
rsNewTable.AddNew
rsNewTable.Fields("JobNo") = Combo6.Value
If Not IsNull(rs1.Fields("InventorFileName")) Then rsNewTable.Fields("InventorFileName") = rs1.Fields("InventorFileName")
rsNewTable.Fields("SubAssy") = rs1.Fields("SubAssy")
rsNewTable.Update
End If
rsNewTable.Close
End If

 

[SonOfEmidec1100]

Looking at the code maybe it could be NULL values causing the problem beacause the WHERE clause is not evaluating to True.

When you compare two fields if both (or either) of them is NULL then the retuned value is NULL not True or False.

Try this using this structure in the WHERE clause

WHERE ((a = b) OR (ISNULL(a) AND ISNULL(b)))

 

[sanders720]

I don't understand how this is going to help. Basically, the program reads if there is a recordset or not. If not, then create one, otherwise don't. I am getting a recordset here, and at least one of the three values is not null.

I changed the code to do something like this:

is rsnewtable.bof and rsnewtable.eof then...

these values should both be true id there are no records in the recordset. Are there other more sure ways to do this?

How would you show the WHERE clause?

Thanks for the help.

 

[sanders720]

I was able to answer my own question, I needed a different quesy statement for two different conditions.:

sqlNewTable = "SELECT * " & _
"FROM tblSubAssyIsolator " & _
"WHERE JobNo = " & Combo6 & " AND "

If Len(rs1!InventorFileName) > 0 Then
sqlNewTable = sqlNewTable & _
"InventorFileName = " & Chr(34) & rs1!InventorFileName & Chr(34) & " AND "
End If




Private Sub cmdSubQty_Click()
Dim rs1 As ADODB.Recordset
Set rs1 = New ADODB.Recordset

Dim rs2 As ADODB.Recordset
Set rs2 = New ADODB.Recordset

Dim sql As String

sql = "SELECT DISTINCT tblBOM.JobNo, " & _
"tblBOM.InventorFileName, " & _
"tblSubAssyListing.SubAssy " & _
"FROM tblBOM INNER JOIN tblSubAssyListing ON tblBOM.SubAssy=tblSubAssyListing.SubAssy " & _
"WHERE tblBOM.JobNo= " & Combo6.Value & " " & _
"ORDER BY tblBOM.InventorFileName DESC; "

Debug.Print sql

Dim rsNewTable As ADODB.Recordset
Set rsNewTable = New ADODB.Recordset

Dim c1 As Integer
Dim c2 As Integer
Dim cn As Integer

Dim subA As String
Dim subB As String

Dim addrec As Integer

rs1.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
rs1.MoveFirst

rs2.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Dim sqlNewTable As String

' sqlNewTable = "SELECT * FROM tblSubAssyIsolator " & _
' "WHERE JobNo = " & Combo6.Value & " And " & _
' "InventorFileName = '" & rs1.Fields("InventorFileName") & "' And " & _
' "SubAssy = '" & rs1.Fields("SubAssy") & "'"

For c1 = 1 To rs1.RecordCount
addrec = 0
subA = rs1.Fields("SubAssy")
rs2.MoveFirst

For c2 = 1 To rs2.RecordCount
subB = rs2.Fields("SubAssy")
If (subB = subA And c1 <> c2) Or (subB = subA And c1 <> c2 And Len(rs1!InventorFileName) = 0) Then
addrec = 0
Else
addrec = 1
End If
rs2.MoveNext
Next c2
If addrec = 1 Then
sqlNewTable = "SELECT * " & _
"FROM tblSubAssyIsolator " & _
"WHERE JobNo = " & Combo6 & " AND "

If Len(rs1!InventorFileName) > 0 Then
sqlNewTable = sqlNewTable & _
"InventorFileName = " & Chr(34) & rs1!InventorFileName & Chr(34) & " AND "
End If

sqlNewTable = sqlNewTable & "SubAssy = " & Chr(34) & rs1!SubAssy & Chr(34) & ";"

Debug.Print "sqlNewTable: " & sqlNewTable

rsNewTable.Open sqlNewTable, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Debug.Print "BOF: " & rsNewTable.BOF
Debug.Print "EOF: " & rsNewTable.EOF
If rsNewTable.BOF And rsNewTable.EOF Then
rsNewTable.AddNew
rsNewTable.Fields("JobNo") = Combo6.Value
If Not IsNull(rs1.Fields("InventorFileName")) Then rsNewTable.Fields("InventorFileName") = rs1.Fields("InventorFileName")
rsNewTable.Fields("SubAssy") = rs1.Fields("SubAssy")
rsNewTable.Update
End If
rsNewTable.Close
End If
rs1.MoveNext
Debug.Print "Working on Record " & c1 & " of " & rs1.RecordCount
Next c1

Set rsNewTable = Nothing

rs2.Close
Set rs2 = Nothing

rs1.Close
Set rs1 = Nothing

DoCmd.OpenForm "frmSubAssyIsolator", acNormal, , stLinkCriteria

End Sub