Query as record source for form

[Cordury]

2 Questions:

I have one form which requires a query to populate it. However it seems that there are more text boxes than query fields. I know that there are larger databases out there in Access than the one I am trying to create. How does one get around this issue without using just one table.

Since I am using a query to populate the form, I can not edit or change the records? I am obviously doing something very very wrong.

Basically, I have a database with store date. Some records are static, ie the Store #, Address, City, State, etc.. while others are not ie dates and such. I want to pull both the static data as well as the variable data to one form that can be edited in the form and saved over.

 

[Cordury]

Good Morning Ginger- Thanks for the extra direction on the Master/Child links. I was clicking on the main form viewing properties but not taking the next step and clicking on a sub form. I am going to work on the utility portion today and see what I can come up with using your suggestions. I need to get your email address or paypal acct. I owe you something more than just continous thank yous.

 

[GingerR]

you don't owe me nuttin'....just help anyone you can whenever you get the chance.....

anyhow, let me know how it goes. i'm fully invested in your little project now you know )

 

[Cordury]

Well if I ever obtain the knowledge, I will certainly attempt to help others.

I think I have made a lot of progress with your assistance of course. My only major concern now is importing data that will have multiple Store ID/#'s. Meaning, I am doing to import Excel Spreadsheets on a weekly basis. These spreadsheets will have store and date information- When they are going to open, when construction is to begin. However, some store dates change and some do not. It just all depends on the circumstances.

And it is obvious you are invested in my project. I appreciate all the time, effort and thought

 

[GingerR]

honestly i enjoy helping cause i learn a lot myself.

as for importing, i've found this process to be the best:

import in a new excel sheet into a temp table that holds just that info. all of it, just like it looks in the excel spreadsheet. in fact, you may be able to just link to the excel sheet instead of importing. there will of course be some data issues, like many people have found....excel has a field with some numbers and text, access tries to make it into a number cause the first row in excel is just a digit.....then access gets all honked up and you get #Error in many cells...anyhow, to get around that i do the "import excel just like it is into access"

then start writing append queries to parse out the various data into the tables it belongs in. at first you should test test test this of course until you get it working how you want it. then you can eventually automate it by putting it all into a macro (have you made one of those yet?) or a module, either of which you can run at the push of a button or at night when no one is around or whenever you want. if every excel sheet will always be called the same thing and reside in the same folder/server share then you got it made. maybe tho you have to manually re-name each and put it in the right place, then hit your button. whatever the case, you will eventually move from a manual process to it all being automated, and in a month you won't even remember all of this.

 

[Cordury]

Ginger,

I bet you thought you got rid of me Not so fast! Things over here have changed somewhat dramatically. Basically, I will be using less sources of data and one would think that this would be a good thing and make things less complicated but it has not.

My db now looks something like so:

frmStores (main form)
Address (sub form - tblAddresses)
Utilities (sub form - tblUtilites)

The majority of information that I would like to import into my db will now be on one spreadsheet called Ptrax. Ptrax will contain:
Store #
Division
Project type
Possession Date
Construction Start (Forecast)
Construction Start (Actual)

And a ton of other fields that I will not be using. I have a couple of questions:

1) Can I name a file "PTrax Current" and link that file to my data base and just replace the contents on a weekly basis? (bc the newest file will contain the most up to date dates and stores). Do you think this is a better route than the append query?

2) I want to add a Comment Memo box. How can I do this if I am going to import the majority of the data every week?