Using 2 Combo boxes to populate a Subform

[gedwards]

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.

[gedwards]

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.

[MMcCarthy]

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:

 
SELECT Field1, Field2, Field3, etc FROM [Time-Off]
WHERE Location = [Forms]![FormName]![locationCombobox]
AND [Date] =  [Forms]![FormName]![dateCombobox]
 

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.

Me.ThirdCombobox.Requery
 

[gedwards]

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.

[MMcCarthy]

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.

Me.SubformName.Requery

[gedwards]

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.

[MMcCarthy]

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

[NeoPa]

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à!

[gedwards]

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.

[gedwards]

*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.

[NeoPa]

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?

[gedwards]

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?

[gedwards]

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.

[MMcCarthy]

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].

 [Forms]![frmLocation]![Time-Off].Form![Combo3].Requery

If that doesn't work let me know.

[gedwards]

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.

[MMcCarthy]

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 ....

 Me.PTO.Requery

[gedwards]

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.

[MMcCarthy]

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.

[gedwards]

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.

[MMcCarthy]

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?

[gedwards]

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?

[MMcCarthy]

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:

 
Private Sub Command00_Click()
 
 
End Sub
 

Add the line as follows:

 
Private Sub Command00_Click()
 
   Me.PTO.Requery
 
End Sub
 

[gedwards]

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.

[MMcCarthy]

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.

[NeoPa]

Try :

Me.SubformName.Form.Requery
or simply
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).

[gedwards]

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?

[gedwards]

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

[MMcCarthy]

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

[NeoPa]

Thanks for that Mary.
You've saved me a long job.

[MMcCarthy]

Thanks for that Mary.
You've saved me a long job.

Anytime Ade