Base: search button and showing the datas

[Roby1]

Hello everyone,
I repost my question hopefully more detailed.
I have a MS Access clients DB that I want to transfer to LO Base.
I start from zero in writing DB and codes, I think I did a lot of job connecting three tables (client’s data, activity, membership history). And this is how it looks like on L.O.Base:

WHAT I NEED TO DO IS:

I start to digit a last name in the SEARCH DROPDOWN BUTTON (which has yellow background), and it shows the various clients (alphabetical order, the dropdown can open if necessary, or can stay closed). Sorry, this is how the old MS database looks like:

When I select the one that I’m interested to or press ENTER, it should retrieve all the datas and show below in table with the green background.

See pic 2

I’m quite new to write codes and stuff, so I need help to set up a query or a macro that does that job.
I also have the code of that search button which is in the old database (microsoft access). This is the code that I found on the MS Access, which is in the “Properties of the field”, the command “After update”:

Private Sub CercaCognome_AfterUpdate()
’ Trova il record corrispondente al controllo
Me.RecordsetClone.FindFirst "[IDCliente] = " & Me![CercaCognome]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Thank you!
Roby

[Ratslinger]

Hello,

The answer here is basically the same as in your original question posted here → How to set up correctly a search dropdown button?.

The code you present in the question is only of interest with the original Access file. Little can be gotten from just that code.

As stated in the other answer, using a table filter will get you what you want without using any code (except for SQL). And as for SQL (Query), a sample (again mentioned in original post answer) would be needed to complete what is actually needed.

Screenshots do not help to get answers for this type of question. Depending on how the form is actually set up (internal form & sub forms), it may be just a matter of retrieving, through the table filter, the main record & the rest will display automatically.

Edited 2018-12-17:

Just to give another example, have copied your form (marked as revised) stripped most of the problems, added list box & button with a small macros. You can choose an item using the list box or all records using the push button.

Sample ---- ListBoxSelect.odb.

[Roby1]

Hey Ratslinger,
what can I post to help you and helping me? My LibreOffice is in Italian Language…
Can be this screenshot of Relationships of any help?

[Ratslinger]

Back to basics - Use a table filter. Original question had a link to a description and how it works. I also included there a working sample. Seems you have three tables on this form. Clienti seems to be main form & that would be retrieved using SQL & table filter. The other two tables would be sub forms linked back to the main form. Again without a sample(.odb file without personal or confidential info) that is about all that can be done.

[Roby1]

Clients DB - test.odb
Let’s try like this

[Ratslinger]

Sorry but that sample simply crashes any time I try accessing the ‘Tables’ section. Of no value.

[Ratslinger]

Also, there are two list boxes. Which one are you trying to select from? Do you need both?

[Ratslinger]

There are two ways to do this. As mentioned previously, use a table filter. This needs no macros and you should be able to do this based upon the already provided info & sample in other question. The other is to write a custom macro. If you are not able to write your own code you are going to run into the same problem you have now - presenting code to someone else to fix and not knowing how it works. You should learn table filtering and use that.

As mentioned above, there is a problem with the sample provided. Additionally, the list boxes for selection are NOT to be on the same form as the rest of the data. They are not actually part of the record being looked for. And then there is the question on the one or two list boxes needed.

As stated a number of times now, table filtering is the way to go if you are not able to write code.

[Roby1]

DB Test for help.odb
I try again. I kept 6 clients with creative and imaginary datas. Hope it works.
The Form I will use mainly is “frm_ANAGRAFICA_CLIENTI”
Anyway thank you for your support

[Ratslinger]

@Roby1 The new file still crashes each time I attempt to access the Tables section or Relationships. Try downloading yourself and see what I am experiencing.

[Roby1]

I tried, and it crashes too… I don’t know what to do, so I try this and that to see what it works…
I’m watching these videos LibreOffice: Base but It seems I cannot apply to my need.
I’m going crazy! It should be very easy to digit a surname in the yellow box and the data appears the the green part, but it’s not?!?!?

[Ratslinger]

@Roby1 You are obviously having multiple problems. Selecting from a list box & having data appear in the form is a relatively simple process. Have already given you samples of this & a link to instructions but you don’t seem to use these. You haven’t even been able to get me a sample so I can demonstrate how to do this using your form.

I will now attempt to duplicate (it will not be exact) what you seem to have and add the selection process. This will be time consuming and cannot say how soon an answer will come. But this seems to be the only approach you are wanting as what I have given you so far doesn’t seem acceptable to you.

[Ratslinger]

@Roby1 After spending hours on this already I must stop. The more I look at what you have the more problems I find. You have little understanding of Base and its forms. You do need to review basics on this and clean up many, many problems.

The problem with the crashing is caused by a table name - tbl_STORICO_ATTIVITA'. The ' is illegal. You also are inconsistent with naming fields using many spaces & special characters which will cause problems.

Your combo boxes should be used for record selection. Instead you have made them part of the record from the tbl_ANAGRAFICA_CLIENTI table. By doing this you would be changing records by trying to select another record. Also combo box cognome legale has a list content of Query with the query name of Ricerca_ANAGRAFICA_CLIENTI which does not exist.

In the sub forms you are trying to link back to the master form with ID Cliente but have chosen to not have these fields in the table control. There can be no link that way. More

[Ratslinger]

At this point there is no sense for me to proceed as it would require me to re-design the entire process and I don’t even know (or want to know) what you are attempting to do. For me to explain all the various problems thus far found would take a large amount of information. This is all in the documentation and I do not want to re-do what is already done.

Again, this is only what has been found thus far. Based on that it is almost certain that there are more problems.

[Ratslinger]

@Roby1 In your original question I had given you a link to examples. One of the links → display specific record in a form contains a sample BikeFiltering.odb. Please look at this for a way to select records. Without this basic knowledge of table filtering you will not have success. Those links also connect to many other similar samples but this is probably the most basic.

[Roby1]

@Ratslinger I’ve said at the beginning that I’m new to write ODB from zero. It’s not easy to follow all the instruction in a foreign language, and I’m really sorry you lost so much time for me. You don’t need to go further. I retry to create a new db following this forum It will take months for me but what to do.

[Roby1]

@Ratslinger Sorry, I didn’t see you posted a revised copy of my odb to help me. Thank you, thank you. You can close this topic if you want. I’m really sorry that I caused you so much trouble.

[Ratslinger]

@Roby1 No apologies necessary. My time was not lost or wasted if I helped you in any way. Please try to use the many posted samples in this or other forums to assist in your learning. Start small as my revised sample. Get a basic form working. Add a search. Add a sub form. One step at a time and test for working condition between each step. This avoids the many problems you have in what you started with.