How do I include selected items that are not in the table?

[Vita]

This code below loops through all the ID's in the temporary table and opens a report for them but only if it finds the ID. How do I get it to open a report for it even if it doesn't?
For example say the table contains ID's 1-10 and I type 15, I want the report to open with something like "Not found".

I thought about rewriting this as a for loop that cycles through the ID's using DAO but I think it would open a different report for each ID. Not to mention I would have to import DAO and get rid of ADO (its an old DB)

Me.Dirty = False
Dim StrSql As String
StrSql = "SELECT ID FROM TempPullsTable"
nz(DoCmd.OpenReport("PullsReport", acViewPreview, , "Autonum IN (" & StrSql & ")"),

[Minty]

I'm pretty sure the open-report where clause won't evaluate that SQL string as a query I'm afraid. It's effectively a simple WHERE clause.
You would need to build that differently.

Or simply include that in the report's underlying data query?

[Vita]

I'm pretty sure the open-report where clause won't evaluate that SQL string as a query I'm afraid. It's effectively a simple WHERE clause.
You would need to build that differently.

Or simply include that in the report's underlying data query?

Me.Dirty = False
Dim StrSql As String
StrSql = "SELECT ID FROM TempPullsTable"
DoCmd.OpenReport("PullsReport", acViewPreview, , "Autonum IN (" & StrSql & ")")

I forgot to remove the NZ my bad. It already does work. I am not sure how though.
The report pulls "Autonum" from Table1 via a record source query. I am guessing this works by opening a report for any Autonum it finds inside of the ID from TempPullsTable.
Is there a NZ function in SQL? I read about something called COALESCE but I am not sure how to use it.

I think if I were to just modify the where clause to be inside a SQL version of NZ then it could just print the ID.

[Vita]

I'm pretty sure the open-report where clause won't evaluate that SQL string as a query I'm afraid. It's effectively a simple WHERE clause.
You would need to build that differently.

Or simply include that in the report's underlying data query?

You actually helped me with this method on a different forum post (https://www.accessforums.net/showthread.php?t=87676)
The dilemma is that if the ID that is typed into the form is not found in the table with Autonum then there is no indicator and it just skips over that ID. Then nobody knows there's an issue.
I am not sure how to handle this. Maybe I have to do things different?

[Welshgasman]

Use a DLookUp() or DCount() to test if it exists first?

[Minty]

If the ID's were queried and presented in a combo box, then only existing correct data would be presented as available choices?

The secret with good database design is making the right tool for the job.

For instance I hate check boxes to indicate something has been done.
Use a date field instead, then you know it was done and When!

[Vita]

Use a DLookUp() or DCount() to test if it exists first?

So make a for loop to cycle through all the ID's and check if they are in the table with Autonum?


I was more hoping to make something like this: (I know this doesn't work)

DoCmd.OpenReport "PullsReport", acViewPreview, , "NZ(Autonum IN (" & strSQL & "), " & strSQL & ")"

My idea was that if it couldn't find the ID(strSQL) in autonum then it would just write the ID in the reports "ID" field and I would get a report with the ID and none of the other information filled out. (That way I would at least know an ID was missed.)

The report is supposed to come up with the ID, length, quantity, etc. In my understanding the existing Where clause displays a report for every Table1!Autonum it finds in TempTable!ID. If a TempTable!ID is not in table1 though it just skips making a record and I don't want that. (Hopefully that helps explain things a bit)

[Vita]

If the ID's were queried and presented in a combo box, then only existing correct data would be presented as available choices?

The secret with good database design is making the right tool for the job.

For instance I hate check boxes to indicate something has been done.
Use a date field instead, then you know it was done and When!

So just change the ID textbox to a combobox that sources the ID's from Table1 and I can set it to be restricted to the list so that if it isn't in there it will alert the user saying so? I suppose this should work!

[Minty]

So just change the ID textbox to a combobox that sources the ID's from Table1 and I can set it to be restricted to the list so that if it isn't in there it will alert the user saying so? I suppose this should work!

If the combo only displays the things you want passed to the process you don't need to warn the user about anything, as you will only be processing a valid entry.
Make the combo row source a query with the right criteria.

[Welshgasman]

So make a for loop to cycle through all the ID's and check if they are in the table with Autonum?


I was more hoping to make something like this: (I know this doesn't work)

DoCmd.OpenReport "PullsReport", acViewPreview, , "NZ(Autonum IN (" & strSQL & "), " & strSQL & ")"

My idea was that if it couldn't find the ID(strSQL) in autonum then it would just write the ID in the reports "ID" field and I would get a report with the ID and none of the other information filled out. (That way I would at least know an ID was missed.)

The report is supposed to come up with the ID, length, quantity, etc. In my understanding the existing Where clause displays a report for every Table1!Autonum it finds in TempTable!ID. If a TempTable!ID is not in table1 though it just skips making a record and I don't want that. (Hopefully that helps explain things a bit)

You could use the OnNoData event of the report perhaps.
My thoughts were as you WERE processing the numbers, you check first. If found run as normal, else pass in a flag in OpenArgs that tells the report to output whatever you want to see.

[Vita]

If the combo only displays the things you want passed to the process you don't need to warn the user about anything, as you will only be processing a valid entry.
Make the combo row source a query with the right criteria.

This works! Thanks! Much simpler than what I was overthinking.