Are Mr_InstrumentPlan and dbo_plan related in any way? Do they have any
common fields where the could be joined? If so, then you could create a
query that would show only those rows you want.
:
I thought about a query first, but the point is: I need to open a query by
selecting different fields from 2 tables, say, my current query is:
Set dbs = CurrentDb
Set rstIteminfo = dbs.OpenRecordset("select * from Mr_InstrumentPlan " _
& "where ShipTo = '" &
Forms!instrumentplan_multiple!ShipTo & "' and Year = " _
& Forms!instrumentplan_multiple!Year & " order by
SetNum")
and then after I read each of the Recordset, I check another table like this:
sCode = DLookup("SegmentCode", "dbo_Item", "[ItemNumber] = '" & pItemNumber
& "'")
which the pItemNumber is the number from the first query Recordset, to
decide if I should show this item or not, can I do this by joining these 2
tables in the dbs.OpenRecordset command?
Thanks a lot!
:
In the part where you have to do the inquiry, rather than code, is it
possilbe you could use a query?
:
Hi Klatuu,
Sorry I didn't explain the situation of my Application clearly, for this
deletion part, I have changed the code to your way, and both your way and my
way work well, since it's just to delete some records if they are duplicated,
there isn't much difference in this case. though I believe your way is much
better considering in the coding way as well as the performance.
I mensioned the performance is not good, because I have another procedure
for inquiry, that is a kind of complicated situation, I need to open a
Recordset first, then for each record I got, I need to check if the item is
right or not by checking another table, and then I can finally decide if I
can show this item on the form or not, there are totally 15 items and their
quantities I can show on the form, it usually takes me 48 seconds to get the
result by running this procedure, that's why I said the performance is not
good... anyway, I'm still looking for some ways to improve it.
Thank you very much for your help and patience!
:
LOL
There is no hard and fast rule. Because you are using an SQL statement as
your record source, you would have to open and close on each iteration. The
Set dbf = CurrentDb, on the other hand, does not have to be reset each time,
so it should be done before the loop. The way you are doing it doesn't do
any damage, it just takes up time.
Notice the difference in the way I am doing it with an Execute method. Not
only does it reduce the lines of code, I believe it would be much faster.
Just for grins, use it the way you have it, and time it. Then change it to
the way I posted it and compare the execution times.
If fact, if you would post back the execution times for both methods, I
would greatly appreciate it. I would be curious to see how much difference
there is. Also, as a point of interest and comparison, it would be nice to
know how many rows in your table match the delete criteria, and what the
record count of the table was before you do the deletes.
Thanks, lilly
:
Hi Klatuu,
I am very honored to have you give me these so professional guidance, thank
you very much!
of course it works! You wrote it!
I do have a lot of operation by using the DAO.Recordset in a loop, and
usually I close it only after the loop finished, is that OK? Do I need to
close it each time in the loop? because I did notice that the performance is
not good....
Thanks again!
:
Private Sub x_delrecord_Click()
Dim dbs As Database
Dim successcount As Integer
Set dbs = CurrentDb
For successcount = 5 to 1 Step -1
dbf.Execute("DELETE * FROM Mr_InstrumentPlan WHERE SetNum = '" _
& Me.Controls("SetNum" & successcount) & "';"
Next successcount
Set dbf = Nothing
End Sub
The above syntax assumes the data type of the field SetNum in the
Mr_InstrumentPlan table is text. If it is numeric, then change replace the
execute statement with this:
dbf.Execute("DELETE * FROM Mr_InstrumentPlan WHERE SetNum = " _
& Me.Controls("SetNum" & successcount) & ";"
Now, two nags.
1. There are no fields on forms. They are controls. Fields are only in
tables and queries. It makes it easier to communicate if you use the correct
terms.
2. Notice in your version of the code, you have to establish the database
and recordset objects for each iteration of the loop. Another problem you
have was that you were not closing the recordset. The issue here is
performance. This would slow your application down. Be careful what you do
inside loops.
Let me know if this works for you. If you need more help or have questions,
please post back.
:
I have several unbound fields named: "SetNum1", "SetNum2", "SetNum3"... in my
form, I need to do the same thing for each of these fields, say:
Private Sub x_delrecord_Click()
Dim dbs As Database, rstCustomers As DAO.Recordset
Dim successcount As Integer
successcount = 5
Do Until successcount = 1
successcount = successcount - 1
Set dbs = CurrentDb
Set rstCustomers = dbs.OpenRecordset("select * from
Mr_InstrumentPlan " _
& "where SetNum = '" & SetNum & successcount
& "'")
If Not rstCustomers.EOF Then
rstCustomers.Delete
End If
Loop
End Sub
I mean for the part: SetNum & successcount , I need it to be SetNum1,
SetNum2, SetNum3.... as the variable "successcount" changes each time, but
this doesn't work, how can I do it?
any help would be great, thanks a lot in advance!
