Interesting form problem

[Winterwolf73]

I have a form that pulls various data from my Customer Table. This form has three tab pages.

Page 1 - Customer Info: is simply a search option to see if a person has any entries. There is an unbound text field that I can enter the persons last name and it will pull all of the entries with said last name. This is working correctly. Though I am going to have to tweek it in the near future to include the customers first name in the query.

Page 2 - New Customer: this one is self explanatory.

Page 3 - Update Customer: This page has a search option by account number. This is also where one of my issues lay. I am using the persons account number for the search query. I want each of the editable field from the table to show up as separate controls inside this page. The query works fine. it is the displaying of the information I am having an issue with.
I have tried to drop the query directly on to the page. I was not happy with this, so i decided to create a form ( with the query as the record set). This i dropped in the main form as a sub-form. I like this. However, here is my first problem. When I enter the customer account number and click search the results show up as separate result "table". The sub-form does not display the results. Which leads me to problems 2 & 3. To see if the data has actually been populated in the sub-form, I hit refresh. The same pop-up windows show up. The first is for the Last Name and the second is for the Account Number. This would not be an issue, but it happens multiple times for each. The Account Number window also pops up when I click the search button.

Here is the code VBA code for the search function.

Code Tags Added by UG
Please use Code Tags when posting VBA Code
Please read this for further information:-
https://www.access-programmers.co.u...e-use-code-tags-when-posting-vba-code.240420/
Please feel free to Remove this Comment

Private Sub Find_Account_Click()

    'User must enter a value in the text box
    If IsNull(Text184) = True Then
        MsgBox "You must enter search criteria."
      
    'Open query using search criteria entered
    Else
        DoCmd.OpenQuery "Query - Update Customer", acViewNormal
    End If
  
End Sub

I have attached a snippet of the form for reference.

 

[theDBguy]

Hi. OpenQuery is not typically the approach we would recommend. Instead, use a bound form and display it.

 

[Winterwolf73]

I had thought about that. But, when it is bound access automatically pulls the first record. I do not want it accidentally overwrite that record. I haven't figured out how to open the report with blank bound fields.

 

[theDBguy]

I had thought about that. But, when it is bound access automatically pulls the first record. I do not want it accidentally overwrite that record. I haven't figured out how to open the report with blank bound fields.

There are several ways to handle that.
1. You can set the Data Entry property to Yes
2. You can navigate to a new record as soon as the form opens (using code)
3. You can apply a filter that results in no records
4. You can use a Record Source that results in no records
etc.

 

[Winterwolf73]

I may be over thinking this just a bit. I am multitasking a little this morning. But, lets say I use the bound from. How would I get the search option to pull that record without the query?

 

[theDBguy]

I may be over thinking this just a bit. I am multitasking a little this morning. But, lets say I use the bound from. How would I get the search option to pull that record without the query?

Because the form is bound, the information you seek is already available to the form, so you don't need a separate query. Just as a test, create a bound form and then add an unbound Combobox using the Wizard and select the third option.

 

[Winterwolf73]

Oh good lord. Like I sad COMPLETELY overthinking this one.

 

[theDBguy]

Oh good lord. Like I sad COMPLETELY overthinking this one.

Hi. Good luck. Let us know how it goes.