[Solved] Auto Fill Form Using ID?

Discuss the database features
Post Reply
User avatar
Giant1985
Posts: 7
Joined: Sat Jul 28, 2012 7:02 am

[Solved] Auto Fill Form Using ID?

Post by Giant1985 »

Ok so yes I am a noob at Base. I have made this form which I will provide screenshot for because I can not find anything on the net software wise that does what I need. I need to be able to put in a claim number that we already have written down and the details to go with it. Already have that going on another form for entering a new claim. Now I am making a form where I can bring up the already entered details into the table by searching ClaimNo. Problem is I can't get the form to do it.

I have added a listbox and it popluated the form with the first claim on no worries. Then I clicked the second claim on the listbox and it only changed the ClaimNo and left the rest of the details the same as the first one. The listbox has since been removed.

I would really prefer to have a search box where the ClaimNo is entered and you hit submit then the rest of the form is filled with the corresponding info from the table so it can be updated.

I have searched the forums and I have searched Google. Everything I come across doesn't seem to fit into what I want from my understanding. I fear I may just need to add some kind of macro but don't know where to start there either. Any help here would be great as it's the last thing I need for this to be ready to use. Thanks in advance.
existing.png
existing.png (7.82 KiB) Viewed 7836 times
Last edited by Hagar Delest on Sun Jul 29, 2012 11:04 am, edited 2 times in total.
Reason: tagged solved.
OpenOffice 3.3.0 on Windows 7
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: Auto Fill Form Using ID?

Post by DACM »

You'll need to create a dedicated filter table. Base the MainForm with ID field on the filter table. Create a SubForm linked to the MainForm by the ID field. Base the SubForm on your table which includes your ID field and details. Move your Form controls (except the ID field) to the SubForm. Add a Push Button to the SubForm with selected Action: Refresh form.

Read: [Example] Arineckaig's Form filtering examples (including a definitive guide to Base SubForms)
and perhaps: [Example #1] Filter/Search with Forms (leveraging SubForms)
...
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
User avatar
Giant1985
Posts: 7
Joined: Sat Jul 28, 2012 7:02 am

Re: Auto Fill Form Using ID?

Post by Giant1985 »

thankyou. I think i understand. Will get a chance to try tomorrow at work. will let you know how i go.
OpenOffice 3.3.0 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Auto Fill Form Using ID?

Post by Villeroy »

Modify the form in your screen shot as follows.

1) Create a new 2-column table (assuming that your ClaimNo is an integer):
Tools>SQL...

Code: Select all

CREATE TABLE "Filter" ("ID" INT PRIMARY KEY, "CID" INT NULL)
Execute
menu:View>Refresh Tables


2) Open the filter table and enter one row with a row ID, say 1 and some claim number in the CID field.

3) Get the form navigator from the 5th button of the tool bar named "Form Design". Right-click the top level "Forms" container and add a new independent form.

4) Get the properties of the new form, tab "Data"
Source type: SQL
Source: SELECT * FROM "Filter" WHERE "ID"=1
Allow edit=Yes
Everything else: No
The form refers to one particular row in the filter table where we enter our CID at row number 1. We don't want to insert nor delete any new rows from this form. We don't need any navigation bar for one row.

5) In the from navigator window drag the ClaimNo with its label from the existing data form to the new filter form. Bind the input box to the CID field of its new form.

6) Drag the data form icon to the filter form so the data form becomes a subform of the filter form. Get the subform properties, tab "Data" and link the subform's ClaimNo to the parent form's CID.

7) Select the subform icon in the navigator and draw a push button. Set the "Action" property to "Refresh form".
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Auto Fill Form Using ID?

Post by Villeroy »

The above outlined method makes creative use of subforms and form controls. The built-in filtering method is much easier to set up but a little bit clumsy to use:
While using the form, click the form filter button on the navigation tool bar.
Your form appears blank for criteria input together with a small extra tool bar. Enter the ClaimNo into its box and click the apply button on the tool bar.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Giant1985
Posts: 7
Joined: Sat Jul 28, 2012 7:02 am

Re: Auto Fill Form Using ID?

Post by Giant1985 »

The claim numbers are not an actual interger its a string - BG0479 sometimes could have letters in last part so BG047G

Sorry didn't think to define that that before. Like I said I'm a noob. Closest thing I've come to writing code (Code worked fine that you wrote btw) like above is the python course I've just started like 2 days ago.

*Update* -> I forgot to mention I did assign the key to ClaimNo when it asked me.
OpenOffice 3.3.0 on Windows 7
User avatar
Giant1985
Posts: 7
Joined: Sat Jul 28, 2012 7:02 am

Re: Auto Fill Form Using ID?

Post by Giant1985 »

Also the form will be used by a few people. Possibly even my boss. So whatever is easier for them to use. If the boss is using it it needs to be easy :-)
OpenOffice 3.3.0 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Auto Fill Form Using ID?

Post by Villeroy »

Code: Select all

CREATE TABLE "Filter" ("ID" INT PRIMARY KEY, "CID" VARCHAR_IGNORECASE(6) NULL)
defines the CID field as a case-insensitive text field with up to 6 characters. BG0479 is equivalent to bg0479.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Giant1985
Posts: 7
Joined: Sat Jul 28, 2012 7:02 am

Re: Auto Fill Form Using ID?

Post by Giant1985 »

Thank you the sub-form is now filling the existing data for me. There data is also editing but I have a problem when trying to edit the notes and add a new line by hitting Enter it is clearing all the data.
OpenOffice 3.3.0 on Windows 7
User avatar
Giant1985
Posts: 7
Joined: Sat Jul 28, 2012 7:02 am

Re: Auto Fill Form Using ID?

Post by Giant1985 »

Fixed the problem with the notes field... Just had to change the form control to Multi line intput yes.

The form is now complete thank you so much for all your help :bravo:
OpenOffice 3.3.0 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Auto Fill Form Using ID?

Post by Villeroy »

... and add a new line by hitting Enter it is clearing all the data.
You should better not add new data through this form. The new row will inherit the CaseNo from the filter form which produces duplicate case numbers making your filter form a bit dangerous because the displayed filter result may not be the only filter result.
Better use some other form with data property "Append new records only". Such a form always jumps to the insert row when loading. It can be attached to the same form document.

I assumed that your CaseNo is enforced to be unique.
Open the table in table design mode.
Open the index editor.
Add a new index on the CaseNo and mark it as unique.
Save the index and the table.
If this fails then you need to clean up existing duplicates.

Query to find duplicates:

Code: Select all

SELECT "Table Name"."CaseNo", COUNT(*) AS "Count"
FROM "Table Name" 
GROUP BY "CaseNo"
HAVING COUNT(*) > 1 
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Giant1985
Posts: 7
Joined: Sat Jul 28, 2012 7:02 am

Re: [Solved] Auto Fill Form Using ID?

Post by Giant1985 »

Fixed the problem with the notes field... Just had to change the form control to Multi line intput yes.
That was all i had to do to fix the problem with enter getting rid of it.

And and everything has been great. Entered a few more claims and got them to come up in the exsiting claims form... The claim no's are generated by the system that claims get entered into but it is a hopefless system that has no way of entering anything like notes once done. It doesn't have any way of know where it is at. That's why I created this to keep track of them once they were created. I didn't need to use the last suggestion but thanks for the input.

All is going great. Everyone loves it and it's working great.
OpenOffice 3.3.0 on Windows 7
Post Reply