Auto Populating a field in a table

[softspoken]

I have a database where I have 5 places to chose types of errors on an application. Those errors are associated with a Grouping. For example: In the table called Summary of Errors I have an error that reads " Agent Calculated New Rates" in the column beside it the Grouping would be "Premium Differences". Within the form, if I selected the "Agent Calculated New Rates" error I would want the grouping to auto populate in the table. Could someone please explain how to do that. I am sure its something simple.

[Jamy]

There are multiple ways of doing this. Make sure however that you do not save the grouping to an individual record. This would just be asking for problems. Just save that lookup value as it is and display (not save) the grouping.

1:
You can have the combobox on the form to lookup another column related to the record. So: click on the combobox and go to the properties.
Go to the tab Data and select the three dots to change the Row Source (if not already so, change the Row source Type to Table/Query). Add the desired field and close.
Then select the tab format and change the Column count to 2 and add: ;0cm to the Column Width.
Use
Add the textbox and change the Control Source to: =[Field name].Column(1)

2:
Use a Dlookup on the Afterupdate event:
Me.[field name to be populated] = Dlookup("Field", "Table", "Field ='" & me.[Fieldname] & "'")
Also add an unbound textbox (you can lock it if you want, so it can't be changed).

3:
There are more ways, however above should be good enough.

If you still want to populate a table with the grouping use option 2 and in stead of using an unbound textbox use a bound one.

[softspoken]

thanks for the info. that allows me to display the grouping but it didnt populate the table with the grouping thats showing in the unbound textbox. I want whats showing in the unbound text box to auto populate in my table.. its probably something simular to what you just suggested but I might have to apply the code to the table ... im new to this so bare with me ... sorry.

[Jamy]

Just change the unbound textbox to a bound one by changing the control source to the field in the table.
If you used option 1 change the =[field].column(1). Copy this to the afterupdate event and add the field name, so: [field you want to populate] = [field].column(1) and you're done.
If you used option 2 you only have to change the control source of the field with the grouping to the name of the field you want to populate.

[softspoken]

I tried that this morning and it didnt work ... this is the code I am using

within the properties of the text box :

control source - =[Application Error 1].column(1) ' Application error 1 is what the grouping is tied to '

After Update - [App Error 1 Grouping]=[Application Error 1].Column(1) ' App Error 1 Grouping is where I need the actual Grouping that is tied to the Application Error 1 to populate'

Hope you can tell me where Im going wrong. Thanks

[Jamy]

So maybe i haven't been clear enough.

The control sources of the grouping field on your form should be App Error 1 Grouping (probably on the drop-down list).
With the field where you choose the error 'Application Error 1'. go to the afterupdate in the event tab. Select [event procedure] and click the three dots, this will open the VBA. Then type in:
[App Error 1 Grouping]=[Application Error 1].Column(1)

Everything should work fine then.

Let me know how it works out.

[softspoken]

its probably me thats not clear ... your code works fine and its showing the grouping within the form but when i click the save record ... it does not save that grouping ( data ) in the table ... is there a way to do that...

[Jamy]

Check if the control source of the field which is showing you the grouping has the field name of the table you wish to populate. If it isn't, change this to the field name of the table you wish to populate.

Look at my last post to change the code in the afterupdate event to lookup the value of the field where you enter the error.

If your control source of the grouping field already has the grouping field name, let me know.

[softspoken]

this is what i have for the control source below ..

[Application Error 1].column(1)

if i change it to [Application Error 1 Grouping].column(1) are you saying that will fix it. I tried that and it didnt work.


After Update - [App Error 1 Grouping]=[Application Error 1].Column(1) ' App Error 1 Grouping is where I need the actual Grouping that is tied to the Application Error 1 to populate'

[Jamy]

The control source should be only:
Application Error 1 Grouping

In the afterupdate event should be:
[event procedure]
Then click the three dots and insert the below code in the sub:
[App Error 1 Grouping]=[Application Error 1].Column(1)

Do this exactly and it should work. Let me know.

[softspoken]

ok some how we are getting crossed because everything you are telling me is working except for actually adding the Grouping to my table. Its definitely something im probably not understanding ... so lets start over ...

I have a field called 'Application Error 1'. When I select something from that drop down box I need for the grouping ( App Error 1 Group ) to automatically be linked to the selection of Application Error ... and when the "Save File" button is pushed i need the table to have both the error that was selected and the grouping associated with it in my table.
From here ... which fields control source needs to be changed to what ... and which fields after update needs to be changed to what ? I am really sorry ..

[softspoken]

upping for feedback