Updating a single record using a macro in a form

[youcanread]

Hi, I'm creating a database of books that we have in our bookstore, using OO 3.3 on WinXP. When a book sells, I want to be able to hit one button that will 1)update the "last sold" field with the current date, 2) check of with a ^ that one sold in the "stat" field, and 3) update the total number of copies of this book we've sold -- "sold".

Using SQL I know how to update each field with an arbitrary WHERE clause:

Code: Select all

UPDATE "Catalogue" SET "last sold" = CURRENT_DATE WHERE "stat" = 999;
UPDATE "Catalogue" SET "stat" = ("stat"+'^') WHERE "stat" = 999;
UPDATE "Catalogue" SET "sold" = ("sold" + 1) WHERE "stat" = 999;

But how can I turn this into a macro, with a WHERE clause that refers only to the current record open in the form?

This is what I have so far:

Code: Select all

Sub updatesale
Dim oStatement
Dim oStatement2
Dim oStatement3
Dim oStatement4
   oStatement = ThisComponent.DrawPage.Forms.getByIndex(0).ActiveConnection.createStatement()
   oStatement.executeUpdate( "UPDATE CURRENT RECORD SET ""last sold"" = CURRENT_DATE WHERE ""stat"" = 999" )
   oStatement2 = ThisComponent.DrawPage.Forms.getByIndex(0).ActiveConnection.createStatement()
   oStatement2.executeUpdate( "UPDATE CURRENT_RECORD SET "sold" = ("sold" + 1) WHERE ""stat"" = 999" )
   oStatement3 = ThisComponent.DrawPage.Forms.getByIndex(0).ActiveConnection.createStatement()
   oStatement3.executeUpdate( "UPDATE CURRENT_RECORD SET "stat" = ("stat"+'^') WHERE ""stat"" = 999" )
End Sub

But when I try to use this macro it highlights this part of the code: oStatement2.executeUpdate( "UPDATE CURRENT_RECORD SET " and says, "BASIC syntax error. Parentheses do not match."

So, first, what's going wrong, and second, how can I change my WHERE clause to refer to only one record at a time?


If anyone can help me with this I will really appreciate it!

[Nocton]

I think that your "" are probably not quite right and in any case I try to avoid SQL within macros.
The following code may be of help. It updates a field called 'DateUpdated' on a form. Set it as the Before Record A.ction event, to update the field if any data/field on the form is changed.

Code: Select all

Sub onUpdateRecord( oEvent as object )
' to update date field with current date
dim oDataForm, oStatement, oResultSet as variant
if oEvent.Source.SupportsService( "com.sun.star.form.component.DataForm" ) then
	if oEvent.Action = com.sun.star.sdbc.ChangeAction.UPDATE then
    	oDataForm = oEvent.Source
        oStatement = oDataForm.ActiveConnection.CreateStatement
        oStatement.EscapeProcessing = False
        oResultSet = oStatement.executequery( "CALL CURRENT_TIMESTAMP" )
        oResultSet.next
        oDataForm.updateTimeStamp( oDataForm.FindColumn( "DateUpdated" ), oResultSet.getTimestamp( 1 ) )
	end if
end if
BadgeName_update
End Sub

Alternatively, this code will update a field in the way that perhaps you want.

Code: Select all

Sub FieldName_update 
dim oForm as object
oForm=ThisComponent.Drawpage.Forms.getByName("Form1") 
oForm.getByName("txtName").text = "SOLD"
oForm.getByName("txtName").commit 
End Sub 

I'm in a rush, but I think I have the above correct - they are adapted from working code.

Regards, Nocton

[youcanread]

Thanks for your response, Nocton! I didn't realize anyone had responded to this because I didn't get an email for it.

Anyway, I tried using the first code you gave me, but I can't seem to get anything to happen. I only changed "DateUpdated" to "last sold" because that's the name of the column and the field. The push button is in a subform that shows information about the record. I don't see an event for the push button called "Before Record Action" but when I use "approve action" I get lots of error messages, and when I use "execute action" nothing happens at all. Is this why it's not working?

For the second code you gave me, do I change "txtName" to the name of the text box field that I want to update? I did that, but as with above nothing happened.

Thank you for your help with this,
Lindsey

[Nocton]

You are using the first code with a command button, so that is why you cannot see the required events. Because it is a record action we want, it needs to be executed at form level. So go to the form's events and then you will see the Before record action. I expect you realised that BadgeName_update should be deleted as that refers to a sub that you don't have/need.

For the second code, yes just replace txtName with your control name (Remember it is the name of the control, not the name of the field the control is assigned to, although often they may be the same). I assign the macro to the When receiving focus event of the text box to be updated.

By the way, I suggest that you avoid spaces in field and control names as they often cause problems and require extra apostrophes (' or ") in code lines and SQL strings so that the space does not act as a delimiter/break. If you want a break between the 'words' then best to use an underscore as in last_sold.

Regards, Nocton

[youcanread]

Ah, ok, I understand. I set up that second code the way you said, but now it has an error and highlights this line in the macro

Code: Select all

oForm=ThisComponent.Drawpage.Forms.getByName("subform")

Is that because it's a subform instead of a main form?

The timestamp code works perfectly. Thanks a lot.

[RPG]

Hello

A subform is part of the mainform. Or in other words: a sub form is part of the form what is one level higher as the actual subform. This is the same as for maps on your hard-disc.

Code: Select all

oForm=ThisComponent.Drawpage.Forms.getByName("MainForm").getByName("Subform")

When you can use getbyname you can use most of the time also hasbyname. Hasbyname returns a value TRUE or FALSE if the name exist or does not exist. Names are case sensitive most of the time.

Romke

[youcanread]

Also, going back to my original question, is there away to fit this part of the code in somehow?
UPDATE "Catalogue" SET "sold" = ("sold" + 1); That's it in SQL, but if there's some way to add it to the timestamp code that would be best. I just need the field "sold" to add 1 to the current number when I update the record.

Thankyou!

[RPG]

Hello

Do you know that there is a SQL command what is called count.

Romke

[youcanread]

no, should I use that instead of SET? I don't want to use SQL though, I want it to fit it into the code above from Nocton for updating with a timestamp.

[RPG]

Hello

I'm not so good in teaching how to solve your problem. I think before you start to solve your problem with macros and SQL start learning how a database is working. I have learned in the same hard way as you start now. But when you want work with a database then the most difficult think is to define your tables. When you have designed your tables then split those things you want do in:
a) Input part.
b) Showing the result.

Doing it in the same form is most of the time not a good idea.

Before you can work it ou you need to understand the tools you use in this case OOo. more precise you need to understand OOo-base, the forms and the controls. You need also to understand the SQL lanuage of the database engine you use.

Forms are for displaying the data you collect with SQL. When you understand in detail all the properties of OOo-base, forms, controls and SQL and you cannot get the data you need then maybe it is time to start with macros. The reason is when you have a good knowledge of OOo-base, forms, controls and SQL then it is more easy to write good macros. But be prepared learning about the API does means you have to understand much more little details. Little details you do not expect so the learning curve is steep.

I hope this helps you

Romke

[youcanread]

Ok, forget what I was saying about SQL, i was just using it to explain what I wanted to do. I just want to know how to update a single record using a macro: input is all the form should do. When I update the record I want one of the fields (number-sold) to add 1 automatically to the total of that record only. Can anyone help me with that? Is there a way to add that action to this code?

Code: Select all

Sub onUpdateRecord( oEvent as object )
' to update date field with current date
dim oDataForm, oStatement, oResultSet as variant
if oEvent.Source.SupportsService( "com.sun.star.form.component.DataForm" ) then
   if oEvent.Action = com.sun.star.sdbc.ChangeAction.UPDATE then
       oDataForm = oEvent.Source
        oStatement = oDataForm.ActiveConnection.CreateStatement
        oStatement.EscapeProcessing = False
        oResultSet = oStatement.executequery( "CALL CURRENT_TIMESTAMP" )
        oResultSet.next
        oDataForm.updateTimeStamp( oDataForm.FindColumn( "last-sold" ), oResultSet.getTimestamp( 1 ) )
   end if
end if
End Sub

[Nocton]

Try this code. As RPG has said, if you have a subform you must reference that under the main form. For clarity I have separated his code line into two.
We have MainForm and its SubForm; Table1 with integer field NoSold; text box control on subform named txtNoSold.
Attach this code to a button's 'Mouse button released' event and every time you click on the button the test by will increment by 1. You should then be able to adapt to suit your situation.

Code: Select all

Sub NoSold_update
dim oForm1, oForm2 as object 
dim sNoSold as string
oForm1=ThisComponent.Drawpage.Forms.getByName("MainForm")
oForm2=oForm1.getByName("SubForm")
sNoSold=oForm2.getByName("txtNoSold").currentvalue
oForm2.getByName("txtNoSold").value = str(val(sNoSold)+1) 'this line changes the text box
oForm2.getByName("txtNoSold").commit 'this line commits the new data to your table/record source
End Sub  

Regards

Nocton

[youcanread]

Thankyou! I think this is just about what I want to do. I tried it out, but it said "Basic runtime error. Property or method not found: value" while hilighting this line: oForm2.getByName("txtNoSold").value = str(val(sNoSold)+1) 'this line changes the text box. I don't know if it makes a difference, but I changed "sNoSold" to "stt." Thank you, thank you for helping me work this out.

[Nocton]

Do I understand that with you have now fixed the error? If you change sNoSold to stt then you must also change the Dim As String statement.

[youcanread]

Ah, no, it's still causing that error message. I did change sNoSold to stt in every place it occurred.

[Nocton]

I cannot understand it as it works perfectly well with me.
For your convenience I have attached the test form I tried it out on.

Regards,
Nocton