Create a tool to fill a table in Access

[jeanpri]

Hello,

This is my first project with Access. Tutorials on the internet never correspond to my project.



I want to create a data base with only one table. The user will be able to add a new line with a tool (several text boxes to fill the blanks).

One the same panel, I want to display the tool, the table and some other functions (like the sum of a column, or print the table).

I think this very easy to do this (only one table). Yon can see on attachment a powerpoint draft of the panel.

Do you have any ideas or examples to begin my project? For the moment, I have already created the access table and put it into my panel.

Thanks !

Jean

DRAFT BASE PROJECT AMS IPD.pdf

[June7]

By 'panel' I assume you mean a form. Do you want to provide the Access file for analysis? Let's see what you have actually accomplished so far.

[jeanpri]

Hello !
Please find attached my project: for the moment, the form (DESIGN_BASE_AMS_IPD1) with a draft of the creation tool with text boxes and a table to view my data.

My two firsts difficulties are to:
- The table must display the only table I work on (DESIGN_BASE_AMS_IPD). In my project the table display only a line, and not all the data.
- The creation tool must create a new line when the user press "Create new Project". For the moment, no action on this button because I don't find how to do this...

Thanks for your support !

Jean

[June7]

The table must display the only table I work on (DESIGN_BASE_AMS_IPD). In my project the table display only a line, and not all the data.

By 'table' you mean the subform?

The form and subform are identical in structure and both are bound to the same data source. Does not make sense to me. Why do you have the subform? If it is to see all records then the Master/Child links cannot be set.

Are you the only user of this database? If you learn to use the intrinsic navigation tools no code is needed.

[jeanpri]

Sorry for the mistake, I meant "The form must display the only table I work on (DESIGN_BASE_AMS_IPD). In my project the table display only a line, and not all the data."

In fact, I create a subform to insert it to my main form, in order to display the table and the creation tool, but as you said it can't works.

I'm the only user of the database.

Can you just help me about the way to display on the same form the table and the creation tool to fill a new line of the table ?

[June7]

You could remove the subform and set the main form to either Continuous or Datasheet view. The 'table' IS the 'creation' tool. Enter new record on the (NEW) row. Continous view allows the form Header to be used and also can arrange and size the controls.

[jeanpri]

Thanks, I can do that, but my first idea was to display the table, the user can't edit the table directly, but he can edit it (create new entries, edit a line) with the "creation tool".

Major values are to highlight with a color a line of table if it has not change since a long time, autofill some cases in the line....

[jeanpri]

Ok, following your advice I changed the form to a table display, with others parameters:

You can find my ACCESS project attached.

Now I have some problems:

1) The case 'DOLLAR VALUE Y1' must be the result of the multiplication of the case 'Qty Y1' and the case 'Price (USD)' of the same line. How to do to automatically fill this case ?

2) I put a button to lock some table lines, which takes YES/NO values, in order to allow the user to lock a line and so to prevent to modify a line by mistake. This button edit a case in the table (Project Lock). When this button is ON, I want to set the parameter 'Locked' of all of the text boxes from NO to YES. What is the solution? I try some entries in the 'Locked' parameter but it doesn't works.

3) I added a button to delete the selected line of the table. What is the procedure to delete a table line when I press the button ?

Thanks a lot !!

Jean

[June7]

1. Set the textbox ControlSource to an expression that refers to the other fields: =[Qty Y1] * [Price (USD)]. Calculated results do not automatically save to table and in this case should not need to save this calculated value. It can be generate whenever needed.

2. Locking a control locks it for ALL records. Would need a field in table to tag the record as Active/Inactive and use conditional code in form Current event to run code that sets the Locked property for each control to Yes or No as appropriate for the record.

3. Why would you want to delete project record? Deleting records should be rarely needed and only with strong justification.
DoCmd.SetWarnings False
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.SetWarnings True

[jeanpri]

1) OK it's working!

2) In facts I have already a field (a case) in my table which define if the line must be LOCKED or UNLOCKED. I want, when I press Check68, that all the selected line of the table be locked. I tried this following code, but all the textboxes "ProjectName" of all the table lines are locked or unlocked when I press the checkbox :

Private Sub Check68_Click()
If Me.Check68 = True Then
Me.ProjectName.Locked = True
Else
Me.ProjectName.Locked = False
End If
End Sub

And I want that only the textbox "ProjectName" on the selected line to be locked or unlocked depending on the value of the checkbox. (note: the checkbox control the value of the LOCKED/UNLOCKED field on the table, but it is working).

3) Thanks it's working!

[June7]

As stated, setting Locked property of textbox sets it for ALL records. Although there appears to be multiple sets of textboxes for multiple records displayed, there really is only one set, as seen in Design view.

[jeanpri]

Thanks for your explication... I'm surprise we can't link to an object parameter a variable...

For example in this rectangle properties windows:



We can't set, instead of the color code, a value corresponding to a numeric field in the table ?

Thanks !

[June7]

Believe would have to use VBA code to set Conditional Formatting (properties not showing in the properties dialog) with the value from record.

[jeanpri]

Perfect. I know how to program in VBA, it's not a problem for me, but how can I do to make a function which is called each time ACCELL display a new line of my table ?

In fact, I have to change the color of a line, depending on a table field value, for example:

(here a shape is highlighted in red)

What is the name of the function in VBA which can be called each time a line is displayed ? In order to put inside the code to test the field and set the rectangle color...

[June7]

What is the condition that determines color? Is it something like if Price is under $100? Can use Conditional Formatting for this. However, there is a limit of 3 conditions in Access 2003. This does not require VBA code. Review http://office.microsoft.com/en-us/ac...010208133.aspx

Also review http://blogs.msdn.com/b/frice/archiv...08/151178.aspx