Search options (Page 17 of 25)

Adam, See revised.

Adam, let me address the SQL statement first. I'll have to take a look at your project later. I might suggest you find some tutorials on SQL. Once you know the syntax of SQL statements, then you just need to be able to apply them inside MVD's SQLExecute command. Here is an online site which provides tutorials and examples of basic SQL statements: https://www.w3schools.com/sql/

The SqlExecute command is simply one big string of an SQL statement inside the parentheses. SQLExecute('Insert.....');  The entire SqlExecute line can actually be on one line, but then it would stretch way past the screen edge. I put line breaks in for readability purposes. It also makes it a little easier for debugging as well.

Since the entire Sql statement is a string, then if you have text string values inside the single quotes, then the strings are placed inside double quotes (quotation marks). Text fields should be inside quotation marks while numeric values typically do not need the quotes.

The Sql Insert command defines the fields you are inserting separated by commas. The actual values being inserted are also separated by commas. That's why you see commas between the field values. If you manually inserted a row, an example might look like this:

Insert into Invoice (Counter1,Counter2,Counter3,Counter4,DBCRvalue,product,PIprice,PIRprice,SIprice,SIRprice) Values ("00001","00002","00003","00004","Purchase","ABC Product",100,40,150,10)

I rewrote the SqlExecute statement to maybe make it a little more understandable and readable to you. It does exactly the same as the one you presented except I formatted it for better understanding of the position of the quotation marks around text fields. Hope this makes sense.

SqlExecute('Insert into Invoice (Counter1,Counter2,Counter3,Counter4,DBCRvalue,product,PIprice,PIRprice,'
       + 'SIprice,SIRprice) Values ('
       + '"' + frmRefund.EdCounter1.Text   + '",'
       + '"' + frmRefund.edCounter2.Text   + '",'
       + '"' + frmRefund.edCounter3.Text   + '",'
       + '"' + frmRefund.edCounter4.Text   + '",'
       + '"' + frmRefund.edDBCR.Text       + '",'
       + '"' + frmRefund.edInvProduct.Text + '",'
       + frmRefund.edPIRpricePI.sqlValue   + ','
       + frmRefund.edPIRprice.sqlValue     + ','
       + frmRefund.edSIRpriceSI.sqlValue   + ','
       + frmRefund.SIRprice.sqlValue       + ')' );

Sorry that I didn't annotate with pictures. Just didn't have time. I'll try to look at your project tomorrow.

Adam, I had a few minutes to take a look at your project updated by Derek. Sorry, I have not had much time to get on the forum these days. The reason you are getting duplicates in the main invoice grids is as follows: The refunds table is a child of the invoice table. Since you are displaying refund fields (Specifically refund number in this case) in the main invoice grids, the tablegrids is doing what it is suppose to do by showing all the child refund data as well as the main invoice data in the grid. If you remove the PIRnum and SIRnum from the respective main invoice grids, then the duplicates will go away.

This should work.

Form1.TabSheet1.Enabled := False;

Adam, I think I have your issues resolved in the attached. The reason your Purchase Invoice counter looked like it skipped a number was because in converting it to a Sale Invoice the purchase Invoice counter still existed.  So the Max() function retrieved the Sale record with the existing Purchase counter. It actually worked as it is suppose to, but not what you intended. It needs to be cleared and that is what I put in the script. I wasn't sure if you intended to insert a new record for a sale or just change the type in the record. Your Refund insert did not work because not all fields values were defined in the statement. Since your counters can be null depending on the type, I also had to modify your Select Max() statement or it would always create an Invoice number of 1 because of the null values embedded in the table for other records types.

I'm not sure I agree with your approach with this particular project. There is a lot of redundant data in the database, especially with all the prefixes stored in every record. It is a very inefficient use of database storage. You would be better served either hard coding them in the script or creating another table with the definitions. Having said that, I do realize you are testing a lot of different scenarios with your overall project, so I won't question the design in this particular project.

Sorry Adam. I was unavailable last week and did not have an opportunity to check the forum. Don't ever think you are bothering me. I don't mind at all helping people. I'm not always available, but if I can. I will help. I think I fixed your project. One thing to note on this particular project. You really do not need the "type" combobox since you are populating the exact value through script and disabling it on the form. And since it is not a user selected combobox field, you could just display the text field (which is hidden right now). I would remove the combobox and unhide, but disable, the text box for "type" and move it into it's place on the form.

The inline editing within a tablegrid is basically a brand new feature and still in it's infancy. It's a very nice feature and I'm appreciative having it, but it could use some more work to make it more robust. I was able to code the tabbing with the tab key while editing in a cell. However, leaving a cell with the tab key does not save the contents of newly typed information in a cell, which of course defeats the purpose of tabbing to the next cell. Apparently, MVD only saves the cell info on the ENTER key or mousing out of the cell. There may be a way to do it that has not been exposed to us yet. So Dimity will have to address this. Good idea though.

One thing that I saw that could be problematic with the checkbox logic is that the "Begin" and "End" statements are missing inside the "IF" statements for each of the checkbox Onclick events. Multiple statements should be inside "Begin" and "End" statements for an "If".

I think I understand now. You are looking for a way for end users (your clients) to control queries and report output. MVD does not have this type of feature built in, but you could probably build something into your project, which could get quite complex. Or find a third-party user-friendly solution.. If I'm not understanding your requirements, forgive me and I'll let someone else give you some input. Maybe Dimitry can chime in when he gets back.

You were very close Adam. Since the convert button does a ShowRecord action, then your script for the frmInvoice OnShow should check for "ShowRecord" instead of "NewRecord". I commented your line and put in the fixed line. Technically, the "ShowRecord" check does not need to be in the "If" statement because the Form1button for "Add Sale" already implies a ShowRecord, but it does not hurt to have it there.

I think Dimitry is on vacation right now. At least that's what it indicates at the top of the forum page. There really is no manual available. It's been asked in this forum in the past. There is an online help page for some assistance on basic things with MVD.

http://www.myvisualdatabase.com/help_en/

I use the Delphi Basics site for help with functions, etc. Keep in mind that not all are incorporated in MVD.

http://www.delphibasics.co.uk/

There is a report band called "Overlay" which I think will satisfy your requirement.

Attached is a project to demonstrate it. I used MVD's sample equipment inventory project. Click on the Report button on the first form.

Just a warning - Report designer will look strange with the overlay expanded to the size you need. You will need to play around with the various objects to fit around the overlay. I suggest that during design mode you increase the size of the paper height of the report to 35 or higher so you will be ale to see objects such as page footer. When you have everything placed properly, then change the paper height back to normal. It's a little weird working with the overlay object, but it' can do what you want. I used a combination of rectangle boxes and line objects to form the grid.