2 Replies - 1370 Views - Last Post: 04 May 2009 - 07:31 PM Rate Topic: -----

#1 williamscel  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 29
  • Joined: 25-October 08

Passing values from one database table to a 2nd database table

Posted 02 May 2009 - 04:44 PM

I am having a problem passing the information from one database table into another database table (different databases in different locations). When I execute the program, I get the following error message:
"Run-time error '-2147217887(80040e21)':
Multiple-step operation generated errors. Check each status value."

The line with the error has been made bold in the code.
How can I fix this?

Private Sub Timer1_Timer()
Dim TODAY As Integer
Dim idno As Integer							 'adodc1 = SMS
Dim sql1 As String							  'adodc2 = Ticket
Dim SQL As String
Adodc1.Recordset.Close
SQL = "select * from sms"
Adodc1.Recordset.Open SQL
Adodc1.Recordset.Requery
While Not Adodc1.Recordset.EOF
  idno = Adodc1.Recordset("id")
  
  sql1 = "select * from ticket where confirmno=" & idno
  Adodc2.Recordset.Close
  Adodc2.Recordset.Open sql1
  Adodc2.Recordset.Requery
  If Adodc2.Recordset.EOF = True Then
	Adodc2.Recordset.AddNew
	Adodc2.Recordset("confirmno") = Adodc1.Recordset("id")							 'adodc1 = SMS
	Adodc2.Recordset("accountno") = Adodc1.Recordset("sender")						 'adodc2 = Ticket
	[b]Adodc2.Recordset("playnos") = Adodc1.Recordset("content")[/b] 
	Adodc2.Recordset("ticketdate") = Now	   'Date changed to Now 1/5/09
	Adodc2.Recordset("cost") = 50			 'ADDED 1/5/09
	TODAY = DatePart("W", Now)			  'Date changed to Now 1/5/09
	Select Case TODAY
	Case 1
	  Adodc2.Recordset("DRAWDATE") = DateAdd("D", 3, Now)	'Date changed to Now 1/5/09
	Case 2
	  Adodc2.Recordset("DRAWDATE") = DateAdd("D", 2, Now)	'Date changed to Now 1/5/09
	Case 3
	  Adodc2.Recordset("DRAWDATE") = DateAdd("D", 1, Now)	'Date changed to Now 1/5/09
	End Select
	If Adodc2.Recordset("flag") = "" Then
		Adodc2.Recordset("flag") = 0					'0=Text Account,	1=Web Account
	End If
	Adodc2.Recordset.Update
	Adodc2.Recordset.Requery
	DataGrid1.Refresh
  End If
  Adodc1.Recordset.MoveNext
	
Wend

End Sub



Is This A Good Question/Topic? 0
  • +

Replies To: Passing values from one database table to a 2nd database table

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Passing values from one database table to a 2nd database table

Posted 04 May 2009 - 06:59 PM

i didn't see any bold letters, hope if you want to add a record you should mention the recordset type also so mention the following things also in your code
lock type
cursortype
Was This Post Helpful? 0
  • +
  • -

#3 vb5prgrmr  Icon User is offline

  • D.I.C Lover

Reputation: 109
  • View blog
  • Posts: 1,016
  • Joined: 21-March 09

Re: Passing values from one database table to a 2nd database table

Posted 04 May 2009 - 07:31 PM

Just by looking at the field names you have used, playnos looks like it might be a numeric field while content looks or seems like it may be a text field. If so then you may have to use the Val function at the least...
Adodc2.Recordset("playnos") = Val(Adodc1.Recordset("content"))


What may be more advantagous to you is to use the IsNumeric function in an If test prior to making this assignment just to be sure that the information entered is a number.

Good Luck
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1