DAO question 1

[sabavno]

hi,

How can open two tables using DAO and then take a value of the field from one table and assign this value to the field of another table?

 

[AlexMidd]

That's a big question. What criteria define which records the value is assigned to, how are the tables related etc? Basically you should open a recordset according to a query which combines the two tables and you could then use SQL to assign the value(s). But exactly how this is done depends on the relationship between the table sand how you choose what values go where etc. Have fun! )

Alex Middleton

 

[scking]

I believe what you need to do is to use a DAO.Recordset to open each table, then using DAO methods synchronize them and perform your assignments.


Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strSQL As String
Dim strCriteria As String

strSQL = "Your SQL here"
Set rst1 = CurrentDb.OpenRecordset(strSQL)
strSQL = "Your new SQL here:
Set rst2 = CurrentDb.OpenRecordset(strSQL)

If rst1.RecordCount <> 0 Then
rst1.MoveFirst
Else
MsgBox &quot;No data found.&quot;
GoTo Exit_Proc
End If

If rst2.RecordCount <> 0 Then
rst2.MoveFirst
Else
MsgBox &quot;No data found.&quot;
GoTo Exit_Proc
End If

Do While Not rst1.EOF
strCriteria = &quot;ID = '&quot; & NHA & &quot;'&quot;
rst2.FindFirst strCriteria
LookForMore:
If Not rst2.NoMatch Then
' Do your assignment thing here
Else
' Maybe write something out to a log file
' about this no match condition

' Maybe even continue to find matches
' rst1.FindNext strCriteria
' GoTo LookForMore
End If
DoEvents
rst1.MoveNext
Loop
----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------