3 Replies - 1705 Views - Last Post: 04 December 2008 - 04:38 PM Rate Topic: -----

#1 el-sid  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 09-October 08

ado update problem

Post icon  Posted 01 December 2008 - 02:38 PM

hi everyone, got a problem with my update to a database. the problem is i want to do an insert in two tables that are related using one command button. the first table- admin-updates, but the second one does not. it seems they cannot use the same connection
Dim cConn As ADODB.Connection
Dim rsAdmin As ADODB.Recordset
Dim Rx As Long, xCount As Integer
Private Sub EnableControls()
  Dim MyControl As Control
  For Each MyControl In Controls
	If TypeOf MyControl Is TextBox Then
	  MyControl.Enabled = True
	End If
  Next
End Sub

Private Sub cmdexit_Click()
Unload Me
End Sub

Private Sub cmdsave_Click()
With rsAdmin
  
	.Open "select * from Admin", cConn, adOpenKeyset, adLockOptimistic
	.AddNew
	
	rsAdmin!Admin_no = txtadmin_no.Text
	rsAdmin!first_name = txtfirstname.Text
	rsAdmin!last_name = txtlastname.Text
	rsAdmin!department = txtdepartment.Text
			.Update
			.Requery
	MsgBox "Update Completed"
	.Close
End With

End Sub

Private Sub Form_Load()
frmadd.Show

  OpenConnection App.Path & "\ExamRegistration.mdb"
  
  'Call ClearControls
  Call DisableControls

Dim strEntry As Integer
 strEntry = Val(InputBox("Enter the Administrator number"))
 
  rsAdmin.Open "select * from Admin where Admin_no=" & strEntry, cConn, adOpenKeyset, adLockOptimistic
  With rsAdmin
	If .BOF = True And .EOF = True Then
		Call ClearControls
		Call EnableControls
		txtadmin_no = strEntry
		txtfirstname.SetFocus
	Else
		txtadmin_no.Text = rsAdmin![Admin_no] & ""
		txtfirstname.Text = rsAdmin!first_name & ""
		txtlastname.Text = rsAdmin!last_name & ""
		txtdepartment.Text = rsAdmin!department & ""
		MsgBox "Record already exists"
		'Exit Sub
	 End If
	 .Close
   End With
End Sub
Private Sub ClearControls()
  Dim MyControl As Control
  For Each MyControl In Controls
	If TypeOf MyControl Is TextBox Then
	  MyControl.Text = ""
	End If
  Next
End Sub
Private Sub DisableControls()
   Dim MyControl As Control
  For Each MyControl In Controls
	If TypeOf MyControl Is TextBox Then
	  MyControl.Enabled = False
	End If
  Next
End Sub
Private Sub OpenConnection(strPath As String)
	' Close on open connection
   If Not (cConn Is Nothing) Then
	 cConn.Close
	 Set cConn = Nothing
  End If
 
  'Create an instance of connection
  Set cConn = New ADODB.Connection
 
  'create an instance of recordset object
   Set rsAdmin = New ADODB.Recordset
  
  'set the cursor location
  cConn.CursorLocation = adUseClient
  ' Open a new connection
  cConn.Open "Provider=microsoft.jet.OLEDB.4.0;" _
   & "Data source= " & strPath
End Sub




please help

Is This A Good Question/Topic? 0
  • +

Replies To: ado update problem

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

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

Re: ado update problem

Posted 02 December 2008 - 03:58 PM

where did you open the second table's record set seems there is no child table denoted here
Was This Post Helpful? 0
  • +
  • -

#3 el-sid  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 27
  • Joined: 09-October 08

Re: ado update problem

Posted 04 December 2008 - 05:59 AM

View Postthava, on 2 Dec, 2008 - 02:58 PM, said:

where did you open the second table's record set seems there is no child table denoted here

sorry i removed it before posting. the thing is i could not update 2 related tables because the connection seemed only dedicated to one result set. so what i was wondering is that is there a way to update both eg inserting a new member in a members table then inserting their username and password in a users table. those two tables are directly linked in that the members admin no is a row on the users table
Was This Post Helpful? 0
  • +
  • -

#4 thava  Icon User is offline

  • D.I.C Lover
  • member icon

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

Re: ado update problem

Posted 04 December 2008 - 04:38 PM

i can't under stand the salutation

but

u can update any table with different recordset and a single connection
if there is a primary and foreign key relation ship use the cascade update in the database side
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1