So here's what I'm trying to do:
I've got about 3,000+ records total that describe packages. Some subset of those packages will be checked by Quality Control before leaving in a delivery, perhaps as many as 3/4 in a shipment. What I'd like to do is have a list of those packages NOT being checked in a subform on the left, and those packages BEING checked in a subform on the right. I'd also like a set of left and right arrows, the left arrow bringing packages OUT of the QC box, the right arrow putting packages IN the QC box. Naturally, after each movement the subforms would need to refresh. The movement buttons are not the problem. The subforms are not the problem. The problem is that the way I'm doing it is HORRIBLY slow.
I've got a table, SOURCE, that lists all packages.
I've got another table, SCHEDULE, that lists which package get QCd in which delivery (there are multiple deliveries).
I've got a query, QC, that lists only those packages that will be QCd in this delivery (from the SCHEDULE table). That's the source for the right subform.
I use a NOT IN against the QC query to populate the left subform.
The right arrow uses VBA to run an SQL query to update the SCHEDULE table by adding the selected record in the left to the SCHEDULE table.
The left arrow also uses VBA to run an SQL query to delete the package from the SCHEDULE table from that delivery.
After an arrow is clicked, I refresh the subforms.
Now, I used queries because I've heard tell that they're faster to update than tables. But when I initially query or refresh the subforms, it goes so slowly as to make the whole form unusable. Am I going about this correctly? Is there a faster way to reference these things?
I've got about 3,000+ records total that describe packages. Some subset of those packages will be checked by Quality Control before leaving in a delivery, perhaps as many as 3/4 in a shipment. What I'd like to do is have a list of those packages NOT being checked in a subform on the left, and those packages BEING checked in a subform on the right. I'd also like a set of left and right arrows, the left arrow bringing packages OUT of the QC box, the right arrow putting packages IN the QC box. Naturally, after each movement the subforms would need to refresh. The movement buttons are not the problem. The subforms are not the problem. The problem is that the way I'm doing it is HORRIBLY slow.
I've got a table, SOURCE, that lists all packages.
I've got another table, SCHEDULE, that lists which package get QCd in which delivery (there are multiple deliveries).
I've got a query, QC, that lists only those packages that will be QCd in this delivery (from the SCHEDULE table). That's the source for the right subform.
I use a NOT IN against the QC query to populate the left subform.
The right arrow uses VBA to run an SQL query to update the SCHEDULE table by adding the selected record in the left to the SCHEDULE table.
The left arrow also uses VBA to run an SQL query to delete the package from the SCHEDULE table from that delivery.
After an arrow is clicked, I refresh the subforms.
Now, I used queries because I've heard tell that they're faster to update than tables. But when I initially query or refresh the subforms, it goes so slowly as to make the whole form unusable. Am I going about this correctly? Is there a faster way to reference these things?
