Table Updates Via Form - Not all Data Updating

[shelby55]

Hi

I am using Access 2003.

I have an unbound form named frmRuralBeds. On it there are 2 combo boxes: fiscal year and Unit.

There is also a subform (sfrmRuralBeds) based on the query qryRuralBeds and is in dataview. The query is based on tblRuralBeds and is different from the table only because it has the fiscal year column based on month. Other data elements include Site, FirstofMonth, number of beds. So each year there will be 12 entries per Site (one for each month). I only have the data elements of month and # of beds visible in the subform.

The query (and therefore the subform) is filtered by the two combo boxes on the MainForm and is working well. However, when I add a new month, it updates the table but it doesn't show a Site Name. How can I get the Site selected in the combo box of the main form into the query itself without typing it in the subform?

Thanks.

 

[dhookom]

How can I get the Site selected in the combo box of the main form into the query itself without typing it in the subform?

I can't see that you have the Site selected in a combo box on the main form. Add the combo box and set the Link Master Child properties of the subform appropriately.

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi

The main form wasn't bound so to link via child/master I have to make it bound and I did on the same query as the subform (qryRuralBeds). However, now nothing is showing up in the subform. And, just to test, I added another month of information and it still doesn't include Unit Name into the table.

So what have I done wrong? Thanks.

 

[dhookom]

You don't have to make the main form bound in order to use the link master/child properties.

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi Duane

Sorry but this isn't making sense: if I make the main form (frmRuralBeds) unbound then when I try to create links with the subreport I get the error message "can't build a link between unbound forms".

So what am I doing wrong? Thanks.

 

[dhookom]

You don't have to use the wizard. Just type in the name of the controls from the main form and the respective field names from the subform.

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi Duane

Sorry for being so dumb....but the main form isn't bound so am I binding the subform to the combo boxes on the main form?

Note that left unbound, and editing the query so that the unit in the query = forms!frmRuralSites!cmbUnit and the fiscal year = forms!frmRuralSites!cmbFiscal

makes the subform filter correctly. I just need entries made via this form to have a Unit attached to it. So I'm still not sure I'm doing this right?

 

[dhookom]

Set the Link Master to your combo boxes and the Link Child to the related fields. This will create a filter and default values for the subform.

If this isn't what you want the try to be a little more descriptive of your setup and requirements.

Duane
Hook'D on Access
MS Access MVP

 

[shelby55]

Hi Duane

I did as you suggested and now when I open the form it prompts me for all 4 items.

For the combo boxes, the fields are Unit_Name and Fyear; for the subreport (based on a query) it's Unit and FiscalYear are the corresponding fields. The Main Form is unbound and the subform is based on qryRuralBeds.

In qryRuralBeds I don't have the fields of Unit and FiscalYear based on the combo boxes of the main form i.e. forms!frmRuralBeds!cmbUnit....should I?

As stated before it was working in its previous state but not including the Unit Name in the addition of any data without the user typing in the Unit Name. I thought it redundant to have the filter showing only that Unit's information to have to add type it into any added data but maybe I do....

I think that you are correctly assessing what I want to see but just not sure why it isn't working as I'd like.

 

[dhookom]

You must have controls on the main form that have controls sources with values that match fields in your subform. If you can't figure this out, please come back with these properties of your combo boxes:

Name:
Row Source SQL:
Bound Column:

Also the Record Source SQL of your subform and the field names in the subform with values that match the combo box values on your main form.


Duane
Hook'D on Access
MS Access MVP