confused about adding values from a query to a column in a table

[hijfte]

I have 2 tables. Table1 (tblPicture) is the main table needed for my form and contains a column called Picture that intends to contain the path to a bunch of pictures. This table is currently empty.
Table2 (tblCaps) has the path information in separate fields. For example, in tblCaps I have:
FilePath | FileName | FileExt
C:\Users\Jeff\Documents\Caps\Pics | L1A1 | bmp
C:\Users\Jeff\Documents\Caps\Pics | L1A2 | bmp
etc for 1200+ rows

I have created a query (Caps Query)

SELECT [FilePath] & "\" & [FileName] & "." & [FileExt] AS Location
FROM Caps;

So now I have
Caps Query
Location
C:\Users\Jeff\Documents\Caps\Pics\L1A1.bmp
C:\Users\Jeff\Documents\Caps\Pics\L1A2.bmp
etc for 1200+ rows.

What I need to do is take the value from Location in Caps Query and add the values to the Picture column in tblPicture so I can view the pictures in my form (the form is working if I can just get all the paths in tblPicture(Picture).

I also have some more fields in tblPicture that I need populated based on the value of Location. For example, for Caps Query(Location): C:\Users\Jeff\Documents\Caps\PicsL1A1.bmp, I would like the following fields populated in tblPicture.
TileLocation | TileNumber | CapPosition
L | 1 | A1


Please help a newbie with minimal access and vba experience. Thanks

 

[Banana]

First, I want to ask you why do you feel you need a table for pictures when you already have the table that stores path to your pictures? Your form can just query the table using same SQL you wrote and get it directly.

If you do intend to import the data into the table for pictures and not use the Caps table at all for future, then the SQL you may want is:

INSERT INTO tblPictures (TileLocation, TileNumber, CapPosition) SELECT Mid([FileName], Len([FileName])-4, 1) AS TileLocation, Mid([FileName], Len([FileName])-3, 1) AS TileNumber, Mid([FileName], Len([FileName])-2, 2) AS CapPosition FROM Caps;

What we are doing is parsing the pictures' name to get each components by using those functions:

Mid() give us a part of string based on the starting position and length specified
Len() gives us the length of a string

So Mid("Hello, World!", 1, 1) would give us "H" which is the first character, with length only 1 character long. Mid("Hello, World!" 1, 2) would give us "He" which is the same starting position but with length of 2. Mid("Hello, World!", 2, 3) would give us "ell", as we're moved the starting position to 2nd character with 3 character length.

With Len(), we can combine with Mid() to give us a certain position from right, regardless of the actual length. So to get fourth character from right:
Mid("Hello, World!", Len("Hello World!")-4, 1) which resolves to:
Mid("Hello, World!", 13-4, 1)
Mid("Hello, World!", 9, 1)
= "o"

I hope that helps.

 

[hijfte]

Thank you. I tried this and got the following error:
Microsoft Access can't append all the records in the append query
Microsoft Access set 1022 field(s) to Null due to a type conversion failure, and it didn't add 0 record(s) to the table due to key violations, 0 record(s) dut to lock violations, and 0 record(s) due to validation rule violations.
I ran the query anyway and for some reason only the CapPosition is populated.

 

[Banana]

It means that there is type mismatch between the data being inserted and the column's definition. In other words, if one column is defined as Number and you're trying to insert strings into it, Access implicitly converts this to Null.

I'd make sure that all three columns in questions are Text and not any other data types.

 

[hijfte]

Thanks. I changed all of them to text (tilenumber was number). I got the same error again and for some reason TileLocation did not populate (so now TileNumber and CapPosition are both populated correctly). TileLocation is setup the same way as the others -
Data Type: Text
Required: No
Allow Zero Length: Yes
Indexed: No
Unicode Compression: Yes
IME Mode: No Control
IME Sentence Mode: None

 

[Banana]

Hmm, this is odd.

1) Is there any relationship defined for TileLocation?

2) What happens if you manually type a letter in the TileLocation? Does Access accept or reject the changes?

 

[hijfte]

1) Is there any relationship defined for TileLocation?
There are no relationships defined

2) What happens if you manually type a letter in the TileLocation? Does Access accept or reject the changes?
I am able to manually enter letters and/or numbers in TileLocation

I actually switched TileLocation with TileNumber b/c those fields needed to be swapped. Now TileNumber is null instead of TileLocation being null. It must have something to do with
Mid([FileName], Len([FileName])-3, 1)

So, here is my query now
INSERT INTO tblPicture ( TileNumber, TileLocation, CapPosition, Picture )
SELECT
Mid([FileName], Len([FileName])-4, 1) AS TileLocation,
Mid([FileName], Len([FileName])-3, 1) AS TileNumber,
Mid([FileName], Len([FileName])-2, 2) AS CapPosition,
[FilePath] & "\" & [FileName] & "." & [FileExt] AS Picture
FROM Caps;

Result: TileNumber is null

when the query was
Mid([FileName], Len([FileName])-4, 1) AS TileNumber,
Mid([FileName], Len([FileName])-3, 1) AS TileLocation,

Result: TileLocation is null

 

[Banana]

The ordering of columns in INSERT INTO is significant. The query is trying to insert TileLocation from Caps into TileNumber for tblPicture.

The query should be:

INSERT INTO (TileLocation, TileNumber, CapPosition, Picture)
SELECT
Mid([FileName], Len([FileName])-4, 1) AS TileLocation, 
Mid([FileName], Len([FileName])-3, 1) AS TileNumber, 
Mid([FileName], Len([FileName])-2, 2) AS CapPosition, 
[FilePath] & "\" & [FileName] & "." & [FileExt] AS Picture
FROM Caps;

Not that explains why you're getting that behavior as they're both text and thus identical.

I forgot to ask- what is the data type of Caps table? If it's not text, then edit this to your query:

CStr(Mid([FileName], Len([FileName])-4, 1)) AS TileLocation

CStr() is a function that converts any data types into a string.

 

[hijfte]

Verified FileName in Caps table is text

Ran with and without 'CStr()' added. Both times got the same error and null.

Tried your most recent example

INSERT INTO (TileLocation, TileNumber, CapPosition, Picture)
SELECT
Mid([FileName], Len([FileName])-4, 1) AS TileLocation, 
Mid([FileName], Len([FileName])-3, 1) AS TileNumber, 
Mid([FileName], Len([FileName])-2, 2) AS CapPosition, 
[FilePath] & "\" & [FileName] & "." & [FileExt] AS Picture
FROM Caps;

and now TileLocation is Null and TileNumber again = L or R
According to the code w/ TileLocation Len-4 shouldn't Tile Location = L if filename = L1A1? Instead TileNumber = L (Len-3 shouldn't = L)

 

[hijfte]

I just figured it out. We needed Len - 3, Len - 2, and Len - 1 instead of 4,3, and 2. Now it works correctly.

 

[hijfte]

Thank you so much for your help. Now, I have this how I want it. What happens as the Caps table grows? If I rerun this query, it just adds all 1022 rows again. I need it to only add new rows. Is there an easy way to only add new rows?

 

[Banana]

Great detective work!


Is there a relationship between Caps and Picture table?

BTW, I asked earlier why you want to move data from one table to other when it could be in just one table?

 

[hijfte]

There is no relationship between Caps and Picture tables and I don't know how to setup a relationship b/w the two.

The reason I want to move data from one table to the other is my current form (which is setup with macros to display the pictures) is pulling all the fields from the Picture table and is displaying the value from the Picture field.
The Caps table is the table that has the FileName, FileExt, and FilePath, which we have concatenated via [FilePath] & "\" & [FileName] & "." & [FileExt]. Since the path data needed to be concatenated, I figured it would be easier to concatenate it to the Picture table, since the form is pulling the data from there. I also pulled the TileLocation, TileNumber, CapPosition fields into Picture table to keep everything in one place.

 

[Banana]

Okay, the reason I asked is because in a well-designed database, any piece of data should be stored in only one and exactly *one* place.

Therefore, there should be just one table to store the information about each picture, and each picture should have just one row to themselves, not one more.

That said, your form can be based on the same query that concatenates the file paths, so you just need to add new picture to only one table, and your form still will work.

To do this, your form's recordsource would be something like this:

SELECT
TileLocation, 
TileNumber, 
CapPosition, 
[FilePath] & "\" & [FileName] & "." & [FileExt] AS Picture
FROM Caps;

Then you'll have the already concatenated fields for your forms to use without the overhead of maintaining two near-identical tables.

Did that make sense?

 

[hijfte]

I have a bunch of other fields on my form that are also on tblPictures. I thought I needed one row on tblPictures per record on the form. Otherwise, if I just have a picture path on tblCaps, how does that link up to my form.

As far as your explaination to change my form's recordsource to a query, I don't know how to do that.
And I was looking at my form. The pictures, tilelocation, and tilenumber are all good; however, CapPosition is showing as #Name? even though in the database it is showing correctly.

 

[hijfte]

is the record source the same as the properties Control Source? So, for txtPicture, I would replace "Picture" with

SELECT
TileLocation, 
TileNumber, 
CapPosition, 
[FilePath] & "\" & [FileName] & "." & [FileExt] AS Picture
FROM Caps;

??

 

[Banana]

No, record source and control source refer to same thing but operate in different way.

Recordsource defines the set of fields that the form will be bound. If you've used the Field List, changing the recordsource will change the list.

Controlsource defines single field that a control can be bound to. Controlsource can be only selected from list of columns in the recordsource.

Here's a article that has a picture showing where you can find the Recordsource property of the form. The article is actually about referencing a subform, but use the second picture to help you find the field. You can open the Properties windows by hitting Alt + Enter when you're in form design view and you can open query builder from there, too. This article deals with expression builder, but you can see how you can open query builder from the form design and set the Recordsource.

Did that help?

 

[hijfte]

When I try clicking the gray square and then clicking Alt + Enter, I get the properties. However, I don't see Record Source... only Control Source. Is it possible my form is setup incorrectly? I've attached the zipped db.

 

[Banana]

Did the gray square highlight a little black square inside?

Alternatively, in the properties windows, you can use drop down list on the top to select "Form" You should be then able to see the recordsource, which is one of properties that belongs only to form and not to any controls.

 

[hijfte]

Yes!! When I change the RecordSource to grab some fields from tblCaps and some from tblPicture, it appears to be pulling from the specific tables rather than just tblPicture.
However, I have a couple problems now.

1)none of the fields are incrementing. There are 1045506 records (should only be 1022), all with
Picture = C:\Users\Jeff\Documents\Caps\Pics\L1A1.bmp.
TileNumber = 1
TileLocation = L

2)tilelocation and tilenumber are showing the correct value from the db; however, CapPosition is showing as #Name? even though in the db from the query below, it is showing A1.

Here is what I set my RecordSource to.

SELECT 
Mid([FileName],Len([FileName])-3,1) AS TileLocation, 
Mid([FileName],Len([FileName])-2,1) AS TileNumber, 
Mid([FileName],Len([FileName])-1,2) AS CapPosition, 
[FilePath] & "\" & [FileName] & "." & [FileExt] AS Picture, 
tblPicture.idxIndex, 
tblPicture.[Brewery Name], 
tblPicture.Style, 
tblPicture.City, 
tblPicture.State, 
tblPicture.Country, 
tblPicture.BackgroundColor, 
tblPicture.Color, 
tblPicture.BeerDescription, 
tblPicture.Rating
FROM Caps, tblPicture;