VBA coding help (1 Viewer)
- Thread starter admessing
- Start date
Whew!!.....managed to get everything working....all the way to the end. Now I just have to see how the final table displays on my map and then run it through a few times to make sure it doesn't break. Then I need to "mash" all the queries into one simple update Module/Script...whatever.
You game for that part?
You game for that part?
Just to go through in order.
You need the append query initially to populate the tables, once they're populated run qryFinalOutput. The initial population is a one-off exercise now you just need to cater for adding new coordinates and photos.
That structure significantly reduces the duplication (though you could legitimately argue that it's not normalised enough) and removes the need for your complicated code to try and turn Access into Excel.
Currently you need one query to add new co-ordinate data and one query to add new photos. A form or two to pretty those up wouldn't hurt but you could get away without them if you really wanted to
One form, 6 text boxes (or a couple of drop down lists), two queries to append and One query to display the data based on two subqueries and about 100 lines of VBA would probably get it up and running, maybe not prettily.
The output is one query to click on: qrySELECT_FinalOuput or you could stick a form on it to pretty it up a bit.
They don't need to be, populate the direction field and you've got one table with all your Photo data rather than 4.
The output is simple at the moment it just displays everything by running one query.
Now, before you go rushing headlong into designing forms and writing VBA, take stock first:
I know those tables aren't fully normalised. For just outputting the data that you have now that isn't necessarily a problem the idea was to get you out of recreating Excel in VBA and thinking about how your data is organised in a relational data model. For ongoing management it might be worth breaking down that data further.
I can see that the coordinate data isn't fully normalised. There really should be at least separate tables for Location and Stand. Easting and Northing perhaps less so. is there additional information that gives context to Location and Stand Data that would further add weight to putting that data into separate tables?
How many unique values of Location are there? How many of Stand? (and easting and northing for that matter?)
You had 5,000+ rows of Co-ordinate data before you started, how many rows are there now?
Arguably the Direction field in tblPhoto should related to a table tblDirection that only contains "North" "South" "East" and "West" rather than it being a text field as it is now. If your users can only select one of those 4 values from a list you don't get typos or abbreviations creeping into your data.
Based on your Word Document, I'd perhaps consider something along the lines of:
yes it complicates the initial set up, but the ongoing management is made much simpler with far less margin for (user) error.
You need the append query initially to populate the tables, once they're populated run qryFinalOutput. The initial population is a one-off exercise now you just need to cater for adding new coordinates and photos.
That structure significantly reduces the duplication (though you could legitimately argue that it's not normalised enough) and removes the need for your complicated code to try and turn Access into Excel.
Currently you need one query to add new co-ordinate data and one query to add new photos. A form or two to pretty those up wouldn't hurt but you could get away without them if you really wanted to
Yes you can. What you can't do is delete co-ordinates that have related photos associated with them, you'd need to delete or re-assign the photo records first. You can add co-ordinates until the cows come home. You can edit them and every photo automatically reflects the changed information when you run the output query. Photos have to be added with a valid Co-ordinate ID, other than that, you can do what you like with them including delete them.
One form, 6 text boxes (or a couple of drop down lists), two queries to append and One query to display the data based on two subqueries and about 100 lines of VBA would probably get it up and running, maybe not prettily.
The output is one query to click on: qrySELECT_FinalOuput or you could stick a form on it to pretty it up a bit.
They don't need to be, populate the direction field and you've got one table with all your Photo data rather than 4.
The output is simple at the moment it just displays everything by running one query.
Now, before you go rushing headlong into designing forms and writing VBA, take stock first:
I know those tables aren't fully normalised. For just outputting the data that you have now that isn't necessarily a problem the idea was to get you out of recreating Excel in VBA and thinking about how your data is organised in a relational data model. For ongoing management it might be worth breaking down that data further.
I can see that the coordinate data isn't fully normalised. There really should be at least separate tables for Location and Stand. Easting and Northing perhaps less so. is there additional information that gives context to Location and Stand Data that would further add weight to putting that data into separate tables?
How many unique values of Location are there? How many of Stand? (and easting and northing for that matter?)
You had 5,000+ rows of Co-ordinate data before you started, how many rows are there now?
Arguably the Direction field in tblPhoto should related to a table tblDirection that only contains "North" "South" "East" and "West" rather than it being a text field as it is now. If your users can only select one of those 4 values from a list you don't get typos or abbreviations creeping into your data.
Based on your Word Document, I'd perhaps consider something along the lines of:
yes it complicates the initial set up, but the ongoing management is made much simpler with far less margin for (user) error.
Who likes free time anyway
Last edited:
I already have a form for the data entry. So, I could add an update button to the form, but would rather have the DB update automatically as soon as the form is closed to avoid forgetullness.
I would gladly breakdown the data further, but I am limited by the parameters set by the department who will be using this DB. I can't get them to change their minds easily...I've been trying for months
.I know it too. Location and stand are there for quick reference and searchabilty. This way, anyone who searches the DB only needs that info to pull all the records for a particular area. Other than that, it is also used in our mapping to link the data to the main SDE server.
Locations = over 3500
Stands= over 56000
Easting and Northing =
- Currently...5033 unique pairs....
- Next year...add another 5000 unique and 500-1000 revisits.
- Possible total = too many to count (for every sq km there are a possible 1 million coordinate points...and we have over 10000 sq km locally).
Yes, it could, but the entry form only allows for the entry of the common portion of the file name. There is a query that then takes that info and builds the file path to the image. This cuts down on the errors that arise from typing long strings.
Who likes free time anyway
Free time?....What's that?!?!?
Similar threads
