Creating a bound form and allowing data entry for unbound fields (not in table)

[mooredk]

I'm trying to build a form based on a table where the user can also enter data to update the table but with data fields not originally in the table. Below is a summary of what I have and what I need:

1.) A file that will be uploaded daily into a Table (ie name Denials extract table).
2.) A form bound to table Denials extract
3.) Additional fields that will be updated by users that is not included in this table b/c they are not available in the system we are getting the data extract from.
4.) The user will look up a key field that will allow them to review it in our system. Once they "work" the record, there will be multiple fields that they will update with this information that are not in the bound recordsource (table Denials extract).

Questions:

1.) Do I create another table with these fields even though they will be blank at first and make them a subform that will have a "primary key field" that will link the main form with the subform? Will they need to "update" a whole new table? Not really sure how that would work since they will be updating fields not in the bound table.

Anyway, I know this is confusing. It is hard for me to wrap my head around. It really seems easy enough but I find it hard to explain at the same time.

Help!

 

[Privateer]

There are a few ways to do what you are asking. But let me start with the easiest. Create the new table with all the appropriate fields and the same field as the primary in the source table. Then open the form using a query where some fields are linked to the source table and some are linked to the target table. It doesn't necessarily need to be a sub form as that will initially show up blank and data entry would be tough. Assuming you have a primary key in the target, as soon as an entry has been made into any target field, just copy the PK in there as well so they can be lined up again. One note about the query. With both tables in the query, draw the line from the source PK to the target PK field, then right click on the line and select option two. This makes it a left join, meaning show all source records and any from the target that match. Hope this helps

 

[mooredk]

Thanks. Some more questions though:

1.) I should create another table that would contain all of the fields from the imported table and the "user" entered fields? Or should the "new" table should only contain the "new" fields entered by user and a "primary key" that will link the 2 tables together?
2.) Once these tables are created, then I should create a query linking these 2 tables (all of the ones from the imported table and only ones equal in the "new" table). Once query is created, then create form based off of this query. When this form is updated with the "user" data, where would these data be stored?
3.) I guess that may be where I get confused (among other places). Should i Have 1 big table that has all of the data?
4.) Also several of the user inputted fields will be combo boxes. SHould I create this field in the "new" table but when I build the form the data source will be from a table created for each combo box?

Sorry so many questions. But I'm feeling like :banghead:

Thanks!

 

[mooredk]

THe file is to be uploaded daily. We do not want duplicates though. So, we are in the process of creating a primary key concatenating multiple fields. The only way I can think of not have duplicates append to the table is to compare the new file to the existing file (table) and pulling out ones that are not duplicates and then append them to the existing file. In my head it sounds easy enough but then trying to work out how that will flow and being able to update the table with the data entered by users that is not coming in from imported file. :banghead: Any thoughts or suggestions or even questions b/c I'm confusing is appreciated!

 

[Privateer]

Its tough to give you a good answer because I don't know what the end game is, which is Pat's point; but I will give you my best ideas based on what we have so far. I would create the new table with only the user entry fields. Link the two with a left join in a query attached to the form and synchronize the two with the primary key. any data changes can be stored in their respective tables. The solution for you might be to create another query, pretty much the same as the one for the form, and just change it to a make table query. That way you can grab the fields from each table and put it into one biggy, if that is what you want. But consider Pat's idea, with daily imports, appending the data (again from both tables) into an existing table would be best. And maybe add a date field to keep track of this. As far as combo boxes, those are on the form, so you could make them for data from either table.

One other suggestion, it sounds like your daily upload is really the same records with just the changes during the last day. The only good way to combat this is to try to get a primary key in the upload. Can you ask the person sending this to you to include a unique identifier. With this you could make a table on your end and do an update query each day. Actually, append any new records and update the existing ones. Good luck

 

[mooredk]

Thanks for all of the suggestions. The daily upload will be the exact data fields as the "initial" table we will update. They are supposed to be modifying the extract to include a field we will use as our primary key. THis field will be a concatenation of several fields from the extract. My concern is the number of tables and getting them all to work in the query based form and the tables getting updated. We will also be running reports from these tables. But it is unclear what reports the users will want just yet.

May be back on here Monday asking more questions!

 

[mooredk]

The daily import would be from the system. There would be no updates from the user at that point for the "new" records. I guess I need to compare the new import to the ongoing data extract table to see what records are new and then append them to this table? And then when the users go on their normal routine they will update the data entry table that will include the "common" field.

 

[mooredk]

I have created 2 tables. 1 for the upload and 1 for the data to be entered. I have updated this table with the "unique id" we have created out of the download. I created a query linking these 2 tables and making it a make table. I have built my form off of this make table. My concern is the daily need for the import and the queries that will need to be run to update the tables with the "new" unique records. I have set up duplicate queries so that we will not import duplicate unique ids in this table. I'm a little concerned on how this will all flow and the risk of losing/duplicating data. I thought I could build a macro to perform all of this w/ a click of a button (command) when the user logs in to start working. My 1st issue is importing the daily file. There is no option to set up a command in the macro to import. I have the specs set up and I know how to do that "manually" but I need it to be more automated and "behind the scenes" for the user. In 2007 you could do this. I'm not seeing this option in 2010 plus any searches I have done have left me empty handed. Any one have ideas on how to set up an import to be run by a command button?