Looking for auto population ability

[Charmagne]

Hello - I am developing an Access 2010 database from scratch and am in need of assistance - we are looking to create two auto population fields. First - fields Sal Min, Sal Mid and Sal Max to auto populate when the schedule is entered. We have two tables PED & Salary Range, one being completed via a form (New Eval) that is requesting a specific schedule to be entered into the field for column 14, then we would like access to auto popluate fields for columns 18, 19 and 20 based on data that is in the salary range table tied to the specific schedule. there is only one salary range for each schedule.

The second is similar concept - when user enters data into one field on a form, what access to pull corresponding information. Again, there is only one possible respone.

 

[pbaldy]

Is this appropriate to the task?

http://www.baldyweb.com/Autofill.htm

 

[Charmagne]

While I understand the concept - I have no idea how to execute.
In my master table I made the schedule (to be given via form by operator) a combo box from the secondary table. So how to I connect that retrieved information to the necessary column in the master table?

 

[MarkK]

Typically it is considered a design flaw to modify related data at the moment of user input. Data is typically stored in it most raw form and then processed at retrieval time. If you modify related stored data at the time of input you create an undocumented dependency on your particular input code or process. In this case a user or future developer may edit data directly, or create some other process that doesn't leverage your process, and then you very easily get a situation where your own data is in conflict with itself. This is, as a developer, a case you want to avoid at all costs.

Only store raw data. If you have some linear calculation you perform on data based on related data, then implement that using a query that does that calculation at retrieval time. Then, as your raw data change over time, your queried result data is guaranteed to be correct as of the moment of the query.

hth

 

[Charmagne]

I do see now that this would expose our data to unintentional changes. Can we protect fields on forms - to only show information but not be open for changes?

Would it be possible for a user to enter data into a field on one form that would auto populate a field on another form with information stored on a seperate table?

 

[MarkK]

To protect data in a form you can set Control.Locked = True, Control.Enabled = False, Form.AllowEdits = False. Also, you can set the value of controls programatically, so that editing them doesn't alter the table values, and so on. Open a read-only recordset, and the list goes on. . .

Would it be possible for a user to enter data into a field on one form that would auto populate a field on another form with information stored on a seperate table?

Yes, this is possible, and you've not provided enough details for me to comment on whether it would be advisable. At first glance I am suspicious, if you want to do this, that you have a table design problem, but like I say, not enough information to say for sure. What exactly do you need to do?

 

[Charmagne]

We are creating a new position via a form from a new build table, each new position must use an existing salary line (have a table of all salary lines). The salary line table contains four columns that need to be added to each new position based on the 'salary id #'. We know by looking at the position what the assigned 'salary id #' is, but there is no way for us to memorize the hundreds of actual lines. So right now we build/create the salary id # then search the salary line table and manually enter those four fields into the new position we just created. Since all the data is already in two tables I was looking for a way to enter the salary id # and have the related four fields auto popluate onto this new table so I wouldn't have to search and copy paste.

 

[MarkK]

If you know the ID of a record in a table you can easily open a recordset to that row and read that data.

But if you know the ID of the record that contains the related data, why do you need to copy that data? Save the SalaryID in the Position record and lookup the salary from the salary table on that basis as required. Or, if the salary data actually belongs in the position record, why have a salary table at all? The point being --> never store the same data twice.

One data point is always a dimension of some greater object. If you design your system well all data points will have one authoritative home. If you want to copy/move data around in your system, this is commonly evidence of a design problem, where data that should be "owned" by one table is actually stored by another. Will salary data change apart from any position? If so, do not copy that data to a position record, link it. On the other hand, if each position's salary is determined independently for every position, don't have a salary table.

If you find that you "build/create" keys you might want to read up on the topic of "meaningful vs meaningless keys." "Building" a key is almost always a waste of effort. If you need human readable handles for things, construct these at retrieval time, not at storage time.

hth

 

[Charmagne]

Let me try to be clearer:

We created an access database to streamline our department and maintain history on our work. We had a 12 step process using word, excel and outlook that we were able to change to a 3 step process using access.

Step/Form 1 is used by analyst to evaluate/rate a new position within our company.
Step/Form 2 is used by assistants to 'build' the position within our seperate payroll system.
Step/Form 3 is used by assistants to notify managers when a position is ready

We are using one table and three queries leading to three forms. Once a position is evaluated the assistants can determine the salary based on three factors - department/exemt status/assigned point value. The assistants then need to finalize Form 3 that is being sent via access e-mail, this is where we need the salary line details to appear. Based on the factors we wanted to find a way to have the last for auto-populate the salary range.

 

[Charmagne]

We would like to create an e-mail button to send just the current screen shot but are having issues with that.

We also like the e-mail auto apply responses feature but are unable to create a button for this.

 

[MarkK]

In your post 7 it seems you have two tables and you need to find data in one table and copy it to the other. In your post 9 you provide an overview of your system, but it only has one table, and you want to autopopulate data, but from where? In 10 you want to capture a screen shot and email it from a button click, and you are having trouble creating a button. Is that correct?

I don't have time to address all of these. Maybe pick one we can work on and let me know.

 

[Charmagne]

Post 9 was to clarify post 7 but I failed to state that we do have another table that only houses the salary lines and is updated via excel weekly from our seperate payroll system.

This issue has the highest priority, with e-mails being secondary.

 

[MarkK]

So you need to retrieve four columns of data from a row in the salary table where you know a unique identifier for that row? Is that correct?

If so, you want to open a recordset. To do so you'll need to write a SELECT query that has a where clause that specifies criteria for the row in question. Do you know how to do these things? Write a SELECT query. Open a recordset.

 

[Charmagne]

Yes, I have created a select query which takes the relationship data and enters the four fields needed. The query works fine. Then I went to the form and added those columns to form options, but it is acting like a filter and not allowing new entries.

 

[MarkK]

What does this mean . . .

Then I went to the form and added those columns to form options

. . . what are form options?

If you have a select query that retrieves the data you want, then you need to open a recordset to get at that data in code. Do you know how to do that? This has nothing to do with forms at the moment.

 

[Simon_MT]

Can you store the ID from the Salary Range file into PED and then Join the two tables in SQL and set the default value Sal Min Sal Mid and Sal Max from the corresponding Salary Range or AfterUpdate set these values and decide whether or not the ID from Salary Range is editable.

I assume that the Salary Ranges can change and therefore the ruling values are set at the time of creation of the PED.

Simon

 

[Charmagne]

Once the fields were added to the query I added them to the form.

No, I do not know how to open a recordset. Previously I built a relationship and added the field to the form and the relationship populated the added field once the first field was filled in.

 

[Charmagne]

No idea why my previous post is gone...

Unfortunately I do not understand what you are suggesting.
New form comes from Table 1, one field on the 'New Form' is for the user to enter a specific code that relates to information on Table 2. I would then like to add a field on the 'New form' that auto populates from Table 2 when the code is entered on the form.