Calculating an In('1','2','3'...) expression from values in a table

[MWKillebrew]

I'm trying to create a tool for co-workers in my department to append data into a local Access database from an AS400 system. I'm trying to make this as user-friendly as possible for end users that won't be familiar or comfortable with adjusting the query SQL.



I have a passthrough query I've already written, but my problem is that I have an In() expression in the query that can vary depending on what the user criteria is. I've already tried to work around this by attempting to create an inner join between my local table (tbl_insco_criteria) and the AS400 file I'm querying, but this hasn't worked. Using a Select query linked to this Passthrough query to do this same action works, but takes considerably more time to generate due to the number of records involved, thus my attempt to find a way to calculate an updated In() expression.

I already have a VBA function that can perform a Find/Replace action to update the character string that needs to be updated, however, I'm having difficulty in writing a function that will calculate an updated In() expression from records entered into a table that can vary in the number of records entered, that can then be passed to my passthrough query using the Find/Replace function.

The table name is tbl_insco_criteria with a single column name of INSCO that is in Long Integer number format. An example of a string that might be generated is IN('305','306','380','800','801'). I would be placing the expression calculation into a form that would then be referenced by the Find/Replace function when that action is performed.

I have found an example that is sort of in the same vein as what I'm trying to accomplish here :http://www.databasedev.co.uk/query_using_listbox.html. However, my goal is for to users enter in the values into the tbl_insco_criteria table (as there would be no standard list of values that I can feed to a listbox, since the values in the AS400 dictionary files can vary from one AS400 to another), and then the VBA would calculate the In() expression SQL string in a form field on the database switchboard that would include all records in the INSCO field in the table. This SQL string would then be inserted into the passthrough query via the Find/Replace function (that I already have working) prior to running the query.

Any assistance would be appreciated.

[pbaldy]

You could open a recordset on that table and loop the results to build your string. Use that string when building the QueryDef. Basically the same but looping a recordset instead of a listbox.

[MWKillebrew]

Thanks for the response Paul. I've tried taking what I think is the relevant portion from the link I posted previously and adjusting it for what I'm trying to accomplish, but still running into issues (see below). I know enough about writing VBA to be dangerous, but not super proficient.

Private Sub Command353_Click()

    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim i As Integer
    Dim strIN As String

Set MyDB = CurrentDb()

Set rst = dbs.OpenRecordset("tbl_insco_criteria", dbOpenTable)

For i = 0 To rst.RecordCount - 1
strIN = strIN & "'" & rst.OpenRecordset(0, i) & "',"

Next i

Me.Text354.Value = "In" & "(" & Left(strIN, Len(strIN) - 1) & ")"

End Sub

[pbaldy]

You didn't mention what the issues were, but for starters, this line:

strIN = strIN & "'" & rst.OpenRecordset(0, i) & "',"

would have to be:

strIN = strIN & "'" & rst(0) & "',"

as you only open the recordset once (you can also use rst!FieldName to get the value). I'd loop like

Do While Not rst.EOF
  'your code here
  rst.MoveNext
Loop

Yours may work, but I don't like relying on the record count.

[MWKillebrew]

Okay, this appears to work:

Private Sub Command353_Click()

    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim i As Integer
    Dim strIN As String

Set MyDB = CurrentDb()

Set rst = MyDB.OpenRecordset("tbl_insco_criteria", dbOpenTable)

Do While Not rst.EOF
strIN = strIN & "'" & rst(0) & "',"
rst.MoveNext
Loop

Me.Text354.Value = "In" & "(" & Left(strIN, Len(strIN) - 1) & ")"

End Sub

Thanks for your assistance Paul!

[pbaldy]

Happy to help and welcome to the site!