Populating a list box, filtered with values from the form

[inepu]

I have 3 tables, Actions, Objects and AO, where "AO" a table that relates Actions and Objects, each row is a pair the other tables' keys)

I have a form that is generated with values from Objects, each form page only shows info on one Object. What I would like to do is to add a list box that shows what Actions are available for this Object.

I would guess that the listbox would have to be populated by the query
"select Actions.* from Actions Inner join AO on Actions.key = AO.Akey inner join Objects on Objects.key = AO.BKey where Objects.Key = [filter]"

And the [filter] value would be the current form page's Object's key, but how can I get it?

How can I do this?

Thanks in advance

[NeoPa]

I'm assuming that AKey & BKey are both string fields. If they are numeric you need to remove the quotes (') and change line #6. If they are dates then you really should have said so in the question :D

...
  strSQL = "SELECT Actions.* " & _
           "FROM [Actions] INNER JOIN [AO] " & _
           "  ON Actions.Key=AO.AKey " & _
           "WHERE AO.BKey='%F'"
  strSQL = Replace(strSQL, "%F", Split(Me.Filter, "'")(1))
  lstActions.RowSource = strSQL

Let us know how you get on with this.

[inepu]

I want the listbox to be populated as I open the form, not with VBA from some event

When you right click the listbox in edit mode, and go to properties, there you can choose the row source to be a table or query you want, right? But I want this query to be filtered with one field from the form that is opened.

Assuming the form is populated by the query:
Select Objects.Key, Objects.Field1; Objects.Field2 from Objects

I want the list box to be populated by the query
select Actions.* from Actions Inner join AO on Actions.key = AO.Akey where AO.OKey = Me![Objects.Key]

If I was using VBA on some event to populate the list box that woud work, but in the listbox's rowsource properties that doesn't work, when I open the form I get a pop up asking me what the value of Me![Objects.Key] is

[NeoPa]

I want the listbox to be populated as I open the form, not with VBA from some event

I'll assume you were not trying to be rude or ungrateful here, but if that's what you wanted, why did you not say so in the first place and save me wasting my time finding a solution that matched the question you asked, but not what you wanted. We may offer our time voluntarily, but that's no reason to treat it as valueless.

If you want this to work in the narrow way specified, you will need to reference the control on the form explicitly. This involves using the name of the form as well as the name of the control (which would appear to be [Objects.Key]) and is not an internal reference (Me. won't work).

Unless you post the name of the form I am unable to show you how this should be done specifically, but in general it would be something like :

SELECT Actions.*
FROM [Actions] INNER JOIN [AO]
  ON Actions.Key=AO.AKey
WHERE AO.BKey=Forms!YourFormName.[Objects.Key]

[inepu]

If I seemed rude I'm sorry, not my intention. I also thought my first question was explicit... And I'm not providing my tables and forms' names because it's all in portuguese and I'd rather learn from a general example than a specific one, also wasting less time explaining the relationship between the 3 tables just because the names are confusing to people other than me.

But, I had already tried the way you've proposed now, and it doesn't work. It either returns "Invalid bracketing of the Forms!F_Actions .[Objects.Key] or if I don't use the square brackets it asks me what the value of "Forms!F_Action s.Objects.Key" is. I've tried several combinations of exclamations points and periods and brackets, none work.

And by "some VBA event" I meant I was looking for a way to have the list displayed as I open each form record, not by pressing a button (or something like that)

I know I can make a subform with textboxes and continuous results, giving me a similar visual effect of what I want (and access provides an easy way to connect a field from the form and the subform). But I need to use a listbox so that the results are multi-selectable

If you are thinking about possible problems in language syntax, Access translates automatically "Forms" to "Formulário s", so the problem is not there.

-----

As I was writing my post, I found out the answer to my problem, but decided to keep the previous text, just so you can see I didn't intended to be disrespectful to your work, but also that It was not only my fault we had an misunderstandin g.

The solution I found was in fact with VBA, and using an event I did not know about.

Private Sub Form_Current()
 strSQL = "select Actions.* <etc etc> where AO.BKey = " & Me![Objects.Key]
 myListBox.RowSource = strSQL
End Sub

That's it and it works, pretty much what you posted the first time, although I didn't understand what the Split was for and didn't know that a Form_Current() existed

sorry of all the trouble and thanks

[NeoPa]

Firstly let me say that I'm sure there was no intention to be rude. Sometimes it appears that way when people have to translate their ideas into English from another language (Portuguese is not the most similar language to English) so I'm glad I made the assumption that this was not your intention. Thank you for clarifying anyway.

Names :
Names can be confusing, as you say, but they are also helpful to someone answering a question as they save that person from having to invent their own, or keep explaining that what they've shown is in place of a name.
Consider :

Me.[asdfgh].Visible = True

...is easier to write AND read than :

Me.[Insert your control name here].Visible = True

...and where there's more than a single reference to an object it's more complicated still.

Please don't consider this as criticism, just as a view from the other side. Something simple to you can nevertheless be quite complicated from here, and leaving item names out is one of those things.

I appreciate that you're looking for a general answer, to learn, rather than a specific answer which only helps in this one case. If you have any difficulty getting the general lesson from a specific answer we are always happy to clarify.

Events in Access :
There are two which you may be interested in here :

Form_Open(Cancel As Integer)
Form_Current()

The first triggers every time the form is opened. The second triggers every time a new record becomes the current one. I will leave you to determine which of these is more correct for your needs. It seems to me that maybe the Open is more correct, but I don't know your project as well as you do so you decide which is the better one to use.

Split() Function :
This is a neat little function (paired with the Join() function) which processes through a string and separates out each element into an array based on the separator character supplied.
Consider the string "[MyStringField] = 'Blob'".

Split("[MyStringField] = 'Blob'")

...returns an array with three string elements in it numbered from 0.
0 = "[MyStringField] = "
1 = "Blob"
2 = ""
We are interested in the string in between the quotes (') which is "Blob".

Split("[MyStringField] = 'Blob'")(1) == "Blob"

And lastly, thank you for replying and letting us know that you've found an answer that you're happy with.

Welcome to TheScripts :)