Entering data into table

[Becker]

I am trying to track the order in which products are completed by their surface finish. We have 6 finishes. I have for each finish a button to specify that finish has been completed. Currently on the table I have a field for the date and one field for each finish. I am successfully adding the total number of each kind of finish per day. There is automatically a new record created each day. I just added fields labeled 1-99 into the table. I would like each field starting at 1 and ending at 99 to be populated with the title of the finish when any of the six buttons is clicked. For example: If I finish a nickle item then I click the nickle button. The field labeled 1 should be populated to say nickle. Then if I complete a black item and click the black button, the field labeled 2 should be populated to say black, etc. How can I tell it to populate the next empty field on the current record?

[zmbd]

Your database is not normalized.[*]> Database Normalization and Table Structures.

So how I would structure this (and this sketchy because I don't know your entire process):

tbl_customer - think adress book
tbl_products - think catalog
tbl_order - would track the projects in each customer order
tbl_project - this would have the project
tbl_finishes - your six finishes
tbl_projectstat e - this tracks the projects and the finish.

Work flow, Customer JohnDoe - PrimaryKey=1
Orders three products (PrimaryKeys= 21, 25, 29)
So in table orders you have three records
Pk_order = 1; FK_Customer = 1; FK_Products = 21
Pk_order = 2; FK_Customer = 1; FK_Products = 25
Pk_order = 3; FK_Customer = 1; FK_Products = 29

Now you could omit the above and keep track of the customer order another way and take the above into the project stage

Now in table project

pk_project=1; fk_order=1; fk_finishes=1; fdate= mmd1yy
pk_project=2; fk_order=1; fk_finishes=2; fdate= mmd2yy
pk_project=3; fk_order=1; fk_finishes=3; fdate= mmd3yy
pk_project=4; fk_order=1; fk_finishes=4; fdate= mmd4yy
pk_project=5; fk_order=1; fk_finishes=5; fdate= mmd5yy
pk_project=6; fk_order=1; fk_finishes=6; fdate= mmd6yy

with similar entries for each of the other product orders and I've used MM#dYY as a generic date entry.

A few joins between the tables and the queries, pretty things up in the forms using look-up fields (comboboxes) and you're up and running.

Follow this thread to see how important proper layout in the beginning phase is to an efficient database

http://bytes.com/topic/access/answers/958921-invoice-layout-tables

[Becker]

So I went and changed around my recent addition to my table and split it into two tables linked together in a query by their primary keys. I think it is normal now. IDK for sure because I am so new. It made it look nicer though and I can see some areas in the future where is will make things easier.

Sometimes it is hard for me to explain what is in my head haha. Basically if I can figure out how to add text to the next empty field in the table (moving left to right) from a button on a form then I would be super happy. I sure hope it is possible.

[zmbd]

We have an abundance of patience here...

sounds like you're starting from scratch... I'll pm you my boilerplate of tools. Please check your bytes.com inbox.

I'll give the most simplistic, crude, method here and once you work the tutorial in the boilerplate you'll be able to handle things in a more elegant method.

Normally a form is "bound" to a record source. What this means is that there is either a table or query that is associated with the form. The fields in the underlying table are then bound to the controls on the form.

For example, take a very simple table:
[tblone]![PK];[tblone]![FieldOne_text]; [tblone]![Fieldtwo_numeri c]

(assume he datatype is indicated by the name)

So if you use the wizard to make the form, selecting this table as the recordsource. The form will have the record source property set to "tblone" and the the inserted controls will default to being named after their bound field as indicated in by their name and the "control source" property... in the this case: you would get three text box controls, named "PK", "FieldOne_t ext" and "Fieldtwo_numer ic" this makes things confusing to say the least as the controls are NOT the fields.

To help illustrate this difference:
Open the form in design view.
Select one of the textbox controls and either in the ribbon or by rightclick show the control properties.
IN the {All}-Tab you can of course see all of the controls properties, the first field will be [name] (which is why you should not use that as a field-name), the second will be [Control Source] - using the wizard both are the same by default. Take a moment to look at each tab grouping so that you get an idea as to how the various properties are grouped... pay special attention to {data} and {events}

Lets change the control names so for each textbox on the form, go to either the {all} tab or the {other} tab and change the names (selecting each control of course (^_^) ).
ctrl_txt_PK
ctrl_txt_FieldO ne_Text
ctrl_txt_FieldT wo_Numeric

There is a property "value" for each control and this is important... when you scroll thru the records on the form in normal view (or dataentry) the value of the currently selected record becomes the value of the control...
ctrl_txt_pk.val ue === value-of(currentrecor d([tblone]![PK]))

following?

OK, next is your command button... add one to the form... cancel any wizards that show up.
Show the properties for the command button, and name it something useful, say, "ctrl_cmd_enter black"
Goto the {events} tab for your command button...
Select the on_click event, click on the button with the three dots [...]
Select Code Builder

The VBA Editor window will open and you should have something like:

Option Compare Database
Option Explicit

Private Sub ctrl_cmd_enterblack_Click()

End Sub

now in

Private Sub ctrl_cmd_enterblack_Click()

End Sub

enter

me.ctrl_txt_fieldone_text.setfocus
me.ctrl_txt_fieldone_text.value = "black"
me.ctrl_cmd_enterblack.setfocus

The setfocus forces the entry in to the record, then we alter the field, and by default when we set the focus outside of the record the record is saved.

Private Sub ctrl_cmd_enterblack_Click()
   me.ctrl_txt_fieldone_text.setfocus
   me.ctrl_txt_fieldone_text.value = "black"
   me.ctrl_cmd_enterblack.setfocus
End Sub

this is the basic, crude, method. There's no error checking and is expecting the user to know what they are doing... etc... dates would be surrounded in "#" strings in quotes, and numeric values are "as is"

[zmbd]

Now mind you, the last post is simply to get you running; however, I would not advise going down this path very far before starting from scratch with a properly normalized database.