Listbox not updating - requery not working

[downhilljon]

Hi,

I have a form with two List boxes, each linked to a table. The purpose of the form is to allow users to add new data into the tables the list boxes are reading from. To do this, there is a text box and button for each list box, to add new data.

The data from the text box adds to the table fine (through ADO and recordset methods), and at the end of my 'on click' event code is a me.listbox.requery line to update the new entry immediately into the listbox.

Most of the time, it doesn't update (it's not consistent).The table updates, but after clicking the button, the list box remains the same. Oddly enough, if I then click the button to add an entry to the other listbox, the first listbox then updates... weird.

Not sure if I should be moving my Me.listbox.requery line to another event are (after update???) to get the update happening at the required time?

Thanks for any help,
Jon

 

[Dom DXecutioner]

Hi,

If i may advise, it would be of great help to the members if you could post the code that you're using in your procedure. Having members trying to speculate what the problem to a code that they don't have a clue what it contains and in what order, may limit the timeliness in which your answer gets responded to.

Having said that...

Oddly enough, if I then click the button to add an entry to the other listbox, the first listbox then updates... weird.

Have you verified that the onclick event requeries the correct listbox?

 

[downhilljon]

Excellent point about posting the code Dom, thanks for pointing it out. See below:

This first piece of code refers to the first list box (brands).
Have also noticed another problem with this particular listbox since original post - after clicking the button to initiate the code, all seems well, until you try to click either button again, when the following error message pops up: "The database has been placed in a state by user "Admin" on machine "SPANKER" which prevents it from being opened or locked". This indicates to me that the code is somehow not finished with the recordset perhaps???

Dim rst As New ADODB.Recordset, cn As String

If Not IsNull(Me!txtBrand) Then
    cn = CurrentProject.Connection
    rst.Open "Brands", cn, adOpenDynamic, adLockOptimistic
    With rst
        .AddNew
        !Brand = Me.txtBrand
        .Update
        .Close
    End With
    Me.lstBrands.Requery
    Set rst = Nothing
End If

The second piece of code is pretty much the same, just refers to different listboxes, buttons etc.

Also, I have double checked that the requery is looking at the right list box.

Thanks again for any help!
Jon

 

[Moniker]

Try this change:

Dim rst As New ADODB.Recordset, cn As String

If Not IsNull(Me!txtBrand) Then
    cn = CurrentProject.Connection
    rst.Open "Brands", cn, adOpenDynamic, adLockOptimistic
    With rst
        .AddNew
        [B][COLOR="Red"].Fields("Brand")[/COLOR][/B] = Me.txtBrand
        .Update
        .Close
    End With
    Me.lstBrands.Requery
    Set rst = Nothing
End If

Honestly, that's a lot of code to just add one field. A simple append will do it:

CurrentDb.Execute "INSERT INTO Brands ( Brand ) SELECT Me.txtBrand AS Brand;"

 

[pbaldy]

I suspect you're running into a problem I've heard about with ADO, referred to as a "lazy write". In essence, a lag between the insert and it being available for the requery. I'd use Moniker's idea, but with the correct syntax.

 

[Moniker]

Ya, ya... no one's perfect.

(Corrected, btw.)

 

[downhilljon]

OK, Thanks for the info. I am assuming the very simple one line of code you replaced mine with is basically an append query?

I tried using it instead, but get the error "Too few parameters. Expected 1."

Any ideas?

I also tried the .Fields change for my original code - same problem, and still getting the following message:

"The database has been placed in a state by user "Admin" on machine "SPANKER" which prevents it from being opened or locked".

 

[Moniker]

OMG, I'm stupid this late. This is really corrected now. Sorry.

CurrentDb.Execute "INSERT INTO Brands ( Brand ) SELECT '" & Me.txtBrand & "' AS Brand;"

 

[downhilljon]

Perfect! That did the trick. Thanks a lot!

 

[Moniker]

NP. Sorry about the initial confusion. One of those days/nights/weeks/months.

Glad it worked.

 

[downhilljon]

OK, next problem!

As I originally mentioned, I have two of these list boxes on the form. First one is now sorted. The second one is essentially the same idea, but updates 2 fields in it's table (Brand and Model). The model info comes from a text box, while the brand info comes from the currently selected brand in the list box. The Brand field in the table is a lookup back to the Brands table, linked through the primary key (autonumber).

So basically, when I click the button 'Add model', I need it to add the new record, which is a brand and a model.

I have built on the SQL statement you first provided me with moniker, but can't seem to get it right:

strSQL = "INSERT INTO Models ( Brand, Model )" & _
            "SELECT '" & Me.lstBrands & "' AS Brand AND '" & _
            Me.txtModel & "' AS Model;"
        
CurrentDb.Execute strSQL
Me.lstModels.Requery

The problem that occurs is an error message saying "Number of query values and destination fields are not the same"

I assume this is telling me that my query is trying to add more (or less) fields than are available in the table, but can't pinpoint the error in the code (not real good at this SQL stuff yet!)

Another point: I wouldl like to avoid duplicates being entered through this form. Can someone point me in the right direction for how to run a quick search for duplicates once the button is clicked also?

Thanks!
Jon

 

[pbaldy]

You would not use "AND" between fields, you would use a comma. And watch out for spacing between lines.

The easiest way to avoid duplicates is to prevent them at the table level. If you want, you could use DCount or open a recordset to test whether the values on the form already exist.

 

[downhilljon]

Done and done!
Thanks a lot guys!