Probably Simple

[XLEAccessGuru]

This is probably simple, but I'm stuck.

I have a Form1.ListBox 1 on Form1 that contains several rows of data from TableA. On the OnClick event of a button on Form1, I need to refresh Form2.Recordsource (a different form) to contain only the items in Form1.ListBox1, but from a different table - TableB - based on related field [ProfileID]. I need to extract the [ProfileID] numbers from Form1.ListBox1 and create a new recordsource to pull records that match those [ProfileID] numbers.

So far I've got the code below but I need to modify 'stSQL' to be the new recordsource for Form2 based on the data in Form1.ListBox1.

If you look where it says:

With Forms!frmIUPSetupModify
            .RecordSource = stSQL
            .Requery
 End With

...this is where the recordsource gets refreshed, but stSQL isn't quite right.

Any help is hugely appreciated as always!!! Here's the code.

Private Sub cmdOK_Click()
On Error GoTo ErrHandle

Dim stSQL As String
Dim stWhere As String

stSQL = "SELECT tblProjectsImport.* FROM tblValidation INNER JOIN tblProjectsImport " _
        & "ON tblValidation.ProfileRecordID = tblProjectsImport.ProfileID WHERE (((tblValidation.TID)=" & iTID & ") "

Select Case stCurProfType
        Case "IUP - Setup or Modify Content"
        stWhere = "AND ((tblProjectsImport.Type)='IUP - Setup or Modify Content'));"
        stSQL = stSQL & stWhere
        With Forms!frmIUPSetupModify
            .RecordSource = stSQL
            .Requery
        End With
    Case "Create New"
        stWhere = "AND ((tblProjectsImport.Type)='Create New'));"
        stSQL = stSQL & stWhere
        With Forms!frmBuildNewDefaultAssign
            .RecordSource = stSQL
            .Requery
        End With
    Case "Default-Existing: Assign"
        stWhere = "AND ((tblProjectsImport.Type)='Default-Existing: Assign'));"
        stSQL = stSQL & stWhere
        With Forms!frmDefaultExistingAssign
            .RecordSource = stSQL
            .Requery
        End With
End Select

DoCmd.Close acForm, "frmValidationReport"

Exit_ErrHandle:
    Exit Sub
    
ErrHandle:
    MsgBox Err.Description, vbCritical, "Error: " & Err.Number
    Resume Exit_ErrHandle
End Sub

 

[ajetrumpet]

If you look where it says:

With Forms!frmIUPSetupModify
            .RecordSource = stSQL
            .Requery
 End With

...this is where the recordsource gets refreshed, but stSQL isn't quite right.

What is happening with it? What do you need? I don't see anything wrong with the concatenation of the all the SQL segments you have. But, if the ListBox that you are referring to is called iTID, then there is something wrong:

When you refer to a listbox, you can't treat the object as though it is one entity like you can for a combo box. Check out Lister's multi-select listbox example in the sample databases section here. His code will show you how to refer to multiple selections for this situation (which is what you're wanting, I'm guessing).

The other thing I'm wondering is why you have a requery action with the Recordsource change. I don't that is needed, but it probably doesn't do any harm (but I'm not really sure about that).

 

[XLEAccessGuru]

Thanks for your feedback.

The requery thing is just an extra that probably isn't needed but is there for added security.

iTID isn't the list box. It's a global variable used to identify the currenlty active ticket number throughout the program while it's being created or edited.

The problem is that my SQL statement is not right. I prefer to use SQL strings rather than pre-built queries whenever I can because queries can get accidentally deleted. In any case, the problem is that my SQL statement isn't right. But what I think I am going to have to do in this case IS build a query and save it, then write my SQL based on that query. I don't see any other way of doing it.

I did consider throwing all of the record IDs from the list box into a dynamic array and forming 'stSQL' from the array, but I don't have enough experience with arrays to perform this task the way I want it to work.

Even so, do you any thoughts or opinions on the array idea, or have you ever built a simple SQL statement from an array?

Thanks for your help thus far!!

 

[XLEAccessGuru]

PS - I should clarify that the current stSQL string isn't right because the list box's rowsource is not static, i.e. it changes each time the form is loaded. THAT's where I'm having the trouble. I need to build the other form's recordsource based on the listbox rowsource each time the list box is updated.

Sorry, I didn't make that clear before. Now does it make a little more sense why I'm stuck?

 

[ajetrumpet]

I did consider throwing all of the record IDs from the list box into a dynamic array and forming 'stSQL' from the array

Even so, do you any thoughts or opinions on the array idea, or have you ever built a simple SQL statement from an array?

Yes I have. And, you might want to take a look at the Randomizer sample I posted here. It's on post #30. There is plenty of code behind the form button, but after the declarations section you can see where I adopted Lister's multi-select listbox code to build an SQL statement from the box's selections. That might give you a start with this concept.

I should clarify that the current stSQL string isn't right because the list box's rowsource is not static, i.e. it changes each time the form is loaded. THAT's where I'm having the trouble. I need to build the other form's recordsource based on the listbox rowsource each time the list box is updated.

As far as this goes Guru, I can tell you this:

As soon as you assign an SQL string as the recordsource for a form, it changes. So, it doesn't matter how you build the statement, it is still just a string.

Another thing I would like to know: Can you tell me what part of the code is used to build the string from the listbox? I don't see that anywhere. Or, is that what you're missing, and want to know how to incorporate it into what you already have?