Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

VBA: Access ComboBox / SubForm/ Input Query

Posted on 2010-11-26
9
3,739 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 40

Assisted Solution

by:als315
als315 earned 200 total points
ID: 34221048
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
ID: 34222151
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
ID: 34222580
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
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.

 

Author Closing Comment

by:Shino_skay
ID: 34222588
Quick solution and extra background information thrown in as well.
0
 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 34222710
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
ID: 34222842
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
ID: 34223101
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
ID: 34223116
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
ID: 34223176
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 Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

808 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