+Selecting a table to query

[WAVP375]

in a database of 'Events' I have a table of event name 'TblEventNames' that I update each time I create a new event table. Update and reports are required from any one of the vents at user chosen times. I am attempting to show all event names in a list box the select the event that I want to run a report on. My thought is to create a query that will run against whichever table I choose from the list box. This way one report bound to the query is all that is required. Or maybe there is a way to make a variable bind in the report itself.

Any thoughts would be appreciated.

[orange]

I am not sure of what you are asking, but this link may be helpful.

Good luck.

[CJ_London]

each time I create a new event table

why are you creating a new table for a new event? You should have one table, with an event FK linked back to the event PK in your EventNames table. Then your report would be easy

[WAVP375]

What is an event FK?

[WAVP375]

Sorry for my ignorance. I follow the use of the primary key in the Event Name table but I will still need a query to select the records for the group of names associated with the event name behind the primary key. How do I make that query selectable.

[June7]

Agree with Ajax, should not create a table for each event. One table and the quandary of how to select particular event table is eliminated.

Use a form for user to select filter criteria. Apply filter to a form or report.

Review: http://allenbrowne.com/ser-62.html

[CJ_London]

PK=Primary Key
FK=Foreign or Family Key

There is a one to many relationship between a PK and FK's in the related tables

[WAVP375]

I see your suggestion is a good one. TblEventDownLoad contains names that have been down loaded from an excel spreadsheet. I have created a query QappVolNamesTable to append the names to the existing events I am attempting to force an event number in to a field in the table to differentiate this event from others in the table. When I execute the query I get an error 'Duplicate Output Alias 'Expr1". The query expression is as follows:

Expr1:[Forms]![FrmEventDownLoad]![EventNumber], append to: EventNumber

[June7]

How is the EventNumber created? Why would you need to run an UPDATE query? If you want to provide db for analysis, follow instructions at bottom of my post.

[WAVP375]

Sorry, not an update query but an append query where I add the new event to the table. The event number is applied in the append query. This is all working fine now. Thanks this approach should be much better in the long run.

I am now have a problem with an update form where I want to populate a list box with persons from only one event where the event number is contained in a text box on the form. The code I am using is

Dim FilterSource As String
FilterSource = "Select[TblVolNames].[EventNumber],

[TblVolNames].[Badge],

[TblVolNames].

[LastName],

[TblVolNames].

[FirstName],

[TblVolNames].

[Volunteers],

[TblVolNames].

[Guests],

[TblVolNames].[

Paid],
From[TblVolNames]; Where [TblVolName[.[EventNumber] = Me.MyEvent"
Me.LstNames.RowSource = FilterSource
Me.LstNames.Requery

Where LstNames is the list box
Where MyEvent is the Textbox with the event number

When I execute the listbox goes empty

[davegri]

Syntax problems:

Paid] 
From [TblVolNames]; Where [TblVolName[.[EventNumber] =" & Me.MyEvent

Delete the comma after Paid], but leave a space
Add a space after FROM
Delete the semicolon after [tblVolNames] but leave the space
Edit like the red

[isladogs]

Further correction

Paid] 
FROM [TblVolNames] WHERE [TblVolName].[EventNumber] =" & Me.MyEvent

[June7]

Also need a space after SELECT:

FilterSource = "SELECT EventNumber, Badge, LastName, FirstName, Volunteers, Guests, Paid FROM TblVolNames WHERE EventNumber = " & Me.MyEvent & ";"

Assumes EventNumber is a number type.

But really don't need to set the listbox RowSource with code. Put the SQL statement in the RowSource property and then just run the Requery method in code.
SELECT EventNumber, Badge, LastName, FirstName, Volunteers, Guests, Paid FROM TblVolNames WHERE EventNumber = [MyEvent];

[WAVP375]

Thanks guys, getting the punctuation correct is the toughest. That working populates the list box with only the records for the selected event. However when I choose one of the items in the listbox the first item in the listbox is all that displays on the form.

[June7]

This is called cascading (dependent) combo or list box. They won't work nice on continuous or datasheet form if the RowSource is a lookup with alias. A frequently discussed topic.