Access 2007 Form design and coding advice for newbie

Question

I have a data entry form that is bound to a data review table called TempHomeSites. After the users enter all the data, the database manager will need to review entries before appending/updating to the master database. I want to accomplish the following:

If the user clicks a specific button (Renewal Request), a form pops up (modal dialog form?) asking for the SiteID number. When the "OK" is button is selected, a query will be run that looks to see if that SiteID exists in the MasterHomeSites table. If it does, I want the data entry form populated with the corresponding data from the MasterHomeSites table so that the user doesn't have to re-enter all the data again. They will only have to manually change the data that has been modified (if any exists).

Once all the data has been entered and saved to the TempHomeSites table, the database manager will execute an update/append query on another form (UpdateOrAppendForm) that will update the fields from the TempHomeSites into the MasterHomeSites table where there is a match on SiteID. For records where there is not match, I want to append these records to the MasterHomeSites table.

How would some of you Access VBA programming gurus approach this? I am pretty good at dechiphering code from other databases and modifying to fit my needs, but I searched and did not see anything addressing this type of scenario. I am super new to Access and at a loss as to where to start. If anyone knows a sample database I can download that deals with some of these requirements, please point me to them.

Thank you.

Answer 1

Rather than prompt for a SiteID you could create a ComboBox that listed all active SiteIDs. (Don't allow edit or addition to the list.) Create a query on TempHomeSites with a criterion on SiteID that used the ComboBox value to select the appropriate record. The OnChange event of the ComboBox can then trigger opening a form bound to the query created above. Edit data and save as required.

How the database manager gets different action options may be a challenge. Access's security model is not know for its strengths. One option is to split the application into a front end with linked tables and a back end with the data. If the Switchboard (Access's menu creation scheme) is in the front end you can make a separate front end for the manager. The back end goes onto some shared network space. The front end on to users' desktop systems.

The manager's option could use a similar form to that described above but with different actions assigned. The resulting TempHomeSites form could have an action assigned to add data to the MasterHomeSites table.

The best thing is to get started. Everything I know about Access I learned because I had to solve some problem like the one you describe. Expect frustration. Perseverance will get you where you want to go. Good luck.