using 1 form to update 3 tables

[shanewa]

I am trying to use one form to update 3 tables
I have a form with 3 text fields and 3 combo boxes (the combo boxes are based on lookup queries).

1. is a text box that's uderlying source needs to be a field from another table (how is this done?) control

source?

2. source is table which form is based on

3. is a combo box based on a query which is not the underlying source for the form

4. source is table which form is based on

5. is a combo box based on a query which is not the underlying source for the form

6. is a combo box based on a query which is not the underlying source for the form

The last two combo boxes need to be exlusive, either an owner or a region. they do not reside in the same

table. They will be put into the table that the form is based on. # 5 which is not based on the underlying

table that the form is based on but does however share the unique id with the table that the form is based

on. both are text characters. any help would be apprecitated.

 

[Fizzio]

To get you started, in order to base a form on more than one table, create a query with all the fields from all the tables you want to be able to write values to then either use the form wizard to build the form for you or create your own. Combo boxes are usually populated by a query which you can create in the properties of the combo (click on ... beside rowsource) or by a value list (which generally remains static) The query, as said before can include fields from any of the tables. Make sure that you have appropriate relationships between your tables.
HTH but if you need more advice, use the search here for normalisation (tables and relationships) and Combo box.

 

[shanewa]

Thanks Fizzio, I did as you suggested and the form is coming along fine but for some reason I cannot use an add record button that works when I switch the form source to a table that has all the info save one text box and the combo boxes use their own queries in both instances, any ideas?

 

[shanewa]

ok, i figured out the new record button, but still have a question of the last two combo boxes needing to be exclusive, also none of the entries should be blank. I will have to come up with an error message.

 

[Fizzio]

What do you mean by 'exclusive' for your combo boxes?
To stop blank entries in the form being accepted, do one of 2 things. At table level, set the field to required = yes or in form level in the properties of the control, set the validation rule to <>Null.
HTH

 

[shanewa]

I need to assign an object such as a computer to a person or a region. In otherwords the computer is assigned to a person or assigned to a region waiting to be assinged to a person. This way they will know exactly where the computer is at all times.

Before they had the computer assigned to the person and the computer not assigned was not tracked. so I have to get the field from the form titled region to be put into a table where the person is now. person and region will share the same table entry. I want to make it so the person filling out the form cannot choose person and region, they must choose one or the other.

 

[Fizzio]

A way to do this (but I'm not sure if it is the best way) Create your 2 combo boxes from queries of the regions / persons but do not include the ID numbers (shock horror) and set the bound column to the name of the region / person. Set both the controlsources of the comboboxes to your [Assignedto] field and on the on_current property of the form,

if isnull(me.Namecombobox) = false then
with me.Regioncombobox
.value = null
.visible = false
end with
else me.RegionCombobox.visible = true
end if

on the afterupdate event of the name combo, enter the same code.

This should allow you to pick a region and store the name in the table but as soon as you pick a name, it hides the region combo and stores the name in the table. when you delete this name (set it to null) the region box becomes visible again.

I'm sure there will be a better way to do this but I can't think of one at the minute.

HTH

 

[David R]

Not to be obtuse, but if [Person] and [Region] are in the same data field, how could they choose one of each?

If they're in different fields perhaps you could make an option group, Person/Region, and then activate/deactivate the appropriate combo box from the Click events.

HTH,
David R

 

[Fizzio]

David, I know what you are saying but I think it is one OR the other, not both. I totally agree with the option button and modifying the table structure to allow the selection of both region and person, as this will allow a person to move around and not be restricted to one region (Shanewa, I believe you have region in the Person table)
I just offered a non-perfect solution (short-cut in otherwords)

 

[David R]

Oh, I wasn't criticizing either you or shanewa, just trying to understand the problem a little more clearly.