Refreshing a form with VBA

[Winterwolf73]

I have a form with a sub form. On the main form is an unbound text box where the user can input an account number. When the user clicks the "Find Account" button the following should occur.
1. Runs a query to search the customer table for the result.
2. Display the results on a bound subform.

Here is what is actually happening.
1. The query will run, but it opens the results in datasheet view. How can I keep this from happening?
2. After the query run, I have to go back and manually refresh the form for the results to show. I have added the following code to the On Current property of my main form. However, it is still not automatically refreshing the form to display the result in the subform.


Private Sub Form_Current()
[Update Customer].Requery
End Sub

 

[moke123]

When the user clicks the "Find Account"

What code runs when this occurs?

Why not apply a filter?

 

[theDBguy]

Is the main form bound? Is the subform linked the main form?

 

[Winterwolf73]

Moke, this is the coding that runs when the user clicks "Find Account".

Private Sub Find_Account_Click()

'User must enter a value in the text box
If IsNull(Text39) = True Then
MsgBox "You must enter search criteria."

'Open query using search criteria entered
Else
DoCmd.OpenQuery "Update Customer Query", acViewNormal
End If

End Sub

I have a Like statement in the query so that I only get the record with the correct account number.

theDBguy,
I tried to link the subform to the main form. However, because the main form is not bound but the child form is Access will not let me link the two. So I have to find a different way.

This shouldn't make a difference, but the subform I am working on is on the third page of a Control Tab. For reference I have added a snippet of the form.

 

[bob fitz]

I have a form with a sub form. On the main form is an unbound text box where the user can input an account number. When the user clicks the "Find Account" button the following should occur.
1. Runs a query to search the customer table for the result.
2. Display the results on a bound subform.

Here is what is actually happening.
1. The query will run, but it opens the results in datasheet view. How can I keep this from happening?
2. After the query run, I have to go back and manually refresh the form for the results to show. I have added the following code to the On Current property of my main form. However, it is still not automatically refreshing the form to display the result in the subform.


Private Sub Form_Current()
[Update Customer].Requery
End Sub

Why don't you just have one form bound to your Customer Details table. Have it move to a new record when first opened.
Have a combo box in the header section in which a user would type the account number. Once selected, code would find the record for viewing/editing. If you use the wizard to create the combo box, Access will even write the code for you.
IMHO this is the most common way of achieving your aim.

 

[bob fitz]

Winterwolf73

Attached is a simple version of the suggestion I made in my last post.

 

[Winterwolf73]

Wow do I feel like an idiot now. I fixed issue number 2 with a simple Me.Refresh call.

Now to my remaining issue. How would I go about fixing issue one. When I click the Find Account button and the query runs, it opens in datasheet mode. How would I keep that from happening?

 

[moke123]

In the afterupdate of the combobox try

    Dim rs As Dao.Recordset
    Set rs = Me.Recordset.Clone

    With rs
        .FindFirst "CusID = " & Me.cmbFind
        If Not .NoMatch Then
            Me.Bookmark = .Bookmark
        End If
    End With

edit:
OOPs ! I didn't realize that was Bobs example. Now you have 2 procedures to choose from.

 

[Minty]

Your query is a select query - why are you opening it? I assume it's the recordsource for the form. No need to open it.

 

[Winterwolf73]

Moke is this to keep the query from opening in data sheet view?

 

[Winterwolf73]

minty. that is what I am trying to keep from happening. lol

The only thing this query does is search the Customer Table for the account number and feeds that to the bound subform on the page.

 

[Minty]

How is it "Feeding it"? Is the query the forms record source?

I think as others have mentioned you are making this much harder than it needs to be.

 

[moke123]

Moke is this to keep the query from opening in data sheet view?

You dont need a query. You just need to find the first matching record.
If you look at Bobs example you can either use his code in the afterupdate or my code in place of Bobs. I assume you'll have to substitute your actual control and field names in them.

 

[moke123]

you could also use a filter along the lines of

Dim strFilter As String
strFilter = "CusID = " & Me.cmbFind
Me.Filter = strFilter
Me.FilterOn = True

for a subform you need to refernce the subform .
Me.YourSubformName.filter = ....

 

[Winterwolf73]

here is a completely newbie side question for you. I have noticed that all of the code i am working with is all in the same window in VBA. Is this ok or should the code for each command be in a separate window?

 

[bob fitz]

here is a completely newbie side question for you. I have noticed that all of the code i am working with is all in the same window in VBA. Is this ok or should the code for each command be in a separate window?

Yes. That's as it should be because that code module is for all the code pertaining to events of that form and the controls (text boxes, buttons etc) it contains.

 

[moke123]

By "window" I think you mean Module. There are 2 basic types of modules - Standard Modules and Class Modules. Class modules include Form Modules and Custom Class Modules.
Most likely you are working in a form module.
Google those terms for more info on use, scope,etc.

 

[Winterwolf73]

Ok that makes sense.

Bob,
I checked out the example you provided. You are right. I think I am making this harder than usual. I think this stems from my inexperience and ignorance with the inner workings of access.
However, I have added the code you provided and it will not display in my form. With that being said, how would i modify the code if the form is a subform?

 

[bob fitz]

However, I have added the code you provided and it will not display in my form.

It will not display what in your form.

Show us your code or post a copy of the db.

 

[bob fitz]

In the example db that I posted create a new form called Form1

Use frmCus as a subform on Form1

Create a combo box with the same properties (Row Source, Column Count and Column Widths) as the one on frmCus which is called cmbFind.
Use the following code in its' After Update event.

    Dim rs As Dao.Recordset
    Set rs = Me.frmCus.Form.Recordset.Clone

    With rs
        .FindFirst "CusID = " & Me.ActiveControl
        If Not .NoMatch Then
            Me.frmCus.Form.Bookmark = .Bookmark
        End If
    End With

You would only need to do this if the sub form is being displayed as a datasheet. When displayed as a "single" or "continuous" form the original combo box called cmbFind would still be visible and useable