A form, query and table

[Guest]

Didn't know which area to post this so I chose here!

I have a form in which there are 3 ComboBoxes, 1 Textbox and a CommandButton.
Each ComboBox has items out of different tables: a manufacturer table, a
product type table and a usage table. They are there to choose each of the
previous, type a product name into the textbox and then click the
CommandButton "Add Record" to add this info into yet another table that is a
complete catalogue of manufacturers, products and product usage.

Sound good in theory, right? Well, it would if it worked! The trouble is it
doesn't do anything when I click the button. Could someone review this and
help out? Thanks in advance! Here's my code:

'================================
Option Compare Database

Private Sub add_data_Click()
On Error GoTo Err_add_data_Click
DoCmd.OpenTable "vList", , acAdd
'===I think I need more code here==='
Exit_add_data_Click:
Text7.Text = ""
Text7.SetFocus
Exit Sub
Err_add_data_Click:
MsgBox Err.Description
Resume Exit_add_data_Click
End Sub

 

[John W. Vinson]

I have a form in which there are 3 ComboBoxes, 1 Textbox and a CommandButton.
Each ComboBox has items out of different tables: a manufacturer table, a
product type table and a usage table. They are there to choose each of the
previous, type a product name into the textbox and then click the
CommandButton "Add Record" to add this info into yet another table that is a
complete catalogue of manufacturers, products and product usage.

Sound good in theory, right? Well, it would if it worked! The trouble is it
doesn't do anything when I click the button. Could someone review this and
help out? Thanks in advance! Here's my code:

I'm confused. If the Form is based on your catalog table, you don't need a
button and you don't need a single line of code! That's what forms are FOR -
to add and edit data in tables.

Why are you taking the extra step of writing code to do what Access does for
you out of the box???

John W. Vinson [MVP]

 

[Guest]

Maybe it's because I don't know how to use Access?

My form is based on three things: vendor name (table1), material types
(table2) and material application method (table3). Table1 is dumped into
ComboBox1 of Form1. Table2 is dumped into ComboBox2 of Form1 and Table3 is
dumped into ComboBox3 of Form1. I added a TextBox (Text7) to be able to enter
a product name. I then added a CommandButton to submit the combined
information of Combo1, Combo2, Combo3 and Text7 to enter a new Record into
Table4 (vList).

As far as I can tell from when I installed Office, there was no form already
in Access to do that unless there's a template somewhere... ?

 

[John W. Vinson]

Maybe it's because I don't know how to use Access?

My form is based on three things: vendor name (table1), material types
(table2) and material application method (table3). Table1 is dumped into
ComboBox1 of Form1. Table2 is dumped into ComboBox2 of Form1 and Table3 is
dumped into ComboBox3 of Form1. I added a TextBox (Text7) to be able to enter
a product name. I then added a CommandButton to submit the combined
information of Combo1, Combo2, Combo3 and Text7 to enter a new Record into
Table4 (vList).

As far as I can tell from when I installed Office, there was no form already
in Access to do that unless there's a template somewhere... ?

Well... you're completely misunderstanding how forms are created and how forms
work.

A Form is typically based on a Table or (more often) on a Query of a single
table, sorting the records into a desired order. The Form is a tool to update
the data in that table.

You put Controls on a form, usually one control bound to each field that you
want to update. A Textbox is one type of control; a Combo Box or Listbox is
another. A Combo Box has a "Row Source" from which it gets data, and a
"Control Source", the field into which it puts the selected value.

You are talking about "Table1 is dumped into ComboBox1". That's a picturesque
way of putting it but it's not really how the tool works! ComboBox1 is a tool
which lets you select one VendorName from the vendors table, and store that
vendor's unique ID into a VendorID field in the main form's recordsource.

In this case, the Recordsource of your form should be Table4; ComboBox1 should
have Table4's VendorID as its Control Source, and Vendors (or a query sorting
vendor names alphabetically) as its RowSource, and so on.

Of course, Office doesn't come with forms all set up for *YOUR* particular
design of Table4... but it does come with a very full-featured Forms design
capability. You can use the Forms Wizard to create a form for you, and the
Toolbox to add or change controls.

I'd suggest you check out some of these links, particularly the Database
Design 101 links on Jeff's page:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

John W. Vinson [MVP]

 

[Guest]

Thanks for the input. Since my last post, I was thinking about what you had
said in your previous post and found out what you just said (I'm a quick
study). Really the only thing I have trouble with is syntax when I'm trying
to make a particular control perform a particular action. Like right now I
have already done what you said so my vendor combobox, product combobox and
product application all populate the way they should and I can add in new
record to the vList table, but I can't seem to get my "previous entry"
display to work correctly... due to syntax! I get this "#name?" where the
previous row of information should be... but that's not a big deal. I will
figure it out sooner or later. It's in there somewhere!

Thanks again for the info, John.

 

[John W. Vinson]

I can add in new
record to the vList table, but I can't seem to get my "previous entry"
display to work correctly... due to syntax! I get this "#name?" where the
previous row of information should be... but that's not a big deal. I will
figure it out sooner or later. It's in there somewhere!

One point to consider: a Table *has no order*. If you're looking for "the
previous entry" on a Form, it's not really there. You can type Ctrl-' in a
control to copy the most recently entered value (during the current Access
session only), and while a record is being edited each control has an OldValue
property which is the value it had when the record was first accessed; but
there really is no such thing as "the previous entry" unless you have a query
which uses data within the table to define that record.

John W. Vinson [MVP]

 

[Guest]

Good information, John, Thanks. In regards to the default buttons on the
bottom of the form, how would I make the ComboBoxes retain that 'OldValue'
when the >* button is clicked?

 

[John W. Vinson]

Good information, John, Thanks. In regards to the default buttons on the
bottom of the form, how would I make the ComboBoxes retain that 'OldValue'
when the >* button is clicked?

You can put code in a control's AfterUpdate event to copy its current value
into its DefaultValue property, so the next entry will duplicate the current
entry unless the user overwrites it. If the combo box is named cboMyCombo the
code would be

Private Sub cboMyCombo_AfterUpdate()
Me.cboMyCombo.DefaultValue = Me.cboMyCombo
End Sub

John W. Vinson [MVP]

 

[Guest]

That produced the #Name? error. I'll keep playin with it and see what I can
come up with. Thanks again for all your help.

 

[John W. Vinson]

That produced the #Name? error. I'll keep playin with it and see what I can
come up with. Thanks again for all your help.

What is the name property of the combo box? The code assumes that it's named
cboMyCombo. I doubt that you actually used that name for your combo box...

John W. Vinson [MVP]