Complicated append

F

Fysh

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
 
K

Ken Snell [MVP]

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 said:
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
Click to expand...
 
F

Fysh

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 said:
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 said:
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
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
F

Fysh

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 said:
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 said:
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 said:
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>



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
Click to expand...
Click to expand...
Click to expand...
 
F

Fysh

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 said:
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 said:
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 said:
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>



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
Click to expand...
Click to expand...
Click to expand...
 
F

Fysh

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 said:
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 said:
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 said:
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>



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
Click to expand...
Click to expand...
Click to expand...
 
F

Fysh

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 said:
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 said:
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 said:
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.



:

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>



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
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Glad you found the solution.

--

Ken Snell
<MS ACCESS MVP>

Fysh said:
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 said:
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 said:
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>

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.



:

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>



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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Canceling an OnClick Event 3
Too Few Parameters, 1 expected 3
Query Online Database 11
qty in table1 minus qty in table2 equal to result column in table2 2
Switching from Corel Paradox to Access 2010 5
Function not working 3
Slow Code/Query Execution 9
Recordset to update table 1

Top