Listbox from Query

[Rob Edwards]

Thought this would be easy...

A report is based off a table: tblServer

There is another table: tblServerIPS, which stores the IPAddresses of each
server (1 to many).

On a report, I want to display server data (name, serialtag, etc.) along
with all the IP addresses of the server.

What is the best way to accomplish this? I've tried the Listbox control and
have been unable to to construct the proper settings. I've looked into
DLookUp but some sites said it would only return single values.

It's a piece of cake in Visual Studio....

 

[Al Campagna]

Rob,
Your tables should look something like this...
tblServers
ServerID Server Serial ServerName
1324 TW-3122 Bob
4312 X-190234 Mary

tblIPAddresses
ServerID IPAddress
1234 207.142.131.248
1234 209.111.423.198
4312 206.615.286.376
1234 821.624.817.717

In the query behind the report, relate the Server table to the IP table via your link
field. (ex. ServerID)
On the report, Group on Server, place the server (ONE) information in the Server Group
Header, and place the IP address field in the Detail section.
The report wil list a server, and then every IP associated with, then list another
server and all it's IP addresses, etc... etc... for all servers.

 

[Rob Edwards]

Thank you for taking the time to respond.

This report actuall

 

[Rob Edwards]

Sorry for the duplicate response... touchpad issue.

Thanks for taking the time to respond. The method you outlined certainly
will work, but the report being structured will need multiple tables
(tblServerIPs, tblHostedApps, tblConnectionPorts) displayed. That is why I
was trying the Listbox control.

Is it possible to accomplish this with the Listbox control?

 

[Rob Edwards]

I've come up with a bit of a workaround.. which almost gets the job done.

I've created a query: qryIPAddresses setting the criteria for ServerID =
Reports!ServerReport!ServerID.

On the Page event, I added the following line:

ListBoxServerIPAddress.Requery

Now the report renders and displays the IP Address in the listbox, for every
record but the first.

While not ideal...it's closer.

 

[Al Campagna]

Rob,
On a report, you don't really use a ListBox to display multiple items of information.
Your tables should be related properly, and if so... they should all come together in the
report query, and flow out of that "joined" recordset.
What would happen if each server had 100 IP addresses each? They may not now, but good
design should allow for that. That listbox would probably be unworkable.
At least, try a IPAddress subform, related to the main report via a Parent/Child field
like our example ServerID...