[Solved] Push button to input data into form

[Tireur]

Hello everyone, I have 2 questions and i'll be glad for any help. I have a OpenOffice Calc spreadsheet connected to a Base database.

Form looks like this:

Code: Select all

ID Date Name Surname Value1 Value2 Value3

1, I have a form to input records there. Because some records repeat often, I'd like to have a push button in my spreadsheet to input those repeated data ( Name, Surname and exact Values 1-3), but with current date and unique ID of course.

2, Because the form has many fields and sometimes more following records may have exactly sama data (but unique ID), I'd like to have another push button to copy data from previous record.

Is there a way to do it? Thanks in advance

[Villeroy]

Wrong database design.
https://en.wikipedia.org/wiki/Database_normalization
https://support.microsoft.com/en-us/kb/283878
http://www.1keydata.com/database-normalization/

[Tireur]

I quite don't understand your answer, probably I haven't described enough how the database looks like.

Database table consists of a list of nutrition packs (which have many substances, here as an example only 3 substances)

Code: Select all

packID  Date    Surname  Name   Substance A   Substance B     Substance C

1     30.10.2015  Doe      John            50            50             100
2     30.10.2015  Parker   Peter           25            30             60
3     30.10.2015  Doe      John            15            80             20
4     30.10.2015  Doe      John            40            20             140
5     30.10.2015  Johnson  Jack            80            50             10
6     30.10.2015  Black    James           15            35             20

Maybe I should have posted this topic into "Macro" section


I found this post

Code: Select all

https://forum.openoffice.org/en/forum/viewtopic.php?p=344413#p344413

, which quite works fine for inserting a new record, but I don't know how to set todays date

[Villeroy]

Your problem is perfectly clear. It is a typical beginners problem. Your table design is wrong which is why you need to enter redundant data. You need a table of persons, a table of packages and a table of substances. Enter all persons, enter all substances. Then your packages combine existing persons with existing substances. Today's date can be added automatically.

[Tireur]

Thanks for the answer, Villeroy, I understand that it would be better to use a different database design, to divide one complicated table into multiple simplier ones. Maybe I'll do it later.

Now, I don't have problems with adding redundant data. I asked question 1, because there is one "Universal" nutrition pack, which has the same compostion all the time. So I want to add this one with a simple click of a button. How can I add a current date there? What should I put as a value for date there?

Here is the code from the mentioned post:

Code: Select all

Sub Insert2Table
REM INSERT RECORDS INTO DATABASE TABLE
Dim Context
Dim DB
Dim Conn
Dim Stmt
Dim Result
Dim strSQL As String
Context=CreateUnoService("com.sun.star.sdb.DatabaseContext") '< get  the database context
DB=Context.getByName("Vaky") '<- change database name
Conn=DB.getConnection("","") '<- username & password pair - HSQL default blank

Stmt=Conn.createStatement()
strSQL="INSERT INTO ""tbl-nov""(""Name"", ""Surname"", ""SubstanceA"", ""SubstanceB"",""SubstanceC"",""SubstanceD"") VALUES('UNI','vak','145','50','8','2')"  '<- change tablename, column name/s and data to be inserted
Stmt.executeUpdate(strSQL) '<- update the inserted data

Conn.close() '<- close the db connection
End Sub

Ad. Question 2 : Is there a way to copy previous record in my "complicated" table, just with unique ID and date?

Thank you very much for your effort and time

[Villeroy]

Packages.odb

yet another m:n database

(12.58 KiB) Downloaded 307 times

[Villeroy]

Create a form.
Download download/file.php?id=18689
Extract the attached archive to directory <user_profile>/Script/python/ (create the python if the directory does not exist).
Add a hidden control named "DefaultCurrentFields" to your form. The Value property is a semicolon separated list of column names of columns you want to be duplicated when moving to a new record.
Point the form's "When Loading" event to the macro "My Macros" > DefaultValues > Form_onLoad.
The ID should be an auto-ID.
The date can be tweaked with menu:;Tools>SQ...
ALTER TABLE "xtable" ALTER COLUMN "date column" SET DEFAULT CURRENT_DATE;

[F3K Total]

Hello,
find attached an example, using methods described here, see also example Invoicing_and_Cloning_with_SubForms.odb, to copy rows using forms, no code.
R

[Tireur]

thanks both of you

I tried to set the current date as default via the ALTER command and it worked, thanks

I liked the macro free version by F3K Total, it looks nice and simple, but unfortunately I can't figure out how to apply it to my database. So I used the macro version and it seems it works