combo box running a query

[Styrker]

OK folks here is my problem. I have a Form and subform. The subform pulls data from one table and uses the info from a combo box (which pulls data from another table) on the main form to sort the data. The problem is that this form tries to rewrite data back to both tables and I get a key error. All I want to do is use the combobox to pull the data on the subform. How should this be done?

 

[gusbrunston]

Hi:

If I understand what you're asking, and I'm not sure I do...

To start with, form/subform construct is designed for tables with a one-to-many relationship: the 'one-side' table (or query) is the data source for the Form; the 'many-side' table (or query) is the data source for the SubForm. Your controls on the Form and Subform should determine where new or edited data is updated.

tbl1='one-side' table =data source for Form.
Bind controls on this Form to tbl1.

tbl2='many-side' table=data source for Subform.
Bind controls on this Subfrom to tbl2.

If you're using the SubForm only to display data from tbl2, then you don't need the form/subform construct. Just show the data from tbl2 on a pop-up form without permitting deletions, edits, etc.

If I haven't understood your question, give some more particulars, and someone will be able to give you a good answer.

Cheers, Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[gusbrunston]

Following email received from Styrker:

Gus,

Yes you are right.

Applicators ='one-side' table =data source for Form.

Jobs='many-side' table=data source for Subform.

I have never created a popup form. Any hints or suggestions are greatly appreciated.

Thanks

Blaine
Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[gusbrunston]

Hi, Blaine

(It's a good idea to post your replies on Tek-tips. I don't mind personal emails, but posting the replies on Tek-tips allows everyone browsing to see the development of the solution to a question.)

An example: I maintain a database that is heavy in posting receipts and checks to client accounts. In several of the forms the user needs to display a list of accounting code numbers.

NUMBER DESCRIPTION    RECEIPT/EXPENSE

 101   Rent received    R
 102   From client      R
 103   Refund           R
 ...   
 401   Cleaning         E
 402   Electrical       E
 403   Plumbing         E
 404   Painting         E
 ...
 601   To client        E
 602   To designee      E
 etc.

The controls on the pop-up form are from tblAccounts, a table that hardly ever changes:

txtAcctID   txtAcctName   txtAcctClass

These controls are bound to tblAccounts. Some of the properties for the pop up form "frmListAccounts" are:

Format > Default view > Continuous
Format > Scroll bars  > Vertical only
Format > Control box  > Yes
Format > Close button > Yes
Format > Border style > Dialog *
Data   > Record Source> tblAccounts
Data   > Allow filters> No
Data   > Allow edits  > No
Data   > Allow add..  > No
Data   > Allow deletes> No
Data   > Data entry   > No
Other  > Pop Up       > Yes *
Other  > Modal        > Yes *

* Wait to change these properties until you're ready to go "public"--it's a little harder to get back into design view from form view if you don't.

You may want to study 'Help' and your instruction book(s) about borders and sizing of pop up forms.

You can set the above form properties on the properties sheet for the form.

If you need further directions for setting properties, let us know.

I hope this is helpful.

But what I want to know is: Why do they have Interstate Highways in Hawaii?






Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com