List Box Not Updating

[LarryB]

Good afternoon all,

I have added a listbox to one of my "edit" forms and I have set the rowsource in the form_current code section to retrieve data from a table based on the value in a form field

List174.RowSource = "SELECT tblCodesAddEdit.Auto, tblCodesAddEdit.[IOE Code], tblCodesAddEdit.[Uptix Code], tblCodesAddEdit.MMDBID FROM tblCodesAddEdit WHERE (((tblCodesAddEdit.MMDBID)=[forms]![frmAddEdit]![mmdbid]))"

This works fine if I open the form directly and I can see all of the record data including the list box

My problem with it is one of the processes in the application is to call the edit form from the main form after a series of append queries to varoius tables are run and the edit form is opened. On opening of the edit form another query is run to go directly to the record that is required to edit.

All fields update fine, but the list box does not. I have created a button and tried various coding me.refresh, setting the listbox.rowsource again, yet the listbox does not update.

If I close the edit form and open it directly, the listbox is populated with the correct data.

I have attached the code behind the edit button on the main form which calls all queries. I have also included the SQL of the Query that finds the recordset on the edit form.

If you can help it would be much appreciated.

Many thanks

Larry

 

[jzwp22]

The form control referenced in your SQL statement is a variable and thus cannot be enclosed in the double quotes

List174.RowSource = "SELECT tblCodesAddEdit.Auto, tblCodesAddEdit.[IOE Code], tblCodesAddEdit.[Uptix Code], tblCodesAddEdit.MMDBID FROM tblCodesAddEdit WHERE (((tblCodesAddEdit.MMDBID)=[forms]![frmAddEdit]![mmdbid]))"

It should be this:

List174.RowSource = "SELECT tblCodesAddEdit.Auto, tblCodesAddEdit.[IOE Code], tblCodesAddEdit.[Uptix Code], tblCodesAddEdit.MMDBID FROM tblCodesAddEdit WHERE (((tblCodesAddEdit.MMDBID)=" & [forms]![frmAddEdit]![mmdbid] & "))"

Also, if the listbox and the mmdbid control are on the same form, you can use the me. shorthand notation & you do not need the paraentheses (in this case). So the simplified SQL text would be as follows:

List174.RowSource = "SELECT tblCodesAddEdit.Auto, tblCodesAddEdit.[IOE Code], tblCodesAddEdit.[Uptix Code], tblCodesAddEdit.MMDBID FROM tblCodesAddEdit WHERE tblCodesAddEdit.MMDBID=" & me.mmdbid

 

[LarryB]

Many thanks for your reply JZ

Unfortunately the code amendments is forcing an input box "input parameter value" when the form opens and I have to enter in the ID. Once entered the listbox populates.

I removed the code from the form current code section and placed it behind a command button.

With the form opened and all fields populated, including MMDBID, on click of the button it still looks for manual input rather than using what is already in the MMDBID field

with the original coding in place for the listbox rowsource on opening the form directly it pulled in the data.

 

[LarryB]

I'm not sure if my table relationship has any bearing on this but I though I should let you know

I have 4 tables - 3 in a one-to-one relationship - realted by primary ID's MMDBID (tblFundAddEdit, tblContactAddEdit, tblWiresAddEdit)

The 4th table is tblcodes and is in a one-to-many relationship with tblFundAddEdit, with the codes table on the Many side

The Listbox is pulling date from the codes table

 

[jzwp22]

Is tblCodesAddEdit.MMDBID a numeric datatype?

What is the bound field of the list box?

You can push the SQL text to a variable and display it in the VBA immediate window to see what is actually being used as the row source for the list box. If needed, you could copy and paste the SQL text from the Immediate window to a new query to test it.

Dim mySQL as string

mySQL= "SELECT tblCodesAddEdit.Auto, tblCodesAddEdit.[IOE Code], tblCodesAddEdit.[Uptix Code], tblCodesAddEdit.MMDBID FROM tblCodesAddEdit WHERE tblCodesAddEdit.MMDBID=" & me.mmdbid

debug.print mySQL 'this prints to the Immediate window

List174.RowSource =mySQL

 

[LarryB]

Apologies JZ, I've made a very amatuerish mistake

Your debug code printed exactly what I have coded for the listbox rowsource, so it was right but the variable name for MMDBID was incorrect and I have just remembered that I changed this from MMDBID in an earlier version of the app.

I spotted this whilst using the Query Builder on the Listbox rowsource

I think I have said this on here before, but it is deserved, another slap in the chops for me!

Thanks again JZ and apologies for wasting your time

 

[jzwp22]

Don't kick yourself! We have all made those types of mistakes along the way. I'm glad you got it worked out.