Listbox query

[brsawvel]

Hello, I have been having a little bit of a problem figuring this one out. using 2007.

I'm trying to add a listbox to my form that only displays records from a table where the 1st field has the same value as the record currently displayed on the form.

For example, if the record I am looking at on the form has a fldA value of "Horse", then I want the listbox to return all records from tblB where fldA has a value of "Horse".

I tried adding this code to both the Before Update and Row Source, but it didn't work for me....

SELECT tblB.fldB FROM tblB WHERE tblB.fldA = '" & fldA.Value & "'

 

[MSAccessRookie]

Hello, I have been having a little bit of a problem figuring this one out. using 2007.

I'm trying to add a listbox to my form that only displays records from a table where the 1st field has the same value as the record currently displayed on the form.

For example, if the record I am looking at on the form has a fldA value of "Horse", then I want the listbox to return all records from tblB where fldA has a value of "Horse".

I tried adding this code to both the Before Update and Row Source, but it didn't work for me....

SELECT tblB.fldB FROM tblB WHERE tblB.fldA = '" & fldA.Value & "'

I think there is a syntax error here (missing the closing "), although it could just be a typeo in the post. I am also not sure that this select statement will do what you want it to even when it is correct.

SELECT tblB.fldB FROM tblB WHERE tblB.fldA = '" & fldA.Value & "'[SIZE=4][COLOR=red][B]"[/B][/COLOR][/SIZE]

 

[brsawvel]

Yeah, I tried that and it didn't work either. Is there a way to cause the listbox to return all records from tblB where fldA equals the value displayed on the form in fldA?

 

[MSAccessRookie]

Yeah, I tried that and it didn't work either. Is there a way to cause the listbox to return all records from tblB where fldA equals the value displayed on the form in fldA?

Please Clarify a point for me regarding your proposed code.

[B]SELECT [COLOR=darkgreen]tblB[/COLOR].[COLOR=darkorange]fldB[/COLOR] FROM [COLOR=darkgreen]tblB[/COLOR] WHERE [COLOR=darkgreen]tblB[/COLOR].[COLOR=darkorange]fldA[/COLOR] = '" & fldA.Value & "'"[/B]

There is a Table called tblB that has two columns (fldA and fldB) and you are trying to SELECT rows that have the value of the Column fldA equal to the current contents of a Control Field that is on your currently opened form, and is ALSO named fldA? This is how I am interpreting the code.

 

[brsawvel]

Yeah, I'm pretty sure we're on the same page...

I like that you use different colors, let me try that and see if we are...

frmA has a control textbox called fldA. frmA displays records from tblA which has different fields, the first being fldA.

In frmA is a listbox that displays records from tblB. tblB has 2 fields, fldA and fldB.

I am trying to get the listbox to display only records from tblB where fldA matches fldA in frmA.

 

[MSAccessRookie]

Yeah, I'm pretty sure we're on the same page...

I like that you use different colors, let me try that and see if we are...

frmA has a control textbox called fldA. frmA displays records from tblA which has different fields, the first being fldA.

In frmA is a listbox that displays records from tblB. tblB has 2 fields, fldA and fldB.

I am trying to get the listbox to display only records from tblB where fldA matches fldA in frmA.

It sounds like changing the RecordSource for the ListBox might be the way to go. Check the properties of the ListBox and verify the current values. If the RecordSource is a table, then you could change it to be a query into the table that has the appropriate WHERE clause to include only the rows that you want to see.

In the On Change event for the control textbox called fldA, you can add VB Code that sets the RecordSource for the ListBox and updates it to contain the revised values.

Let me know if this helps

 

[brsawvel]

I'm not real good with writing code...How would I write VB to set the record source for the listbox?

The Query I wrote for the listbox is...

SELECT [COLOR=Red]tblB[/COLOR].[COLOR=Orange]fldB[/COLOR]
FROM [COLOR=Red]tblB[/COLOR]
WHERE [COLOR=Red]tblB[/COLOR].[COLOR=Blue]fldA[/COLOR]='" & [COLOR=Purple]fldA[/COLOR].Value & "'
ORDER BY [COLOR=Red]tblB[/COLOR].[COLOR=Orange]fldB[/COLOR];

 

[mikevds]

I am trying to do the samething with a hymnal and cd db also a books db
Can someone help me?

I have all the data tables however when I populate the forms it don't cascade.

I am attatching a sample file

 

[jal]

- Basic code for loading a table into a listbox
listboxGrid.ColumnCount = 10
listBoxGrid.ColumnHeads = true ' shows the column names.
listboxGrid.RowSourceType = "Table/Query" 'default, optional line of code
listboxGrid.RowSource = "SELECT * FROM Customers"
listboxGrid.Requery
If using a Query object, don't use the Query prefix
listboxGrid.RowSource = "Query.qryFillGrid" 'wrong
Instead use this:
listboxGrid.RowSource = "qryFillGrid" 'no prefix.
- if you want to clear the grid
listboxGrid.RowSource = ""
listboxGrid.Requery
- SET THE COLUMN Widths - Here we do it for four columns, using inches.
listboxGrid.ColumnWidths = " 0.5in; 1.35in; 0.55in; 0.35in "

 

[Dougalsworld]

I do something similar where I have a combobow with types of training sessions (cboCourseList) and when I select this I want a second combobox (cboCourseDate) to only show the dates from tbl_Sessions where the selected course is available. I would have thought it would work the same way for a listbox

SELECT tbl_Sessions.CourseDate, tbl_Sessions.ReqSent FROM tbl_Sessions WHERE ((tbl_Sessions.CourseCode)=Forms!frm_Bookcourse_New2!cboCourseList));