Fear of importing

[Master Klick]

I have a form that shows an image of a card based on the selection of a listbox. The image path is in the querty that the form runs off of. I set the control source of the image to: =
[List].[Column](6). That is simply the path for the image to be displayed.

Now my issue is, I just discovered that some of the cards have two sides. In the folder where they reside the file shares the card name, normally. In the case of two sided cards the file name is the card name with a 1 or a 2 at the end. So, now I'm trying to set the form up to, with regular cars, show the card face and the default back. In the case of two sided cards I'm trying to get the form to add the "1" or "2" to the end of the file name and display each side. One way I've been working on is setting the control source for the new image to:

=if(FileExists("
[List].[Column](6)"),"
[List].[Column](6)","C:\Program Files (x86)\Gatherer Extractor\pics\" & "
[List].[Column](8)" & "
[List].[Column](2)" & ".full.jpg")

I was hoping this would simply build a new file path. I used something similar, although far less complex in excel, which is how I got the image paths into my database in the first place.



So, as my second attempt, I figured I could work it out in excel and just have a front and back image field. My problem there is that I'm afraid to import the table again. I'm not sure if I can import it and have it only update 1 or 2 fields or have it just add one field. Or, if I can import it as a whole new table, delete the old table, rename the new one and still have all my codes and references and all that work the same.

I suppose I'm asking for help with the import. Is there a way the update the table without having to redo everything? Can't do it manually. Well, I could, but it's 23335 records so far and that would take me years.

[June7]

A function along what you attempted should be able to dynamically append the suffix.

Can you set a link to the Excel worksheet? Linked sheets can be manipulated like tables (although not edited). The linked sheet can be used as source for an UPDATE sql action to populate the new field(s) in table or an INSERT action to add new records.

[Master Klick]

I could try. You're saying if I link them through the External Data tab I can update the fields that way?

[June7]

Link to the spreadsheet. Use the linked sheet as source for data to update the table fields, presuming there is a common unique ID value for query join.

[Master Klick]

Yup, that worked. Well, importing the table again and then linking it worked. Although could get the code to work in excel either. So I added two fields and a third picture. the other two fields just refer to the other two possible file types and if there isn't one it's just blank. Works good enough. Now my only question is, can I set the control source so it will show either a single default unless another image is loaded. Just to make it pretty.

[June7]

You want the Image control to show a generic image if specific image not available?

Just as a function call in the ControlSource should be possible, the ControlSource can have a conditional statement, like:

=Nz([image path fieldname], "generic image path")