Auto update query

[He cries for help]

Can someone walk me through the steps for creating an update query? I have
two tables named "posnow" and Suppliers. both contain the same fields
"suppliers, contact, telephone and fax". (The supplier table contains the
information and the posnow table is the main design table)

I want to auto update fields on a form with the same name using this query.

Also should what should be used on the form, a list box, combo box or other
to look up the main field(supplier) to fill in the balance of the fields?
Please be definitive with you anwser for I am just a amatuer at this. Thanks
for you help...

 

[John W. Vinson]

Can someone walk me through the steps for creating an update query? I have
two tables named "posnow" and Suppliers. both contain the same fields
"suppliers, contact, telephone and fax". (The supplier table contains the
information and the posnow table is the main design table)

I want to auto update fields on a form with the same name using this query.

Why?

Relational databases use the "Grandmother's Pantry Principle": "A place - ONE
place! - for everything, everything in its place".

Storing the supplier's contact (or multiple contacts), phone (or multiple
phones), etc. redundantly in a second table is neither necessary nor good
design. Store it ONCE and use a Query or another builtin Access tool such as a
combo box to look it up as needed. That's what relational databases are *FOR*.

If you want to *display* (not store) this information on the form, include the
fields in a Combo Box and put textboxes on the form with control sources like

=comboboxname.Column(n)

where n is the zero based index of the field you want to display.

John W. Vinson [MVP]

 

[He cries for help]

I do want to store this information as a part of the database, this form and
its information will be a record for government contracts. I need to recall
this info when needed and the fields I have refered to in this posting is
intergal. This information in not being actually saved in two locations, the
vendor table is informational only imported from a excel database,the main
table posnow is where all the info will be stored. I would have never thought
that autofilling four boxes on a form and saving this info upon exiting would
have been this hard.

 

[John W. Vinson]

I do want to store this information as a part of the database, this form and
its information will be a record for government contracts.

Sorry, but that is incorrect.

A Form IS NOT A RECORD.

A form is a *window* which can flexibly and dynamically display data.

You store information in tables. If it needs to be a permanent, unchangable
record, Access is not an appropriate medium.

I need to recall
this info when needed and the fields I have refered to in this posting is
intergal. This information in not being actually saved in two locations, the
vendor table is informational only imported from a excel database,the main
table posnow is where all the info will be stored. I would have never thought
that autofilling four boxes on a form and saving this info upon exiting would
have been this hard.

It's NOT hard.

Suppose you store an address in the POSNOW table.

You then get another vendor table with a corrected address for a vendor. The
address you had previously was wrong, and the new spreadsheet corrects that
error.

If you have stored the address in the posnow table, THAT ADDRESS IS WRONG.

Is it your business rule that you should store incorrect data permanently,
even when a correction is available?

IF you really want to do so - and I'll still argue That It Is A Bad Idea - you
can use VBA code in a combo box's AfterUpdate event to copy fields from the
combo box's row source into bound textboxes on the form:

Private Sub cboVendor_AfterUpdate()
If Not IsNull(Me!cboVendor) Then
Me!txtAddress = Me!cboVendor.Column(2)
Me!cboPhone = Me!cboVendor.Column(3)
<etc>
End If
End Sub

Note that the Column() property is zero based; (2) means the third column in
the combo's row source query.

John W. Vinson [MVP]

 

[He cries for help]

Sorry, but that is incorrect.

A Form IS NOT A RECORD.

A form is a *window* which can flexibly and dynamically display data.

You store information in tables. If it needs to be a permanent, unchangable
record, Access is not an appropriate medium.


It's NOT hard.

Suppose you store an address in the POSNOW table.

You then get another vendor table with a corrected address for a vendor. The
address you had previously was wrong, and the new spreadsheet corrects that
error.

If you have stored the address in the posnow table, THAT ADDRESS IS WRONG.

Is it your business rule that you should store incorrect data permanently,
even when a correction is available?

IF you really want to do so - and I'll still argue That It Is A Bad Idea - you
can use VBA code in a combo box's AfterUpdate event to copy fields from the
combo box's row source into bound textboxes on the form:

Private Sub cboVendor_AfterUpdate()
If Not IsNull(Me!cboVendor) Then
Me!txtAddress = Me!cboVendor.Column(2)
Me!cboPhone = Me!cboVendor.Column(3)
<etc>
End If
End Sub

Note that the Column() property is zero based; (2) means the third column in
the combo's row source query.

John W. Vinson [MVP]

 

[John W. Vinson]

Did you have another question, HCFH? It looks like you just reposted my reply.

John W. Vinson [MVP]