Query with Inputbox as a recordsource -

[RonQA]

Here is what I have:
========================
Query:
========================
SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((MsysObjects.Name) Like [Enter Work Order] & "*") AND ((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1))
ORDER BY MsysObjects.Name;

This searches for the desired table.

=========================
VBA Code:
=========================
Dim NewName As String
DoCmd.SetWarnings False
NewName = [Find Previous Checklist]![Name]
Me.Report.RecordSource = NewName
DoCmd.SetWarnings True

My goal is to change the report recordsource by searching for a table and then opening that report.

I have tried different variations with no luck. When the report is launched it does not open the inputbox for data entry.

Thanks,

 

[PWise]

dont see any call to an input box

try

Dim NewName As String
DoCmd.SetWarnings False
NewName = inputbox "select table name"
Me.Report.RecordSource = NewName
DoCmd.SetWarnings True

 

[RonQA]

This line is in the query which brings up a data entry box. Like [Enter Work Order]

The query searches for a specific table.

Thanks,

 

[MajP]

Public Function getLike() As Variant
Dim rtn As String
rtn = InputBox("Enter name or cancel for all records.", "Enter Name")
If Not Trim(rtn) = "" Then
getLike = "*" & rtn & "*"
Else
getLike = "*"
End If
Debug.Print getLike
End Function

SELECT * from tbl
WHERE tbl.fieldname Like getLike()

 

[dhookom]

I generally leave the same query as the record source of a report and run some code to change the SQL of the query. If you have multiple similar tables then it suggests your tables aren't designed correctly.

Duane
Hook'D on Access
MS Access MVP

 

[RonQA]

Here is some more detail:

1. I run a query that creates a table based on a number.
2. Run a report that fires a different query that searches the database for the new table ex. "123456_something" that query brings up a databox asking for the number. All I have to enter is "123456" and it finds the right table.

Now how do I pass the table found "123456_something" to the report recordsource?

The query finds the right table with no problem, I just can't pass it to the report.

Sorry if I was vague.

 

[dhookom]

Let's assume your report is based on a query named "qsel_Something". You could use a little DAO code to change the SQL property of qsel_Something.

'I hate input boxes
Dim strSQL as String
Dim strNumber as String
strNumber =  Inputbox("Enter the exact number and don't make a mistake")
strSQL = "SELECT * from " & strNumber & "_something"
Currentdb.QueryDefs("qsel_Something").SQL = strSQL
DoCmd.OpenReport "rptSomething", acPreview

Duane
Hook'D on Access
MS Access MVP