Solved

VBA: Access ComboBox / SubForm/ Input Query

Posted on 2010-11-26
9
3,673 Views
Last Modified: 2012-05-10
Hi,

           I need help in making a combo box as the input parameter for a query in a sub form. Please excuse my wording as I'm still trying to learn access.

I created a combo box in a form that shows all the "Bank_LegalName" from tblBanks. It's a simple select query.

I created another query named qryBankName_Input with the following sql:

SELECT tblBankContacts.Bank_Contacts
FROM tblBankContacts
WHERE tblBankContacts.Bank_LegalName=Forms!frmBankName.Combo0;

As you can tell now, my form name is frmBankName. In this form, under the combo box with the 1st query above, I created a subform/subreport with the control wizard on using the 2nd query, qryBankName_Input, as the recordsource

I then built an event vba code (which I think I used the wrong command)

Private Sub Combo0_Change()
DoCmd.OpenQuery ("qryBankName_Input")
End Sub

The problem is when I change the combo box, a new window pops up showing me the query results (not what I had in mind). If I change the bankname from the combobox, close the form, reopen it, the results would be sitting in there as intended.

Can anyone assist in helping me understand how can I have the query that's embedded as a subform in the main form to be "bound?" to the combo box? I want on each change of the combo box, show the result BankContacts in the subform on the fly.

thanks expertexchange!

 
0
Comment
Question by:Shino_skay
  • 4
  • 4
9 Comments
 
LVL 39

Assisted Solution

by:als315
als315 earned 200 total points
Comment Utility
You need only requery-refresh your form:
Private Sub Combo0_AfterUpdate()
Me.Requery
Me.Refresh
End Sub
0
 
LVL 24

Accepted Solution

by:
Bitsqueezer earned 300 total points
Comment Utility
Hi,

if your subform has a recordsource which is set to your query then using Requery is correct, but not Refresh (and in no case both of them). Requery starts the complete query again showing all current records, Refresh only updates the records in the current form (not showing deleted or inserted records of another user). So normally in the most cases Requery is the right method to use.

As the combobox is part of your main form you need to requery the subform only. If your subform container has for example the name "SubformBankContacts" then you would use this:

Me.SubformBankContacts.Form.Requery

Open in new window


This only requeries the subform, "Me.Requery" requeries the complete form, the main form and the subform and all comboboxes inside which is not necessary.

Cheers,

Christian
0
 

Author Comment

by:Shino_skay
Comment Utility
Hi, I combined both answers and used

Me.[qrybankname_input subform].Form.Requery

It works and thanks for the little subtle difference with refresh and how it doesn't show deleted or inserted records by another users.

Quick question here. The combo box in design mode says it's Unbound. I'm not sure how to bound it as mentioned by Bitsqueeze.

I looked at the properties for the subform that was created by the wizard. It says the record source is

SELECT qryBankName_Input.Bank_Contacts FROM qryBankName_Input;

I changed it to

SELECT tblBankContacts.Bank_Contacts
FROM tblBankContacts
WHERE tblBankContacts.Bank_LegalName=Forms!frmBankName.Combo0;

I would think that would make the subform query bound to the combo box?

Either ways, that's a minor issue. you guys are great. Took me forever and I was getting nowhere figuring this out. Thanks with much appreciation.

0
 

Author Closing Comment

by:Shino_skay
Comment Utility
Quick solution and extra background information thrown in as well.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

the question would be, why do you use a combobox in an mainform and use a subform to display the data if the mainform and combobox are unbound?

You could also insert a header in a form and insert the unbound combobox there and then display the records in the detail area of the form (the only advantage of using a subform is that it doesn't flicker during scrolling). A subform is most useful when you have a bound mainform and a subform with corresponding detail records so the subform can be linked to the records of the mainform - in this case you wouldn't need a WHERE in the subform's SELECT command.

"Bound" and "unbound" means only that the ControlSource of a control is bound to a table field (or in case of forms, the RecordSource is bound to a table/view). Using a WHERE in the subform which uses the value of the combobox is no binding in the normal sense of binding a form or control. You only use a parameter in a SELECT which uses a value from anywhere else. If you would change your combobox into a textbox and enter the value manually you would not find any difference in execution so you see there is no binding (not more than the WHERE clause need the name of the control to find the value). If you would change the combobox into a label with a hard coded value it would be the same.

The SELECT will only be executed once, at the time the form loads. Changing the value of the combobox doesn't affect the SELECT, this is why you need the manual Requery command which will run when the AfterUpdate event of the combobox is fired.
Only if you would use the Link fields of a subform container Access changes the contents of the subform automatically.

Cheers,

Christian
0
 

Author Comment

by:Shino_skay
Comment Utility
i just read what you wrote christian and while its' english, it not sinking in.

Ideally. I want to create a combobox that shows all the bank names and if you scroll through the combobox, it would display the contacts email address for each bank and then have a  add command button which updates the tables with any new email address to each respective bank.

I think I did this incorrect. I have 2 tables

1) tblBanks which as 4 fields
Bank_LegalName as the PK
Bank_ID
Bank_SubmissionFileName
Bank_Password

2 )tblBankContacts which has 2 fields
Bank_LegalName
Bank_Contacts
(I used both fields as the PK here).

I created a 1-M relationship between the 2 tables on the Bank_LegalName field

so can't I somehow bound the Bank_LegalName from tblBanks to Bank_LegalName from tblBankContacts in a form using a combo box or am I totally wrong and confused about how access works?

this is a whole new question and i'll be more than happy to write a new one so you can get more credit.

Thanks for the overtime Christian
0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

so in this case it would be much more comfortable if you create a double continous form.

1. create a continous form for the banks which displays all the fields you want
2. insert a footer into the form
3. insert a subform into the footer (Access will show a messagebox that it is not possible to use subforms in continous forms and it sets the form type back to single, ignore that message and set it back to continous form)
4. insert your contact form into the subform
5. link the master form with the subform using the link fields where you have the relationship.

If you now click through the records of the master form you will see the contacts of the chosen bank immediately, without any line of code. This is the comfortablest way of realizing this.

I would also recommend not to use a name field to link tables, insert an autoincrement field into both tables and link the tables using the ID fields instead.

Cheers,

Christian
0
 

Author Comment

by:Shino_skay
Comment Utility
I think I can competently follow these instructions except I have 1 question.

you are advising not to link the tables using the pk field, in this case "Bank_Legalname", and link both tables to a newly created autonumber field?

I' can't visualize it now but I guess I'll find out the reasoning when I apply this.

Thanks and I appreciate the extended effort. This is why I love this forum.

0
 
LVL 24

Expert Comment

by:Bitsqueezer
Comment Utility
Hi,

computers are numbercrunchers and work better with numbers...:-)
But to be more detailed: A PK is in most cases a clustered index which means that this is not only an index but also the physical order of the table. Comparing numbers with each other is easier for the database than comparing strings.

Here are some explanations about using primary keys:

Create or remove a primary key

In fact an ID field as PK is in most cases faster than a string field or other datatypes. You should always use long integer as number format for the ID field as it allows around 2 billion records which in most cases will never be reached.

Look at the link above, you'll find some explanations about candidate keys which may be of help.

Cheers,

Christian
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now