Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Reply
 
Thread Tools Search this Thread
  #1  
Old 19-03-2009
Member
 
Join Date: Jan 2009
Posts: 67
Problem in Ms Access In Visual Basic

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
Reply With Quote
  #2  
Old 19-03-2009
Member
 
Join Date: Apr 2008
Posts: 1,948
Re: Problem in Ms Access In Visual Basic

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
Reply With Quote
  #3  
Old 19-03-2009
Member
 
Join Date: May 2008
Posts: 2,008
Re: Problem in Ms Access In Visual Basic

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
Reply With Quote
  #4  
Old 19-03-2009
Member
 
Join Date: Apr 2008
Posts: 2,001
Re: Problem in Ms Access In Visual Basic

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
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags: ,



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Problem in Ms Access In Visual Basic"
Thread Thread Starter Forum Replies Last Post
Difference between Visual Basic and Access Well-Tech Windows Software 4 07-09-2010 04:09 PM
Access Outlook from Visual Basic Application Ebenezer Windows Software 3 11-06-2009 07:38 PM
Problem Customizing Visual Basic.Net Coldman Software Development 4 18-03-2009 02:05 PM
Visual Basic 2005 or Visual Basic 6 Aasha Software Development 5 15-01-2009 06:56 PM
Visual Basic on LAN djbbenn Software Development 2 05-08-2008 02:15 PM


All times are GMT +5.5. The time now is 07:22 PM.