I am having a small query regarding visual basic. I am trying to include a ms access sheet in vb program. the problem is whenever i try to enter data from my form but i prompts me that data i duplicate. Please provide some solution regarding this
I am having a small query regarding visual basic. I am trying to include a ms access sheet in vb program. the problem is whenever i try to enter data from my form but i prompts me that data i duplicate. Please provide some solution regarding this
Code:Set rs1 = New ADODB.Recordset 'rs1.Open "SELECT Amount,Market,Total_Fees,PL,Trading_ID from tblOrders WHERE Broker='" & cboBroker.Text & "' AND Currency='" & cboCurrency.Text & "' AND Trading_Date=" & "#" & Format(DTPicker1.Value, "dd-MMM-yyyy") & "#" & ";", conn, adOpenDynamic, adLockOptimistic rs1.Open "SELECT * from tblOrders WHERE Broker='" & cboBroker.Text & "' AND Currency='" & cboCurrency.Text & "' AND Trading_Date=" & "#" & Format(DTPicker1.Value, "dd-MMM-yyyy") & "#" & ";", conn, adOpenDynamic, adLockOptimistic Set rs2 = New ADODB.Recordset 'rs2.Open "Select Balance_Date,Details,DR,CR,Flag,BID,CID,Flag from tblInputBalance", conn, adOpenDynamic, adLockOptimistic rs2.Open "Select * from tblInputBalance", conn, adOpenDynamic, adLockOptimistic Do While Not rs1.EOF rs2.AddNew rs2.Fields("Balance_Date") = Format(DTPicker1.Value, "dd-MMM-yyyy") rs2.Fields("Details") = rs1.Fields("Amount") & " **** " & rs1.Fields("Market") rs2.Fields("DR") = rs1.Fields("Total_Fees") If rs1.Fields("PL") < 0 Then rs2.Fields("CR") = rs1.Fields("PL") ElseIf rs1.Fields("PL") > 0 Then rs2.Fields("DR") = rs1.Fields("PL") End If rs2.Fields("Flag") = "1" rs2.Fields("BID") = brid rs2.Fields("CID") = curid If Not rs2.EOF Then rs2.MoveNext If Not rs1.EOF Then rs1.MoveNext Loop rs1.Close rs2.Close Set rs1 = Nothing Set rs2 = Nothing
Code:Set rs1 = New ADODB.Recordset rs1.Open "SELECT Amount,Market,Total_Fees,PL from tblOrders WHERE Broker='" & cboBroker.Text & "' AND Currency='" & cboCurrency.Text & "' AND Trading_Date=" & "#" & Format(DTPicker1.Value, "dd-MMM-yyyy") & "#" & ";", conn, adOpenDynamic, adLockOptimistic If rs2.RecordCount > 0 Then MsgBox "Record Exists" Else Do While Not rs1.EOF Set rs2 = New ADODB.Recordset rs2.Open "Select Balance_Date,Details,DR,CR,Flag,BID,CID from tblInputBalance", conn, adOpenDynamic, adLockOptimistic rs2.AddNew rs2.Fields("Balance_Date") = Format(DTPicker1.Value, "dd-MMM-yyyy") rs2.Fields("Details") = rs1.Fields("Amount") & " **** " & rs1.Fields("Market") rs2.Fields("DR") = rs1.Fields("Total_Fees") If rs1.Fields("PL") < 0 Then rs2.Fields("CR") = rs1.Fields("PL") ElseIf rs1.Fields("PL") > 0 Then rs2.Fields("DR") = rs1.Fields("PL") End If rs2.Fields("Flag") = "1" rs2.Fields("BID") = brid rs2.Fields("CID") = curid rs1.MoveNext rs2.Update Loop
Code:Private Sub Command1_Click() connect esql = "SELECT * from testable WHERE rate >40" rec.Open (esql), conn, adOpenDynamic, adLockOptimistic rec1.Open "Select * from testable2", conn, adOpenDynamic, adLockOptimistic Do While Not rec.EOF rec1.AddNew rec1.Fields(0) = rec.Fields(0) '(DTPicker1.Value, "dd-MMM-yyyy") rec1.Fields(1) = rec.Fields(1) 'rs1.Fields("Amount") & " **** " & rec1.Fields(2) = rec.Fields(2) If Not rec1.EOF Then rec1.MoveNext If Not rec.EOF Then rec.MoveNext Loop rec.Close rec1.Close Disconnect End Sub
Bookmarks