Using Access 2k. I have form with a button, when I press this I have a
procedure, which deletes information in about 8 temp tables, then it imports
delaminated txt files into these tables. I then have it run some update
queries and/or append queries for the permanent tables. However I have one,
which is giving me nightmares for the past 3 days. I tried using queries and
code, but for some reason I can get it to append the right record. The
problem is I have several stand-alone systems, which import data into one
system via txt files. Here is my code hopefully someone can point me in the
right direction. I have tried several variations but nothing works it keeps
find the first record in tblPracticalResults and that's it.

Dim dbs As dao.Database
Dim rst As dao.Recordset
Dim rst2 As dao.Recordset
Dim rst3 As dao.Recordset
Dim rst4 As dao.Recordset
Dim strSQL As String
Dim strSql2 As String
Dim strWhere As Variant
Dim varRec As Integer
Dim varStudent As Integer
Dim varPracID As Integer
Dim varID As Variant
Dim varPRID As Variant
Dim k as Integer

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblCommentsTemp")
Set rst2 = dbs.OpenRecordset("tblPracticalResultsTemp")

rst.MoveLast
rst.MoveFirst
varRec = rst.RecordCount
For k = 1 To varRec
varStudent = DLookup("StuID", "tblStudents", "SID= '" & DLookup("SID",
"tblStudentsTemp", "StuID = " _
& rst![StudentID]) & "'")
strSql2 = ("SELECT PracticalResultsID " & " FROM tblPracticalResults " & "
WHERE (([PRID] = '" & (varA & "/" & DLookup("PracticalIDNumber", _
"tblPracticalData", "UniquePracticalIdentifier ='" & _
DLookup("UniquePracticalIdentifier", "tblPracticalDataTemp",
"PracticalIDNumber= " _
& DLookup("PracticalIDNumber", "tblPracticalResultsTemp",
"PracticalResultsID= " & _
rst![PracticalResultsID])) & "'") & "/" & rst2![PractCATID]
& "/" & rst2![Attribute] _
& "/" & rst2![AttributeDetail]) & "'" & "))")
Debug.Print strSql2
Set rst3 = dbs.OpenRecordset(strSql2)
strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
rst3.FindFirst strWhere
If Not NoMatch Then

strSQL = "Insert Into tblComments (CStuID, Comments, PracticalResultsID,
StudentID,TimeStampField,Reason)" & _
"SELECT " & "'" & (varA & "/" & rst3![PracticalResultsID] &
"/" & rst![Reason] & "/" & varStudent) _
& "'" & "," & "'" & rst![Comments] & "'" & "," &
rst3![PracticalResultsID] & "," & varStudent & "," & "'" _
& rst![TimeStampField] & "'" & "," & "'" & rst![Reason] & "';"

CurrentDb.Execute strSQL, dbFailOnError
End If

rst.MoveNext
Next k
rst.Close
rst2.Close
rst3.Close
Set rst = Nothing
Set rst2 = Nothing
Set rst3 = Nothing
Set dbs = Nothing

We have no idea how to identify what you mean by "the right record". We
don't even know what the data are.

You're going to need to provide a lot more details, please. Tell us what
you're trying to do with this code - in words.

I do note this part of your code that may be the problem:

Set rst3 = dbs.OpenRecordset(strSql2)
strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
rst3.FindFirst strWhere

You're asking ACCESS to find the record in rst3 whose PracticalResultsID
value is the same as the value that is in the first record of rst3. Perhaps
one of these rst3 usages is incorrect?

--

Ken Snell
<MS ACCESS MVP>



"Fysh" <(E-Mail Removed)> wrote in message
news:1B0F4E6B-A0F4-4696-8E0B-(E-Mail Removed)...
> Using Access 2k. I have form with a button, when I press this I have a
> procedure, which deletes information in about 8 temp tables, then it
> imports
> delaminated txt files into these tables. I then have it run some update
> queries and/or append queries for the permanent tables. However I have
> one,
> which is giving me nightmares for the past 3 days. I tried using queries
> and
> code, but for some reason I can get it to append the right record. The
> problem is I have several stand-alone systems, which import data into one
> system via txt files. Here is my code hopefully someone can point me in
> the
> right direction. I have tried several variations but nothing works it
> keeps
> find the first record in tblPracticalResults and that's it.
>
> Dim dbs As dao.Database
> Dim rst As dao.Recordset
> Dim rst2 As dao.Recordset
> Dim rst3 As dao.Recordset
> Dim rst4 As dao.Recordset
> Dim strSQL As String
> Dim strSql2 As String
> Dim strWhere As Variant
> Dim varRec As Integer
> Dim varStudent As Integer
> Dim varPracID As Integer
> Dim varID As Variant
> Dim varPRID As Variant
> Dim k as Integer
>
> Set dbs = CurrentDb()
> Set rst = dbs.OpenRecordset("tblCommentsTemp")
> Set rst2 = dbs.OpenRecordset("tblPracticalResultsTemp")
>
> rst.MoveLast
> rst.MoveFirst
> varRec = rst.RecordCount
> For k = 1 To varRec
> varStudent = DLookup("StuID", "tblStudents", "SID= '" & DLookup("SID",
> "tblStudentsTemp", "StuID = " _
> & rst![StudentID]) & "'")
> strSql2 = ("SELECT PracticalResultsID " & " FROM tblPracticalResults " & "
> WHERE (([PRID] = '" & (varA & "/" & DLookup("PracticalIDNumber", _
> "tblPracticalData", "UniquePracticalIdentifier ='" & _
> DLookup("UniquePracticalIdentifier",
> "tblPracticalDataTemp",
> "PracticalIDNumber= " _
> & DLookup("PracticalIDNumber", "tblPracticalResultsTemp",
> "PracticalResultsID= " & _
> rst![PracticalResultsID])) & "'") & "/" &
> rst2![PractCATID]
> & "/" & rst2![Attribute] _
> & "/" & rst2![AttributeDetail]) & "'" & "))")
> Debug.Print strSql2
> Set rst3 = dbs.OpenRecordset(strSql2)
> strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> rst3.FindFirst strWhere
> If Not NoMatch Then
>
> strSQL = "Insert Into tblComments (CStuID, Comments, PracticalResultsID,
> StudentID,TimeStampField,Reason)" & _
> "SELECT " & "'" & (varA & "/" & rst3![PracticalResultsID] &
> "/" & rst![Reason] & "/" & varStudent) _
> & "'" & "," & "'" & rst![Comments] & "'" & "," &
> rst3![PracticalResultsID] & "," & varStudent & "," & "'" _
> & rst![TimeStampField] & "'" & "," & "'" & rst![Reason] &
> "';"
>
> CurrentDb.Execute strSQL, dbFailOnError
> End If
>
> rst.MoveNext
> Next k
> rst.Close
> rst2.Close
> rst3.Close
> Set rst = Nothing
> Set rst2 = Nothing
> Set rst3 = Nothing
> Set dbs = Nothing
>
>
>

Thanks for the response. Well here it goes. I am trying to append to
tblComments from tblCommentsTemp. The tblComments has foreign key to
tblPracticalResults (PracticalResultsID) and it has a FK of tblPracticalData
(PracticalIDNumber)and so forth. The tblPracticalresults has about 100
records for every PracticalIDNumber. Because these are stand alone systems
each of these tables has a autonumber field, but also a txt field. The txt
field is the PK, but I used the autonumber for the FK. What I am trying to
do is append to tblComments, but get the correct PracticalResultsID from
tblPracticalResults after itself has been appended. Because the
PracticalResultsID changes when brought into the main system so can the txt
field. I have an append query for tblPracticalResults which actually changes
the txt field at the same time. The problem I am having when I step through
the code is it keeps locating the first record in tblPracticalResults and not
finding the correct record that was changed that relates back to
tblCommentsTemp.PracticalResultsID. I have tried several variations, but it
keeps giving me the first record (in other words it could be 150th record in
tblPracticalResults that I am trying to relate it to). I am off work now but
I will stay on line tonight to see if you can help me through this mess. I
have been able to update/append every table except this one. It is the one
that is the furthest out on my relationships.

Should I place rst2 in a different place? I tried it without the statement
of strSql2 and did a series of DLookpus, but I got the same result so I
switched it to this statement hoping to get a different result, but I didn't.
Also, should I go a different route and update the tblCommentsTemp table
with the correct info then from there append to tblComments? Thanks for any
help. I know this all seems confusing that was not my intention.



"Ken Snell [MVP]" wrote:

> We have no idea how to identify what you mean by "the right record". We
> don't even know what the data are.
>
> You're going to need to provide a lot more details, please. Tell us what
> you're trying to do with this code - in words.
>
> I do note this part of your code that may be the problem:
>
> Set rst3 = dbs.OpenRecordset(strSql2)
> strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> rst3.FindFirst strWhere
>
> You're asking ACCESS to find the record in rst3 whose PracticalResultsID
> value is the same as the value that is in the first record of rst3. Perhaps
> one of these rst3 usages is incorrect?
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
>
>
> "Fysh" <(E-Mail Removed)> wrote in message
> news:1B0F4E6B-A0F4-4696-8E0B-(E-Mail Removed)...
> > Using Access 2k. I have form with a button, when I press this I have a
> > procedure, which deletes information in about 8 temp tables, then it
> > imports
> > delaminated txt files into these tables. I then have it run some update
> > queries and/or append queries for the permanent tables. However I have
> > one,
> > which is giving me nightmares for the past 3 days. I tried using queries
> > and
> > code, but for some reason I can get it to append the right record. The
> > problem is I have several stand-alone systems, which import data into one
> > system via txt files. Here is my code hopefully someone can point me in
> > the
> > right direction. I have tried several variations but nothing works it
> > keeps
> > find the first record in tblPracticalResults and that's it.
> >
> > Dim dbs As dao.Database
> > Dim rst As dao.Recordset
> > Dim rst2 As dao.Recordset
> > Dim rst3 As dao.Recordset
> > Dim rst4 As dao.Recordset
> > Dim strSQL As String
> > Dim strSql2 As String
> > Dim strWhere As Variant
> > Dim varRec As Integer
> > Dim varStudent As Integer
> > Dim varPracID As Integer
> > Dim varID As Variant
> > Dim varPRID As Variant
> > Dim k as Integer
> >
> > Set dbs = CurrentDb()
> > Set rst = dbs.OpenRecordset("tblCommentsTemp")
> > Set rst2 = dbs.OpenRecordset("tblPracticalResultsTemp")
> >
> > rst.MoveLast
> > rst.MoveFirst
> > varRec = rst.RecordCount
> > For k = 1 To varRec
> > varStudent = DLookup("StuID", "tblStudents", "SID= '" & DLookup("SID",
> > "tblStudentsTemp", "StuID = " _
> > & rst![StudentID]) & "'")
> > strSql2 = ("SELECT PracticalResultsID " & " FROM tblPracticalResults " & "
> > WHERE (([PRID] = '" & (varA & "/" & DLookup("PracticalIDNumber", _
> > "tblPracticalData", "UniquePracticalIdentifier ='" & _
> > DLookup("UniquePracticalIdentifier",
> > "tblPracticalDataTemp",
> > "PracticalIDNumber= " _
> > & DLookup("PracticalIDNumber", "tblPracticalResultsTemp",
> > "PracticalResultsID= " & _
> > rst![PracticalResultsID])) & "'") & "/" &
> > rst2![PractCATID]
> > & "/" & rst2![Attribute] _
> > & "/" & rst2![AttributeDetail]) & "'" & "))")
> > Debug.Print strSql2
> > Set rst3 = dbs.OpenRecordset(strSql2)
> > strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> > rst3.FindFirst strWhere
> > If Not NoMatch Then
> >
> > strSQL = "Insert Into tblComments (CStuID, Comments, PracticalResultsID,
> > StudentID,TimeStampField,Reason)" & _
> > "SELECT " & "'" & (varA & "/" & rst3![PracticalResultsID] &
> > "/" & rst![Reason] & "/" & varStudent) _
> > & "'" & "," & "'" & rst![Comments] & "'" & "," &
> > rst3![PracticalResultsID] & "," & varStudent & "," & "'" _
> > & rst![TimeStampField] & "'" & "," & "'" & rst![Reason] &
> > "';"
> >
> > CurrentDb.Execute strSQL, dbFailOnError
> > End If
> >
> > rst.MoveNext
> > Next k
> > rst.Close
> > rst2.Close
> > rst3.Close
> > Set rst = Nothing
> > Set rst2 = Nothing
> > Set rst3 = Nothing
> > Set dbs = Nothing
> >
> >
> >
>
>
>

I can not say for sure that I'm understanding fully the interrelationships
and such here, but I still believe that the error in your code is what I
pointed out in my first reply. Take a look again at these code steps from
your code:

Set rst3 = dbs.OpenRecordset(strSql2)
strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
rst3.FindFirst strWhere

This code opens a recordset named rst3. The code then gets the value of the
first record in the rst3 recordset. It then finds that same record (the
first record) in the rst3 recordset... in other words, the
rst3.FindFirst strWhere
is doing absolutely nothing for you because it's finding the same record
that already is the current record. It's saying this:

"open a recordset that has a value of 1 for the primary key in the first
record"
"then find the record in this recordset that has a value of 1 for the
primary key"
Answer: it finds the first record because that is the record from which
you took the value that you're trying to find in that same recordset!


So I believe that
rst3.FindFirst strWhere

perhaps needs to be this (finding the matching record in the rst recordset)?
rst.FindFirst strWhere


--

Ken Snell
<MS ACCESS MVP>

"Fysh" <(E-Mail Removed)> wrote in message
news:F9ED1261-1D15-45E6-BF3A-(E-Mail Removed)...
> Thanks for the response. Well here it goes. I am trying to append to
> tblComments from tblCommentsTemp. The tblComments has foreign key to
> tblPracticalResults (PracticalResultsID) and it has a FK of
> tblPracticalData
> (PracticalIDNumber)and so forth. The tblPracticalresults has about 100
> records for every PracticalIDNumber. Because these are stand alone
> systems
> each of these tables has a autonumber field, but also a txt field. The
> txt
> field is the PK, but I used the autonumber for the FK. What I am trying
> to
> do is append to tblComments, but get the correct PracticalResultsID from
> tblPracticalResults after itself has been appended. Because the
> PracticalResultsID changes when brought into the main system so can the
> txt
> field. I have an append query for tblPracticalResults which actually
> changes
> the txt field at the same time. The problem I am having when I step
> through
> the code is it keeps locating the first record in tblPracticalResults and
> not
> finding the correct record that was changed that relates back to
> tblCommentsTemp.PracticalResultsID. I have tried several variations, but
> it
> keeps giving me the first record (in other words it could be 150th record
> in
> tblPracticalResults that I am trying to relate it to). I am off work now
> but
> I will stay on line tonight to see if you can help me through this mess.
> I
> have been able to update/append every table except this one. It is the
> one
> that is the furthest out on my relationships.
>
> Should I place rst2 in a different place? I tried it without the
> statement
> of strSql2 and did a series of DLookpus, but I got the same result so I
> switched it to this statement hoping to get a different result, but I
> didn't.
> Also, should I go a different route and update the tblCommentsTemp table
> with the correct info then from there append to tblComments? Thanks for
> any
> help. I know this all seems confusing that was not my intention.
>
>
>
> "Ken Snell [MVP]" wrote:
>
>> We have no idea how to identify what you mean by "the right record". We
>> don't even know what the data are.
>>
>> You're going to need to provide a lot more details, please. Tell us what
>> you're trying to do with this code - in words.
>>
>> I do note this part of your code that may be the problem:
>>
>> Set rst3 = dbs.OpenRecordset(strSql2)
>> strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
>> rst3.FindFirst strWhere
>>
>> You're asking ACCESS to find the record in rst3 whose PracticalResultsID
>> value is the same as the value that is in the first record of rst3.
>> Perhaps
>> one of these rst3 usages is incorrect?
>>
>> --
>>
>> Ken Snell
>> <MS ACCESS MVP>
>>
>>
>>
>> "Fysh" <(E-Mail Removed)> wrote in message
>> news:1B0F4E6B-A0F4-4696-8E0B-(E-Mail Removed)...
>> > Using Access 2k. I have form with a button, when I press this I have a
>> > procedure, which deletes information in about 8 temp tables, then it
>> > imports
>> > delaminated txt files into these tables. I then have it run some
>> > update
>> > queries and/or append queries for the permanent tables. However I have
>> > one,
>> > which is giving me nightmares for the past 3 days. I tried using
>> > queries
>> > and
>> > code, but for some reason I can get it to append the right record. The
>> > problem is I have several stand-alone systems, which import data into
>> > one
>> > system via txt files. Here is my code hopefully someone can point me
>> > in
>> > the
>> > right direction. I have tried several variations but nothing works it
>> > keeps
>> > find the first record in tblPracticalResults and that's it.
>> >
>> > Dim dbs As dao.Database
>> > Dim rst As dao.Recordset
>> > Dim rst2 As dao.Recordset
>> > Dim rst3 As dao.Recordset
>> > Dim rst4 As dao.Recordset
>> > Dim strSQL As String
>> > Dim strSql2 As String
>> > Dim strWhere As Variant
>> > Dim varRec As Integer
>> > Dim varStudent As Integer
>> > Dim varPracID As Integer
>> > Dim varID As Variant
>> > Dim varPRID As Variant
>> > Dim k as Integer
>> >
>> > Set dbs = CurrentDb()
>> > Set rst = dbs.OpenRecordset("tblCommentsTemp")
>> > Set rst2 = dbs.OpenRecordset("tblPracticalResultsTemp")
>> >
>> > rst.MoveLast
>> > rst.MoveFirst
>> > varRec = rst.RecordCount
>> > For k = 1 To varRec
>> > varStudent = DLookup("StuID", "tblStudents", "SID= '" & DLookup("SID",
>> > "tblStudentsTemp", "StuID = " _
>> > & rst![StudentID]) & "'")
>> > strSql2 = ("SELECT PracticalResultsID " & " FROM tblPracticalResults "
>> > & "
>> > WHERE (([PRID] = '" & (varA & "/" & DLookup("PracticalIDNumber", _
>> > "tblPracticalData", "UniquePracticalIdentifier ='" & _
>> > DLookup("UniquePracticalIdentifier",
>> > "tblPracticalDataTemp",
>> > "PracticalIDNumber= " _
>> > & DLookup("PracticalIDNumber",
>> > "tblPracticalResultsTemp",
>> > "PracticalResultsID= " & _
>> > rst![PracticalResultsID])) & "'") & "/" &
>> > rst2![PractCATID]
>> > & "/" & rst2![Attribute] _
>> > & "/" & rst2![AttributeDetail]) & "'" & "))")
>> > Debug.Print strSql2
>> > Set rst3 = dbs.OpenRecordset(strSql2)
>> > strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
>> > rst3.FindFirst strWhere
>> > If Not NoMatch Then
>> >
>> > strSQL = "Insert Into tblComments (CStuID, Comments,
>> > PracticalResultsID,
>> > StudentID,TimeStampField,Reason)" & _
>> > "SELECT " & "'" & (varA & "/" &
>> > rst3![PracticalResultsID] &
>> > "/" & rst![Reason] & "/" & varStudent) _
>> > & "'" & "," & "'" & rst![Comments] & "'" & "," &
>> > rst3![PracticalResultsID] & "," & varStudent & "," & "'" _
>> > & rst![TimeStampField] & "'" & "," & "'" & rst![Reason]
>> > &
>> > "';"
>> >
>> > CurrentDb.Execute strSQL, dbFailOnError
>> > End If
>> >
>> > rst.MoveNext
>> > Next k
>> > rst.Close
>> > rst2.Close
>> > rst3.Close
>> > Set rst = Nothing
>> > Set rst2 = Nothing
>> > Set rst3 = Nothing
>> > Set dbs = Nothing
>> >
>> >
>> >
>>
>>
>>

Thanks again for the response. However, that did not resolve my problem.
When I step through the code rst3![PracticalResultsID] part still points to
the first record in the table. I believe it has something do to with my
strSql2 string. I will keep trying and keep an eye on this string. Also, I
will let you know if I resolve this problem, if you see anything here that
could fix it please let me know thanks.

"Ken Snell [MVP]" wrote:

> I can not say for sure that I'm understanding fully the interrelationships
> and such here, but I still believe that the error in your code is what I
> pointed out in my first reply. Take a look again at these code steps from
> your code:
>
> Set rst3 = dbs.OpenRecordset(strSql2)
> strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> rst3.FindFirst strWhere
>
> This code opens a recordset named rst3. The code then gets the value of the
> first record in the rst3 recordset. It then finds that same record (the
> first record) in the rst3 recordset... in other words, the
> rst3.FindFirst strWhere
> is doing absolutely nothing for you because it's finding the same record
> that already is the current record. It's saying this:
>
> "open a recordset that has a value of 1 for the primary key in the first
> record"
> "then find the record in this recordset that has a value of 1 for the
> primary key"
> Answer: it finds the first record because that is the record from which
> you took the value that you're trying to find in that same recordset!
>
>
> So I believe that
> rst3.FindFirst strWhere
>
> perhaps needs to be this (finding the matching record in the rst recordset)?
> rst.FindFirst strWhere
>
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "Fysh" <(E-Mail Removed)> wrote in message
> news:F9ED1261-1D15-45E6-BF3A-(E-Mail Removed)...
> > Thanks for the response. Well here it goes. I am trying to append to
> > tblComments from tblCommentsTemp. The tblComments has foreign key to
> > tblPracticalResults (PracticalResultsID) and it has a FK of
> > tblPracticalData
> > (PracticalIDNumber)and so forth. The tblPracticalresults has about 100
> > records for every PracticalIDNumber. Because these are stand alone
> > systems
> > each of these tables has a autonumber field, but also a txt field. The
> > txt
> > field is the PK, but I used the autonumber for the FK. What I am trying
> > to
> > do is append to tblComments, but get the correct PracticalResultsID from
> > tblPracticalResults after itself has been appended. Because the
> > PracticalResultsID changes when brought into the main system so can the
> > txt
> > field. I have an append query for tblPracticalResults which actually
> > changes
> > the txt field at the same time. The problem I am having when I step
> > through
> > the code is it keeps locating the first record in tblPracticalResults and
> > not
> > finding the correct record that was changed that relates back to
> > tblCommentsTemp.PracticalResultsID. I have tried several variations, but
> > it
> > keeps giving me the first record (in other words it could be 150th record
> > in
> > tblPracticalResults that I am trying to relate it to). I am off work now
> > but
> > I will stay on line tonight to see if you can help me through this mess.
> > I
> > have been able to update/append every table except this one. It is the
> > one
> > that is the furthest out on my relationships.
> >
> > Should I place rst2 in a different place? I tried it without the
> > statement
> > of strSql2 and did a series of DLookpus, but I got the same result so I
> > switched it to this statement hoping to get a different result, but I
> > didn't.
> > Also, should I go a different route and update the tblCommentsTemp table
> > with the correct info then from there append to tblComments? Thanks for
> > any
> > help. I know this all seems confusing that was not my intention.
> >
> >
> >
> > "Ken Snell [MVP]" wrote:
> >
> >> We have no idea how to identify what you mean by "the right record". We
> >> don't even know what the data are.
> >>
> >> You're going to need to provide a lot more details, please. Tell us what
> >> you're trying to do with this code - in words.
> >>
> >> I do note this part of your code that may be the problem:
> >>
> >> Set rst3 = dbs.OpenRecordset(strSql2)
> >> strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> >> rst3.FindFirst strWhere
> >>
> >> You're asking ACCESS to find the record in rst3 whose PracticalResultsID
> >> value is the same as the value that is in the first record of rst3.
> >> Perhaps
> >> one of these rst3 usages is incorrect?
> >>
> >> --
> >>
> >> Ken Snell
> >> <MS ACCESS MVP>
> >>
> >>
> >>
> >> "Fysh" <(E-Mail Removed)> wrote in message
> >> news:1B0F4E6B-A0F4-4696-8E0B-(E-Mail Removed)...
> >> > Using Access 2k. I have form with a button, when I press this I have a
> >> > procedure, which deletes information in about 8 temp tables, then it
> >> > imports
> >> > delaminated txt files into these tables. I then have it run some
> >> > update
> >> > queries and/or append queries for the permanent tables. However I have
> >> > one,
> >> > which is giving me nightmares for the past 3 days. I tried using
> >> > queries
> >> > and
> >> > code, but for some reason I can get it to append the right record. The
> >> > problem is I have several stand-alone systems, which import data into
> >> > one
> >> > system via txt files. Here is my code hopefully someone can point me
> >> > in
> >> > the
> >> > right direction. I have tried several variations but nothing works it
> >> > keeps
> >> > find the first record in tblPracticalResults and that's it.
> >> >
> >> > Dim dbs As dao.Database
> >> > Dim rst As dao.Recordset
> >> > Dim rst2 As dao.Recordset
> >> > Dim rst3 As dao.Recordset
> >> > Dim rst4 As dao.Recordset
> >> > Dim strSQL As String
> >> > Dim strSql2 As String
> >> > Dim strWhere As Variant
> >> > Dim varRec As Integer
> >> > Dim varStudent As Integer
> >> > Dim varPracID As Integer
> >> > Dim varID As Variant
> >> > Dim varPRID As Variant
> >> > Dim k as Integer
> >> >
> >> > Set dbs = CurrentDb()
> >> > Set rst = dbs.OpenRecordset("tblCommentsTemp")
> >> > Set rst2 = dbs.OpenRecordset("tblPracticalResultsTemp")
> >> >
> >> > rst.MoveLast
> >> > rst.MoveFirst
> >> > varRec = rst.RecordCount
> >> > For k = 1 To varRec
> >> > varStudent = DLookup("StuID", "tblStudents", "SID= '" & DLookup("SID",
> >> > "tblStudentsTemp", "StuID = " _
> >> > & rst![StudentID]) & "'")
> >> > strSql2 = ("SELECT PracticalResultsID " & " FROM tblPracticalResults "
> >> > & "
> >> > WHERE (([PRID] = '" & (varA & "/" & DLookup("PracticalIDNumber", _
> >> > "tblPracticalData", "UniquePracticalIdentifier ='" & _
> >> > DLookup("UniquePracticalIdentifier",
> >> > "tblPracticalDataTemp",
> >> > "PracticalIDNumber= " _
> >> > & DLookup("PracticalIDNumber",
> >> > "tblPracticalResultsTemp",
> >> > "PracticalResultsID= " & _
> >> > rst![PracticalResultsID])) & "'") & "/" &
> >> > rst2![PractCATID]
> >> > & "/" & rst2![Attribute] _
> >> > & "/" & rst2![AttributeDetail]) & "'" & "))")
> >> > Debug.Print strSql2
> >> > Set rst3 = dbs.OpenRecordset(strSql2)
> >> > strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> >> > rst3.FindFirst strWhere
> >> > If Not NoMatch Then
> >> >
> >> > strSQL = "Insert Into tblComments (CStuID, Comments,
> >> > PracticalResultsID,
> >> > StudentID,TimeStampField,Reason)" & _
> >> > "SELECT " & "'" & (varA & "/" &
> >> > rst3![PracticalResultsID] &
> >> > "/" & rst![Reason] & "/" & varStudent) _
> >> > & "'" & "," & "'" & rst![Comments] & "'" & "," &
> >> > rst3![PracticalResultsID] & "," & varStudent & "," & "'" _
> >> > & rst![TimeStampField] & "'" & "," & "'" & rst![Reason]
> >> > &
> >> > "';"
> >> >
> >> > CurrentDb.Execute strSQL, dbFailOnError
> >> > End If
> >> >
> >> > rst.MoveNext
> >> > Next k
> >> > rst.Close
> >> > rst2.Close
> >> > rst3.Close
> >> > Set rst = Nothing
> >> > Set rst2 = Nothing
> >> > Set rst3 = Nothing
> >> > Set dbs = Nothing
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>

I got it, but I had to get it a different way. Could you look at my code and
possibly suggest a cleaner method of getting this? Thanks

Dim varA As Variant
Dim dbs As dao.Database
Dim rst As dao.Recordset
Dim strSQL As String
Dim varRec As Integer
Dim k as Integer
Dim varPracID As Integer
Dim varID As Integer
Dim varPRID As Integer
Dim varPracCatID As Integer
Dim varATTID As Integer
Dim varAATDID As Integer
Dim varStudent As Integer

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblCommentsTemp")

rst.MoveLast
rst.MoveFirst
varRec = rst.RecordCount
For k = 1 To varRec
varPracID = rst![PracticalResultsID]
varPracCatID = DLookup("PractCatID", "tblPracticalResultsTemp",
"PracticalResultsID=" & varPracID)
varATTID = DLookup("Attribute", "tblPracticalResultsTemp",
"PracticalResultsID=" & varPracID)
varAATDID = DLookup("AttributeDetail", "tblPracticalResultsTemp",
"PracticalResultsID=" & varPracID)

varStudent = DLookup("StuID", "tblStudents", "SID= '" & DLookup("SID",
"tblStudentsTemp", _
"StuID = " & rst![StudentID]) & "'")

varID = DLookup("PracticalResultsID", "tblPracticalResults", "PRID= '" &
(varA & "/" & DLookup("PracticalIDNumber", _
"tblPracticalData", "UniquePracticalIdentifier ='" & _
DLookup("UniquePracticalIdentifier", "tblPracticalDataTemp",
"PracticalIDNumber= " _
& DLookup("PracticalIDNumber", "tblPracticalResultsTemp",
"PracticalResultsID= " & _
varPracID)) & "'") & "/" & varPracCatID & "/" & varATTID _
& "/" & varAATDID & "'"))

strSQL = "Insert Into tblComments (CStuID, Comments, PracticalResultsID,
StudentID,TimeStampField,Reason)" & _
"SELECT " & "'" & (varA & "/" & varID & "/" & rst![Reason] & "/" &
varStudent) _
& "'" & "," & "'" & rst![Comments] & "'" & "," & varID & "," &
varStudent & "," & "'" _
& rst![TimeStampField] & "'" & "," & "'" & rst![Reason] & "';"

CurrentDb.Execute strSQL, dbFailOnError

rst.MoveNext
Next k
rst.Close
Set rst = Nothing
Set dbs = Nothing


varA = DLookup("ActivityID", "tblActivity", "ActivityID= " &
DLookup("Activity", "tblSetupInfo"))


"Ken Snell [MVP]" wrote:

> I can not say for sure that I'm understanding fully the interrelationships
> and such here, but I still believe that the error in your code is what I
> pointed out in my first reply. Take a look again at these code steps from
> your code:
>
> Set rst3 = dbs.OpenRecordset(strSql2)
> strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> rst3.FindFirst strWhere
>
> This code opens a recordset named rst3. The code then gets the value of the
> first record in the rst3 recordset. It then finds that same record (the
> first record) in the rst3 recordset... in other words, the
> rst3.FindFirst strWhere
> is doing absolutely nothing for you because it's finding the same record
> that already is the current record. It's saying this:
>
> "open a recordset that has a value of 1 for the primary key in the first
> record"
> "then find the record in this recordset that has a value of 1 for the
> primary key"
> Answer: it finds the first record because that is the record from which
> you took the value that you're trying to find in that same recordset!
>
>
> So I believe that
> rst3.FindFirst strWhere
>
> perhaps needs to be this (finding the matching record in the rst recordset)?
> rst.FindFirst strWhere
>
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "Fysh" <(E-Mail Removed)> wrote in message
> news:F9ED1261-1D15-45E6-BF3A-(E-Mail Removed)...
> > Thanks for the response. Well here it goes. I am trying to append to
> > tblComments from tblCommentsTemp. The tblComments has foreign key to
> > tblPracticalResults (PracticalResultsID) and it has a FK of
> > tblPracticalData
> > (PracticalIDNumber)and so forth. The tblPracticalresults has about 100
> > records for every PracticalIDNumber. Because these are stand alone
> > systems
> > each of these tables has a autonumber field, but also a txt field. The
> > txt
> > field is the PK, but I used the autonumber for the FK. What I am trying
> > to
> > do is append to tblComments, but get the correct PracticalResultsID from
> > tblPracticalResults after itself has been appended. Because the
> > PracticalResultsID changes when brought into the main system so can the
> > txt
> > field. I have an append query for tblPracticalResults which actually
> > changes
> > the txt field at the same time. The problem I am having when I step
> > through
> > the code is it keeps locating the first record in tblPracticalResults and
> > not
> > finding the correct record that was changed that relates back to
> > tblCommentsTemp.PracticalResultsID. I have tried several variations, but
> > it
> > keeps giving me the first record (in other words it could be 150th record
> > in
> > tblPracticalResults that I am trying to relate it to). I am off work now
> > but
> > I will stay on line tonight to see if you can help me through this mess.
> > I
> > have been able to update/append every table except this one. It is the
> > one
> > that is the furthest out on my relationships.
> >
> > Should I place rst2 in a different place? I tried it without the
> > statement
> > of strSql2 and did a series of DLookpus, but I got the same result so I
> > switched it to this statement hoping to get a different result, but I
> > didn't.
> > Also, should I go a different route and update the tblCommentsTemp table
> > with the correct info then from there append to tblComments? Thanks for
> > any
> > help. I know this all seems confusing that was not my intention.
> >
> >
> >
> > "Ken Snell [MVP]" wrote:
> >
> >> We have no idea how to identify what you mean by "the right record". We
> >> don't even know what the data are.
> >>
> >> You're going to need to provide a lot more details, please. Tell us what
> >> you're trying to do with this code - in words.
> >>
> >> I do note this part of your code that may be the problem:
> >>
> >> Set rst3 = dbs.OpenRecordset(strSql2)
> >> strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> >> rst3.FindFirst strWhere
> >>
> >> You're asking ACCESS to find the record in rst3 whose PracticalResultsID
> >> value is the same as the value that is in the first record of rst3.
> >> Perhaps
> >> one of these rst3 usages is incorrect?
> >>
> >> --
> >>
> >> Ken Snell
> >> <MS ACCESS MVP>
> >>
> >>
> >>
> >> "Fysh" <(E-Mail Removed)> wrote in message
> >> news:1B0F4E6B-A0F4-4696-8E0B-(E-Mail Removed)...
> >> > Using Access 2k. I have form with a button, when I press this I have a
> >> > procedure, which deletes information in about 8 temp tables, then it
> >> > imports
> >> > delaminated txt files into these tables. I then have it run some
> >> > update
> >> > queries and/or append queries for the permanent tables. However I have
> >> > one,
> >> > which is giving me nightmares for the past 3 days. I tried using
> >> > queries
> >> > and
> >> > code, but for some reason I can get it to append the right record. The
> >> > problem is I have several stand-alone systems, which import data into
> >> > one
> >> > system via txt files. Here is my code hopefully someone can point me
> >> > in
> >> > the
> >> > right direction. I have tried several variations but nothing works it
> >> > keeps
> >> > find the first record in tblPracticalResults and that's it.
> >> >
> >> > Dim dbs As dao.Database
> >> > Dim rst As dao.Recordset
> >> > Dim rst2 As dao.Recordset
> >> > Dim rst3 As dao.Recordset
> >> > Dim rst4 As dao.Recordset
> >> > Dim strSQL As String
> >> > Dim strSql2 As String
> >> > Dim strWhere As Variant
> >> > Dim varRec As Integer
> >> > Dim varStudent As Integer
> >> > Dim varPracID As Integer
> >> > Dim varID As Variant
> >> > Dim varPRID As Variant
> >> > Dim k as Integer
> >> >
> >> > Set dbs = CurrentDb()
> >> > Set rst = dbs.OpenRecordset("tblCommentsTemp")
> >> > Set rst2 = dbs.OpenRecordset("tblPracticalResultsTemp")
> >> >
> >> > rst.MoveLast
> >> > rst.MoveFirst
> >> > varRec = rst.RecordCount
> >> > For k = 1 To varRec
> >> > varStudent = DLookup("StuID", "tblStudents", "SID= '" & DLookup("SID",
> >> > "tblStudentsTemp", "StuID = " _
> >> > & rst![StudentID]) & "'")
> >> > strSql2 = ("SELECT PracticalResultsID " & " FROM tblPracticalResults "
> >> > & "
> >> > WHERE (([PRID] = '" & (varA & "/" & DLookup("PracticalIDNumber", _
> >> > "tblPracticalData", "UniquePracticalIdentifier ='" & _
> >> > DLookup("UniquePracticalIdentifier",
> >> > "tblPracticalDataTemp",
> >> > "PracticalIDNumber= " _
> >> > & DLookup("PracticalIDNumber",
> >> > "tblPracticalResultsTemp",
> >> > "PracticalResultsID= " & _
> >> > rst![PracticalResultsID])) & "'") & "/" &
> >> > rst2![PractCATID]
> >> > & "/" & rst2![Attribute] _
> >> > & "/" & rst2![AttributeDetail]) & "'" & "))")
> >> > Debug.Print strSql2
> >> > Set rst3 = dbs.OpenRecordset(strSql2)
> >> > strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> >> > rst3.FindFirst strWhere
> >> > If Not NoMatch Then
> >> >
> >> > strSQL = "Insert Into tblComments (CStuID, Comments,
> >> > PracticalResultsID,
> >> > StudentID,TimeStampField,Reason)" & _
> >> > "SELECT " & "'" & (varA & "/" &
> >> > rst3![PracticalResultsID] &
> >> > "/" & rst![Reason] & "/" & varStudent) _
> >> > & "'" & "," & "'" & rst![Comments] & "'" & "," &
> >> > rst3![PracticalResultsID] & "," & varStudent & "," & "'" _
> >> > & rst![TimeStampField] & "'" & "," & "'" & rst![Reason]
> >> > &
> >> > "';"
> >> >
> >> > CurrentDb.Execute strSQL, dbFailOnError
> >> > End If
> >> >
> >> > rst.MoveNext
> >> > Next k
> >> > rst.Close
> >> > rst2.Close
> >> > rst3.Close
> >> > Set rst = Nothing
> >> > Set rst2 = Nothing
> >> > Set rst3 = Nothing
> >> > Set dbs = Nothing
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Also, I forgot to mention I need to check to see if this record exist in
tblComments if so then don't append else append. Any suggestions? Thanks

"Ken Snell [MVP]" wrote:

> I can not say for sure that I'm understanding fully the interrelationships
> and such here, but I still believe that the error in your code is what I
> pointed out in my first reply. Take a look again at these code steps from
> your code:
>
> Set rst3 = dbs.OpenRecordset(strSql2)
> strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> rst3.FindFirst strWhere
>
> This code opens a recordset named rst3. The code then gets the value of the
> first record in the rst3 recordset. It then finds that same record (the
> first record) in the rst3 recordset... in other words, the
> rst3.FindFirst strWhere
> is doing absolutely nothing for you because it's finding the same record
> that already is the current record. It's saying this:
>
> "open a recordset that has a value of 1 for the primary key in the first
> record"
> "then find the record in this recordset that has a value of 1 for the
> primary key"
> Answer: it finds the first record because that is the record from which
> you took the value that you're trying to find in that same recordset!
>
>
> So I believe that
> rst3.FindFirst strWhere
>
> perhaps needs to be this (finding the matching record in the rst recordset)?
> rst.FindFirst strWhere
>
>
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "Fysh" <(E-Mail Removed)> wrote in message
> news:F9ED1261-1D15-45E6-BF3A-(E-Mail Removed)...
> > Thanks for the response. Well here it goes. I am trying to append to
> > tblComments from tblCommentsTemp. The tblComments has foreign key to
> > tblPracticalResults (PracticalResultsID) and it has a FK of
> > tblPracticalData
> > (PracticalIDNumber)and so forth. The tblPracticalresults has about 100
> > records for every PracticalIDNumber. Because these are stand alone
> > systems
> > each of these tables has a autonumber field, but also a txt field. The
> > txt
> > field is the PK, but I used the autonumber for the FK. What I am trying
> > to
> > do is append to tblComments, but get the correct PracticalResultsID from
> > tblPracticalResults after itself has been appended. Because the
> > PracticalResultsID changes when brought into the main system so can the
> > txt
> > field. I have an append query for tblPracticalResults which actually
> > changes
> > the txt field at the same time. The problem I am having when I step
> > through
> > the code is it keeps locating the first record in tblPracticalResults and
> > not
> > finding the correct record that was changed that relates back to
> > tblCommentsTemp.PracticalResultsID. I have tried several variations, but
> > it
> > keeps giving me the first record (in other words it could be 150th record
> > in
> > tblPracticalResults that I am trying to relate it to). I am off work now
> > but
> > I will stay on line tonight to see if you can help me through this mess.
> > I
> > have been able to update/append every table except this one. It is the
> > one
> > that is the furthest out on my relationships.
> >
> > Should I place rst2 in a different place? I tried it without the
> > statement
> > of strSql2 and did a series of DLookpus, but I got the same result so I
> > switched it to this statement hoping to get a different result, but I
> > didn't.
> > Also, should I go a different route and update the tblCommentsTemp table
> > with the correct info then from there append to tblComments? Thanks for
> > any
> > help. I know this all seems confusing that was not my intention.
> >
> >
> >
> > "Ken Snell [MVP]" wrote:
> >
> >> We have no idea how to identify what you mean by "the right record". We
> >> don't even know what the data are.
> >>
> >> You're going to need to provide a lot more details, please. Tell us what
> >> you're trying to do with this code - in words.
> >>
> >> I do note this part of your code that may be the problem:
> >>
> >> Set rst3 = dbs.OpenRecordset(strSql2)
> >> strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> >> rst3.FindFirst strWhere
> >>
> >> You're asking ACCESS to find the record in rst3 whose PracticalResultsID
> >> value is the same as the value that is in the first record of rst3.
> >> Perhaps
> >> one of these rst3 usages is incorrect?
> >>
> >> --
> >>
> >> Ken Snell
> >> <MS ACCESS MVP>
> >>
> >>
> >>
> >> "Fysh" <(E-Mail Removed)> wrote in message
> >> news:1B0F4E6B-A0F4-4696-8E0B-(E-Mail Removed)...
> >> > Using Access 2k. I have form with a button, when I press this I have a
> >> > procedure, which deletes information in about 8 temp tables, then it
> >> > imports
> >> > delaminated txt files into these tables. I then have it run some
> >> > update
> >> > queries and/or append queries for the permanent tables. However I have
> >> > one,
> >> > which is giving me nightmares for the past 3 days. I tried using
> >> > queries
> >> > and
> >> > code, but for some reason I can get it to append the right record. The
> >> > problem is I have several stand-alone systems, which import data into
> >> > one
> >> > system via txt files. Here is my code hopefully someone can point me
> >> > in
> >> > the
> >> > right direction. I have tried several variations but nothing works it
> >> > keeps
> >> > find the first record in tblPracticalResults and that's it.
> >> >
> >> > Dim dbs As dao.Database
> >> > Dim rst As dao.Recordset
> >> > Dim rst2 As dao.Recordset
> >> > Dim rst3 As dao.Recordset
> >> > Dim rst4 As dao.Recordset
> >> > Dim strSQL As String
> >> > Dim strSql2 As String
> >> > Dim strWhere As Variant
> >> > Dim varRec As Integer
> >> > Dim varStudent As Integer
> >> > Dim varPracID As Integer
> >> > Dim varID As Variant
> >> > Dim varPRID As Variant
> >> > Dim k as Integer
> >> >
> >> > Set dbs = CurrentDb()
> >> > Set rst = dbs.OpenRecordset("tblCommentsTemp")
> >> > Set rst2 = dbs.OpenRecordset("tblPracticalResultsTemp")
> >> >
> >> > rst.MoveLast
> >> > rst.MoveFirst
> >> > varRec = rst.RecordCount
> >> > For k = 1 To varRec
> >> > varStudent = DLookup("StuID", "tblStudents", "SID= '" & DLookup("SID",
> >> > "tblStudentsTemp", "StuID = " _
> >> > & rst![StudentID]) & "'")
> >> > strSql2 = ("SELECT PracticalResultsID " & " FROM tblPracticalResults "
> >> > & "
> >> > WHERE (([PRID] = '" & (varA & "/" & DLookup("PracticalIDNumber", _
> >> > "tblPracticalData", "UniquePracticalIdentifier ='" & _
> >> > DLookup("UniquePracticalIdentifier",
> >> > "tblPracticalDataTemp",
> >> > "PracticalIDNumber= " _
> >> > & DLookup("PracticalIDNumber",
> >> > "tblPracticalResultsTemp",
> >> > "PracticalResultsID= " & _
> >> > rst![PracticalResultsID])) & "'") & "/" &
> >> > rst2![PractCATID]
> >> > & "/" & rst2![Attribute] _
> >> > & "/" & rst2![AttributeDetail]) & "'" & "))")
> >> > Debug.Print strSql2
> >> > Set rst3 = dbs.OpenRecordset(strSql2)
> >> > strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> >> > rst3.FindFirst strWhere
> >> > If Not NoMatch Then
> >> >
> >> > strSQL = "Insert Into tblComments (CStuID, Comments,
> >> > PracticalResultsID,
> >> > StudentID,TimeStampField,Reason)" & _
> >> > "SELECT " & "'" & (varA & "/" &
> >> > rst3![PracticalResultsID] &
> >> > "/" & rst![Reason] & "/" & varStudent) _
> >> > & "'" & "," & "'" & rst![Comments] & "'" & "," &
> >> > rst3![PracticalResultsID] & "," & varStudent & "," & "'" _
> >> > & rst![TimeStampField] & "'" & "," & "'" & rst![Reason]
> >> > &
> >> > "';"
> >> >
> >> > CurrentDb.Execute strSQL, dbFailOnError
> >> > End If
> >> >
> >> > rst.MoveNext
> >> > Next k
> >> > rst.Close
> >> > rst2.Close
> >> > rst3.Close
> >> > Set rst = Nothing
> >> > Set rst2 = Nothing
> >> > Set rst3 = Nothing
> >> > Set dbs = Nothing
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>
>

Never mind I figured that part out.

If DCount("CStuID", "tblComments", "CStuID = '" & (varA & "/" & varID & "/"
& rst![Reason] & "/" & _
varStudent) & "'") > 0 Then
Else
strSQL = "Insert Into tblComments (CStuID, Comments,
PracticalResultsID, StudentID,TimeStampField,Reason)" & _
"SELECT " & "'" & (varA & "/" & varID & "/" & rst![Reason] &
"/" & varStudent) _
& "'" & "," & "'" & rst![Comments] & "'" & "," & varID & ","
& varStudent & "," & "'" _
& rst![TimeStampField] & "'" & "," & "'" & rst![Reason] & "';"

CurrentDb.Execute strSQL, dbFailOnError
End If


"Fysh" wrote:

> Also, I forgot to mention I need to check to see if this record exist in
> tblComments if so then don't append else append. Any suggestions? Thanks
>
> "Ken Snell [MVP]" wrote:
>
> > I can not say for sure that I'm understanding fully the interrelationships
> > and such here, but I still believe that the error in your code is what I
> > pointed out in my first reply. Take a look again at these code steps from
> > your code:
> >
> > Set rst3 = dbs.OpenRecordset(strSql2)
> > strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> > rst3.FindFirst strWhere
> >
> > This code opens a recordset named rst3. The code then gets the value of the
> > first record in the rst3 recordset. It then finds that same record (the
> > first record) in the rst3 recordset... in other words, the
> > rst3.FindFirst strWhere
> > is doing absolutely nothing for you because it's finding the same record
> > that already is the current record. It's saying this:
> >
> > "open a recordset that has a value of 1 for the primary key in the first
> > record"
> > "then find the record in this recordset that has a value of 1 for the
> > primary key"
> > Answer: it finds the first record because that is the record from which
> > you took the value that you're trying to find in that same recordset!
> >
> >
> > So I believe that
> > rst3.FindFirst strWhere
> >
> > perhaps needs to be this (finding the matching record in the rst recordset)?
> > rst.FindFirst strWhere
> >
> >
> > --
> >
> > Ken Snell
> > <MS ACCESS MVP>
> >
> > "Fysh" <(E-Mail Removed)> wrote in message
> > news:F9ED1261-1D15-45E6-BF3A-(E-Mail Removed)...
> > > Thanks for the response. Well here it goes. I am trying to append to
> > > tblComments from tblCommentsTemp. The tblComments has foreign key to
> > > tblPracticalResults (PracticalResultsID) and it has a FK of
> > > tblPracticalData
> > > (PracticalIDNumber)and so forth. The tblPracticalresults has about 100
> > > records for every PracticalIDNumber. Because these are stand alone
> > > systems
> > > each of these tables has a autonumber field, but also a txt field. The
> > > txt
> > > field is the PK, but I used the autonumber for the FK. What I am trying
> > > to
> > > do is append to tblComments, but get the correct PracticalResultsID from
> > > tblPracticalResults after itself has been appended. Because the
> > > PracticalResultsID changes when brought into the main system so can the
> > > txt
> > > field. I have an append query for tblPracticalResults which actually
> > > changes
> > > the txt field at the same time. The problem I am having when I step
> > > through
> > > the code is it keeps locating the first record in tblPracticalResults and
> > > not
> > > finding the correct record that was changed that relates back to
> > > tblCommentsTemp.PracticalResultsID. I have tried several variations, but
> > > it
> > > keeps giving me the first record (in other words it could be 150th record
> > > in
> > > tblPracticalResults that I am trying to relate it to). I am off work now
> > > but
> > > I will stay on line tonight to see if you can help me through this mess.
> > > I
> > > have been able to update/append every table except this one. It is the
> > > one
> > > that is the furthest out on my relationships.
> > >
> > > Should I place rst2 in a different place? I tried it without the
> > > statement
> > > of strSql2 and did a series of DLookpus, but I got the same result so I
> > > switched it to this statement hoping to get a different result, but I
> > > didn't.
> > > Also, should I go a different route and update the tblCommentsTemp table
> > > with the correct info then from there append to tblComments? Thanks for
> > > any
> > > help. I know this all seems confusing that was not my intention.
> > >
> > >
> > >
> > > "Ken Snell [MVP]" wrote:
> > >
> > >> We have no idea how to identify what you mean by "the right record". We
> > >> don't even know what the data are.
> > >>
> > >> You're going to need to provide a lot more details, please. Tell us what
> > >> you're trying to do with this code - in words.
> > >>
> > >> I do note this part of your code that may be the problem:
> > >>
> > >> Set rst3 = dbs.OpenRecordset(strSql2)
> > >> strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> > >> rst3.FindFirst strWhere
> > >>
> > >> You're asking ACCESS to find the record in rst3 whose PracticalResultsID
> > >> value is the same as the value that is in the first record of rst3.
> > >> Perhaps
> > >> one of these rst3 usages is incorrect?
> > >>
> > >> --
> > >>
> > >> Ken Snell
> > >> <MS ACCESS MVP>
> > >>
> > >>
> > >>
> > >> "Fysh" <(E-Mail Removed)> wrote in message
> > >> news:1B0F4E6B-A0F4-4696-8E0B-(E-Mail Removed)...
> > >> > Using Access 2k. I have form with a button, when I press this I have a
> > >> > procedure, which deletes information in about 8 temp tables, then it
> > >> > imports
> > >> > delaminated txt files into these tables. I then have it run some
> > >> > update
> > >> > queries and/or append queries for the permanent tables. However I have
> > >> > one,
> > >> > which is giving me nightmares for the past 3 days. I tried using
> > >> > queries
> > >> > and
> > >> > code, but for some reason I can get it to append the right record. The
> > >> > problem is I have several stand-alone systems, which import data into
> > >> > one
> > >> > system via txt files. Here is my code hopefully someone can point me
> > >> > in
> > >> > the
> > >> > right direction. I have tried several variations but nothing works it
> > >> > keeps
> > >> > find the first record in tblPracticalResults and that's it.
> > >> >
> > >> > Dim dbs As dao.Database
> > >> > Dim rst As dao.Recordset
> > >> > Dim rst2 As dao.Recordset
> > >> > Dim rst3 As dao.Recordset
> > >> > Dim rst4 As dao.Recordset
> > >> > Dim strSQL As String
> > >> > Dim strSql2 As String
> > >> > Dim strWhere As Variant
> > >> > Dim varRec As Integer
> > >> > Dim varStudent As Integer
> > >> > Dim varPracID As Integer
> > >> > Dim varID As Variant
> > >> > Dim varPRID As Variant
> > >> > Dim k as Integer
> > >> >
> > >> > Set dbs = CurrentDb()
> > >> > Set rst = dbs.OpenRecordset("tblCommentsTemp")
> > >> > Set rst2 = dbs.OpenRecordset("tblPracticalResultsTemp")
> > >> >
> > >> > rst.MoveLast
> > >> > rst.MoveFirst
> > >> > varRec = rst.RecordCount
> > >> > For k = 1 To varRec
> > >> > varStudent = DLookup("StuID", "tblStudents", "SID= '" & DLookup("SID",
> > >> > "tblStudentsTemp", "StuID = " _
> > >> > & rst![StudentID]) & "'")
> > >> > strSql2 = ("SELECT PracticalResultsID " & " FROM tblPracticalResults "
> > >> > & "
> > >> > WHERE (([PRID] = '" & (varA & "/" & DLookup("PracticalIDNumber", _
> > >> > "tblPracticalData", "UniquePracticalIdentifier ='" & _
> > >> > DLookup("UniquePracticalIdentifier",
> > >> > "tblPracticalDataTemp",
> > >> > "PracticalIDNumber= " _
> > >> > & DLookup("PracticalIDNumber",
> > >> > "tblPracticalResultsTemp",
> > >> > "PracticalResultsID= " & _
> > >> > rst![PracticalResultsID])) & "'") & "/" &
> > >> > rst2![PractCATID]
> > >> > & "/" & rst2![Attribute] _
> > >> > & "/" & rst2![AttributeDetail]) & "'" & "))")
> > >> > Debug.Print strSql2
> > >> > Set rst3 = dbs.OpenRecordset(strSql2)
> > >> > strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
> > >> > rst3.FindFirst strWhere
> > >> > If Not NoMatch Then
> > >> >
> > >> > strSQL = "Insert Into tblComments (CStuID, Comments,
> > >> > PracticalResultsID,
> > >> > StudentID,TimeStampField,Reason)" & _
> > >> > "SELECT " & "'" & (varA & "/" &
> > >> > rst3![PracticalResultsID] &
> > >> > "/" & rst![Reason] & "/" & varStudent) _
> > >> > & "'" & "," & "'" & rst![Comments] & "'" & "," &
> > >> > rst3![PracticalResultsID] & "," & varStudent & "," & "'" _
> > >> > & rst![TimeStampField] & "'" & "," & "'" & rst![Reason]
> > >> > &
> > >> > "';"
> > >> >
> > >> > CurrentDb.Execute strSQL, dbFailOnError
> > >> > End If
> > >> >
> > >> > rst.MoveNext
> > >> > Next k
> > >> > rst.Close
> > >> > rst2.Close
> > >> > rst3.Close
> > >> > Set rst = Nothing
> > >> > Set rst2 = Nothing
> > >> > Set rst3 = Nothing
> > >> > Set dbs = Nothing
> > >> >
> > >> >
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >

Glad you found the solution.

--

Ken Snell
<MS ACCESS MVP>

"Fysh" <(E-Mail Removed)> wrote in message
news:C638C563-6BE9-42E5-815A-(E-Mail Removed)...
> Never mind I figured that part out.
>
> If DCount("CStuID", "tblComments", "CStuID = '" & (varA & "/" & varID &
> "/"
> & rst![Reason] & "/" & _
> varStudent) & "'") > 0 Then
> Else
> strSQL = "Insert Into tblComments (CStuID, Comments,
> PracticalResultsID, StudentID,TimeStampField,Reason)" & _
> "SELECT " & "'" & (varA & "/" & varID & "/" & rst![Reason]
> &
> "/" & varStudent) _
> & "'" & "," & "'" & rst![Comments] & "'" & "," & varID &
> ","
> & varStudent & "," & "'" _
> & rst![TimeStampField] & "'" & "," & "'" & rst![Reason] &
> "';"
>
> CurrentDb.Execute strSQL, dbFailOnError
> End If
>
>
> "Fysh" wrote:
>
>> Also, I forgot to mention I need to check to see if this record exist in
>> tblComments if so then don't append else append. Any suggestions?
>> Thanks
>>
>> "Ken Snell [MVP]" wrote:
>>
>> > I can not say for sure that I'm understanding fully the
>> > interrelationships
>> > and such here, but I still believe that the error in your code is what
>> > I
>> > pointed out in my first reply. Take a look again at these code steps
>> > from
>> > your code:
>> >
>> > Set rst3 = dbs.OpenRecordset(strSql2)
>> > strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
>> > rst3.FindFirst strWhere
>> >
>> > This code opens a recordset named rst3. The code then gets the value of
>> > the
>> > first record in the rst3 recordset. It then finds that same record (the
>> > first record) in the rst3 recordset... in other words, the
>> > rst3.FindFirst strWhere
>> > is doing absolutely nothing for you because it's finding the same
>> > record
>> > that already is the current record. It's saying this:
>> >
>> > "open a recordset that has a value of 1 for the primary key in the
>> > first
>> > record"
>> > "then find the record in this recordset that has a value of 1 for
>> > the
>> > primary key"
>> > Answer: it finds the first record because that is the record from
>> > which
>> > you took the value that you're trying to find in that same recordset!
>> >
>> >
>> > So I believe that
>> > rst3.FindFirst strWhere
>> >
>> > perhaps needs to be this (finding the matching record in the rst
>> > recordset)?
>> > rst.FindFirst strWhere
>> >
>> >
>> > --
>> >
>> > Ken Snell
>> > <MS ACCESS MVP>
>> >
>> > "Fysh" <(E-Mail Removed)> wrote in message
>> > news:F9ED1261-1D15-45E6-BF3A-(E-Mail Removed)...
>> > > Thanks for the response. Well here it goes. I am trying to append to
>> > > tblComments from tblCommentsTemp. The tblComments has foreign key to
>> > > tblPracticalResults (PracticalResultsID) and it has a FK of
>> > > tblPracticalData
>> > > (PracticalIDNumber)and so forth. The tblPracticalresults has about
>> > > 100
>> > > records for every PracticalIDNumber. Because these are stand alone
>> > > systems
>> > > each of these tables has a autonumber field, but also a txt field.
>> > > The
>> > > txt
>> > > field is the PK, but I used the autonumber for the FK. What I am
>> > > trying
>> > > to
>> > > do is append to tblComments, but get the correct PracticalResultsID
>> > > from
>> > > tblPracticalResults after itself has been appended. Because the
>> > > PracticalResultsID changes when brought into the main system so can
>> > > the
>> > > txt
>> > > field. I have an append query for tblPracticalResults which actually
>> > > changes
>> > > the txt field at the same time. The problem I am having when I step
>> > > through
>> > > the code is it keeps locating the first record in tblPracticalResults
>> > > and
>> > > not
>> > > finding the correct record that was changed that relates back to
>> > > tblCommentsTemp.PracticalResultsID. I have tried several
>> > > variations, but
>> > > it
>> > > keeps giving me the first record (in other words it could be 150th
>> > > record
>> > > in
>> > > tblPracticalResults that I am trying to relate it to). I am off work
>> > > now
>> > > but
>> > > I will stay on line tonight to see if you can help me through this
>> > > mess.
>> > > I
>> > > have been able to update/append every table except this one. It is
>> > > the
>> > > one
>> > > that is the furthest out on my relationships.
>> > >
>> > > Should I place rst2 in a different place? I tried it without the
>> > > statement
>> > > of strSql2 and did a series of DLookpus, but I got the same result so
>> > > I
>> > > switched it to this statement hoping to get a different result, but I
>> > > didn't.
>> > > Also, should I go a different route and update the tblCommentsTemp
>> > > table
>> > > with the correct info then from there append to tblComments? Thanks
>> > > for
>> > > any
>> > > help. I know this all seems confusing that was not my intention.
>> > >
>> > >
>> > >
>> > > "Ken Snell [MVP]" wrote:
>> > >
>> > >> We have no idea how to identify what you mean by "the right record".
>> > >> We
>> > >> don't even know what the data are.
>> > >>
>> > >> You're going to need to provide a lot more details, please. Tell us
>> > >> what
>> > >> you're trying to do with this code - in words.
>> > >>
>> > >> I do note this part of your code that may be the problem:
>> > >>
>> > >> Set rst3 = dbs.OpenRecordset(strSql2)
>> > >> strWhere = "PracticalResultsID = " &
>> > >> rst3![PracticalResultsID]
>> > >> rst3.FindFirst strWhere
>> > >>
>> > >> You're asking ACCESS to find the record in rst3 whose
>> > >> PracticalResultsID
>> > >> value is the same as the value that is in the first record of rst3.
>> > >> Perhaps
>> > >> one of these rst3 usages is incorrect?
>> > >>
>> > >> --
>> > >>
>> > >> Ken Snell
>> > >> <MS ACCESS MVP>
>> > >>
>> > >>
>> > >>
>> > >> "Fysh" <(E-Mail Removed)> wrote in message
>> > >> news:1B0F4E6B-A0F4-4696-8E0B-(E-Mail Removed)...
>> > >> > Using Access 2k. I have form with a button, when I press this I
>> > >> > have a
>> > >> > procedure, which deletes information in about 8 temp tables, then
>> > >> > it
>> > >> > imports
>> > >> > delaminated txt files into these tables. I then have it run some
>> > >> > update
>> > >> > queries and/or append queries for the permanent tables. However I
>> > >> > have
>> > >> > one,
>> > >> > which is giving me nightmares for the past 3 days. I tried using
>> > >> > queries
>> > >> > and
>> > >> > code, but for some reason I can get it to append the right record.
>> > >> > The
>> > >> > problem is I have several stand-alone systems, which import data
>> > >> > into
>> > >> > one
>> > >> > system via txt files. Here is my code hopefully someone can point
>> > >> > me
>> > >> > in
>> > >> > the
>> > >> > right direction. I have tried several variations but nothing
>> > >> > works it
>> > >> > keeps
>> > >> > find the first record in tblPracticalResults and that's it.
>> > >> >
>> > >> > Dim dbs As dao.Database
>> > >> > Dim rst As dao.Recordset
>> > >> > Dim rst2 As dao.Recordset
>> > >> > Dim rst3 As dao.Recordset
>> > >> > Dim rst4 As dao.Recordset
>> > >> > Dim strSQL As String
>> > >> > Dim strSql2 As String
>> > >> > Dim strWhere As Variant
>> > >> > Dim varRec As Integer
>> > >> > Dim varStudent As Integer
>> > >> > Dim varPracID As Integer
>> > >> > Dim varID As Variant
>> > >> > Dim varPRID As Variant
>> > >> > Dim k as Integer
>> > >> >
>> > >> > Set dbs = CurrentDb()
>> > >> > Set rst = dbs.OpenRecordset("tblCommentsTemp")
>> > >> > Set rst2 = dbs.OpenRecordset("tblPracticalResultsTemp")
>> > >> >
>> > >> > rst.MoveLast
>> > >> > rst.MoveFirst
>> > >> > varRec = rst.RecordCount
>> > >> > For k = 1 To varRec
>> > >> > varStudent = DLookup("StuID", "tblStudents", "SID= '" &
>> > >> > DLookup("SID",
>> > >> > "tblStudentsTemp", "StuID = " _
>> > >> > & rst![StudentID]) & "'")
>> > >> > strSql2 = ("SELECT PracticalResultsID " & " FROM
>> > >> > tblPracticalResults "
>> > >> > & "
>> > >> > WHERE (([PRID] = '" & (varA & "/" & DLookup("PracticalIDNumber", _
>> > >> > "tblPracticalData", "UniquePracticalIdentifier ='"
>> > >> > & _
>> > >> > DLookup("UniquePracticalIdentifier",
>> > >> > "tblPracticalDataTemp",
>> > >> > "PracticalIDNumber= " _
>> > >> > & DLookup("PracticalIDNumber",
>> > >> > "tblPracticalResultsTemp",
>> > >> > "PracticalResultsID= " & _
>> > >> > rst![PracticalResultsID])) & "'") & "/" &
>> > >> > rst2![PractCATID]
>> > >> > & "/" & rst2![Attribute] _
>> > >> > & "/" & rst2![AttributeDetail]) & "'" & "))")
>> > >> > Debug.Print strSql2
>> > >> > Set rst3 = dbs.OpenRecordset(strSql2)
>> > >> > strWhere = "PracticalResultsID = " & rst3![PracticalResultsID]
>> > >> > rst3.FindFirst strWhere
>> > >> > If Not NoMatch Then
>> > >> >
>> > >> > strSQL = "Insert Into tblComments (CStuID, Comments,
>> > >> > PracticalResultsID,
>> > >> > StudentID,TimeStampField,Reason)" & _
>> > >> > "SELECT " & "'" & (varA & "/" &
>> > >> > rst3![PracticalResultsID] &
>> > >> > "/" & rst![Reason] & "/" & varStudent) _
>> > >> > & "'" & "," & "'" & rst![Comments] & "'" & "," &
>> > >> > rst3![PracticalResultsID] & "," & varStudent & "," & "'" _
>> > >> > & rst![TimeStampField] & "'" & "," & "'" &
>> > >> > rst![Reason]
>> > >> > &
>> > >> > "';"
>> > >> >
>> > >> > CurrentDb.Execute strSQL, dbFailOnError
>> > >> > End If
>> > >> >
>> > >> > rst.MoveNext
>> > >> > Next k
>> > >> > rst.Close
>> > >> > rst2.Close
>> > >> > rst3.Close
>> > >> > Set rst = Nothing
>> > >> > Set rst2 = Nothing
>> > >> > Set rst3 = Nothing
>> > >> > Set dbs = Nothing
>> > >> >
>> > >> >
>> > >> >
>> > >>
>> > >>
>> > >>
>> >
>> >
>> >