Refreshing a form with VBA (1 Viewer)

Winterwolf73

New member
Local time
Today, 05:34
Joined
Jul 3, 2020
Messages
26
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
 

theDBguy

I’m here to help
Staff member
Local time
Today, 03:34
Joined
Oct 29, 2018
Messages
21,357
Is the main form bound? Is the subform linked the main form?
 

Winterwolf73

New member
Local time
Today, 05:34
Joined
Jul 3, 2020
Messages
26
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.
 

Attachments

  • Annotation 2020-07-14 053415.png
    Annotation 2020-07-14 053415.png
    13.1 KB · Views: 120

bob fitz

AWF VIP
Local time
Today, 10:34
Joined
May 23, 2011
Messages
4,717
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

AWF VIP
Local time
Today, 10:34
Joined
May 23, 2011
Messages
4,717
Winterwolf73

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

Attachments

  • Customers.accdb
    504 KB · Views: 141

Winterwolf73

New member
Local time
Today, 05:34
Joined
Jul 3, 2020
Messages
26
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

AWF VIP
Local time
Today, 06:34
Joined
Jan 11, 2013
Messages
3,849
In the afterupdate of the combobox try
Code:
    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

AWF VIP
Local time
Today, 10:34
Joined
Jul 26, 2013
Messages
10,353
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

New member
Local time
Today, 05:34
Joined
Jul 3, 2020
Messages
26
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

AWF VIP
Local time
Today, 10:34
Joined
Jul 26, 2013
Messages
10,353
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

AWF VIP
Local time
Today, 06:34
Joined
Jan 11, 2013
Messages
3,849
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

AWF VIP
Local time
Today, 06:34
Joined
Jan 11, 2013
Messages
3,849
you could also use a filter along the lines of
Code:
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

New member
Local time
Today, 05:34
Joined
Jul 3, 2020
Messages
26
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

AWF VIP
Local time
Today, 10:34
Joined
May 23, 2011
Messages
4,717
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

AWF VIP
Local time
Today, 06:34
Joined
Jan 11, 2013
Messages
3,849
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

New member
Local time
Today, 05:34
Joined
Jul 3, 2020
Messages
26
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

AWF VIP
Local time
Today, 10:34
Joined
May 23, 2011
Messages
4,717
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

AWF VIP
Local time
Today, 10:34
Joined
May 23, 2011
Messages
4,717
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.
Code:
    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
 

Users who are viewing this thread

Top Bottom