markjenkinsrf
New member
- Local time
- Yesterday, 22:14
- Joined
- Sep 3, 2013
- Messages
- 7
I have some questions concerning the use of Queries and Dlookup. I am creating a db that stores the settings of a type of telecomm equipment known as a channel bank (CB). Cb’s have lots of variables that can be programmed and I am trying to keep track of these in my db.
The CB is question has 12 plugin cards (into Slots), and each card can have up to 12 ports. Each port has a circuit number assigned to it, so a matrix of 12 x 12 exists for each CB. The overall system has many CBs, and each CB is assigned to a Site and a Location.
The hierarchy is thus Site, Location, Slot, Port, and finally CircuitNumber. A record will exist for up to 144 ports in a CB. (some are unused, empty). This data is stored in a table.
This info is shown on a form that physically looks like the CB, with lots and lots of textboxes, one for each port. I use Dlookup for each textbox (port) to pull the circuit number out of the table. Thus
Dlookup(“circuitnumber”, “table”, site = listbox, location = listbox, slot = 1-12, port = 1-12)) The site and location are user selected with two listboxs, while slot and port are hard coded as they don’t change. This is working, but pretty slow on my split db, with 144 Dlookups required. To speed things up, I’ve changed the record source for the form from the table to a query. The query has two criteria built into it, Site and Location from the listboxes. I changed the Dlookups to Dlookup(“circuitnumber”, “Query”, slot, port), but this only works once when the form is loaded. If the user changes the Site and Location in the listboxes, the form doesn’t update with the new circuit numbers from the different Site/Location. If I use a Dlookup with all four parameters (same as when used on the Table) it works ok. I think I need to update the query, but not sure how.
In summary, when I switched to a Query with 2 of the parameters (Site/Location) built into the query, I assumed the Dlookup would then just need the other two parameters ( slot/port). But it doesn’t work and I still need all 4 parameters in the Dlookup for it to work.
Thanks for any assistance.
The CB is question has 12 plugin cards (into Slots), and each card can have up to 12 ports. Each port has a circuit number assigned to it, so a matrix of 12 x 12 exists for each CB. The overall system has many CBs, and each CB is assigned to a Site and a Location.
The hierarchy is thus Site, Location, Slot, Port, and finally CircuitNumber. A record will exist for up to 144 ports in a CB. (some are unused, empty). This data is stored in a table.
This info is shown on a form that physically looks like the CB, with lots and lots of textboxes, one for each port. I use Dlookup for each textbox (port) to pull the circuit number out of the table. Thus
Dlookup(“circuitnumber”, “table”, site = listbox, location = listbox, slot = 1-12, port = 1-12)) The site and location are user selected with two listboxs, while slot and port are hard coded as they don’t change. This is working, but pretty slow on my split db, with 144 Dlookups required. To speed things up, I’ve changed the record source for the form from the table to a query. The query has two criteria built into it, Site and Location from the listboxes. I changed the Dlookups to Dlookup(“circuitnumber”, “Query”, slot, port), but this only works once when the form is loaded. If the user changes the Site and Location in the listboxes, the form doesn’t update with the new circuit numbers from the different Site/Location. If I use a Dlookup with all four parameters (same as when used on the Table) it works ok. I think I need to update the query, but not sure how.
In summary, when I switched to a Query with 2 of the parameters (Site/Location) built into the query, I assumed the Dlookup would then just need the other two parameters ( slot/port). But it doesn’t work and I still need all 4 parameters in the Dlookup for it to work.
Thanks for any assistance.
Check out this idea of Pat's : 