I am able to create list / combo boxes in a form where the fields displayed in the box (for the users to select) will depend on (1) what has been entered in other fields on the form and (2) results from other queries/look up tables. I'm happy with all of this.

Where I'm stuck is that I am now trying to create a list box which will display certain fields based on data entered in the form, where one piece of data will be completely new. In other words, I can't simply run a query in the 'RowSource' property because it won't return anything (as the query won't be based on data it can recoginse and thus cross reference).

I'm guessing I need to look up the appropriate VBA for accessing a table/query to see if certain data exists and then provide the form with the right fields that will be displayed in the list box. At the moment I have no idea how to do this. I would really appreciate some pointers/direction.

I hope this makes sense! Thanks.

Have a look at the DLookup and DCount functions. I think they're the easiest way of checking if data exists in a table.

Hope that helps.

Thanks Alc, I have used DCount in a module and I believe I'm half way there.

Next step, I need some code to do this:

1) If DCount returns 0, I need the list box (on the form) to display one set of fields.
2) If DCount returns >=1, I need a different set of fields in the list box.

I could get this function to return a 0 or a 1, but is there some way I can pass this variable to the query builder (for my list box on the form)?

Would something like the following work for you?

Dim str_SQL as String

If DCount(some criteria) > 0 Then
str_SQL = "SELECT fields A FROM tablename;"
Else
str_SQL = "SELECT fields B FROM tablename;"
End If

Then set the listbox source to the SQL string.

I think its exactly what I want. My code (in a module) is as follows:

Function ResultFilter()

Dim str_SQL As String
Dim ResultType As Integer

ResultType = DCount("[TestResult]", "[TR 1st pass]", "[TestResult] = '1st Pass' Or [TestResult] = '1st Fail'")

If ResultType > 0 Then
str_SQL = "SELECT TestResults.TestResult, TestResults.TestResultType From TestResults WHERE (((TestResults.TestResultType)='1' Or (TestResults.TestResultType) Is Null));"
Else
str_SQL = "SELECT TestResults.TestResult, TestResults.TestResultType From TestResults WHERE (((TestResults.TestResultType)='2' Or (TestResults.TestResultType) Is Null));"
End If
End Function

I copied the strings from the SQL view (had to change " to '). I confirmed the query worked perfectly with these strings before pasting it into the code.

My list box has the RowSource set to str_SQL.

I have tried various ways of running the Module, such as =ResultFilter() in the before update property etc, but the list box is constantly blank. I'm not sure if this should be a private function or not! Any ideas?

I'm not convinced you need to use a module.

At what point is the refresh required to happen?
Let's say it's after a given field (Field1) has been updated.

In the AfterUpdate event of Field1 you could put something like you have now.

Dim str_SQL As String
Dim ResultType As Integer

ResultType = DCount("[TestResult]", "[TR 1st pass]", "[TestResult] = '1st Pass' Or [TestResult] = '1st Fail'")

If ResultType > 0 Then
str_SQL = "SELECT TestResults.TestResult, TestResults.TestResultType From TestResults WHERE (((TestResults.TestResultType)='1' Or (TestResults.TestResultType) Is Null));"
Else
str_SQL = "SELECT TestResults.TestResult, TestResults.TestResultType From TestResults WHERE (((TestResults.TestResultType)='2' Or (TestResults.TestResultType) Is Null));"
End If

ListBoxName.RecordSource = str_SQL
ListBoxName.Requery

It works!! :))

Thank you ever so much for your help. This has been a good learning experience for me and now the users won't be able to select the wrong fields again!

Good news.

Glad I could help. This forum has been invaluable for me, so it's nice to pass something on.