ControlSource manipulation via command button!

[Filip Brnic]

A question, i have txt boxes that have no control source, and in them i store numeric values from 1-5
When i finish inserting values in those 1-17 textboxes, i want that data to be saved, the problem is i need multiple tables and its all in 1 form, basically i have a button that creates each table in the form itself, now the question is, can i just change the control source of the textboxes by doing something like (lcTableName + "." + lcFieldName) and then appending the values? because what im trying to do is have those textboxes store the information, so when i make a new table via form, and then have to store the information again, i wanna be able to just have those textboxes interactivelly change their control sourcers, so basically i want a text3 to store information of a table ExampleTable, and then when i click on a command to change the ControlSource it should display the values of it?
I dont know if you guys understand, ill explain 1 more way just in case
command button makes a table appends blank 17 times, selects the textboxes and gives them all a controlsource (idk how to do that),then when i store the information, i just click the other command button (insert) that would then store the values in textboxes inside the table itself by doing this
go top
replace examplefield with thisform.text1
go 2 replace examplefield with thisform.text2 etc etc.
so now i have multiple command buttons that all need their own controlsource since all of them create a table and need the same process to be done.

 

[Mike Lewis]

I'm finding it difficult to understand your question. It is not at all clear what you are asking. However, I will do my best to help.

First, if I've understood it right, there is no need to set the ControlSources dynamically in this way. You can set them either at design time, or at run time in the Init of each textbox or the Init of the form.

That said, you say that you "have a button that creates each table in the form itself". Do you really mean that the tables are actually created while the form is active? I can't see any reason for that. You would normally create all your tables in advance. That way, you can simply set the ControlSources for the individual fields (as I explained above). Then let the user edit the data in the normal way, after which the data would automatically be saved in the fields that the ControlSource points to.

If you did need to create the tables at run time, it would be better to do so in the form's Load, rather than while the form is active. That way, you could set the control sources at run time, with something like [tt]THISFORM.Text1.ControlSource = "mytable.field1"[/tt].

You can do all this even if the table is initially empty. You would simply append a blank record, which the user would be able to edit as usual.

The code you showed adds to the confusion. You show that you are doing multipl REPLACEs. But if the textboxes have control sources, you don't do REPLACEs. That's the whole point of the ControlSource.

Basically, it looks like you need to re-think your design.

If I have misunderstood your question, my apologies. Perhaps you could try to explain the problem more clearly.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Chris Miller]

Like Mike I don't really know where to begin.

The general idea of a controlsource is a twoway binding, it both reads from a table and it stores to a table. And I don't know if that's already news to you or if it's even the origin of your idea to not need a table at first.

What puzzles me more than the idea to inverse the usual usage of data binding is, that you want to do this to be able to create multiple tables with the same data in them, so that only needs to be entered once. Well, what hinders you to copy a table you create once, why so complicated? Even if it would work, and it's just a matter of trying it out, why would anybody do such a thing by setting lots and lots of controlsources to a new table1, then table2, when the step to produce as many copeis of the data in a table is just a COPY FILE command?

The second thing you get wrong is that youu assume you can feed multiple records of the same table with textboxes. No matter how you position them in rows, a textbox is a control that always would be bound to the current record within the workarea, that is set in the controlsource, so your binding would rather be using a grid that's a control for listing and also updating multiple rows and a whole table.

Your idea of this whole way of working being easier than first needing a table also is showing you have no idea of the simple concept of creating an unpopulated new row of a table, you simply first use APPEND BLANK and then your textboxes can be used to enter the data you actually want to save. No need to start unbound just because you don't know what to store before the user enters it. That's what blank records are for.

There are technical reasons to not be able to use append blank, as default values may not be sufficient to have an overall valid record, so an APPEND BLANK would trigger exceptions breaking table or field rules or vialoate uniqueness of indexes. That's going into the topic of the art of defining your tables, though and just needs the right design to work.

I still wonder why you would need many copies of the same data in multiple tables. I don't want to bore you with database theory, but one core aspect of a well designed database is to prevent redundancy, in simple words prevent any double data.

You explicitly want a form with it's current input values to be a template for creating tables. Why at all? You don't need to explain, actually, but that queston should make you think if you can't get what you actually want another way.

Chriss

 

[Chris Miller]

To also give some direct answers, that you could also deduce from what I already wrote

1. A controlsource has to refer to something already existing, as the first step is not to write the contorol value to the controlsource, it is, as the name also suggestively says the source of what is displayed in the control. And writing your change back to the source of the control is only a secondary feature, even though that's what the more known feature, not only judging by your understanding.

2. I tried, and you can switch in many ways, from unbound to bound, from bound to one table to another, but the first step after switching is reading from the controlsource. So you can't get what you mainly want, first write a value unbound and then write it to a controlsource you set, the controlsource will first overwrite the entered value.

Both of that and mainly point 2 means your idea is not fruitful in any way. It wouldn't even work for the first table you bind. So the essence is your table has to exist first and be the controlsource to use controls to change values of records. So APPEND BLANK is the way to get to a record you can bind to, it's not the only way, though.

You can't change that behavior of the controlsource to enforce first writing in the control value, that step of writng always and only happens when the valid event happened and confirms the validity of the value by returning either .t. or nothing (which returns .t. anyway). And the valid event is triggered by leaving the contol, so after you entered and either move to the next textbox or click a button and thereby move the focus from the textbox. TAB or in case SET CONFIRM is ON also ENTER can move the focus, too. The major point is not every single keystroke you do also changes the table data, that's all only within the control until you finally cause the wtite back to the field specified in the controlsource by leaving the control and setting focus to the next or any other control (ie also by clicking another control and focusing it that way).

So let's come to the next point, then how do you store something in an unbound control into a table? The usual way to store data from an unbound control into a table is building up an INSERT SQL statement that has the values of such unbound controls within the values clause of the insert statement. And once you have that it's also only a matter of changing the table name to redo the same insert into another table. For example by using macro substitution, building an SQL string inside a string variable, executing it by macro substiutution, change the table name to another table and then repeat that.

Use COPY TO or COPY FILE or, of coure, CRETE TABLE to create the second table, depending on the exact situation, maybe COPY TO is the better and more versatile way of copying specifically tables. But you may also first create an empty secondary table and then use APPEND FROM the original DBF maybe also using the FOR clause to only copy partial data. Many ways to copy data, just not the one you thought of, sorry. There's also insert-SQL without a concrete list of values but instead using a select as part of the insert, so insert into tableB select * from tableA also is a way to copy a whole table with some implications about the tableB not being created by this. For both filling and creating a second table you could do Select * from tableA into table tableB. That, on the other hand, is never inserting into an already existing tableB but is creating it, becuase the INTO clause of SELECT is for specifying what to create, an array, a cursor or a table file.

Confusing, isn't it? But your idea was confusing me, on the other hand. How would you even consider it easier to populate a table or a second table by needing to switch many controlsources, if there are COPY commands, both COPY FILE and COPY TO. There's even one more: COPY STRUCTURE to copy the structure of a table. Yet another way to first create a table and then use one of the many ways to insert data into it. When the goal is to copy something, why not search for COPY in the help? You'll be put on one of the paths to a solution.

Chriss

 

[Chris Miller]

I also want to bring up another topic besides whether it's feasable or not to change the controlsources at runtime.

It's much more difficult with grids, though also not impossible, but I wonder why you want to have that switch. I know you made clear to want to populate new tables, I wonder if that's all about that. It brings me back to stating there's no good use of copies of data, usually.

Let's assume you want to switch between tables and you think you best use separate tables for separate lists, something like recipes, list of components, would be something you could put into separate tables and switch betwenn them. Well, let me point out that tables are not meant to be documents, you rather would sstore ingredients of all recipes into one large table and then filter for only the few ingredients of one recipe, so you never need to switch controlsources, no matter of which controls, what you switch is which records to display or not. And then it's best practice to also never actually bind a table to controls directly, query just the records currently necessary into a cursor and display that cursor. the table is for long time persistence/storage of the data, not for the direct display of it. For that you can always use cursors.

That's a crass restriction. Of course, as with any rule, there are exceptions, but don't let the exception become the norm.

As it's quite hard and cumbersome to change controlsources, even if it is just the one major controlsource of a grid specifying the table it should bind to, it's not the easiest way to split up data into multiple tables and let forms switch between tables. It's far simpler to switch between groups of records. Be it by querying the part of the data to currently display, be it by a SET FILTER. That's how controls are designed for, too. One other hint that points out a controlsource is not designed to change is the forms bindcontrols property.

Bindcontrols was introduced quite late, but it's not an on/off switch for data binding, it is designed to once switch the batabinding mechanism from the off statte to on and then keep it on until the form releases. It means the form can handle anything necessary to make the controlsources available not only in the form data environment or the load, but even include interactivity with the user before the data binding finally is turned on.

But again, even that switch on would read from the controlsources as first step and so that still is not a way to predefine a textbox controlsource, let it start unbound, let users enter values in the unbound state until a button click switches the connection to the controlsource on and saves the entered values, that won't happen.

The way this all is designed points out controlsources are meant to be fixed and it's not a big restriction, as you always can manage what part of the data is shown. Most controls only show one current record anyway and you switch to other data simply by moving within the rows. Many of the controls that show a list, like listbox, combobox or grid enable not only to select but at the same time navigate to the selected row and thereby also let all the single value controls switch to that, and that's how programmers use these controls to offer data navigation to users, for example.


Chriss