By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,560 Members | 949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,560 IT Pros & Developers. It's quick & easy.

Using 2 Combo boxes to populate a Subform

P: 16
First off, I am a complete newbie to Access. I've used it maybe a half dozen times, all my database knowledge is with Oracle.

I'm building a database/form in Access 2003. I'm trying to get it so that I can select a Location from one combo box, and a date from another combo box, and have a Subform populate with the correct data for each date/location selected. I'm not really sure how to go about this, but I've been reading maybe using a command button to do the code execution.

More info on what i'm trying to do. I"m trying to select a Site location (Location), and the Date (Date), and have the subform populate each employee that is off at that site on that date. What is the best way to do this? And would the code be the same as if i were to do it in Oracle?

I've got a Access book coming in the mail so i can learn it, but if i can get this little bit working ASAP that would be great. Thank-you in advance.
Nov 27 '06 #1
Share this Question
Share on Google+
29 Replies


P: 16
Also

I have 3 tables Date(has each date for the week), Time-Off(date/employee name/general/location for time off), Location(site location). Date, and location are combo boxes as well.
Nov 27 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
Also

I have 3 tables Date(has each date for the week), Time-Off(date/employee name/general/location for time off), Location(site location). Date, and location are combo boxes as well.


The Row Source of the third combo box should be something like:

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT Field1, Field2, Field3, etc FROM [Time-Off]
  3. WHERE Location = [Forms]![FormName]![locationCombobox]
  4. AND [Date] =  [Forms]![FormName]![dateCombobox]
  5.  
Then you need to either create a command button and put the following in the On Click event or put the following in the After Update event of the date and location comboboxes.

Expand|Select|Wrap|Line Numbers
  1. Me.ThirdCombobox.Requery
  2.  
Nov 27 '06 #3

P: 16
I think I may have over explained myself, or maybe I'm just not quite understanding you right.

My form looks something like this

XXxxXX <-- Combo Box (Location)
XXxxXX <--- Combo Box (Date)

XXxxxXX <---- Subform/Subreport ( Time-off) (Should show the employee name etc. from this table)

Each Combo Box is populated from a table. And the subform should have a query result from the combo boxes.

So should i need to add another combo box? Again maybe im just not understanding you correctly.
Nov 27 '06 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
I think I may have over explained myself, or maybe I'm just not quite understanding you right.

My form looks something like this

XXxxXX <-- Combo Box (Location)
XXxxXX <--- Combo Box (Date)

XXxxxXX <---- Subform/Subreport ( Time-off) (Should show the employee name etc. from this table)

Each Combo Box is populated from a table. And the subform should have a query result from the combo boxes.

So should i need to add another combo box? Again maybe im just not understanding you correctly.
Just use the query I gave you as the record source of the subform rather than the row source of a third combo box.

Make the requery statement on the subform name e.g.

Expand|Select|Wrap|Line Numbers
  1. Me.SubformName.Requery
Nov 27 '06 #5

P: 16
Excellent that is kinda what I thought I should do. One more question however, where may I find the record source for the subform. I know where to find the Row source for the combo box, but the record source is not there for the Sub-form.


Thank-you again, you have been a great help. I really wish now I would have learned some Access instead of just Oracle.
Nov 27 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Excellent that is kinda what I thought I should do. One more question however, where may I find the record source for the subform. I know where to find the Row source for the combo box, but the record source is not there for the Sub-form.


Thank-you again, you have been a great help. I really wish now I would have learned some Access instead of just Oracle.
Open the subform as a form on its own away from the main form. It should be on the form list. You can get the normal properties there.

Mary
Nov 27 '06 #7

NeoPa
Expert Mod 15k+
P: 31,602
Alternatively, click on the subform item, then click again. This will select the subform within the subform item.
Once in there you can click on the little square at the top-left which will select the form itself (rather than any of the sections or controls). With that selected show the properties window et voilą!
Nov 27 '06 #8

P: 16
I have just 2 more final questions.

First one is on,

[Forms]![FormName]![Combobox]

What does the [Forms] part of the line represent? I know FormName would be the name of the form the combo box is located on, but not sure what that would be or if its changed?

I tried adding a command button with an On-Click of:

Me.SubformName.Requery but that just give me a Access error. I also tried adding it to the after update of each combo box but that also gave an error.
Nov 28 '06 #9

P: 16
*Edit*

A little bit more information as well, my form is named Location, the subform is Time-Off.


SELECT [Time-Off].[Employee Name], [Time-Off].Date, [Time-Off].Location, [Time-Off].Coverage
FROM [Time-Off]
WHERE ((([Time-Off].Date)=[Forms]![Location]![date]) AND (([Time-Off].Location)=[Forms]![Location]![location]));
That is what my SQL looks like on the subform Record Source.
Nov 28 '06 #10

NeoPa
Expert Mod 15k+
P: 31,602
Subforms are a quagmire of strange references and difficult code.
To use subforms, and reference them correctly, you need to remember some fundamental concepts.
A Page is an object that represents a 'Tab' on a form. When you have a form with multiple Tabs, each one is a separate page which is displayed in fundamentally the same space.
A Subform is an object that can be added to a (main) form or a Page on a form, just like other objects (TextBox control; ComboBox control; Frame control; etc).
A Subform is merely an item (on your main form) which contains a form (the actual subform). This means, perversely, that a Subform object is not actually a form at all, but a container for one.

I hope this can help to clarify certain problems that are frequently found, particularly with referencing items on a 'subform'.

Bearing that in mind, where is the Record Source you have set up with your SQL?
Nov 28 '06 #11

P: 16
The record source is on the subform. I opened the subform on its own, and went to the properties for the form-->Record Souce-->SQL View

I think thats what your asking right?
Nov 28 '06 #12

P: 16
Here is a screenshot of my form.




If I try to open the Sub-form on its own this is what pops up, for basically asking for values for each combo box. I think it has something to do with the sub-form sql not being right, but not sure




This is a screenshot of my sql code for the subform to populate based on the selections of the combo boxes.

Nov 28 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
I have just 2 more final questions.

First one is on,

[Forms]![FormName]![Combobox]

What does the [Forms] part of the line represent? I know FormName would be the name of the form the combo box is located on, but not sure what that would be or if its changed?
Looking at the forms above it all seems correct.

I tried adding a command button with an On-Click of:

Me.SubformName.Requery but that just give me a Access error. I also tried adding it to the after update of each combo box but that also gave an error.
Ok.

The first thing you need to check is that you are using the correct subform name. Open frmLocation in design view and click on the frame surrounding the subform. Open the properties and see what value is in the Name property under the Other tab. This is the 'Name' you will need.

If [Time-Off] is what's in there try the following. Otherwise substitute whatever value you find for [Time-Off].

Expand|Select|Wrap|Line Numbers
  1.  [Forms]![frmLocation]![Time-Off].Form![Combo3].Requery
If that doesn't work let me know.
Nov 28 '06 #14

P: 16
I'm still getting an error.







Heres a little update on everything.


I have three tables (tbLocation, tbDate, tbTime-Off), I have 1 form (frmLocation), 1 Subform (PTO),

2 Combo boxes, (Combo1, Combo2)

I changed some names etc. just so I dont get confused and put the wrong thing in the wrong place.
Nov 28 '06 #15

MMcCarthy
Expert Mod 10K+
P: 14,534

I have three tables (tbLocation, tbDate, tbTime-Off), I have 1 form (frmLocation), 1 Subform (PTO),

2 Combo boxes, (Combo1, Combo2)

I changed some names etc. just so I dont get confused and put the wrong thing in the wrong place.
In the click event, try ....

Expand|Select|Wrap|Line Numbers
  1.  Me.PTO.Requery
Nov 28 '06 #16

P: 16
I get an invalid syntax error when I try to go to view mode, instead of design mode.

BTW just in case I haven't said it before, I really want to thank you all for this help, I appreciate it soooo much.
Nov 28 '06 #17

MMcCarthy
Expert Mod 10K+
P: 14,534
I get an invalid syntax error when I try to go to view mode, instead of design mode.

BTW just in case I haven't said it before, I really want to thank you all for this help, I appreciate it soooo much.
You can't view the subform on its own as it is based on a value in the main form.

It was only necessary to open in design view to check/change the record source.

To open it in normal view you open the main form.
Nov 28 '06 #18

P: 16
The command button is added to the main form correct? If so thats what I did.

Then after adding the button, and putting in Me.PTO.Requery in the On-Click field. But when i go to view the form, I get the error.
Nov 28 '06 #19

MMcCarthy
Expert Mod 10K+
P: 14,534
The command button is added to the main form correct? If so thats what I did.

Then after adding the button, and putting in Me.PTO.Requery in the On-Click field. But when i go to view the form, I get the error.
When you add the Me. you get a drop down list of values. Was PTO one of the values available?
Nov 28 '06 #20

P: 16
I don't get any drop down list. The (Me.PTO.Requery) is a macro right? I don't have any macro's, should they be built into access?
Nov 28 '06 #21

MMcCarthy
Expert Mod 10K+
P: 14,534
I don't get any drop down list. The (Me.PTO.Requery) is a macro right? I don't have any macro's, should they be built into access?
It's not a macro. It's VBA code.

In the On Click event. Put [Event Procedure] then click on the button to the right with the dots on it. It will open with:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Command00_Click()
  3.  
  4.  
  5. End Sub
  6.  
Add the line as follows:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Command00_Click()
  3.  
  4.    Me.PTO.Requery
  5.  
  6. End Sub
  7.  
Nov 28 '06 #22

P: 16
I knew there was something I wasn't doing correctly. Its not giving an error, whoever, when I select fromt the 2 combo boxes, and hit the command button, nothing populates.
Nov 28 '06 #23

MMcCarthy
Expert Mod 10K+
P: 14,534
The first thing you need to check is that you are using the correct subform name.

Open frmLocation in design view and click on the frame surrounding the subform.

Open the properties and see what value is in the Name property under the Other tab.

This is the 'Name' you will need.
Did you do this?

If it's not PTO then substitute whatever it is for PTO.
Nov 28 '06 #24

NeoPa
Expert Mod 15k+
P: 31,602
Try :
Expand|Select|Wrap|Line Numbers
  1. Me.SubformName.Form.Requery
  2. or simply
  3. SubformName.Form.Requery
SubformName must match the name of the Subform object on your form and NOT the name of the contained form (unless the names are the same of course).
Nov 29 '06 #25

P: 16
Tried them both and nothing, just more errors when i try to run it. And yes the Subform name is PTO.

This just seems to have me stumped. Just a few more things I want to be sure of:

If I have the command button with the on-click event, I do not need anything for code on the combo boxes right? And nothing other then the query for the sql code for the subform correct?

Also not sure if this would maybe make it easier but would having the command button pop another form instead of a subform be easier?
Nov 29 '06 #26

P: 16
If its not to much trouble maybe you could take a look at my database? I would rather you not make any changes, as I would rather do it myself but maybe you could see if i have everything where it should be.

HERE
Nov 29 '06 #27

MMcCarthy
Expert Mod 10K+
P: 14,534
If its not to much trouble maybe you could take a look at my database? I would rather you not make any changes, as I would rather do it myself but maybe you could see if i have everything where it should be.

HERE
Ok

The problem is with the combo boxes.

Your list of Dates has the bound field as an ID field and is trying to compare that to the date. You can either remove the id field or change the bound column property under the data tab to 2.

The exact same problem exists on the Location combo box.

Mary
Nov 29 '06 #28

NeoPa
Expert Mod 15k+
P: 31,602
Thanks for that Mary.
You've saved me a long job.
Nov 29 '06 #29

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks for that Mary.
You've saved me a long job.
Anytime Ade
Nov 29 '06 #30

Post your reply

Sign in to post your reply or Sign up for a free account.