Link to home
Start Free TrialLog in
Avatar of Summit-IT
Summit-IT

asked on

Access Requery not working

When I add data to a table and requery the table the listbox does not refresh until I add the second entry eventhough the requery is in the code.

Private Sub add_vendor_Click()
Dim err As Integer
Dim cnn1 As ADODB.Connection
Dim rstvendor As ADODB.Recordset
Dim strCnn As String
   

'Check that all fields are filled in
addvendor.SetFocus
If addvendor.Text = "" Then
err = err + 1
MsgBox "The Add Vendor field is empty!" & err
End If

'if no errors insert data
If err < 1 Then
' Open a connection.
    Set cnn1 = New ADODB.Connection
   mydb = "C:\HPI\hpi_draw.mdb"
   
 strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
    cnn1.Open strCnn
 
' Open contact table.
    Set rstvendor = New ADODB.Recordset
   
    rstvendor.CursorType = adOpenKeyset
    rstvendor.LockType = adLockOptimistic
    rstvendor.Open "Vendors", cnn1, , , adCmdTable


'get the new record data
rstvendor.AddNew
        rstvendor!Vendor = addvendor.Text
        'MsgBox addvendor.Text
        rstvendor.Update
        If rstvendor.Status > 0 Then
            MsgBox "Error: " & rstvendor!Vendor & " was not added!"
            rstvendor.Close
            cnn1.Close

        Else
            MsgBox "New Vendor: " & rstvendor!Vendor & " has been successfully added"
            addvendor.Value = ""
            listvendor.Requery
            rstvendor.Close
            cnn1.Close

        End If
       
Else
MsgBox "An Error has occurred, please check and try again"
End If
 
End Sub
 

   
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image



            MsgBox "New Vendor: " & rstvendor!Vendor & " has been successfully added"
            addvendor.Value = ""

            listvendor.rowsource=listvendor.rowsource   'add this line

            listvendor.Requery
            rstvendor.Close
            cnn1.Close
Avatar of Summit-IT
Summit-IT

ASKER

I added the line:

listvendor.rowsource=listvendor.rowsource

but it is still sometimes not requering.
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I used your guess routine and it made the refresh worked.  I tied the same routine into the delete portion of the form and it doesnt work.  Instead it sometimes it seems to do nothing eventhough the record is deleted in the database and other times it says #Deleted.

Private Sub delete_vendor_Click()

Dim cnn1 As ADODB.Connection
Dim strCnn As String
Dim cmd As String

    Set cnn1 = New ADODB.Connection
   mydb = "C:\HPI\hpi_draw.mdb"
   
 strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mydb
    cnn1.Open strCnn
 
' Delete Vendor Row
MsgBox (listvendor.Value)
cmd = "delete from vendors where vendorid=" & listvendor.Value
cnn1.Execute (cmd)
DoEvents
listvendor.Requery
cnn1.Close
Summit-IT,

"I used your guess routine and it made the refresh worked."

Ok, so then at this point we need to close this question, because the original issue has been resolved.

The Delete issue should be posted as a new question, to give other experts a fair shot at answering it.

JeffCoachman