using tables as a listbox and reports source

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello

The only code I’m any good at is the cutting and pasting kind.

I'm trying to create a form that will list all tables in the open database and then allow the user to select a table as the record source for a report. So I've managed to make a form that lists the appropriate tables and I've applied the following code.

So far I’ve been able to come up with the following:

This is what I have as the listbox's row sourc

SELECT [Name] FROM MSysObjects WHERE (Left([MSysObjects].[Name],3)='tbl') And ([MSysObjects].[Type]=1) ORDER BY [Name];

On the listbox's double click event

Private Sub List0_DblClick(Cancel As Integer
'** this will assign selected table as report's sourc
Dim strTblName As Strin

strTblName = Me.List0.Valu

' opens allowance report in design view to choose tabl
DoCmd.OpenReport "rptEquipAllow", acPreview, "", "

End Su

But I’m missing something, and am not sure what it is as it's not populating the report with the clicked table...why

Thanks
 
laura said:
I'm trying to create a form that will list all tables in the open database and then allow the user to select a table as the record source for a report. So I've managed to make a form that lists the appropriate tables and I've applied the following code.

So far I’ve been able to come up with the following:

This is what I have as the listbox's row source

SELECT [Name] FROM MSysObjects WHERE (Left([MSysObjects].[Name],3)='tbl') And ([MSysObjects].[Type]=1) ORDER BY [Name];

On the listbox's double click event:

Private Sub List0_DblClick(Cancel As Integer)
'** this will assign selected table as report's source
Dim strTblName As String

strTblName = Me.List0.Value

' opens allowance report in design view to choose table
DoCmd.OpenReport "rptEquipAllow", acPreview, "", ""
Click to expand...


You have to explicitly set the report's RecordSource
property. You might be able to get away with this:

DoCmd.OpenReport "rptEquipAllow", acPreview
DoEvents
Reports!rptEquipAllow.RecordSource = strTblName

If that causes you trouble, then you could place the table
name in a hidden text box so the report's Open event can set
its own record source.
 
Back
Top