Record Validation - Help Required 1

[midiman69]

I have a tempory table to which a list of part number are pasted from Excel.
I need to validate these numbers for being Known or New parts
I have the following code that finds "New" part numbers and inserts them in the "tblnewparts" table - this works fine.
I can't find a way of inserting the "Known" records in to tblxfileboms" then deleting all records from the tempory table.

Can any one help??

Dave


Private Sub btnvalidate_Click()
On Error Resume Next

Dim rsTarget As DAO.Recordset
Dim rsSource As DAO.Recordset
Dim strSQL As String
Dim Cancel As Boolean



Set rsSource = CurrentDb.OpenRecordset("tbltemp")
Set rsTarget = CurrentDb.OpenRecordset("tblnewparts")


If Not rsSource.BOF Then
Do Until rsSource.EOF
If IsNull(DLookup("[IZPN]", "tblpartsdatabase", "[IZPN] = '" &
rsSource("partno") & "'")) Then
If MsgBox("This is a New Part - Do You Wish To Add?",
vbYesNo, "Project Costing Database") = vbNo Then
' do nothing
Else
rsTarget.AddNew
rsTarget("xfile") = rsSource("xfile")
rsTarget("issue") = rsSource("issue")
rsTarget("partno") = rsSource("partno")
rsTarget("qty") = rsSource("qty")
rsTarget.Update

End If
End If
rsSource.MoveNext
Loop

End If
rsTarget.Close
rsSource.Close

Set rsTarget = Nothing
Set rsSource = Nothing

End Sub

 

[Eupher]

Since you are already looping through your entire recordset and copying the new parts to the new parts table, why not copy the "not new" records to the known parts table during the same pass?

FWIW, Since you've gone to the trouble of populating a temp table, I think you could complete all the tasks (including deleting all the records from the temp table) with simple SQL statements (or saved queries) instead of looping through your recordset.

Unfortunately my SQL skills are not that great. I expect by the time I post back with some actual SQL that works, PHV or some other guru will have already done it, so... ;-)

Ken S.

 

[Craig0201]

Private Sub btnvalidate_Click()
On Error Resume Next

Dim rsTarget As DAO.Recordset
Dim rsOtherTarget As DAO.Recordset
Dim rsSource As DAO.Recordset
Dim strSQL As String
Dim Cancel As Boolean



Set rsSource = CurrentDb.OpenRecordset("tbltemp")
Set rsTarget = CurrentDb.OpenRecordset("tblnewparts")
Set rsOtherTarget = CurrentDb.OpenRecordSet("tblxfileboms")


If Not rsSource.BOF Then
Do Until rsSource.EOF
If IsNull(DLookup("[IZPN]", "tblpartsdatabase", "[IZPN] = '" &
rsSource("partno") & "'")) Then
If MsgBox("This is a New Part - Do You Wish To Add?",
vbYesNo, "Project Costing Database") = vbYes Then
rsTarget.AddNew
rsTarget("xfile") = rsSource("xfile")
rsTarget("issue") = rsSource("issue")
rsTarget("partno") = rsSource("partno")
rsTarget("qty") = rsSource("qty")
rsTarget.Update

End If
Else
'Do your other stuff here.
End If
End If
rsSource.MoveNext
Loop

End If
rsTarget.Close
rsSource.Close
rsOtherTarget.Close

Set rsTarget = Nothing
Set rsOtherTarget = Nothing
Set rsSource = Nothing

End Sub

 

[Craig0201]

Ken,

I thought that until I saw a question being asked. Which makes a query not viable.

Craig

 

[midiman69]

Thanks for your help guys - much appreciated.

I now have the code below - I keep getting a compile error - Do with out loop - can't seem to sort it out - Any further help would be very much welcome

Dave


Private Sub btnvalidate_Click()
On Error Resume Next

Dim rsTarget As DAO.Recordset
Dim rsOtherTarget As DAO.Recordset
Dim rsSource As DAO.Recordset
Dim strSQL As String
Dim Cancel As Boolean



Set rsSource = CurrentDb.OpenRecordset("tbltemp")
Set rsTarget = CurrentDb.OpenRecordset("tblnewparts")
Set rsOtherTarget = CurrentDb.OpenRecordset("tblxfileboms")


If Not rsSource.BOF Then
Do Until rsSource.EOF
If IsNull(DLookup("[IZPN]", "tblpartsdatabase", "[IZPN] = '" & rsSource("partno") & "'")) Then
If MsgBox("This is a New Part - Do You Wish To Add?", vbYesNo, "Project Costing Database") = vbYes Then
rsTarget.AddNew
rsTarget("xfile") = rsSource("xfile")
rsTarget("issue") = rsSource("issue")
rsTarget("partno") = rsSource("partno")
rsTarget("qty") = rsSource("qty")
rsTarget.Update

End If
Else
Do Until rsSource.EOF
If Not IsNull(DLookup("[IZPN]", "tblpartsdatabase", "[IZPN] = '" & rsSource("partno") & "'")) = True Then
rsOtherTarget.AddNew
rsOtherTarget("xfile") = rsSource("xfile")
rsOtherTarget("issue") = rsSource("issue")
rsOtherTarget("partno") = rsSource("partno")
rsOtherTarget("qty") = rsSource("qty")
rsOtherTarget.Update


End If
End If
rsSource.MoveNext
Loop
End If
rsTarget.Close
rsSource.Close
rsOtherTarget.Close


Set rsTarget = Nothing
Set rsOtherTarget = Nothing
Set rsSource = Nothing



End Sub

 

[JoeAtWork]

There's no reason to loop through the recordset twice. On each iteration the part is either going to be found or not be found. So just use If/Else to do the appropriate action:

Private Sub btnvalidate_Click()
On Error Resume Next

    Dim rsTarget As DAO.Recordset
    Dim rsOtherTarget As DAO.Recordset
    Dim rsSource As DAO.Recordset
    Dim strSQL As String
    Dim Cancel As Boolean

    Set rsSource = CurrentDb.OpenRecordset("tbltemp")
    Set rsTarget = CurrentDb.OpenRecordset("tblnewparts")
    Set rsOtherTarget = CurrentDb.OpenRecordset("tblxfileboms")

    If Not rsSource.BOF Then
        Do Until rsSource.EOF
            If IsNull(DLookup("[IZPN]", "tblpartsdatabase", "[IZPN] = '" & rsSource("partno") & "'")) Then
                If MsgBox("This is a New Part - Do You Wish To Add?", vbYesNo, "Project Costing Database") = vbYes Then
                    rsTarget.AddNew
                    rsTarget("xfile") = rsSource("xfile")
                    rsTarget("issue") = rsSource("issue")
                    rsTarget("partno") = rsSource("partno")
                    rsTarget("qty") = rsSource("qty")
                    rsTarget.Update

                End If
            Else
                'The previous If statement already established
                'that the part already exists
                rsOtherTarget.AddNew
                rsOtherTarget("xfile") = rsSource("xfile")
                rsOtherTarget("issue") = rsSource("issue")
                rsOtherTarget("partno") = rsSource("partno")
                rsOtherTarget("qty") = rsSource("qty")
                rsOtherTarget.Update
           
           
            End If

            rsSource.MoveNext
        Loop
    End If

    rsTarget.Close
    rsSource.Close
    rsOtherTarget.Close

    Set rsTarget = Nothing
    Set rsOtherTarget = Nothing
    Set rsSource = Nothing
 
End Sub

 

[Zion7]

maybe a SQL statement is viable?

Private Sub btnvalidate_Click()
On Error Resume Next

    Dim strTable As String
    Dim rsSource As DAO.Recordset
    Dim strSQL As String
    Dim Cancel As Boolean

    Set rsSource = CurrentDb.OpenRecordset("tbltemp")

    
    With rsSource
        If Not .BOF Then
            Do Until .EOF
                Cancel = False
                If IsNull(DLookup("[IZPN]", "tblpartsdatabase", "[IZPN] = '" & !partno & "'")) Then
                    If MsgBox("This is a New Part - Do You Wish To Add?", vbYesNo, "Project Costing Database") = vbYes Then
                        strTable = "tblnewparts"
                    Else
                        Cancel = True
                    End If
                Else
                    strTable = "tblxfileboms"
                End If
                
                strSQL = "INSERT INTO " & strTable & "(xfile,issue,partno,qty)" & _
                         "VALUES(" & !xFile & ",'" & !issue & "'," & !partno & "," & !qty & ")"
                
                If Not Cancel Then CurrentProject.Connection.Execute strSQL, , 129
    
                .MoveNext
            Loop
        End If
    End With

    rsSource.Close:    Set rsSource = Nothing
 
End Sub

 

[midiman69]

Thaks for the help guys.
Both examples don't insert "known" part numbers into tblxfileboms, only tblnewparts??

Dave

 

[JoeAtWork]

Both my and Zion7's code put "known" parts into tblxfileboms.

The logic is:

If PartNo Doesn't Exist in tblpartsdatabase
Add it to tblnewparts
else
Add it to tblxfileboms
End If

It's an either/or situation. After the else you don't need to check again if it does exist because you've already confirmed that it does in the first If statement.

I.E.
If "PartNo Doesn't Exist" = False must mean that it does exist.

 

[Zion7]

Thank-you Joe, for explaining that.
...and if by chance, you go the SQL route, (less recordsets
always better, less redundancy), be sure to check the data type,
on the VALUES statement.

 

[midiman69]

Thanks for your help guys - much appreciated

I put a requery at the end of the code to requery both tables and all works fine now.

Cheers

Dave