Subform not updating

[chad101]

I have a customer subform on page 2 of my inspection log. A combo box at the top allows the user to lookup customers based on name. My problem is after I enter a new customer and part number into the database it shows up in my combo box but I cannot get my two underlying subforms to pull up any information on it unless I close out the form and re-open it. here is a copy of my database and the code I am using for my combo box after update event. You would think the “requery” action fro the subforms would allow me to pull new information with out closing and reopening the form.

Private Sub Combo4_AfterUpdate()
Me.QuerytblSpecTable_subform.Requery
Me.QuerytblPartNumbers_subform2.Requery
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[customer] = '" & Me![Combo4] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub

Sample included

 

[chad101]

Modules & VBA

Note to admin/moderator: Could you move this thread to Modules & VBA?

Thanks, Chad

 

[clerics]

Form add new Customer of your Program only insert it into tblPartNumbers ! But Form_show ( there re 2 form, right ?) the left one can't show it's data be select from tblSpect by query QuerytblSpecTable . The right one can show it cuz it's data be selected from tblPartNumbers by query QuerytblPartNumbers !
Now may be you can correct it by yourself
(there re alot of way to do it! for example, you also add new customer into tblSpect ... or anyway ) Good luck

 

[chad101]

Thank you

Thank you I work 3rd shift and i just got home ...very tired lol.

I will try to fix this error when i wake up and I'll post my results

Once again, thank you for the feed back

Chad

 

[chad101]

I could not get it to work. When you enter a new customer name it won't show up in the subform. BUT, if you close the form and re-open it the new customer name displays in *both* subforms. I tried the requery & refreash options as well with no luck.

So i came up w/ a generic solution. Since new customers are entered through through a different form. I coded the main form to close when the "new customer" is opened and then once the user closes the "new customer" form it automaticly re-opens the main form and takes the user back to the customer info tab.

"Add Customer" BUTTON
on click......

Private Sub AddNewCustomer_Click()
On Error GoTo Err_AddNewCustomer_Click
'close log form so upon re-opening subform will re-refreash
DoCmd.Close
'open customer entry from
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "QuerytblCustomer"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_AddNewCustomer_Click:
Exit Sub

Err_AddNewCustomer_Click:
MsgBox Err.Description
Resume Exit_AddNewCustomer_Click
End Sub

Customer Entry Form
on close.....

Private Sub Form_Close()
're-open customer form
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "LOGBETA"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms!LOGBETA.TabCtl113.Pages(1).SetFocus


End Sub

works for me

Thank you for your time clerics, I really appreciate your input

 

[clerics]

if you only want to correct that fault --->

Private Sub Combo4_AfterUpdate()
Me.QuerytblSpecTable_subform.Requery
Me.QuerytblPartNumbers_subform2.Requery
' Find the record that matches the control.

Me.Requery ' <--- add this code here
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[customer] = '" & Me![Combo4] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
End Sub
Err_AddNewCustomer_Click:
MsgBox Err.Description
Resume Exit_AddNewCustomer_Click
End Sub