[Solved] Form for searching a specific data

Discuss the database features
Post Reply
Shuttlefan
Posts: 4
Joined: Tue Mar 21, 2017 6:06 pm

[Solved] Form for searching a specific data

Post by Shuttlefan »

New to Base (and database in general) so I'm not sure if I'm on the right path or not.

I have a table as shown and I want to have a way to remove quantity from an entry.
Ideally, I'd like to have the user just enter the UPC code, it should then lookup the UPC and pull out all data that matches the UPC and has greater than 0 quantity.
The same UPC could pull up multiple entries as they may have different expire dates.
for each row, as I decrease the quantity, it should reflect back the change to the main data table.

It's not clear to me how I would tell the Table Control to pick the UPC defined in the form and use that to select the data in the main data table.
for each column, when I go to control->Data, there's no field where I can enter a SQL statement like I can for list or combo box.

I would appreciate someone teaching me how to do is. Or even let me know if I'm on the right path or not.

Thanks
Attachments
screenshot of table and form
screenshot of table and form
Last edited by Shuttlefan on Fri Mar 24, 2017 10:32 pm, edited 1 time in total.
OpenOffice 4.1.3 on Windows10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Form for searching a specific data

Post by UnklDonald418 »

There is a tutorial/example that might help
viewtopic.php?f=100&t=42845
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Form for searching a specific data

Post by UnklDonald418 »

I guess I should point out that a MainForm/SubForm would work for that.
The UPC would be on the MainForm and the table would be on the SubForm, using UPC value to link the forms together.
If you have a limited set of values for the UPC you could use a list box or combo box control on the MainForm
It wouldn't take much to convert your current form
Using the Form Navigator found on the Form Design ToolBar add a SubForm and then drag the table control onto the SubForm. Right click on the new SubForm and select properties to get the form properties dialog. On the Data tab you can select your data source and a little further down the list you will find Link Master Fields. Click on the ellipsis (...) and you can choose the Master and Slave link fields, in your case they would probably both be UPC.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Shuttlefan
Posts: 4
Joined: Tue Mar 21, 2017 6:06 pm

Re: Form for searching a specific data

Post by Shuttlefan »

Thanks, I will go take a look at the example. UPC will actually come from a barcode scanner. I'm just typing it in as i develop the forms.

THis is for a simple church pantry use. I was able to figure out the adding inventory part. Just need to figure out how to account for usage of inventory.
OpenOffice 4.1.3 on Windows10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Form for searching a specific data

Post by UnklDonald418 »

Here is an example of invoicing that may help
viewtopic.php?f=100&t=56006
It is probably more complex than you need, but if I recall correctly it does show a basic version of inventory control
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Shuttlefan
Posts: 4
Joined: Tue Mar 21, 2017 6:06 pm

Re: Form for searching a specific data

Post by Shuttlefan »

Hmmm....I think I got subforms somewhat working.

But few issues:
I want only the quantity column to be editable...but it seems I can update the UPC column, even though I set them as read only
When I type in a upc NOT in database, it should return nothing but it now returns the next field

The other issue is I can't figure out how to change the color of the table (background or text).
Attachments
pantry.odb
(37.36 KiB) Downloaded 138 times
OpenOffice 4.1.3 on Windows10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Form for searching a specific data

Post by UnklDonald418 »

I uploaded something for you to try. See if FormCkOut works the way you want.

It is based on an example found at:
viewtopic.php?f=100&t=42845
scroll to the bottom and download “LIKE_user_input_filter.odb ”
The explanation is found in form “1 Text Box Search”.

To change the color of the table open the form in the Edit/Design mode. Click on the Form Navigator
(In case you're not familiar, it is found on the Form Design toolbar at the bottom left of the screen).
Right click on the SubForm_Grid and select Properties to get the Properties Table Control dialog. On the General tab locate Background color and click on the down-arrow to get a list of available colors. The Font property also allows color changes from the Font Effects tab.
Attachments
pantry01.odb
(46.15 KiB) Downloaded 138 times
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Shuttlefan
Posts: 4
Joined: Tue Mar 21, 2017 6:06 pm

Re: Form for searching a specific data

Post by Shuttlefan »

Yes, this is it! Thanks!
It will take me some time to digest how I would know to put in a button.
OpenOffice 4.1.3 on Windows10
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: [Solved] Form for searching a specific data

Post by UnklDonald418 »

There is more to it than just a button.
As the example “1 Text Box Search” explains it also makes use of a filter table that always has only one row, and linked parameter query.
Not something someone new to Base would likely figure out on their own.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply