How To Insert Into Table Values From Textboxes And Cursor? 1

[wrgt9740]

Hi. I'm making a customer purchase form with textboxes, a cursor, and a table.
The textboxes are txtInvoice and txtName.
The cursor is csrPurchases with fields "ITEM" and "PRICE".
The table tblTransactions has fields "INVOICE", "NAME", "ITEM", and "PRICE".
Is it possible to get values from these textboxes and cursor, and insert or append them into the table?

An example would be a customer buying supplies. txtInvoice is 11111, txtName is "BOB". Customer's purchases will be recorded into csrPurchases, like the following:
PENCIL, 1.00
BALLPEN, 2.00
ERASER, 5.00

How can I insert all data from textboxes and cursor into tblTransactions? I'm hoping to have the following records in the table:
11111, BOB, PENCIL, 1.00
11111, BOB, BALLPEN, 2.00
11111, BOB, ERASER, 5.00

Thank you for your time.

 

[Chris Miller]

As direct answer to your question you'd make the fields of the tblTransactions the controlsources of the textboxes and then their values are both displayed by the textbox and stored to the fields. That's surely not the full answer, but I guess it's the major thing you're needing to learn about controls.

Your design is off of a norm, you'd usually have a table of customers, a table of orders and a table of order items and they'd all be related to each other. Then you don't store the name "BOB" into every transaction line, but the id of the customer bob. Also not the item name but the id of an item or product, etc. The transaction table would also usually only have 1 record per transaction and point to the order, where the order details are pointing at, so there is no need to repeat all this, the transaction is for the whole order and details are already recorded there.

What a form would mainly record as a customer adds itmes to an order is the orderdetails or orderitems.

You find two sample databases that have the necessary data structure coming with foxpro as tastrade.dbc and northwind.dbc Both Tastrade and Northwind are folders in the directory Home()+"samples".

Chriss

 

[Chris Miller]

But let's say you want to get there slowly, don't want to go into all details and also more complex forms necessary to provide a product search, product listing, picking a product into a shopping cart to let that become order items, etc.

You want a form textbox to be fixed with a name and with every new item added to tblTransactions you want that name to be in that record. Well, then the direct answer to that would be to have your txtName unbound, because it should not change due to navigating in records, and it should not become empty just because you APPEND BLANK to tblTransactions...

Well, you do [tt]INSERT INTO tblTransactions (Invoice, Name) Values (thisform.txtInvoice.Value, thisform.txtName.value)[/tt] and then have the textboxes for Item and price bound to the item and price fields per controlsource property of these textboxes. So the txtInvoice and txtName provide current default values for the two fields of tblTransactions.

Usually default values of fields are defined in the table definition, but you will never ever define your table with DEFAULT thisform.txtName.value. So that's not the way to do it. And that also points out that you don't structure data that way that some template/default value has to come from a form control.

A best practice for numbers like invoice numbers that should be unique and perhaps sequential is generating them automatic, and that's the job of the database, that number should not come from a txtInvoice.Value where it's entered, that should come from an autoinc integer field, for example. And the thing about these generated numbers is they are only generated once. Therefore you have one major transaction record and while multiple rows belong to the same order they are stored separately, so there is only one main record and several related detail records.

Edit: The reason I tell this is, your question seems to ask how to get the repeated invoice number and name from the form, and well, you simply read them in, but in a normalized data structure you never have the need to store the same data twice. The only things that repeat are references, all orderitems point to their order by a reference. And these references are keys. primary/foreign keys. The primary keys are non -repeating unique values that identify one and only one row of a table and for reference of multiple detail records in other tables they are stored repeatedly in foreign key fields, which by their name point to a "foreign", or simply "another" table, to one exact record there.

Chriss

 

[wrgt9740]

Hi Chriss, thanks for your great reply. This will serve as my guide in making the ideal database design.

I'll take a look into your solution. I'll just need some time to think on how to apply it on my form.