How to pass variable or popup prompt in 'Content' in Base Form

English
#1

Hi,

Are there a method to pass variable or prompt popup that able to fill the ‘Content’ field in Form configuration. It is for Table source not for Query nor SQL command.

If no solution, is it possible with js macro ?

Thanks

#2

It seems to me that this forum is being populated only by GURUS, who are bothered by plain SQL. I am sorry but do absolutely not know how to use java script.

Could you however make yourself more clear!!???

#3

I want to change table name many time. The task must be done automatically (by variable, prompt, formula, code, …) not manually by editing the form.

#4

So you have one form and several tables with identical layout anc column names in your database. And you wish to connect this single form to different tables.
.
It should be possible with an external form. Therefore you save your form as a seperate Writer-file, then reconnect to a database table. Changing this connection should be possible by macro.
.
Your design is a bit unusual for a database, so this is no default route. Is this an read-only approach?

#5

Yes. I have several table with the same structure. The goal is to switch between these tables with just one form. I dont want to use many forms nor many .odb files. Editing the form for each access is not good method too.

#6

All tables must be updatable.

#7

Could you tell me some entry point in macro for Base because I intend to modify the ‘Content’ field in order to change the table name. I have few knowledge of javascript macro but for Base only not for Text.

#8

Maybe start with the guide I linked below.
I didn’t see any direct example for your use, so you may need to inspect the form using MRI or another object inspector.

Another route may be to extract the data to Calc via sql-command. As a macro control the complete command you could change the table.

Starting point would be this example by @Villeroy
https://forum.openoffice.org/en/forum/viewtopic.php?t=95587

#10

qa107695.odt (13.4 KB)

#9

Hi,
There is an example using a Base macro in the attached file.
OneFormSeveralTables.odb (12.9 KB)

#11

Thanks for all your help. I will test and check your files and macro.

#13

I succeed to change the name of the table with a schema prefix.
I use javascript macro and bellow is a few part of the code :

oXRowSet = UnoRuntime.queryInterface(XRowSet, oXForm );
oXPropertySet = UnoRuntime.queryInterface(XPropertySet, oXRowSet);
oXPropertySet.setPropertyValue(“Command”, newTablename.toLowerCase() );
oXRowSet.execute();

But the problem is the main GridControl attached to the table is not updatable and I can not insert data too.
The table contains primary key and there is no reason It can not be modified.

Could someone give me few code to modify a Grid Control to be fully editable ?

#14

Hello,

Sorry if i am not kin with vba. Anyhow, as long as I remember, If your Form displays a view or a query result, data cannot be modified.
On the other hand, should your Form or SubForm be in a spreadsheet mode with data directly related to a table (no constraint) you may then add or remove values as you please.

Regards

#15

This is plain wrong.

#16

Ok Villeroy, if I edit the Form or Subform properties, I can choose option, because it usually comes read-only.

I beg your pardon

#17

Data is not view not query, it is a table.

#18

If the record set is editable, it will be editable in a form as well. A querie’s result set is editable if:

  • the database driver allows to do so
  • you have the required privileges from the database engine
  • it is a parsed query, which is the opposite of “direct SQL mode”. All views are “direct SQL”.
  • the record set comes from a single table and includes the primary key. The primary key is needed to get hold of a particular record for editing.
  • under some circumstances (I’m not sure) you may be able to edit a record set made of more than one table including all their primary keys. Normally, you don’t need that feature because you can use arbitrary hierarchies of forms and subforms, each one bound to a single table.
  • Calculation results like "Amount * "VAT" AS "Price" remain read-only, of course.
#19

I found the solution.
In order to permit the GridControl to be editable, set manually the Form Properties like this:
Content : nothing , must be left empty
Analyze SQL Command : YES

Now, it is possible to change ‘Content’ by using macro and it is possible to edit the grid.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.