button: append once update second time

[werder101010]

I found this code in a post made by JerryBrightbill:

rec_num = recno() 'get record number of current record

tbl1 = table.open("sourcetable") 'open a new instance of the same table
tbl1.fetch_goto(rec_num) ' find the current record in the new instance of the table

tbl2 = table.open("destinationtable")
tbl2.enter_begin()
tbl2.destination_field1 = tbl1.source_field1
tbl2.destination_field2 = tbl1.source_field2
' and so on until you have filled all of the fields in the destination with values from the current record

tbl2.enter_end(.T.)
tbl1.close()
tbl2.close()

I use this code to copy two fields to the invoice table outside of the set. The invoice table contains a third field for the invoice number with auto increment turned on. The code is activated with a button (onpush event) on a form (the source). Everything works fine but when the button is pressed twice it also produces two invoices. I'm looking for some code to prevent this. When the button is pressed the second time it should perform an update instead. Is there an easy way to do this?

[Stan Mathews]

Re: button: append once update second time

When the button is pressed the second time it should perform an update instead.

What does this mean? To performs some sort of update you would have to find a previously entered record and change it.

As posted the code uses .enter_begin() and .enter_end(). You would have to add something to find the desired previously entered record and use .change_begin() and .change_end().

[werder101010]

Re: button: append once update second time

Maybe we can first focus on the part of my question you do understand. Is there some code that could prevent making double invoices for the same record?

[Tim Kiebert]

Re: button: append once update second time

Try something like this

rec_num = recno() 'get record number of current record

tbl1 = table.open("sourcetable") 'open a new instance of the same table
tbl1.fetch_goto(rec_num) ' find the current record in the new instance of the table
vCurrInv = tbl1.InvoiceNumber    'use you actual invoicenumber field here

tbl2 = table.open("destinationtable")
tbl2.order("InvoiceNumber")  'Order the table because fetch_find in the next line needs to
                             'have the table ordered on the field in which it is looking
                             'Substite your actual invoicenumber field here for table two.

If tbl2.fetch_find(vCurrInv) > 0 then                 'If current invoice is found in table 2 then the record
    tbl2.change_begin()                               'number is returned which is greater than 0 so table 2
    tbl2.destination_field1 = tbl1.source_field1      'is put in change mode.
    tbl2.destination_field2 = tbl1.source_field2
    tbl2.change_end() 
Else                                                  'If invoice number is not found then the record number
    tbl2.enter_begin()                                'with the nearest match is returned but as a negative value
    tbl2.destination_field1 = tbl1.source_field1      'so it will be less than 0 which cause the table to be put
    tbl2.destination_field2 = tbl1.source_field2      'into enter mode.
    ' and so on until you have filled all of the fields 
    'in the destination with values from the current record
    tbl2.enter_end(.T.)
end if

tbl1.close()
tbl2.close()

[Tbaker]

Re: button: append once update second time

You could add a logical field to your source table. When you push the button you would have to add code to check if the field is blank then you proceed with the code copying the records from table1 to table2. If the logical field is set to true you do nothing (or give a message that the copying has already taken place. Then at the end of the code for copying you would set the field in the source table to true meaning that the records has already been copied once. That way if you push the button the second time the field would have been set to true and the copying would not proceed.

Hope I made that clear enough. I'm sure you will hear other ways of doing this.

FWIW

Tom Baker

Edit: I see that Tim Kiebert has already posted a solution.

[werder101010]

Re: button: append once update second time

Edit

[Stan Mathews]

Re: button: append once update second time

You've been given two options which approach the same question in different manners. I imagine these two answers are based on the same question I had about what you wanted.

Tom's approach is to prevent two invoices based on the same source record (composite set record) based on a flag being set. This would mean that the source invoice could not be edited and re-invoiced.

Tim's approach assumes you might want to edit the source invoice and update the re-copy it to the invoice storage table.

So the question of deciding which option to use depends on what you really wanted originally. Do you want to prevent the second press of the button from creating an invoice or do you want to allow the user the chance to edit an invoice and change the record in the invoice storage table.

I say this not to pick apart your question in any petty way. We just needed to know more details in order to get you what you want. If my previous post was taken as derogatory, I apologize. I do tend to be brief.

[werder101010]

Re: button: append once update second time

Thanks Tim and Tom,

I would like to start with Tim�s solution. I probably did something wrong because I�m still able to produce double invoices. I attached a small example to test the code. Maybe you can tell me what I did wrong?

[werder101010]

Re: button: append once update second time

Thanks Stan,

I�ll have to read everything slowly so I understand what has bin said. I�ll get back to you.

[werder101010]

Re: button: append once update second time

Hi Stan,

When I edit the source table I would like to update / re-copy it to the invoice storage table. So Tim�s solution would cover it all.

[Tim Kiebert]

Re: button: append once update second time

Werder

You need to change the I_nr field to 'User entered' from 'Auto increment' in field rules and add the following line in the 'enter' section of the code.

tbl2.I_nr = vCurrInv

or

tbl2.I_nr = tbl1.S_nr

[werder101010]

Re: button: append once update second time

This is really great and it now work's perfect! Tim thanks for the code. I only added �topparent.commit()� to the script because otherwise the update function would not work. At least not while being on the form in the changed record itself. The attachment contains the final result for people who are looking for the same solution.

[werder101010]

Re: button: append once update second time

I sometime receive the error message "record number is past end of table". The message appears after pressing the button on the form. Does anybody know what this messages means?