I have a main form that inserts data into a table. There's then a subform that should be re-populated based on the new info just inserted into the table from the main form. To avoid a "XXXX has put the database into a locked state so you can't modify the records" error, I 've split my database up between client and server (Which was very painful for newbies to do). So I linked my client to the server tables, but I don't build out a working table of some sort with the new data, because it seems senseless. Now, the database works fine, but I can't seem to get the subform to update whenever I save data using the main form. The only way the subform updates is by closing the form and opening it again or changing a field on the main form that the sub form is bound to with the parent child link, and then changing the field back to what it was. Here's the code I used, with a tremendous amount of stuff I found on the web to try to coax the form to requery:
Code:
Option Compare Database
Private Sub cardnum_AfterUpdate()
Me.Filter = "[Card Number] = """ & cardnum & """"
Me.FilterOn = True
End Sub
Private Sub plu_AfterUpdate()
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim RA As Long
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
With cmd
.CommandText = "Select * from MenuItem where PLU='" & plu & "'"
.CommandType = adCmdUnknown
.ActiveConnection = CurrentProject.Connection
Set rs = .Execute
End With
rs.MoveFirst
nme = rs!Description
price = rs!price * 0.85
rs.Close
Set rs = Nothing
Set cmd = Nothing
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\Documents and Settings\Administrator\My Documents\scrappink.mdb;" & _
"Mode=ReadWrite;" & _
"Persist Security Info=False"
cn.Open
With rs
.Open "CustomerItem", cn, adOpenDynamic, adLockOptimistic, adCmdTable
.AddNew
!Item = plu
![Item Name] = nme
!Customer = cardnum
!price = price
!quantity = qty
!Tax = price * qty * 0.0925
![Total Amount] = price * qty * 1.0925
.Update
End With
cn.Close
Set cn = Nothing
DBEngine.Idle dbRefreshCache
DoEvents
DoEvents
Me![CustomerItem subform].Requery
Me.Requery
DoCmd.RunCommand acCmdRefresh
qty = 1
plu = ""
plu.SetFocus
End Sub
Private Sub plu_BeforeUpdate(Cancel As Integer)
Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset
Dim strSQL As String
Dim RA As Long
Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
With cmd
.CommandText = "Select * from MenuItem where PLU='" & plu & "'"
.CommandType = adCmdUnknown
.ActiveConnection = CurrentProject.Connection
Set rs = .Execute
End With
If rs.EOF Then
MsgBox "This item doesn't exits"
Cancel = True
End If
rs.Close
Set rs = Nothing
Set cmd = Nothing
End Sub
