add data from other table

[anil]

Hi all
i have 3 tables-tblnew(Location,Site,Adate),
tbllog(Location,Site,Bdate,SampleID) and
tblsample(SampleID,Site,Result,CDate).

I use append query in vba to add data from tblnew into tbllog such as
(Example)
insert into tbllog(Location,site,Adate) select location,site,BDate from
tblNew.

Then I used seek in vba to find records fro a particular
date(19/05/2006) in tblLog and put them in tblSample as
insert into tblSample(CDate,Site)select BDate,Site from tblog.
This helps me to get SampleID Which is autoNumber.

Now I want to Update/Append records in tbllog as
insert into tbllog(SampleID) Select SampleID from tblSample where
date=#19/05/2006#.
So that I HAve log record of Location,Site,Date,SampleID in one go.The
output looks as

LOCATION SITE ADATE SAMPLEID
Ara 1 19/05/2006 1
Ara 2 19/05/2006 2

The net Question is CAN I ADD DATA TO FIELD-SAMPLEID(WHICH IS NULL)
WHEN ALL OTHER FIELDS- LOCATION,ADATE,SITE ARE FILLED i.e I JUST WANT
TO REPLACE NULL VALUE(OR 0) FROM SAMPLEID WITH SOME DATA FROM
TBLSAMPLE.Using append query adds the data into new records.I just want
to add SampleID where ADATE is date(any)
thanks
anil

 

[anil]

Thanks all
I found the solution using update Query.But the results are not
satisfying.
Here is the code

Sub Update_tbllog()
Dim str As String, str1 As String, SQLStr As String
Dim rst As ADODB.Recordset, rst1 As ADODB.Recordset
Dim intanswer As Integer

Set rst = New ADODB.Recordset
Set rst1 = New ADODB.Recordset

rst.ActiveConnection = CurrentProject.Connection
rst.CursorLocation = adUseServer
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
rst1.ActiveConnection = CurrentProject.Connection

SQLStr = "Select * from tblSample where [tblSample].[Cdate]=#" &
Format([Forms]!frmSamplingSchedule!SelectADate, "dd/mm/yyyy") & " # "

rst.Open SQLStr
If rst.RecordCount = 0 Then
rst.Close
MsgBox "There are No Records in the table for Date = " &
Format([Forms]!frmSamplingSchedule!SelectADate, "dd/mm/yyyy") & " "
intanswer = MsgBox("Do You want to add records to tblSample",
vbYesNo)
Select Case intanswer
Case vbYes
str = "INSERT INTO tblSample(SiteID,CDate)" & _
"SELECT tbllog.SiteID,tbllog.ADate AS CDate "
& _
"FROM tblLog " & _
"where [tbllog].[Adate]=#" &
Format([Forms]!frmSamplingSchedule! SelectADate, "dd/mm/yyyy") & " #;
"
DoCmd.RunSQL str

MsgBox "Data in tblsample appended." & vbCrLf & "The Data
will be updated in tblLog also"
str1 = "UPDATE tblLog INNER JOIN tblSample ON
tblLog.SiteID = tblSample.SiteID " & _
"SET tblLog.SampleID =
[tblSample].[SampleID]" & _
"where [tbllog].[Adate]=#" &
Format([Forms]!frmSamplingSchedule!SelectADate, "dd/mm/yyyy") & " #; "
DoCmd.RunSQL str1
Case vbNo
MsgBox ("Then GOOD BYE !!")
Exit Sub
End Select
Exit Sub
Else
MsgBox "Date " &
Format([Forms]!frmSamplingSchedule!SelectADate, "dd/mm/yyyy") & "
found in tblSample"
MsgBox "You have already generated data." & vbCrLf & "" &
vbCrLf & "So GOOD BYE !!"
Debug.Print rst.RecordCount
End If
Set rst = Nothing
End Sub

 

[anil]

Sorry the problem did not come last time,don;t know why.
I am appending siteID from tblLog into tblSample to generate
SampleID(which is autonumber).Then I used the update query to add
SampleID into tblLog.
problem is when I append data from tbllog into tblSample say it append
89 records but when I updates the tbllog back adding sampleID it gives
different records say 90,93,94,99 or any.
Actually records are 89 but there are some double enteries.i.e sampleId
and siteID have double enteries and they are random every time.Can
someone help me to fix it either by changing code or correcting my
mistake.
thanks