[Solved] Open New Form to Current Record

[pastorjeremywilson]

Well, I've been pouring over the forums and doing Google searches to try to avoid duplicating a question, but I haven't found anything quite like what I'm trying to do.

I'm creating a database of congregants with their contact info and membership status. Eventually, my main form's controls will be read-only (to prevent accidental editing, etc), then I'll have a new form open up when I want to edit a particular person, like the example below:


Now, to open the new form, I've got that "Edit Contact Info" button pointing to this macro:
REM *****Open Contact Data Form*****
Sub OpenContactForm
Dim sNewDocumentName as string
sNewDocumentName="LBCN Membership Database Contact Form"
ThisDatabaseDocument.FormDocuments.getbyname(sNewDocumentName).open
End Sub

What I want to be able to do is have "LBCN Membership Database Contact Form" open to the particular record that is currently showing in the main form, "LBCN Membership Database Form". Right now, it always opens to the first record in the table, presumably because the macro isn't telling it to do any different. Is there some simple code I can add to my macro to force the new form to open at the currently displayed record?

P.S. I'm somewhat new to OpenOffice Base, and only have a basic understanding of programming macros, so you might just as well assume I won't know what you're talking about unless you spell it out for me.

[MTP]

A quick note on Base's confusing use of the word "form" (in case you haven't come across it):

A "form document" is the entire file, and by default is a Writer file. When your macro code does

Code: Select all

oNewDoc = ThisDatabaseDocument.FormDocuments.getByName(sNewDocumentName).open

(notice I added a name and an "=" in front of your code) that code is working with a "form document".

A "form" is a collection of controls inside the document. So you'll need code like

Code: Select all

oNewDoc.DrawPage.Forms.GetByIndex(0)

for your macro to access the "form" and change the record number. You might look at this other recent thread Form always opens first record for some relevant code snippets.

[pastorjeremywilson]

OK, I must not be putting that code in the right place in my macro. First, I changed my macro to where I replaced the "ThisDatabaseDocument.....open" with the oNewDoc.DrawPage... code, so that it looked like this:

REM *****Open Contact Data Form*****
Sub OpenContactForm
Dim sNewDocumentName as string
sNewDocumentName="LBCN Membership Database Contact Form"
REM ThisDatabaseDocument.FormDocuments.getbyname(sNewDocumentName).open
oNewDoc.DrawPage.Forms.GetByIndex(0)
End Sub

That kicked out an error, so I tried putting it in like this:

REM *****Open Contact Data Form*****
Sub OpenContactForm
Dim sNewDocumentName as string
sNewDocumentName="LBCN Membership Database Contact Form"
oNewDoc = ThisDatabaseDocument.FormDocuments.getbyname(sNewDocumentName).open
oNewDoc.DrawPage.Forms.GetByIndex(0)
End Sub

That didn't give me an error, but it still opens only to the first record. Thank-you for the link. I tried to understand what was going on there, but there was so much code that I'm not familiar with there that I wasn't able to see how it applied to my situation.

[MTP]

Your second example (with no errors) is the correct next step.

Now, you need to add code for your macro to lookup the ID of the person you want to edit. To start with, you'll need to get the form (collection of controls) that has the ID. If your form document only has one form (one mainform, no subforms), this will work:

Code: Select all

SourceForm = ThisComponent.drawPage.Forms.GetByIndex(0)

If your form document has multiple forms (such as a mainform and a subform), you'll need to know the form name. You can see this in the Form Navigator window.

To open the Form Navigator, first open your form document in edit mode (right-click on the form name in Base and choose "Edit"). Make sure the toolbar "Form Design" is visible (should be a checkmark next to "Form Design" in View→Toolbars; if not, click on it to make the toolbar visible). The icon for the Form Navigator is the sixth from the left or top and looks like a rectangle with a compass in the upper right corner.

Once you've found the form name, replace my word FormName with the actual name of your form:

Code: Select all

SourceForm = ThisComponent.drawPage.Forms.GetByName("YourFormName")

Now that you have your form (whether just by getting the only form or by name), you'll get the ID number. I see in your image a field labeled ID - you'll need to look up the name of that field in the Form Navigator. For this example, let's say that is a text box and its name is "Text Box 1" (replace that text with your actual name):

Code: Select all

ID = SourceForm.getByName("Text Box 1").Text

If the field is not a text box you might have to have something different instead of .Text

OK, now you have your ID number, you can open the new form document, get the form inside the new document, and finally we get to filtering the new form to only show that one ID. Make sure to replace YourTableName and YourIDColumnName with the actual names of your table and ID column:

Code: Select all

NewForm = oNewDoc.DrawPage.Forms.GetByIndex(0)
NewForm.Filter =("YourTableName.YourIDColumnName = " & ID)
NewForm.ApplyFilter = True
NewForm.reload()

[pastorjeremywilson]

That worked like a charm. Thank-you, thank-you, thank-you! I had to tweak things a little bit as I was getting an error when it was trying to grab the ID for the Filter command, but I found out that it didn't like that my table name had spaces in it. Once I renamed the table and changed the data source for the forms, it worked.

Thank-you for walking me through this step-by-step. I'm learning as I go!

I might just as well paste my final macro here just in case someone stumbles along and wants to see what it looks like:

Code: Select all

Sub OpenContactForm
	Dim sNewDocumentName as string
	sNewDocumentName="LBCN Membership Database Contact Form"
	oNewDoc = ThisDatabaseDocument.FormDocuments.getbyname(sNewDocumentName).open

	SourceForm = ThisComponent.drawPage.Forms.GetByIndex(0)
	ID = SourceForm.getByName("txtID").Text
	NewForm = oNewDoc.DrawPage.Forms.GetByIndex(0)
	NewForm.Filter = ("LBCNMembershipDatabaseTable.ID1 = " & ID)
	NewForm.ApplyFilter = True
	NewForm.reload()
End Sub

[MTP]

So glad you got it working

[martinbone]

I am trying to do the same thing in my own database.

However, Openoffice doesn't seem to like this line (it says column not found ...) :
NewForm.Filter = ("XXXDatabaseTable.XXXDatabaseColumn = " & ID)

It seems to think that the ID (eg. SENE001) ID is part of the column name it's looking for. Has anyone else had this problem?
FYI - my ID column isn't integer - it's text - eg. "SENE001" is one of the unique ID's in that column ... Is that the problem?

Would be grateful for any help.

[martinbone]

It looks like it was a problem with not using an Integer ID. I used another field for the ID (that was integer) - and it worked fine.

I am trying to do the same thing in my own database.

However, Openoffice doesn't seem to like this line (it says column not found ...) :
NewForm.Filter = ("XXXDatabaseTable.XXXDatabaseColumn = " & ID)

It seems to think that the ID (eg. SENE001) ID is part of the column name it's looking for. Has anyone else had this problem?
FYI - my ID column isn't integer - it's text - eg. "SENE001" is one of the unique ID's in that column ... Is that the problem?

Would be grateful for any help.

[Randomxnp]

I have used the code from Pastor Wilson's comment successfully, on a form with no sub form (Form_select_band in the upload). Thank you very much for that Pastor.

When I try to use similar code in a form with a subform I get the error

BASIC runtime error.
An exception occurred
Type: com.sun.star.container.NoSuchElementException
Message: .

and highlight in the code

SourceForm = ThisComponent.drawPage.Forms.GetByName("SubForm")

The whole Macro is:

sub ContactToAction
Dim sNewDocumentName as string
sNewDocumentName="Form_action"
oNewDoc = ThisDatabaseDocument.FormDocuments.getbyname(sNewDocumentName).open

SourceForm = ThisComponent.drawPage.Forms.GetByName("SubForm")
ID = SourceForm.getByName("ID").Text
NewForm = oNewDoc.DrawPage.Forms.GetByIndex(0)
NewForm.Filter = ("Table_action.BandID = " & ID)
NewForm.ApplyFilter = True
NewForm.reload()
end sub

This is in line with MTP's instructions (thanks MTP) for a form with multiple forms. I have checked navigator and the form name is SubForm. I have tried changing this and using the new name in place of SubForm in the code.

A version of the database with fake information is uploaded below. It is a new database I am writing, so much is not finished yet, and I am learning to use Base as I go along so it has not been debugged from some mid-course adjustments. However this part should work. It is to be used by someone uninterested in computers, so I am trying to make it all as simple and foolproof as possible with navigation by buttons.

The form in question is "Form_contact" and the Macro is Standard.Open_filtered.ContactToAction (document, Basic).

Thanks for all help

[Villeroy]

The database is small enough to show all information related to a selected band in one window.

[Randomxnp]

The database is small enough to show all information related to a selected band in one window.

This form is intended for adding a new record. It will not show any existing records (I have worked out how to manage that). Forms to deal with existing records will either use tables or another stage of filtering, I've not yet decided.

[Villeroy]

Enter a new band with related info? You can build a tree of forms and subforms for new records only. It is important that you fill the forms in the right order, the n-side of a relation after the 1-side, band info first then the related contacts and actions of the band.
If one band is supposed to have many contacts and actions, you need another table design anyway. With the current design, every contact and every action belongs to one band.

[Randomxnp]

That is the correct structure. Each band can have many contacts and actions, but each action or contact only relates to one band. The info will come in over time, often long after the band details have been entered.

The typical use is to enter a band's details, then on first contact with the band certain details will be discussed (information field in "Table_contact") but at the same time various actions are agreed upon, either by band, us or a third party, which might need tracking to ensure they are completed on time. These are to be saved Table_action referenced back to the relevant band. Subsequent contacts will also be recorded, and where necessary actions added on those contacts.

However thanks to your questioning I think I have hit upon a problem. I don't think I can filter to get a new record, as there is no data, it being a currently-unwritten record. I will probably have to amend Form_action to show all data (currently it only allows a new record of Table_action) just not allow it to be amended, and maybe have some code to select a new record.

But that still leaves me with the problem above unsolved. Any Idea what is wrong with my code?

Thanks a lot.

[Villeroy]

A subforms new record inherits the dependent field values from its parent. After you saved a new band record, you go to the new contact subform and enter the contact info. Then you go to the new action subform and enter a new action. The band-IDs for the new action and contact come from the parent form.

Generally speaking, I do not recommend any macros with Base forms. I know well how to write them however since many years I do not use more than 10 or 20 lines of code for my databases. With macros disabled every form can be used with a few more clicks.

[Randomxnp]

I don't want it all on the same form, as it gets cluttered on the screen if I am going to have multiple subforms. I want a new form to open up but with the same band selected. If it was for my own use I would not be bothered, but the main user needs something working by pushing obvious buttons as far as possible. In this case she will be entering the information on that contact with the band at the time the information needs to be entered, so I want the button to be on that form.

As far as I can see the code I used should be able to do what I need. Can you see why I am getting an error?

[Villeroy]

I just notice that all your forms are generated by the wizard. They have no more than one subform and no list boxes. The stupid form wizard does not cover more than 10% of the capabilities. It can help to get a form design started but it never leads to a satisfactory result.

[Randomxnp]

I've amended some beyond what the Wizard will do, although I am still learning, hence there are some changes I want to make like using list boxes that I have not got round to yet. I had worked out how to make more than one subform, but for the particular user I have tended to use the wizard because I want to keep each form as simple as possible, and use big buttons for navigation. That is why I have chosen to work this way.

[Villeroy]

_NewBand takes a new band and optionally a new contact and new action.
_action shows all actions with their bands and the contacts of that band.
_contact shows all contacts with their bands and the actions of that band.

I deleted the other forms because the file was too big to be uploaded to this forum (128 KB)

[Randomxnp]

No. As I said that is not he structure I want. Thanks for trying to help simplify things, but that is useless for the purpose. I am not making this complicated just to make it hard for myself!

Thanks for all your efforts trying to help, but my database structure is developing how I want it. I just want help with the code.

[Villeroy]

You don't even understand the structure of your form document, let alone the API.

ThisComponent.drawPage.Forms.GetByName("SubForm")

Does the collection of forms in ThisComponent's draw page have an element "SubForm"? The form navigator tells you the truth. Without having ever built up a form manually without wizard, you do not even know what the form navigator is. Where can we start to explain things? On API level? Certainly not.

[Randomxnp]

I had already looked in the form navigator, as per MTP's instructions. That says there is a form element called "SubForm", which contains the element I want. That is the problem I was having. Here is the Navigator panel:

[Villeroy]

ThisComponent.DrawPage.Forms has an element "MainForm". That one has an element "SubForm"

[Randomxnp]

Thanks a lot! You have solved the problem.

It should be ThisComponent.DrawPage.Forms.MainForm.GetByName("SubForm").

This brings up the other problems I had expected, but this code itself is good. Cheers!