Solved Appending a Query to a Combobox (1 Viewer)

petermeter

New member
Local time
Today, 07:22
Joined
Jan 26, 2022
Messages
12
Hi
I'm kinda stuck. I have a table which contains the filenames of a specific folder. These filenames then get processed to create a given term I already have in my DB. My goal is it now to run an append query to add these filenames to a different table. I made sure that the product of these processed filenames are the exact string which already exists in the combobox (where the names get added) of the target table.
But the filenames don't get appended because in one table where the new names are, the fields are a text datatype and in the new table the fields are comboboxes and set to the number data type (although I see the correct names with 0;2 column width).
There are 2 possible solutions I can think of:

1. I make a new query with all the source tables and the filename table, make a cartesian product and exclude the 0 values with "Is not Null"
Unfortunately, I can't get "Is Not Null" to work, it does not show any difference.
2. I somehow get ms access to understand that the filenames exist in the combobox.

Btw I tested the append query with the corresponding numbers in the combobox instead of the corresponding names and that worked.

I hope I explained my problem clearly.
 

June7

AWF VIP
Local time
Yesterday, 22:22
Joined
Mar 9, 2014
Messages
5,424
What do you mean by "all the source tables" - plural? If these file names are already listed in combobox RowSource then they are already in a table. Why would you want to duplicate to another table? If target table has lookup fields saving ID key, then that is what your procedure should save.

Perhaps you should provide db for analysis. Follow instructions at bottom of my post.
 

petermeter

New member
Local time
Today, 07:22
Joined
Jan 26, 2022
Messages
12
What do you mean by "all the source tables" - plural? If these file names are already listed in combobox RowSource then they are already in a table. Why would you want to duplicate to another table? If target table has lookup fields saving ID key, then that is what your procedure should save.

Perhaps you should provide db for analysis. Follow instructions at bottom of my post.
The table to which I want to append the new data to, consists of comboboxes from different tables. The new data is basically a combination of these comboboxes.

It is a list of spare parts from different motorcycles. The motorcycles and spare part names all repeat themselves, so it's essentially just a combination of a few tables. The filenames consist of the model information and the spare part name (I split them into individual fields). The list of all spare parts is my target table and the source tables are the model information and the spare part name, which are separate tables and are "represented" as comboboxes in the spare part table.
I hope this clarified it a bit.
 

June7

AWF VIP
Local time
Yesterday, 22:22
Joined
Mar 9, 2014
Messages
5,424
Should be able to include lookup tables in query and update target fields with the ID values from those lookup tables. Not really understanding why this would be a cartesian product query. I need to see data.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:22
Joined
Feb 19, 2002
Messages
42,981
Combo boxes don't hold data. Tables hold data. You can't append to a combobox. You append to a table.
 

petermeter

New member
Local time
Today, 07:22
Joined
Jan 26, 2022
Messages
12
The target table where i want to append to is "parts"
I get the filenames to "tblpictures"
I process them in tblpictures_v
I try to assign them to the corresponding partname_id in assign_v, this is where cartesian product happens, which is not a problem but "Is Not Null" doesn't seem to work, so I am not able to process it any further
append_parts is the query where I try to append tblpictures_v to parts, which works if the values are the corresponding id of the source table but not the name in the second column.
 

Attachments

  • db.zip
    246.7 KB · Views: 261

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 01:22
Joined
Feb 28, 2001
Messages
27,001
This sounds very much like you have an unnormalized layout.

The table to which I want to append the new data to, consists of comboboxes from different tables. The new data is basically a combination of these comboboxes.

This actually sounds like it is backwards from the way we normally build things. Are you suggesting that you have combo boxes based on different tables and want to then combine the underlying tables? OR are these combo boxes designed with explicit lists stored in the combo control itself? I am having trouble visualizing what your layout is like. Without that visualization, it will be hard for us to give you best advice.
 

petermeter

New member
Local time
Today, 07:22
Joined
Jan 26, 2022
Messages
12
I just uploaded my db.
Data which is repeated many times and not changed is saved in tables and whenever I have a unique part it is a combination of those tables, does this make sense?
I know appending from filenames to comboboxes is ugly, but it would save me a ton of time if I get the combining to work. It also would not corrupt any relationships since I have to manually press append and is not joined or anything.
 

petermeter

New member
Local time
Today, 07:22
Joined
Jan 26, 2022
Messages
12
Sorry for my chaotic explanation, I find it really difficult to explain my problem clearly.
 

petermeter

New member
Local time
Today, 07:22
Joined
Jan 26, 2022
Messages
12
Yes, it is kind of the root of my problem. I try to assign a value which is equal to the 2 column of the combo box to this combo box. It worked with the ID with said combo box, but with that I have separate issues, namely that I cannot filter out NULL values in my cartesian product.
 

petermeter

New member
Local time
Today, 07:22
Joined
Jan 26, 2022
Messages
12

Attachments

  • Capture.PNG
    Capture.PNG
    9.1 KB · Views: 260

June7

AWF VIP
Local time
Yesterday, 22:22
Joined
Mar 9, 2014
Messages
5,424
Yes, I figured out it is a query, not a table and deleted my comment.

Still have not explained the logic of translating image names but I will try to understand from the query design.

Something to consider: change db to overlapping windows so objects can be opened and sized to view side by side.
 

petermeter

New member
Local time
Today, 07:22
Joined
Jan 26, 2022
Messages
12
I get the filenames with some VBA, it is in "Form_tblpictures_populate", then It just gets processed down into model_id and the name of the part in tblpictures_v. The translating works because of the image naming convention.
 

June7

AWF VIP
Local time
Yesterday, 22:22
Joined
Mar 9, 2014
Messages
5,424
Expression for f field can return empty string then assign_v query applies criteria of Not Is Null. Null and empty string are not same thing. Either filter for empty string or change expression to return Null. Consider:

SELECT assign_v.partname, assign_v.f
FROM assign_v
WHERE (((assign_v.f)<>""));
 

petermeter

New member
Local time
Today, 07:22
Joined
Jan 26, 2022
Messages
12
Thanks for this suggestion, (((assign_v.f)<>"")) does indeed work, but I still got a data type mismatch since I need "x" for the filtering. I found that the "Like" operator works. I used (assign_v.x Like "[!a-z]") which finally works :) :) . My guess it has something to do with the combo box being a number and the other being a string.
Thank you very much for your help.
 

petermeter

New member
Local time
Today, 07:22
Joined
Jan 26, 2022
Messages
12
Some things to consider for somebody who runs into similar issues like data type mismatch and stuff. I ran into problems when processing more data because of the type of data, so I just used "VAL(whatever)" to convert my numbers stored as text to numbers, now "<> 0 " works also flawlessly.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:22
Joined
Feb 19, 2002
Messages
42,981
Naming a query "tblxxx" can only lead to confusion.
Use the correct data types for the data. Then you don't have to convert them on the fly.
 

Users who are viewing this thread

Top Bottom