If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
A form, query and table
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 |
#2
|
|||
|
|||
A form, query and table
On Thu, 12 Apr 2007 16:44:04 -0700, IT_roofer
wrote: 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] |
#3
|
|||
|
|||
A form, query and table
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" wrote: On Thu, 12 Apr 2007 16:44:04 -0700, IT_roofer wrote: 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] |
#4
|
|||
|
|||
A form, query and table
On Fri, 13 Apr 2007 07:34:02 -0700, IT_roofer
wrote: 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/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html John W. Vinson [MVP] |
#5
|
|||
|
|||
A form, query and table
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" wrote: On Fri, 13 Apr 2007 07:34:02 -0700, IT_roofer wrote: 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/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html John W. Vinson [MVP] |
#6
|
|||
|
|||
A form, query and table
On Fri, 13 Apr 2007 10:34:00 -0700, IT_roofer
wrote: 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] |
#7
|
|||
|
|||
A form, query and table
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" wrote: On Fri, 13 Apr 2007 10:34:00 -0700, IT_roofer wrote: 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] |
#8
|
|||
|
|||
A form, query and table
Internet Explorer cannot display the webpageOn Mon, 16 Apr 2007 10:44:02 -0700, IT_roofer wrote:
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] |
#9
|
|||
|
|||
A form, query and table
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" wrote: Internet Explorer cannot display the webpageOn Mon, 16 Apr 2007 10:44:02 -0700, IT_roofer wrote: 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] |
#10
|
|||
|
|||
A form, query and table
On Mon, 16 Apr 2007 15:28:00 -0700, IT_roofer
wrote: 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] |
Thread Tools | |
Display Modes | |
|
|