Basic Query: display on form if found question.

[jal]

I made a couple of improvements. First I got rid of the Misc table because another poster gave me a solution to that syntax error.

Secondly, for casees of nonmatches I now use two columns

Detail0/2 Needed.....Detail0/2 FullName

With a non-match, the FullName will be empty but the first column should still populate. Here is the new version:

SELECT Important_0, [Detail0/4-Needed], [Detail0/1-Needed], [Detail0/2-Needed], A.FullName as [Detail0/2-FullName]
FROM
(
SELECT Important_0, FIRST(D.[Detail0/4-Needed]) as [Detail0/4-Needed], FIRST(D.[Detail0/1-Needed]) as [Detail0/1-Needed], FIRST(D.[Detail0/2-Needed]) as [Detail0/2-Needed]
FROM [Database] as D
WHERE Important_2 = @NumberToFind
GROUP BY Important_0

UNION ALL

SELECT Important_0, FIRST(D.[Detail0/4-Needed]) as [Detail0/4-Needed], FIRST(D.[Detail0/1-Needed]) as [Detail0/1-Needed], FIRST(D.[Detail0/2-Needed]) as [Detail0/2-Needed]
FROM [Database] as D
WHERE Important_1 = @NumberToFind
GROUP BY Important_0
) as Connections
LEFT JOIN AbbreviationsForDetails02Needed as A
ON A.Abbreviation = Connections.[Detail0/2-Needed]

Feel free to rollback to an older versoin of the query if you don't want to use the abbreviations table.

 

[hunoob]

Hi Jal! We are having visitors for a few days, so I will have very limited time to test your code. Will get back to you till/on weekend!

 

[hunoob]

Hi Jal! Sorry for being away.. I just tried your provided database and it is working very very good! Thank you so much for that! However there are still 2 (small) problems:

1. How could I decrease the font size of the findings table to Tahoma 8? You see the search box and the column findings box has Tahoma 8, but the connections table font format is lot more larger...I would like that all fonts to have the same size. Is it possible?

2. Probably my description was missunderstanding. I now talk about the column/field 'Detail0/2-Needed'. In the attached file I changed some values in this field. As you can see in this field there are three type of codenames. L, C and E05B01. Each code stands for something different. (For example: L = regular buyer, C = retailer, E05B01 = small shop). So if the cell contains "L" then in the connections table the 'Detail0/2-FullName' field should contain "regular buyer". If another cell contains an "L C" then in the connections table the 'Detail0/2-FullName' field should contain "regular buyer, retailer". So if in one field there are two codenames, then the 'Detail0/2-FullName' field should also contain two codenames. Can this be done?

(In excel I split the cells using the space character as separator and then the adjacent columns contained only one carachter. Next I changed the characters to the descriptive names and rejoined them...but this was excel and not acces.. )

Thank you in advance

 

[jal]

Hi Jal! Sorry for being away.. I just tried your provided database and it is working very very good! Thank you so much for that! However there are still 2 (small) problems:

1. How could I decrease the font size of the findings table to Tahoma 8? You see the search box and the column findings box has Tahoma 8, but the connections table font format is lot more larger...I would like that all fonts to have the same size. Is it possible?

If you right-click the listbox and choose properties, you should be able to find a font property whose size you can change. However, this might cause the column-widths to need resizing. You can alter my code which was something like this as I recall:

listbox1.ColumnWidths = "1in; 7in; 3in; 2in;"

That would mean four columns, the first one is one inch. Adjust them as necessary.

2. Probably my description was missunderstanding. I now talk about the column/field 'Detail0/2-Needed'. In the attached file I changed some values in this field. As you can see in this field there are three type of codenames. L, C and E05B01. Each code stands for something different. (For example: L = regular buyer, C = retailer, E05B01 = small shop). So if the cell contains "L" then in the connections table the 'Detail0/2-FullName' field should contain "regular buyer". If another cell contains an "L C" then in the connections table the 'Detail0/2-FullName' field should contain "regular buyer, retailer". So if in one field there are two codenames, then the 'Detail0/2-FullName' field should also contain two codenames. Can this be done?

As I said before, this is not what I set up for.
It can probably be done but this seems a bit complicated. If I have time I'll give it some thought but it doesn't look easy to do.

I've become pretty busy lately, I am afraid.

 

[hunoob]

As I said before, this is not what I set up for.
It can probably be done but this seems a bit complicated. If I have time I'll give it some thought but it doesn't look easy to do.

I've become pretty busy lately, I am afraid.

Ok, no problem. Just take your time! If you got the solution please post it! Thank you very much and have a nice weekend!

Anyway...till you figure out something I think I will do the following. There are alltogether 4 type of codes. In the columns there are maximum two codenames. So I'll make the 12 combinations plus the single ones and this way it will work...hopefully!

By the way... is it possible to make a very similar search as you already made. The difference would be that this type of search should display only the columns/fields in which the "number to find" is present. The most important difference would be that this type of search would have a table look which means that I would be able to type many many codenumbers (even copy them from excel and paste it to the searching column/field) in the searching column/field and then when I press search than the code would display found/not found in the relevant column field. You can see what I mean if you open the attached file and open the second form ("Listsearch" form).

Thank you very much!

 

[jal]

Anyway...till you figure out something I think I will do the following. There are alltogether 4 type of codes. In the columns there are maximum two codenames. So I'll make the 12 combinations plus the single ones and this way it will work...hopefully!

I was hoping you would handle it this way. I can think of two other ways, both involving similar code, but this is a good amount of code and the problem with more code is potentially more bugs. The first way is a UDF (User Defined Function) which becomes part of the SELECT statement. The second way is to run code that executes before the user loads the form, this code would provide a translation of the 4 symbols that would either overwrite the existing Detail 0/2 column or update a new column called Detail 0/2-translated.

I just don't see the advantage of doing all this when you could take the approach you proposed.

 

[jal]

By the way... is it possible to make a very similar search as you already made. The difference would be that this type of search should display only the columns/fields in which the "number to find" is present. The most important difference would be that this type of search would have a table look which means that I would be able to type many many codenumbers (even copy them from excel and paste it to the searching column/field) in the searching column/field and then when I press search than the code would display found/not found in the relevant column field. You can see what I mean if you open the attached file and open the second form ("Listsearch" form).

That looks fairly do-able, maybe I'll give it a shot.

 

[jal]

Take a look at Form2. This is hopefully the sort of "table search" you had in mind.

 

[jal]

And yes, copy and paste does work with this table search.

 

[hunoob]

Hi Jal! Thank you sooo much for the list search! It is definetelly what I wanted! Thank you for it!

I will check the whole application tomorrow with the real database. I will try everything and FULLY test it...and will post the final version for you.
I have a question/request though.... how can I clear the LBconnections table on the form? I created a button which clears the other labels (me.fieldname = Null) but this command does not work on the LBconnections. How can I clear that table also?
Thank you very much!

 

[jal]

Clear the listbox this way:

Set Me.LBconnections.Recordset = Nothing
Me.LBconnections.Requery

 

[hunoob]

Thank you Jal...again! Your solution is working.
I am testing now the codes with the original database. Sometimes the code is running a bit long. Not too much...but... Anyway is there a possibility to do the following?
I have a picture on the form. The default property is set to: not visible. Is there a way or code, that sets the status of this picture to visible only when the macro is running? I mean..while the macro/code is running this picture (Picture3) is visible, and when the macro/code is not running it's status is invisible.
Thank you again and a lot!!!

 

[jal]

I have a picture on the form. The default property is set to: not visible. Is there a way or code, that sets the status of this picture to visible only when the macro is running? I mean..while the macro/code is running this picture (Picture3) is visible, and when the macro/code is not running it's status is invisible.
Thank you again and a lot!!!

I don't have much experience with images - you'll probably have to figure out the code for visibility yourself. But I suppose, at the start of my code, it might be something like this:

image1.Visible = False


and then at the end of my code

image1.Visible = true

I'm pretty sure that's the wrong code - you'll have to research this one.

Thank you Jal...again! Your solution is working.
I am testing now the codes with the original database. Sometimes the code is running a bit long.

I am surprised that it would run slow. Are you talking about Form2? Or do you mean the listbox in the MainForm? What exactly is running slow? Be precise please.

I don't recall it being slow in my tests. And I noticed you changed some of the SQL. Any chance you altered the query?

 

[hunoob]

Hi Jal! Your code it is not slow... The original file I am working with it's almost 1 Gigabyte, so when I do a query it takes around 3 seconds. This is not long for sure...but the users of this file are very computer illiterates... so I would like to have something that shows them, that the code is working... and I added a small graphic with a red and green LED light... that is why I asked this question... while the code is working the red light is on...why there is no code running the green light is on... It is not an issue..but anyway it would be something that is fancy and would add an extra design to the application!

Anyway...I tried your solution and it works!! ... I mean the: image1.Visible = False/True
Thank you very much!

Today I will FULLY test the application against the excel application (which I think works well)...I really hope that there will be no problems! Keep my fingers crossed... Will let you know about the results!

 

[hunoob]

Hi Jal! Maybe you can help with my problem. I am trying to do the show image when the macro is running.

Here is the code:

Private Sub btnSearch_Click()
    Call ShowImage 'makes the image25 visible
    Msgbox "Please press ok"
    Call Search 'makes the search query
    Image25.Visible = False
    
    
End Sub

the code above works perfectly..when I click the button the code makes the image visible, then displays the messagebox and then makes the query

now..if I delete the Msgbox "Please press ok" line from the code.... then it does not work the way I want it...
when I press the button...it starts "thinking" (for 1-2 seconds) and then shows the results and shows the image for a very very short time...just like a blink of an eye... you cannot really see that there was something shown...
why is working well when I have the messagebox line, and why doesn't when I delete the messagebox line?
I really do not understand it.

UPDATE: Ok..it is working now. Solved with the repaint method

 

[hunoob]

I am testing the database right now...your macro seems to work properly! I will get back to you very soon...if everything worked out fine!

 

[hunoob]

Hi Jal! I tested the database, made many many queries and it seems to be fully functional. Of course my collegues will test further in the coming days...but I really do not think that we will have errors...
There's one recent issue though.... can we somehow add a label which shows the name of the company. This might be a bit tricky, because:
- if the codenumber we are searching for is in column/field important_0 then the name is +5 columns to left
- if the codenumber we are searching for is in column/field important_1 or important_2 then the name is +4 columns to left
Can you help me add this label please?

...or do you think would it be better to create a new database with the codenumbers in one column/field and company names in the next column/field...and then make a query which searches for the codenumber and displays the name? (this solution would mean a lot of extra work)
Thank you in advance!

 

[jal]

You lost me on that one. Don't you know in advance the name of the column which contains the company name? Why not just pull that column as part of the original query?

 

[hunoob]

Hi Jal! Sorry for the incomplete post. The columns which contain the company names are these ones:
- in casethe codenumber we are searching for is in column/field important_0 then the company name is in: "Detail 0/4-Needed"
- in case the codenumber we are searching for is in column/field important_1 then the company name is in: "Detail 1/5"
- in case the codenumber we are searching for is in column/field important_2 then the company name is in: "Detail 2/5"
Thank you in advance!

 

[jal]

Hi Jal! Sorry for the incomplete post. The columns which contain the company names are these ones:
- in case the codenumber we are searching for is in column/field important_0 then the company name is in: "Detail 0/4-Needed"
- in case the codenumber we are searching for is in column/field important_1 then the company name is in: "Detail 1/5"
- in case the codenumber we are searching for is in column/field important_2 then the company name is in: "Detail 2/5"
Thank you in advance!

You mention 3 different cases - but I thought all three cases could be true at once? Are you saying that only one of these cases can be true at a time?